Ask the Expert

Best backup strategy for a data warehouse

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

    Requires Free Membership to View

BACKUP STRATEGY

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.

ARCHIVED REDO LOG FILES

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.

TYPE OF BACKUP TO ADOPT

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.

BACKUP METHOD

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.

FREQUENCY OF BACKUPS

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


This was first published in April 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: