Instance
Names vs. SIDs
Both SQL Server and Oracle allows multiple
instances of the server software to run concurrently on the same machine. These
multiple execution contexts are completely independent of each other: as far as
one database engine is concerned, it does not know or care if another is
running on the same box.
In SQL Server, this mechanism is enabled
through the concept of instances. SQL Server can run either as a named
or a default instance. The default instance has the same name as the
Windows server hosting it. Obviously, there can be only one default instance
per host, but it is possible to run multiple named instances on that
same machine. A named instance is identified in the form of HOSTNAMEINSTANCE_NAME,
where each INSTANCE_NAME running on the host has to be unique. Each
instance will have its own set of binaries with some common components shared
between all.
For Oracle, it works the same way. When
installing Oracle, the DBA needs to specify a Global Database Name and a
System Identifier (SID). Instance and databases are completely
separate entities in Oracle. A global database name uniquely identifies a
database in the network where it is hosted and can have a fully qualified name
in the form of database_name.network_domain_name. A SID on the other
hand identifies the instance associated with the database. In most cases where
a single instance is associated with a single database, the SID and the database
name will be the same. Oracle Real Application Cluster (RAC) environments are
an exception: RAC allows multiple instances to access the same database hosted
in a shared storage; the instance names are different from the database name in
such cases. However, just like a SQL Server machine, an Oracle database server
cannot have two instances running with the same SID. Another area of
similarity is that once specified during setup, a SQL Server instance name or
an Oracle SID cannot be changed later.
A SQL Server DBA can run the following query
to know the name of the instance he is currently logged on to:
SELECT
@@SERVERNAME
An Oracle DBA will run queries like the
following to get the instance and database name:
SELECT INSTANCE_NAME, HOST_NAME, VERSION,
DATABASE_STATUS FROM V$INSTANCE;
SELECT NAME, DATABASE_ROLE, CREATED FROM
V$DATABASE;
System Databases and System Tablespaces
A SQL Server instances will have five system
databases (four for versions before 2005) present: master, model, msdb, tempdb
and resource. An Oracle database will need a minimum of three system
tablespaces for its operation: SYSTEM, SYSAUX and TEMP.
The master and the resource databases are the
central repositories of all the information SQL Server needs to manage itself.
Among many other things, it contains the system configuration settings, list of
databases and the location of their files, end-points, linked servers and user
accounts (or “logins”). System level objects are stored in a read-only database
known as the “resource” database where this information comes from.
For Oracle, SYSTEM tablespace is the
equivalent of the master database. SYSTEM tablespace contains the data
dictionary, which is Oracle’s metadata about itself. The data dictionary
can be compared to SQL’s resource database. And probably you have already
guessed: Oracle will not start if the SYSTEM tablespace is unavailable or
corrupted.
For a SQL Server instance, the model database
is the “template” that is used for every new database created in that instance.
You can make a change to the model database and the change will be reflected to
every new database created afterwards. For Oracle, there is no such template,
but when you create a tablespace, you can specify whether it will be a
permanent tablespace or of any other type like a TEMP or an UNDO tablespace.
Permanent tablespaces are the ones that hold user data.
SQL Server’s tempdb is used as a “scratch
pad” for the whole instance. Tempdb is created every time the instance is
restarted and destroyed every time the instance is shut down. Oracle’s TEMP
tablespace does pretty much the same task: it is used to hold large scale sort
operation results. However, SQL’s tempdb can also be used for row versioning.
When enabled for a database, row versioning ensures the database engine keeps track
of each version of a data row as it is modified. The pre-modification copy of
the row is copied to a version store in the tempdb database. Queries requesting
the data row will get the latest committed version. When a read operation uses
an isolation level based on row versioning, it does not block other
transactions trying to modify the same data. This is because the read query is
not placing a shared lock on the data rows. However, this behaviour needs to be
explicitly enabled for each database.
Oracle uses the same concept with a different
type of tablespace – known as the UNDO tablespace. An undo tablesapce
holds read-consistent copy of data that is currently being updated by a DML
statement. As a user starts making changes to data, a pre-update version of
that data is stored in the UNDO tablespace. If another user wants to query
those same rows of data, s/he will get the pre-update version from the UNDO
tablespace. Unlike SQL’s version store, this feature does not have to be explicitly
enabled – it is part of Oracle’s concurrent data access mechanism.
Finally, SQL Server’s msdb database is the
required for its Agent service’s operation. SQL Server Agent is responsible for
scheduled jobs, alerts, replication and log shipping among many other things.
Without the msdb database, the Agent service will not be running.
There is no clear equivalent of msdb in an
Oracle. The SYSAUX tablespace is a system tablespace, created during the
installation process. It holds information such as Oracle’s Automatic
Workload Repository (AWR), spatial and multimedia data, XML database etc.
]]>