A High Level Comparison Between Oracle and SQL Server
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:
An Oracle DBA will run queries like the following to get the instance and database name:
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.