Oracle Terminology for the SQL Server DBA

The translation team hard at work.

At some point you’re going to be confronted with an Oracle installation or even just an Oracle DBA. Communicating with a DBA who works on a different product can be difficult, it’s like speaking US English and having a conversation with a native English speaker from Scotland. The words are the same, but they have different meanings.

While this is by no means an exhaustive list, it will help SQL Server DBAs have a meaningful conversation with their Oracle colleagues.

Oracle refers to the database as the data files on a disk that store data.

Database instance
The set of memory structures and system processes that manage database files. Basically, the instance is executables and memory. Oracle has some different terms to separate out plan cache, buffer pool, and other concepts. But at a high level, executables and memory make a database instance.

So far things seem the same. Up until Oracle 12c, though, these two concepts were close to one in the same – one instance of Oracle housed one database (things like Oracle RAC not included). One thing to take note of – Oracle on Windows runs within one process, just like SQL Server. On Linux, however, there will be multiple Oracle processes each with a clearly defined purpose.

A tablespace is roughly analogous to a filegroup. You can create tables and indexes inside a tablespace. Like a filegroup, you can take tablespace backups separate from the rest of the database.

Unlike SQL Server, each tablespace can have many different options – some tablespaces can be logged while others are not. During tablespace creation, DBAs can manage a variety of features of each tablespace including a separate undo tablespace (see below), per user disk quotas, logging, or even on-disk block size (this can be helpful when dealing with LOB data).

In short, Oracle DBAs can customize database behavior at the tablespace level as well as at the database level. This can be useful for controlling archive data performance, blobs, or managing other aspects of storage.

This tablespace is vast and untouched.
This tablespace is vast and untouched.

Default tablespace
Every user is created with a default tablespace. The default tablespace defines where that user’s tables and indexes will be created unless a different location is specified. This is like setting up a default filegroup, but it can be set per user instead of per database, and it provides finer grained control. A default tablespace is not like a default schema in SQL Server – users can create objects with different schemas inside their default tablespace. This isn’t related to object ownership like schemas in SQL Server, it’s related to object placement on disk.

Temporary tablespace
You know how SQL Server has one tempdb? Within Oracle, database administrators can specify a different temporary work space on a user by user basis. Fast OLTP workloads can have access to SSD temporary tablespace while data warehouse queries and ETL jobs can have their own temporary tablespace that uses rotational disks. Heck, you could even allocate PCI-Express storage for executives’ temporary tablespace if they needed lightning fast joins or just wanted to foot the bill for PCI-Express storage.

Undo tablespace
Oracle uses MVCC by default (in SQL Server you’d call it READ COMMITTED SNAPSHOT ISOLATION). Row versions have to be stored somewhere, but there’s no tempdb. The undo tablespace is used to track changes that have been made and to put the database back into a consistent state if a transaction is rolled back. Although it is possible to create multiple undo tablespaces, only one undo tablespace will be used any single Oracle instance at a time.

If only one tablespace can be active per Oracle instance, why have multiple undo tablespaces? Oracle RAC can contain multiple Oracle instances reading the same database. Each of the Oracle RAC instances can have a separate undo tablespace. If this sounds confusing, don’t worry – Oracle RAC is complex and deserves a separate blog post.

Once upon a time, Oracle DBAs had to configure the undo tablespace by hand. This was called the rollback segment. Poorly configured rollback segments led to “snapshot too old” errors and grumpy DBAs. If you ever encounter Oracle using a rollback segment, kindly ask the DBA why they aren’t using automatic rollback management (undo tablespaces).

Redo log files
It’s a transaction log file! A key Oracle difference is that everything gets logged, even the undo information. Redo log files are used just like SQL Server transaction log files.

Like SQL Server’s transaction log, Oracle can have multiple redo log files. These log files are written to in a circular fashion – the log files are written to in order and, when all log files are full, Oracle will circle around to the beginning again. Unlike SQL Server’s transaction log, you need to have multiple redo log files in Oracle. You can get by with two log files, but three or more is the preferred way to configure Oracle.

Unlike SQL Server, having multiple redo log files is the preferred way to manage Oracle logging. There are even multiple groups of redo log files, by default: two, but this can and should be configured, based on RPO/RTO needs.

Archived redo log files
These are redo log files that have been backed up. There are a number of ways to have Oracle automatically manage creating backups of redo log files that vary from manual to completely automated. If the disks storing these files fills up, Oracle will not be able to write to the data files – active redo log files can’t be archived any more. To ensure safety, writes are stopped.

Temporary tables
Oracle temporary tables are similar to SQL Server’s with one major exception – they’re statically defined. Even though an Oracle temp table definition will stick around until dropped, the data only persists for the duration of a session (or transaction if the table is configured that way).

The data inside a temporary table exists only for the current session – you can’t view data in another session’s temp table. The upside is that temp table metadata is always available for other users to query.

Oracle backups are very different from SQL Server backups – they’re both more simple and more complex than SQL Server at the same time. Many Oracle shops use a tool call Oracle Recovery Manager (RMAN) to handle database and redo log backups, archival, and even the expiration and deletion of backup files.

Previous Post
Our Senior DBA Training Class: Attendee Feedback
Next Post
Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

12 Comments. Leave new

  • Steve Wales
    July 9, 2014 11:45 am

    A very nice post, Jeremiah, but I would like to point out something that is not specifically accurate.

    Archived log files are not specifically online redo logs that are backed up.

    Online redo logs are written to in a circular fashion, this is true. If you have a group of three logs, log 1 is written to until it fills, then a log switch occurs and log 2 starts being written to. When it fills, another log switch happens and log 3 is written to.

    When #3 fills, we switch back to #1.

    At some time between #1 filling and #1 being needed again, the Archiver process fires and writes out the contents of the full online redo log to the archive destination (assuming you are running in archive log mode).

    So while I guess you could call it a backed up online redo log, it is not a backup in the sense that a DBA needs to make the backup, it is something that Oracle does in the background.

    The archived logs then need to be backed up by the DBA much like other pieces of the the database need backups.

    In the event of a failure, you need your archive logs (or backups restored of them) in order to recover the database to a point in time.

    For recover up to point of failure it is possible … Even probable … that you will need both your archive logs and your most recent online redo logs to recover to the failure point.

    RMAN handles backups on archived logs as a part of it’s integrated backup solution too.

    • Thanks for the clarification on archived log files. I put together a post on RMAN so that I could afford to be brief here.

    • Actually, Jeremiah is correct. An archived log is a binary exact copy of an online log created by the arch process once the instance has switched out off that online log.

      • My intent was to clarify why Jeremiah had written … when he stated that “These are redo log files that have been backed up” … if you’ve never worked with Oracle before it is possible that this could be taken to mean that the DBA had to do something here.

        The follow on sentence about manual vs fully automated back ups of these logs could have further added to the confusion.

        Manually having to switch archive logs was configured by the initialization parameter log_archive_start in Oracle 9i, but this was deprecated in 10g. (Oracle 9i documentation link:

        Manual archiving would also stop the database if the archive logs filled until you issued ALTER DATABASE ARCHIVE LOG ALL.

        While, in the overall scheme of things, an archived log is what Jeremiah said it is (a backed up copy of an online redo log), I was trying to add some clarification as to the behavior of how the image is transferred from online log to archived log.

        For the definitive word, refer to the Oracle documentation. Chapter 11 of the Concepts Guide explains all about Online Redo Logs and how they become Archived Redo Logs.

        I apologize if my comment added more confusion than clarification.

        • Thanks to both of you for adding to the post to clarify things more. I really appreciate it.

          • I hate it when brain says one thing but fingers type another.

            In my last comment “Manual archiving would also stop the database if the archive logs filled” should say “Manual archiving would also stop the database if the ONLINE logs filled”.

            Sorry about that! 🙂

          • To clarify further for those hapless SQL DBAs who are trying to get familiar with Oracle, what he means by the online redo logs filling is that they are all waiting to be archived. It’s a really bad idea to set up an Oracle database in ARCHIVELOG mode (roughly analagous to SQL’s FULL recovery model) and NOT have the online redo logs automatically be archived. It sort of like having a SQL database in FULL recovery model but not backing up the transaction log. In SQL the DB will continue to run until you fill the disk containing the log file, but in Oracle things will stop much sooner – when all online redo logs need to be archived. For example, if you had 3 online redo logs of 500m each, you’d get a whopping 1.5g of log activity written to the online redo logs, and then you’re database would suddenly hang.

            Here’s another rough analogy regarding the logs. An Oracle online redo log is similar to a VLF in SQL. That VLF cannot be reused for a DB in full recovery model until it’s backed up. Similarly an Oracle online redo log file can’t be reused until it’s been archived if the database is in archivelog mode.

  • What about different definitions for schema, explain vs. execution plans, etc.

    This could end up as a pretty extensive list.

  • Thanks, how about one for sql server to DB2?

  • Hi, “Oracle can have multiple redo log files” is a wrong sentence. Oracle must have at least two redo log files!


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.