By: Juan Carlos Olamendy Turruellas




One of the most important regularly task done by any DBA is to back up the database. In short, a database backup involves making physical copies of your database files to a secure storage (maybe off-site) in order to re-create the database when there is some loss of data. In terms of Oracle database, we need to keep copies of the following files:

  • Initialization parameter file (also known as init.ora)
  • Control files
  • Database files
  • Online and archived redo logs

A backup is the key component in any disaster recovery strategy. It’s very important to note that “the more time we spend planning and testing backups, the less time we’ll spend recovering the database”.


This is the first article in a series where we’re being learning about the principles, concepts and real world scripts for doing backups to Oracle database.


In this first article, I’ll talk about the most important terms related to backups in Oracle databases. In the next article, I’ll talk about doing low-level manual backups in order to apply the principles and concepts of this article. In the last articles, I’ll talk about a tool that automates the backup process (no more low level tasks) named Recovery Manager (also know as RMAN).


First of all, let’s answer the existential question: What is backup and recovery?


Backup: It’s a mean for recovering a database from failures and disasters.

Common types of failures and disasters are:

  • Statement failure. It’s caused by an error in an Oracle program.
  • User or application error. It’s caused when a user accidentally deletes data that was not to be deleted; or the application program causes a similar error.
  • User process failure. It’s caused by an abnormal disconnection or process termination.
  • Instance failure. It’s caused by problems on the Oracle instance such as:
    • Lack of SGA memory to be allocated,
    • Failure of a background process.
  • Media failure. It’s caused by a physical problem on the storage subsystem (disks, etc)

There are mainly two types of backups:

  • Physical backup: It’s the creation of copies of critical physical database files. It can be made by using either the Recovery Manager utility program or OS utilities such as the UNIX cp/dd commands.
  • Logical backup: It’s the use of the Oracle Data Pump Export to extract specific data (not working with physical database files) and to store that data to an export binary file.

Recovery: It’s a mean for reconstructing an Oracle database by copying backup files back to their original locations of an Oracle database and also updating the database files by applying redo log records from the redo log files in order to bring a database back to the point in time where a failure occurred. As a summary, when a database is recovered, it’s first restored from a physical backup, and then redo log records are used to roll forward to the point of failure.


Now let’s analyze the principal concepts related to backup in Oracle databases.


Archivelog and noarchivelog modes


Archivelog mode: In this mode, Oracle database saves/archives the filled redo logs. So, if we’re running in archivelog mode, we can recover (we may execute the restore and update tasks in the recovery process) the database to any point in time using the archived logs.


Noarchivelog mode: In this mode, the filled redo logs are overwritten and not saved. Remember that redo logs works in a circular way. It implies that we can restore only the backup (we may run the first step in a recovery process and not the whole process), so we’ll lose all the changes made to the database after the backup was made. For example, if there is a media failure, a database in noarchivelog mode may be restored from a backup, but it’ll lose all changes made to the database since the backup was made.


Oracle instances in the production environment usually run in archivelog mode because:

  • We can recover completely from an instance failure as well as a media failure.
  • We don’t need to shut down the Oracle instance to get backed up. We can perform online/hot/open backups in this mode by keeping the database open
  • We can execute a tablespace point-in-time recovery (PITR).

In short, we may run an Oracle database in noarchivelog mode only when we don’t mind about the most recent data on recovering.


Whole and partial backups


Whole backup: It’s also termed for offline/cold/closed backups. In this mode, we back up all files related to an Oracle instance. We can make a whole database backup in either archivelog or noarchivelog mode. In order to execute a whole backup, we need to shutdown the instance to get all files consistent. A database can be restored from this type of backup without performing the update step, so the recovery only points to the last backup (not to the point-of-last-committed-transaction).


Partial backup: Basically, it means that we may back up either a part of a database, such as a particular tablespace and database files. It’s remarkable to say that we can’t back up a database partially if the database is running in noarchivelog mode, unless all the tablespaces in the partial backup are read-only.


Consistent and inconsistent backups


  • Consistent: It means there is no need to go through the update step in the recovery process. When a backup is used to recover a database or a part of a database (such as a tablespace or a database file), first we need to restore the backup, and then we update the database with the last changes since the backup was made. In the case of a consistent backup, we don’t have to perform any update steps. Technically, a consistent database means that the system change numbers (SCN) stored in all the database file headers are identical and they’re also the same in the control files. The identical SCN means that the database files contain data taken from the same point in time. Since the data is consistent, we don’t need to perform any update steps after you restore (or copy back) a set of backup files.
    In order to make a consistent backup, the Oracle instance must be shut down (with a normal SHUTDOWN or SHUTDOWN TRANSACTIONAL statement).
  • Inconsistent: It means that there is always a need to undergo the update step of the recovery process. Most production systems can’t be shut down for a consistent backup, so we must back up the database files while the database is open for transactions. Since the database files may be modified by transactions while backing them up, we end up with an inconsistent backup. It doesn't mean there is anything wrong with your backups. During a recovery process, it isn’t sufficient to merely restore these backups. In addition to restoring these backups, we must also update the database by applying all archived and online redo logs from the time of the backup to the time to which we want to recover the database. Oracle database reads the log files and apply all necessary changes to the restored backup files. Since we can make an inconsistent backup of a database while it’s open, most production databases use inconsistent backups as the foundation of their backup strategy.




In this first part, I've talked about the key principles and terms related to backup and restore processes in Oracle databases. In next articles, I’ll illustrate these principles and concepts with real world examples.