General Guidelines for Backup/Recovery Strategies for Oracle Production Support

Backup and recovery are critical processes underlying any mission-critical system. A well-implemented data backup and recovery strategy is a cornerstone for every deployment, making it possible to restore and recover all or part of a database without data loss.

Backup & recovery SLA needs to be in place with backup time / restoration times. It is recommended to maintain the Oracle archives on the disk for at least a week before purging.

Restoration Scripts should be designed for various test cases for restoration on same or other system, which can be used in case of recovery.

Also it is highly recommended to test the recovery on same or different hardware platform, also test the media with other tape devices to check backward compatibility. Various possible test cases should be carried out for rigorous testing. Also it is recommended to document with change control for all the files, which needs to be backed up.

Restoration SLA should be defined for all kinds of files including log and output files with retention period.

It is recommended to test all possible recovery scenarios (scenarios like loss of a control file, online redo log) to be better equipped to handle different circumstances. Also the recovery procedures need to be documented to reduce manpower dependency. Backup policy for file system backup also needs to be in place.

Oracle strongly recommends using RMAN as the backup and recovery tool. (If the database files, control files and redolog files are moved to ASM, RMAN is only tool to take backup and do recovery)

Backup Schedule and Retention Policy

UAT/DEV

• Daily hotbackup of database
• Weekly coldbackup of database and application
• Daily backup on tape/remote disk for hotbackup
• Weekly backup on tape/remote disk for coldbackup
• Retention for hotbackup – 1 day
• Retention for coldbackup – 1 week

PROD

• Daily hotbackup of database
• Weekly coldbackup of database and application
• Daily backup on tape/remote disk for hotbackup
• Weekly backup on tape/remote disk for coldbackup
• Retention for hotbackup – 1 month
• Retention for coldbackup – 6 months

Recovery and restoration policy

UAT/DEV

• In case of a cold reboot of the server, it is assumed that database and application services would be properly shut down, so no recovery would be required in this case, only the services would need to be started
• In case of a hot reboot of the server, it is assumed that database and application services would not be properly shut down, thus in certain scenarios, a recovery might be required
• In case of a server or storage crash, the latest backup would be restored from Tape/remote disk

PROD

• In case of a cold reboot of the server, it is assumed that database and application services would be properly shut down, so no recovery would be required in this case, only the services would need to be started
• In case of a hot reboot of the server, it is assumed that database and application services would not be properly shut down, thus in certain scenarios, a recovery might be required
• In case of a server or storage crash, the latest backup would be restored from Tape/remote disk