How many times you have seen different backup script with different commands ? Have you ever asked if all consider Oracle best practices and what is pros and cons of each ?
Here, I am trying to focus on some Oracle best recommendations in terms of backup/recovery and then at the end represent a sample RMAN backup script which takes into account all those recommendations
Recommendations :
- Check logical corruption to make sure backup is good.
- Put full database backup as incremental level 0 so it is considered in incremental backup/recovery scenario.
- Make sure that backup pieces have time-stamp to overcome look-up performance issue when recovery catalog is backup.
- Make sure to have each datafile in a single backup piece so for partial recovery RMAN goes through only one piece.
- Make sure to have unique name for each backup piece so RMAN does not overwrite backup pieces if for any reason backup is taken more often.
- Tag backups properly to make searching them and restoring them easier.
- Keep DBID in controlfile backup piece in order to save time to find it when it is required.
- Take current controlfile backup although autobackup is ON,that way it guarantees that always controlfile backup is there.
Based on the above, the following is the standard template that covers all features.
run
{
allocate channel t1 type disk;
BACKUP AS COMPRESSED BACKUPSET check logical INCREMENTAL LEVEL 0 DATABASE filesperset 1 plus archivelog format '/mnt/u05/backuptest/backup_%d_set%s_piece%p_copy%c_%T_%U' TAG = DB_BACK_FULL_DAILY ;
backup current controlfile format '/mnt/u05/backuptest/backup_controlfile_%d_DBID%I_%T_%U.ctl' TAG=CTL_BACK_FULL_DAILY;
backup spfile format '/mnt/u05/backuptest/backup_parameter_%d_DBID%I_%T_%U.ctl' TAG=PARAM_BACK_FULL_DAILY ;
delete obsolete;
release channel t1;
}
