A High Level Comparison Between Oracle and SQL Server – Part II
In terms of data availability, both platforms offer a point-in-time recovery option. If desired, this feature can be disabled as well (this may be the case for test or development system). When a SQL Server database is configured for point-in-time recovery, it is said to be in full recovery mode. Every data modification in the database is logged in the transaction log irrespective of the recovery mode the database is using. These log records remain in the file until a transaction log backup is made. The database’s recovery model can also be turned to simple recovery mode where transaction log records are truncated every time a checkpoint occurs. A checkpoint flushes the modified data in the buffer cache and log cache into the data files and log files on the disk. If the database is marked for simple recovery, all records in the transaction log before the oldest open transaction are deleted when a checkpoint occurs. This is because SQL Server knows that all committed transactions before the oldest open transaction have been hardened to the data files.
Now if there is an error (physical or logical) detected in the database, its last full backup can be restored first and then all the transaction log backups taken after the full backup can be applied on top. This feature allows the database’s state to be brought forward or brought back to any point in time. However this will work only if the database is in full recovery mode.
Oracle has a similar concept. An Oracle database can either be in an ARCHIVELOG mode or NOARCHIVELOG mode. When running in NOARCHIVELOG mode, the contents of the first redo log group is overwritten as the last log group in the chain becomes full. This happens because Oracle redo log groups are used in a circular fashion. However, this also means the system cannot go back to a point in time in the deleted transaction records. Functionally, this is equivalent to SQL’s simple recovery mode.
If the Oracle database is running in ARCHIVELOG mode, one or more archiver processes will also be running in the background. An archiver process backs up the contents of a redo log group as it becomes full and saves it in a separate file in the disk subsystem. This saved copy of the redo log group is known as the archived log. Once a log group is archived, it can be overwritten. If you think about it, archiving an Oracle redo log group is functionally equivalent to a SQL Server transaction log backup and the ARCHIVELOG mode is equivalent to the full recovery mode.
For Oracle, just like SQL Server, the archived logs can be applied after a full backup has been restored. The difference is that for SQL Server, the transaction log backup task needs to be set up manually as a scheduled job, whereas for Oracle the archiver process takes care of the backup automatically once the database is configured for it.
Finally, equivalence between the two platforms can be also seen in terms of recovery time. As mentioned at the beginning of this section, when the database engine starts, it will go through the redo and undo phases. The total time spent in these two phases is known the recovery interval. Obviously, a database administrator will want the recovery interval to be as minimal as possible. In SQL Server, the DBA can configure this interval by running a command like the following:
This command modifies a system configuration parameter. When recovery interval is set, SQL Server will adjust the frequency of its checkpoint process against each database so that the time spent in recovering each database does not exceed this interval. The recovery interval is specified is minutes.
Oracle’s equivalent to recovery interval is the Mean Time To Recover (MTTR). This can be set by modifying the Oracle initialisation parameter FAST_START_MTTR_TARGET. This parameter can be set to fine-tune checkpoint frequency. Its value determines how many seconds Oracle will spend in database recovery after a server crash. The parameter can be set using a command like this:
In both Oracle and Microsoft SQL Server, a large number of tables and other objects like views, functions and stored procedures are automatically created when the database software is installed and the database(s) created. These system level tables contain metadata about the logical and physical attributes of the instance and the database(s) hosted by it. Oracle calls this set of tables the data dictionary; in SQL Server these are known as system tables.
In SQL Server 2005 and latter, majority of the system level objects are contained within the resource database and some within the master database. In Oracle, the data dictionary tables are located in the SYSTEM and SYSAUX tablespaces. From SQL Server 2005, direct access to a database’s system tables is impossible; instead, a set of views known as catalogue views have been created to provide a uniform interface to the system metadata. In Oracle, the data dictionary table names are cryptic to discourage direct access and modification by users. Here as well, a set of views have been created to help DBAs and developers query the data. Oracle calls these views data dictionary views.
The definitions of the catalogue views are stored in the SQL Server resource database. These system views belong to a special user schema called the sys schema and can be accessed from any database. For example, to list all the database present in a SQL Server instance, the DBA can run the following query from any database:
To list all the objects present in any database, the sys.objects catalogue view can be used:
In Oracle, the data dictionary is owned by the Oracle SYS user. However, unlike SQL Server, each data dictionary view can exist in three different forms and each form will expose a different set of information. The dictionary views are therefore divided into three different types. The prefix of a view name will indicate the type of information it exposes:
· Views prefixed with USER_ will allow users to query information about the objects they have created.
· Views prefixed with ALL_ enables users to query information about the objects they have created and objects they have been given access to.
· The DBA_ views are for database administrators. These views show a complete set of information about all objects in the database. Normal users do not have access to DBA_ views.
As an example, the two queries below will return different result sets:
The first will only show the tables created by the user running the query and the second will show all the tables present in the database.