SQL Server Vs Oracle Database terminologies

Applicable: DBA & Developers

In this session we are are going to see some similar terminologies that SQL Server and Oracle Database technologies named differently but which is almost same considering it's use and functions. It must be helpful for developers or DBAs who is changing from one of these technologies to another as a part of job change or project requirements. Let's see what under the box...

1. Logical database structure

In Oracle the largest logical unit of databases is named as Tablespaces. Tablespaces are made up of Segments. Segments are allocated to a specific object such as a table. Segments are made up of extents and finally extents are made up of Blocks.Blocks are the fundamental logical unit of database structure. In SQL Server tablespaces are considered as File Groups which is a set of physical files. SQL Server says nothing about the segments but it still has extents which is made up of 8 pages, and a page is similar to Block in Oracle.

2. Recovery model

Simply a recovery model is the property of database that controls how the transaction are logged. SQL Server give us 3 different recovery models which is known as Simple,Bulk-logged and Full. In Simple recovery model no transactions are logged as a result if a failure occurs everything since the last backup is lost. Bulk-logged logs almost every transactions but excluding major log generating operations like bulk copy program and index maintenance. Full recovery model logs each and every operation. In Oracle we have two modes which is known as ARCHIVELOG and NOARCHIVELOG. In ARCHIVELOG mode whenever a log file is filled it is archived into a separate location and a new file is used. In case of failure the archived files is used to reconstruct the data. In NOARCHIVELOG mode new transactions overwrites the old one and no archiving process is done as as result reconstruction of data in case of failure is not possible.

3. Instance-database relationship

An instance is the copy of database engine and it's services running on our primary memory as an operating system service. In SQL server the instance-database relationship is many-to-one. It means a single instance has multiple databases under it's management. Resources allocated to the instance are shared by all the database operations. In Oracle prior to 12c the relationship was one-to-one. It means a single instance controls and co-ordinates only one database. In 12c Oracle introduced a new technology known as Oracle Multitenant Architecture. With the use of multitenant architecture many-to-one relationship is also possible. Both SQL Server and Oracle Databases have one more relationship called one-to-many in which two instances(mostly different machines) points toward a single database which is part of high availability solutions. SQL Server's Fail over clustering setup and Oracle's Data Guard is an example for one-to-many relationship.

4. Template Database

Template Database is nothing but a system database which act as a template for newly creating databases. In SQL Server this template DB is called Model Database. Whenever a new user database is created it inherits all the properties of Model database.

With the Multitenant architecture Oracle also introduced a template DB known as Seed Database which is used whenever a new pluggable database is created.