Data warehouses hold data that originate from countless range of sources, which are transformed before inserting into a database. Managing the recovery of a large data warehouse can be a daunting task and traditional OLTP backup and recovery strategies may not meet the needs of a data warehouse.
A backup strategy should be planned during the system design phase, focusing more on what to backup and frequency of the same. The amount of resources available to perform a backup or a recovery; and the recovery time objective (the amount of time one can afford the system or part of the system to be unavailable) are two most important variables that need to be focused upon while planning a backup and recovery strategy.
NOLOGGING operations must be taken into account when planning a backup and recovery strategy. Traditional recovery, restoring a backup and applying changes from the archive log, do not apply for NOLOGGING operations. Above all, watch out for failure of subsequent operations, that rely on the data manipulated by this operation. Never make a backup when a NOLOGGING operation is taking place. Plan for one of the following or a combination of the following strategies:
The ETL strategy. Recover a backup that does not contain non-recoverable transactions and replay the ETL that has taken place between the backup and the failure.
The incremental backup strategy. Perform a backup immediately after an otherwise non-recoverable transaction has taken place. Oracle provides a tracking file feature that enables incremental backups based on changed data blocks. RMAN leverages the tracking file feature.
The incremental backup strategy. Perform a backup immediately after an otherwise non-recoverable transaction has taken place. Oracle provides a tracking file feature that enables incremental backups based on changed data blocks. RMAN leverages the tracking file feature.
Strategies for Backup and Recovery
Planning a backup and recovery strategy can be a challenging task; especially when there are huge gigabytes of data that need to be immunized from all the virtual and dynamic threats. The following practices can help you implement your warehouse’s backup and recovery strategy:
Best Practice A: Use ARCHIVELOG Mode
Best Practice B: Use RMAN
Best Practice C: Use Read-Only Tablespaces
Best Practice D: Plan for NOLOGGING Operations
Best Practice E: Not All Tablespaces are Equally Important
Best Practice B: Use RMAN
Best Practice C: Use Read-Only Tablespaces
Best Practice D: Plan for NOLOGGING Operations
Best Practice E: Not All Tablespaces are Equally Important
Blink consulting is a premiere management consulting and technology company that facilitates organizations to achieve their business goals while maximizing value from their investments. Headquartered in Boston we are an Oracle gold partner working with a zeal to provide innovative, integrated and productive solutions to dynamic enterprises. We provide Flexible solutions in Data recovery and Backup along with Oracle e-business suite implementation services.
(Source: Backing up and Recovering a Data Warehouse by Oracle)
