Note: This blog was posted on behalf of Gary Parker, Senior Enterprise Data Architect and Consultant at Performance Tuning Corporation.

If you’ve worked as an Oracle DBA for any amount of time, you’ve probably come to realize that one of the most important aspects of your job is to insure that you implement a reliable and recoverable backup solution. Not knowing whether or not you can successfully restore the Oracle database in the event of a failure is the quickest way that I know of to find yourself in the unemployment office.

In a perfect world, you have the server resources available to you so you can periodically test your backup solution and verify that you can perform a successful restore. The reality is usually much different. In today’s economic reality, chances are good that the DBA staff is much too busy or too small to perform periodic testing and verification of the backup solution.

One step you can to mitigate this risk to insure that your RMAN scripts do more than just a simple backup of the database.

Now, every DBA out there who has been around for a while has their own philosophy regarding how to accomplish an RMAN backup. My preference is to practice a little “RMAN Over-Kill” to insure I’ve created a backup of everything that I need to successfully restore the Production database.

My RMAN backup philosophy is to perform the RMAN backup to a disk location on shared storage. My Production database is RAC, so a shared filesystem is crucial to insure I can run a backup or restore from any node in the cluster. I’m running an 11gR2 database on a 3-node RAC cluster with the backup directory configured on an OCFS2 shared filesystem. The RMAN backup is scheduled to complete before the data server backup of the OS filesystems is initiated; that allows for the RMAN backup to be written to disk and then the backup location is included in the daily OS-level backup to tape.

Having the current Oracle backup on disk allows for an easy restore process when things go south in the middle of the night (as those scenarios always seem to go). Additionally, I make sure the RMAN backup is including the multiple objects needed to restore the database.

This is a sample of the RMAN run block that I use to process the Production database backup. The RMAN backup is initiated using a BASH shell script.[vc_row fullwidth=”false” attached=”true”][vc_column width=”1/6″][mk_padding_divider size=”40″][/vc_column][vc_column width=”2/3″ css=”.vc_custom_1425491295602{margin-top: 0px !important;margin-right: 0px !important;margin-bottom: 0px !important;margin-left: 0px !important;border-top-width: 0px !important;border-right-width: 0px !important;border-bottom-width: 0px !important;border-left-width: 0px !important;padding-top: 0px !important;padding-right: 0px !important;padding-bottom: 0px !important;padding-left: 0px !important;background-color: #efefef !important;background-position: 0 0 !important;background-repeat: no-repeat !important;border-left-style: none !important;border-right-style: none !important;border-top-style: none !important;border-bottom-style: none !important;}”][vc_column_text disable_pattern=”true” align=”left”]

$ORACLE_HOME/bin/rman target / <<EOFRUN
{
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman-bk/controlfile/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRADG/snapcf_prod913.f';
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK MAXPIECESIZE 1G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK MAXPIECESIZE 1G;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK MAXPIECESIZE 1G;
CONFIGURE CHANNEL 4 DEVICE TYPE DISK MAXPIECESIZE 1G;
## Nov 2014 Added configuration of archivelog deletion policy for standby database
##         Archived logs will not be deleted until they've been applied to the standby and
#           also backed up 3 times to disk
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 3 TIMES TO DEVICE TYPE DISK;
SHOW ALL;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK COPY;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK COMPLETED BEFORE 'SYSDATE-1';
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
## Oct 2014 Added a TAG value to each backup
BACKUP DEVICE TYPE DISK FORMAT '$BKUP_DIR/%d_full_%U' DATABASE PLUS ARCHIVELOG TAG 'prod1_DB_Backup' ;
BACKUP ARCHIVELOG ALL NOT BACKED UP SINCE TIME='SYSDATE-1' FORMAT '$BKUP_DIR/%d_Archive_%U' TAG 'prod91_Archive_Backup' ;
BACKUP SPFILE FORMAT '$BKUP_DIR/%d_spfile_%U' TAG 'prod1_SPFILE_Backup' ;
BACKUP CURRENT CONTROLFILE FORMAT '$BKUP_DIR/%d_ctl_%U' TAG '$l_CTLFile_Backup' ;
SQL "create pfile=''$BKUP_DIR/$1_backup_PFILE.txt'' from spfile";
SQL "alter database backup controlfile to trace as ''$BKUP_DIR/$1_backup_CF.txt'' reuse";
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-5' BACKED UP 3 TIMES TO DISK;
## Jan 2012 Repeat this to pick up sets made obsolete by this successful backup.
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
}
## Oct 2014
## Added backup reporting and restore validation checks
report schema;
report unrecoverable;
list db_unique_name all;
list backup summary;
restore controlfile validate;
restore spfile validate;
restore database validate;
quit
EOF
# Copy the Database password file to the backup location
cp –p $ORACLE_HOME/dbs/orapw$1 $BKUP_DIR/.

[/vc_column_text][/vc_column][vc_column width=”1/6″][mk_padding_divider size=”40″][/vc_column][/vc_row][vc_row][vc_column width=”1/1″ css=”.vc_custom_1425491269018{margin-top: 0px !important;margin-right: 0px !important;margin-bottom: 0px !important;margin-left: 0px !important;}”][vc_column_text disable_pattern=”true” align=”left” margin_bottom=”0″]The RMAN RUN block first cleans up any expired or obsolete backup sets and then creates separate backup sets for the following objects:

  • a backup set for the Database
  • a backup set for all Archived Logs
  • a binary backup of the System Parameter file
  • a binary backup of the Control file
  • a text backup of the Parameter file
  • a text backup of the Control file

At the end of the RMAN RUN block, some RMAN reporting is done and then each of the new backup sets are validated. This insures that the new backup sets can be used for restore, if needed

Finally, the shell script creates a copy of the Oracle password file in the backup directory location. Once the backup is completed, the contents of the backup directory are copied off to tape as part of the nightly OS server backup.

Even though the hardware may not be available for testing of full restore of the database, you have the ability to include options in the RMAN backup to provide a reasonable assurance that your database backup is viable if ever a restore scenario is warranted.

I hope this helps.[/vc_column_text][/vc_column][/vc_row]