1

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?

  • 1
    There are a number of reasons why expdp is a poor substitute for archivelog mode + RMAN hot backups. The biggest reason is: point in time recovery. – Kris Johnston Jan 19 '17 at 21:38
2

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.

  • Thanks @JoshBonello, do you know if I need to do anything special if I configure RMAN to do hot backups to prune old archive redo log files, or does Oracle entirely manage that so the number of files doesn't continue to grow past the last few (3?) weeks? – user46688 Jan 19 '17 at 20:09
  • 3
    expdp options for creating a consistent export dump: FLASHBACK_SCN, FLASHBACK_TIME, CONSISTENT=Y. Automatic purging of archivelogs in FRA can be enabled by setting the archivelog deletion policy in RMAN, for example: CONFIGURE ARCHIVELOG DELETION POLICY TO backed up 1 times to disk;. – Balazs Papp Jan 19 '17 at 20:22
  • 1
    @Josh Bonello It does not require locking. It uses undo data, works like flashback query. – Balazs Papp Jan 19 '17 at 20:29
  • 1
    @BalazsPapp Thanks for the clarification. I didn't realize dp had made that pretty massive improvement over exp. user46688 If the archived redo log was written at any time before the beginning of a full RMAN backup, you're safe to purge it. – Josh Bonello Jan 19 '17 at 20:59
  • 2
    @user46688 - as long as I don't write or change anything in SYS schema since the full cold backup - You shouldn't write or change anything is SYS, but Oracle internal processes will, all the time. I see no reason to complicate your life with this strategy, use RMAN instead. – mutap Jan 19 '17 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.