Instances, Databases & Tablespaces
Perhaps the first architecture level
difference between SQL Server and Oracle is in the concept of instances
and databases.
An instance in SQL Server terms means a self
contained application service that involves operating system files, memory
structures, background processes and registry information. An instance is
represented by a service in Windows and can be either in a running or stopped
state. When running, an instance occupies a portion of the server’s memory and
also spawns a number of background processes.
Central to a SQL Server instance are its databases.
A SQL Server database is the repository of data and the program code for
manipulating that data. If an instance is not running, databases within it
cannot be accessed.
There are two types of SQL Server databases: system
databases and user databases. When a SQL Server instance is first
installed, five system databases are created: master, model, msdb,
tempdb and resource. If there is more than one SQL Server
instance running in a machine, each instance will have its own dedicated set of
system databases. An instance cannot start if any of its system databases
except msdb is inaccessible or corrupted. User databases on the other hand are
created by DBAs and developers after the instance has been installed and the
system databases have started. These are the databases that store an
organisation’s business information.
So in short, a SQL Server instance will always
include databases (even if it is only the system ones) and a database will
always be associated with one (and only one) instance.
At the physical level, a SQL Server database
is represented by a set of operating system files residing in the server’s disk
system. There are two types of database files: the data file and the transaction
log file. At the very minimum, a database will have one data file and one
transaction log file. A data file is the main repository of information in a
SQL database. A transaction log file on the other hand records the changes that
have been applied to the data. This file is required by SQL Server for system
recovery. A data or log file will always belong to a particular database: no
two databases can share the same data or log file. If the database is large, it
can have multiple data files. Multiple data files in a database can be
logically grouped into structures known as filegroups.
With Oracle, it works in somewhat reverse
direction. When Oracle starts, it works just like SQL in that a portion of the
server’s memory is allocated for its operation. This memory area, known as the System
Global Area (SGA), is divided into a number of distinct structures. Along
with the memory space, a number of background processes are also started that
interact with the SGA. Together, the memory space and the processes constitute
an Oracle instance. Note that the Oracle database is still not in the
picture. In fact an Oracle instance could be running perfectly okay without its
database even being online or accessible. When installing Oracle, there is an
option to install only the software and to create the database later.
A database in Oracle is a collection of
operating system files. Unlike SQL Server, an Oracle database does not
represent the logical grouping of objects; rather it is a single, collective
term for a number of files on the disk that primarily hold data.
The files that make up an Oracle database can
be classified into three types: data file, redo log file and the control
file. Data files are where all the data resides. There can be any number of
data files in an Oracle database. Redo log files are like SQL Server
transaction logs in that they record every change made to the data and is used
for system recovery. Control files are a special kind of file that contains
small but vital pieces of information about the database. Without this file,
the instance will not be able to open the database.
Apart from data, redo log and control files,
the database will also contain a parameter file, password file
and optionally, archive log files. We will discuss about each type of
Oracle database files shortly.
When an Oracle system starts, at first the
instance is created in the memory. The instance then connects to the database
residing on disk and finally opens the database for user interaction. When the
system is shut down, the instance is erased from memory: all the memory
structures and the processes are gone, but the database will still exist in the
disk, albeit in a closed state. As said previously, it is possible to have the
Oracle instance running without opening the database – this is a major
difference from SQL Server where an instance cannot start without its systems
databases being online first. However, just like SQL Server, it is impossible
to connect to an Oracle database if the instance has not started.
Generally the relationship between an Oracle
instance and its database is one-to-one. An instance will have one database
associated with it. A database on the other hand can have one or more instances
accessing it. A standalone Oracle installation will comprise of a single
instance accessing a single database. Oracle installations configured as RAC
(Real Application Cluster) will have multiple instances running in
different machines accessing the same database in a shared disk.
So where does the logical grouping of
database objects lie within Oracle? In SQL Server, this logical grouping is
done by the database itself. For Oracle, it is done through something called tablespaces.
An Oracle tablespace is a logical structure that groups together tables, views,
indexes and other database objects. For example, your production Oracle
database may have one tablespace dedicated for the HR application and another
tablespace for payroll. Each tablespace is physically represented by one or
more data files on disk and form a part of the database. The database is
logically made up of a number of tablespaces and the tablespaces in turn are
physically made up of one or more data files. So Oracle’s equivalent for a SQL
Server database is a tablespace.
And since they are so similar in their
functions, the process of creating a database in SQL Server is quite similar to
creating a tablespace in Oracle. Whether creating a database or a tablespace,
the DBA needs to specify a name first. The DBA then assigns one or more data
files to the database or tablespace and specifies the initial size and growth
increments for each file.
Just like a SQL Server user database can be
made offline or read-only, so can be an Oracle user tablespace. And just like
one or more data files in a SQL Server user database can be made read-only, one
or more data files in an Oracle user tablespace can be marked offline.
However, databases and tablespaces do differ
in the following areas:
·
In SQL Server, data files can be logically
grouped into filegroups. Oracle tablespaces do not have this concept.
·
In SQL Server databases, each database will
have its own transaction log and the log file properties will need to be
specified during database creation. For Oracle, transactions for the whole
database (that means for every tablespace) are recorded in one redo log.
Consequently, there is no provision to create individual log files for
tablespaces.
·
For SQL Server, the database can be created
with simple recovery mode. Simple recovery mode means the inactive
portion of the database log will be truncated after each checkpoint. Oracle has
a similar concept which we will discuss later – but it is not possible to
configure that property for individual tablespaces.