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:
sp_configure ‘show advanced option’,
1
reconfigure
sp_configure ‘recovery interval’, <time-in-minutes>
reconfigure
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:
ALTER
SYSTEM SET FAST_START_MTTR_TARGET=<number_of_seconds> SCOPE=spfile;
System
Metadata
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:
SELECT
* FROM sys.databases
To list all the objects present in any
database, the sys.objects catalogue view can be used:
SELECT
* FROM sys.objects
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:
SELECT
TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;
SELECT
TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES;
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.
]]>