Friday, May 17, 2013

Oracle Backup & Recovery Best Practices

Backup is essential to survive the database failure and loss. To successfully recover data in case of unforeseen events not only you should have a proper backup strategy but you must also follow best practices for backup and recovery. Here are Oracle backup and recovery best practices:

  1. First and most important, your backup and recovery procedure must always be properly documented.
  2. Check your database with different failure cases and maintain a recovery plan for each type of failure.
  3. Time to time update your backup and recovery plan.
  4. Detection of Oracle block corruption at an early stage minimize the risk of data loss. By setting Checksum you can detect block corruption early by using below command:
    SQL> ALTER SYSTEM SET DB_BLOCK_CHECKING = TRUE SCOPE = BOTH;
  5. Run your Oracle database in Archive log mode.
  6. To detect logical block corruption use below command:
    RMAN> BACKUP CHECK LOGICAL DATABASE PLUS ARCHIVELOG DELETE INPUT;
  7. If you are using RMAN backup in Oracle 10g then always turn on block checking option to backup only these blocks that have been changed since last backup.
  8. Test your backup weekly to determine if your backup is working properly or not. If you are using RMAN backup then use below command:
    RMAN> RESTORE VALIDATE DATABASE;
  9. Use RMAN backup compression to improve recovery time.
  10. Turn on automatic backup for your control file. Use below command:
    RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
  11. Test your recovery process also to know how it will work in case of disaster. Use below command:
    SQL> RECOVER DATABASE EMP;
  12. When you are backing up archive log then don't use 'delete all input' as this command delete the both copies of archive log after backup.

1 comment:

  1. Best option Oracle Database Recovery Tool can has been skillfully built to repair the damaged or inaccessible DBF files. Hence, it restores Tablespaces, Tables, Nested Tables, Nested Object Types.

    See full details at: http://www.mannatsoftware.com/stellar-phoenix-oracle-database-recovery.html

    ReplyDelete