Manage Learn to apply best practices and optimize your operations.

Best backup strategy for a data warehouse

What is the best backup stratgy for the data warehouse (Oracle)? Online, offline, ARCH. or NOARCH. mode?


Backup strategy should take care of the heterogeneous kinds of data-storage i.e., ODS & data warehouse & should consist of the following:

  1. Maintain multiple identical copies of online redo logs on different disks.
  2. Archive redo logs to multiple locations or make frequent backups of archived redo logs.
  3. Maintain multiple, concurrent copies of control file using Oracle multiplexing in conjunction with operating system mirroring
  4. Take frequent backups of data files and control file and store them in a safe place on more than one media.


Whenever possible, the database should be running in ARCHIVELOG mode for the ODS (Operational Data Store) & in NOARCHIVELOG mode for the Data warehouse.

Running the database in ARCHIVELOG mode has the following consequences:

  • You can completely recover the database from both instance and media failure.
  • You can perform a hot backup, that is, a backup made while the database is open and available for use.
  • You can transmit and apply archived redo logs to a standby database, which is an exact replica of your primary database.
  • You have more recovery options, for example, you can perform incomplete recovery and tablespace point-in-time recovery (TSPITR).
  • You have to perform additional administrative operations to store the archived redo logs.
  • You require extra disk space to store the archived logs.
Running the database in NOARCHIVELOG mode has the following consequences:
  • You can only back up the database while it is completely closed after a clean shutdown.
  • Typically, your only media recovery option is to restore the whole database. You lose all changes made after the last whole database backup.
  • Because no archived redo log is created, no additional administration is necessary.


In each type of physical backup, one can either back up a file or group of files.

  • Whole Database Backups
  • Tablespace Backups
  • Datafile Backups
  • Control File Backups
Online tablespace backups should be taken for the ODS & for the data warehouse. Whole database backup should be done offline. Tablespace backups are only valid if the database is operating in ARCHIVELOG mode. The reason is that redo is required to make the restored tablespace consistent with the other tablespaces in the database.


The standard backup method to adopt should be the Recovery Manager (RMAN), which is a utility that establishes a connection with a server session and manages the movement for data for backup and recovery operations.

If RMAN is not used, back up can be manually done by executing commands specific to the operating system or the Oracle export utility can be used to make logical backups, which can later be imported back into the database. But RMAN should be the primary choice.


The data warehouse databases should be backed-up offline on a weekly basis. The advantage of taking infrequent backups is that you free Oracle's resources for other operations. For the ODS, online tablespace backups have to be made on alternate days. In this case, one has to take hot backups, multiplex (that is, have multiple copies of) your online redo logs, and archive your redo logs to several different locations

Dig Deeper on Oracle data warehousing