RMAN Best Practices
Table of Contents
TURN ON BLOCK CHECKING
- REASON: The aim is to detect, very early, the presence of corrupt blocks in the database. This has a slight performance overhead, but will allow Oracle to detect early corruption caused by underlying disk, storage system, or I/O system problems. Any overhead can be mitigated by performing thorough Volume & Performance testing against the database before releasing into production.
SQL> alter system set db_block_checking = true scope=both;
TURN ON BLOCK TRACKING WHEN USING RMAN
- REASON: The block tracking file contains a bitmap that is used during incremental backups. Each bit represents an extent, so only those extents that have been modified will be read and backed up. If this is not used, all blocks must be read to determine if they have been modified since the last backup.
SQL> alter database enable block change tracking using file os_file_name ;
DUPLEX LOG GROUPS AND MEMBERS AND HAVE MULTIPLE ARCHIVE LOG DESTINATIONS
- REASON: If an archivelog is corrupted or lost, by having multiple copies in multiple locations, the other logs will still be available and could be used. If an online log is deleted or becomes corrupt, there would be another member that could be used to recover if required.
SQL> alter system set log_archive_dest_2='location=/new/location/archive2' scope=both; SQL> alter database add logfile member '/new/location/redo21.log' to group 1;
USE THE 'CHECK LOGICAL' PARAMETER WHEN BACKING UP THE DATABASE
- REASON: This will cause RMAN to check for logical corruption within a block as well as the normal head/tail checksumming. This is the best way to ensure that there will be a good backup.
RMAN> backup check logical database plus archivelog delete input;
TEST THE BACKUP
- REASON: This will do everything except actually restore the database. This is the best method to determine if the backup is good and usable before being in a situation where it is critical and issues exist.
RMAN> restore validate database;
HAVE EACH DATAFILE IN A SINGLE BACKUP PIECE
- REASON: When doing a partial restore, RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete. This is especially relevant with tape backups of large databases or where the restore is only on an individual or a few files.
RMAN> backup database filesperset 1 plus archivelog delete input;
CHOOSE THE RIGHT RETENTION POLICY
- REASON: Choose the retention policy carefully. Make sure that it compliments the tape subsystem retention policy and the requirements for backup recovery strategy. If not using a catalog, ensure that the control_file_record_keep_time instance parameter matches the retention policy.
SQL> alter system set control_file_record_keep_time=21 scope=both;
- This will keep 21 days of backup records.
RUN REGULAR CATALOGUE MAINTENANCE
- REASON: Delete obsolete will remove backups that are outside the retention policy. If obsolete backups are not deleted, the catalog will continue to grow until performance becomes an issue.
RMAN> delete obsolete;
- REASON: crosschecking will check that the catalogue/controlfile matches the physical backups. If a backup is missing, it will set the piece to EXPIRED so that when a restore is started, it will not be eligible, and an earlier backup will be used. To remove the expired backups from the catalogue/controlfile use the delete expired command.
RMAN> crosscheck backup; RMAN> delete expired backup;
PREPARE FOR CONTROLFILE LOSS
SQL> set autobackup on
- REASON: This will ensure that there is always an up to date controlfile available that has been taken at the end of the current backup, and not during.
RMAN> configure controlfile autobackup on;
KEEP BACKUP LOGS
- REASON: The backup log contains parameters for tape access locations of controlfile backups that can be utilised if complete loss occurs.
- REASON: During a recovery situation this will show how the recovery will go without actually doing it, and can avoid having to restore source datafiles again.
SQL> recover database test;
DO NOT SPECIFY 'DELETE ALL INPUT'
- REASON: delete all input will backup from one destination then delete both copies of the archivelog where as delete input will backup from location 1 and then delete what has been backed up. The next backup will back up those from location 2 as well as new logs from location 1, then delete all that are backed up. This means that there will be the archivelogs since the last backup available on disk in location 2 (as well as backed up once) and two copies backup up prior to the previous backup.