Oracle 11.2: Backup and recovery strategy based on full cold backup plus expdp

backuporacleoracle-11goracle-11g-r2rman

Are there any issues with the following strategy for backup/recovery in Oracle 11.2.0.4?

  1. Perform a full database cold backup once a month, moving the file off the server for safekeeping.
  2. Write a cron job to perform expdp of all schemes except SYS daily, retaining only the last two weeks of files, and moving them off the server for safekeeping.
  3. In the event of a failure, perform full database recovery using the cold backup, then do an impdp from the latest file (to restore all schemas except SYS). I can accept losing all database data since the last expdp file was taken (which is 24 hours, worst case).

The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?

Best Answer

I would highly recommend taking weekly RMAN hot full backups and daily RMAN incremental backups as well as retaining archivelogs for at least 1 week.

The issue with using expdp/impdp is that it can and will create inconsistent snapshots of your schema during the hot backup process (unless you do a cold backup daily...which is not very practical). If you happen to lose your entire database and begin to recover using impdp, you're at the mercy of the exports to be logically consistent which is only possible if no inserts, updates, or deletes happened at the time of the expdp export. You'll get data, but you can't guarantee that all the tables will be consistent with one another.

Your weekly hot backup will provide you a new "ground truth" database backup that will have guaranteed consistency after applying enough recovery to push through the database changes after the end of the hot backup. You can either use archivedlogs or incremental backups to apply recovery.

A daily incremental backup will speedup mean-time-to-recovery in the event that you do need to make a restore happen. Incremental backups recover faster than archivedlogs. On their own, incremental backups are meaningless without some sort of full backup that preceded them.

To this statement:

The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?

You're technically correct with the information you're providing. However, above I point out a critical flaw about the schema that makes this information irrelevant. That flaw is that you cannot guarantee data integrity with an expdp export without making it a cold export, and being cold is not advantageous for anyone.

Related Question