Introduction
It is quite common nowadays to see DBAs supporting databases from different vendors. Although much is common between database products, there are also many differences that can be a challenge for a DBA or a database developer who is new to a particular database product.
The purpose of this article series is to present Oracle 10g features from a SQL Server 2005 stand point.
In this article, I introduce you to the concept of Instance and Database which is a key term in understanding RDBMS architecture. Like anything you build, be it a nest of Taj Mahal, the foundation is the key.
Instance and Database
In Oracle, processes (background processes) and memory allocated make up an instance.
Database refers to the physical files(.dbf and .log etc). Having a database is not necessary to run an instance. If the instance is not part of parallel server configuration (Real Application Clusters) the relationship between an instance and a database is always 1:1. The main properties of an Oracle instance are specified using the initialization parameter file (pfile or spfile). When the instance is started, the parameter file is read and the instance is configured accordingly.
Having a database is not necessary to run an Oracle instance.
In SQL Server, an instance refers to processes, memory allocated and physical files associated with default system databases used by that particular installation. By default SQL server installation comes with a set of system databases (with its own hierarchy of objects and settings).
In SQL Server, the settings for the instance are stored in the registry, master/resource database and msdb database.
To an Oracle DBA, creating a database means creating an entire database system that contains control files, redo logs and, data dictionary and tablespaces. In the coming section, we will discuss what these objects are and how they are mapped to the SQL Server equivalents.
In SQL Server, these tasks are accomplished as part of the installation process. Hence creating a database in SQL Server implies adding a user database to the already existing system databases.
One SQL Server instance can hold more than one user defined database and the instance and database ratio is 1..32767.
Oracle storage architecture encompasses,
- Data File: Similar to SQL Server, data file is an operating system file to store objects and data. One or more data files are logically grouped together to make a tablespace. One data file is associated with only one tablespace. Unlike SQL Server, there are no Primary/Secondary data file types in Oracle.
- Redo Log: The Redo Log is similar to Log File in SQL Server. Oracle writes all final changes made to data first to the redo log files before applying the changes to data files. The set of redo log files that are currently being used to record the changes to the database are called online redo log files. These logs can be archived or copied to a different location before being reused and the saved logs are called archived redo logs. Archive Log Mode setting similar to Recovery Model setting in SQL Server decides whether the Redo Logs need to be archived or overwritten for reuse.
- Tablespace: A Tablespace is a set of one or more data files. The SQL Server equivalent of the tablespace is the filegroup. While filegroups are similar in function, their organization differs from that of tablespaces. Oracle tablespaces are at the instance level. SQL Server filegroups come under and are associated with the individual databases. There are three types of tablespaces.
a) Permanent tablespaces
b) Undo tablespaces
c) Temporary tablespaces
In SQL Server, file groups are of two types, primary and secondary. System wide tablespaces in Oracle include,
- System Tablespace (Permanent): The System tablespace stores the data dictionary for the instance/database, which is some what equivalent to the master/resource database in SQL Server.
- Sysaux Tablespace (Permanent): The Sysaux tablespace stores all auxiliary database meta data related to options and features such as RMAN, Job Scheduling, AWR repository, etc. Sysaux tablespace can be roughly equated to the msdb database in SQL Server.
- Temp Tablespace (Temporary): The Temp tablespace contain data that persists only for the duration of a user's session. Oracle uses Temp tablespace as a sort work area and for join operations involving internal work tables. Similar to Tempdb database in SQL Server.
- Undo Tablespace (Undo): An Oracle database has a method of managing information that is used to roll back, or undo, changes to the database. The information consists of records of the actions of transactions, primarily before they are committed. Undo records provide read consistency (avoiding reader-writer blocking scenarios) by maintaining the before image of the data for users who are accessing the data at the same time another user is changing it.
There are major differences in the way Undo is managed in SQL Server. UNDO management is implemented in SQL Server as part of the transaction logs of each database which handle both redo and undo related functions.
- Control File - The Control file is a file that Oracle maintains to manage the state of the database. Every database has at least one control file or multiple identical copies. The control file contains the names and locations of the data files, redo log files, backup set details and SCN. In SQL Server, the master/resource database has some of the information that is stored in the control file of an Oracle database.
Oracle Schema
The objects that a user owns are collectively called schema. A schema is bound to exactly one user. A SQL Server database has the features of an Oracle scheme because objects are created inside the database.
In Oracle, the schema and tablespace have independent identities – objects of a schema can be created in different tablespaces, and a single tablespace can contain objects from multiple schemas. In this context, SQL Server databases are similar to Oracle tablespaces – an owner can create objects in different databases, and a database can contain objects from different schemas.
Summary
Oracle’s functional equivalents in SQL Server
| Oracle | SQL Server |
| System tablespace | Master/Resource database |
| Sysaux tablespace | MSDB database |
| Database template files(Data warehouse, General purpose, Transaction processing) | Model database |
| Undo tablespace | Tempdb Database/Transaction Log |
| Temp tablespace | Tempdb |
| Redo Log | Transaction Log |
| Schema/User | Schema, Not a User |
