Oracle 12c Backup and Recovery Best Practices

Oracle 12c Backup and Recovery Best Practices

Disaster recovery is becoming increasingly important in our data-centered business world. If a company or client has crucial data, they're going to want to make sure it's able to be saved in any event. Oracle has released an e-book detailing best Oracle 12c practices - including backup and recovery best practices. Below are the highlights.

1. Determine a Backup Frequency and Retention Policy
It's important to create a backup frequency and retention policy to ensure regular backups and important data isn't lost. When creating this policy, keep these criteria in mind:

• • Criticality of the data: The Recovery Point Objective (RPO) determines how much data your business can acceptably lose if a failure occurs. The more critical the data, the lower the RPO and the more frequently data should be backed up. If you are going to back up certain tablespaces more often than others, with the goal of getting better RPO for those tablespaces, then you also must plan for doing TSPITR as part of your recovery strategy. This requires considerably more planning and practice than DBPITR, because you must ensure that the tablespaces you plan to TSPITR are self-contained.
  • Estimated repair time: The Recovery Time Objective (RTO) determines the acceptable amount of time needed for recovery. Repair time is dictated by restore time plus recovery time. The lower the RTO, the higher the frequency of backups, that is, backups are more current, thereby reducing recovery time.
  • Volume of changed data: The rate of database change effects how often data is backed up:
    • For read-only data, perform backups frequently enough to adhere to retention policies.
    • For frequently changing data, perform backups more often to reduce the RTO

2. Use an RMAN Recovery Catalog

The benefits of using a recovery catalog include:

• • Storing backup information for a longer retention period than what can be feasibly stored in the control file. If the control file is too small to hold additional backup metadata, then existing backup information is overwritten, making it difficult to restore and recover using those backups.
    
    
  • Storing metadata for multiple databases.
    
    
  • Offloading backups to a physical standby database and using those backups to restore and recover the primary database. Similarly, you can back up a tablespace on a primary database and restore and recover it on a physical standby database. Note that backups of logical standby databases are not usable at the primary database.

3. Create Backups in NOCATALOG Mode and Then RESYNC CATALOG When Not Using Recovery Appliance

When creating backups to disk or tape, you can use the target database control file as the RMAN repository so that the success of the backup does not depend on the availability of the database connection to the recovery catalog. To use the target database control file as the RMAN repository, run RMAN with the NOCATALOG option. Immediately after the backup is complete, the new backup information stored in the target database control file should be synchronized to the recovery catalog using the RESYNC CATALOG command.


4. Enable Block Change Tracking for Incremental Backups

Oracle database includes the BLOCK CHANGE TRACKING feature for incremental backups which improves incremental backup performance by keeping track of which database blocks have changed since the previous backup. If BLOCK CHANGE TRACKING is enabled then RMAN uses the block change tracking file to identify which blocks to include in an incremental backup. This avoids the need to scan every block in the data file, reducing the number of disk reads during backup.

Starting with Oracle Database 11g, you can enable BLOCK CHANGE TRACKING on both the primary and physical standby databases. You should enable change tracking for any database where incremental backups are being performed. For example, if backups have been completely offloaded to a physical standby database, then Block Change Tracking should be enabled for that database (this requires Active Data Guard). If backups are being performed on both the primary and physical standby databases, then enable Block Change Tracking for both databases.


5. Enable Autobackup for the Control File and Server Parameter File

You should configure RMAN to automatically back up the control file and the server parameter file (SPFILE) whenever the database structure metadata in the control file changes or when a backup record is added.

The control file autobackup option enables RMAN to recover the database even if the current control file, catalog, and SPFILE are lost. Enable the RMAN autobackup feature with the CONFIGURE CONTROLFILE AUTOBACKUP ON statement.

You should enable autobackup for both the primary and standby databases. For example, after connecting to the primary database, as the target database, and the recovery catalog, issue the following command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;


6. Offload Backups to a Physical Standby Database

In an Oracle Data Guard configuration you can offload the process of backing up control files, data files, and archived redo log files to a physical standby database system, thereby minimizing the effect of performing backups on the primary system. You can use these backups to recover the primary or standby database.


7. Set UNDO Retention for Flashback Query and Flashback Table Needs

.To ensure a database is enabled to use Flashback Query, Flashback Versions Query, and Flashback Transaction Query, implement the following:

Set the UNDO_MANAGEMENT initialization parameter to AUTO. This ensures the database is using an undo tablespace.

Set the UNDO_RETENTION initialization parameter to a value that allows UNDO to be kept for a length of time that allows success of your longest query back in time or to recover from human errors.

Set the RETENTION GUARANTEE clause for the undo tablespace to guarantee that unexpired undo will not be overwritten.

The Flashback Table also relies on the undo data to recover the tables. Enabling Automatic Undo Management is recommended and the UNDO_RETENTION parameter must be set to a period for which the Flashback Table is needed. If a given table does not contain the required data after a Flashback Table, it can be flashed back further, flashed forward, or back to its original state, if there is sufficient UNDO data.

Click here to read Oracle's full "Database High Availability Best Practices" e-book.