Best backup strategy for a data warehouse

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.

• 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

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