Instructor's Notes

(If applicable)

• Lesson 11 – RMAN Maintenance (Handling expired and obsolete backups.)
• Author: Dave Anderson, SkillBuilders
• www.skillbuilders.com
• Last Update: October 17, 2007

Instructor's Notes

(If applicable)

• In this lesson you will learn how to:
• Check for expired backups with the CROSSCHECK command
• Deleting expired backups
• Removing unwanted or obsolete backups
• Concepts and retention policy review
• REPORT OBSOLETE command
• Deleting obsolete backups with the DELETE OBSOLETE Command
• Overriding configured retention policy

Instructor's Notes

(If applicable)

• It is obviously possible that the RMAN catalog (control file or actual catalog) has a record of a backup set, but the backup set (or one or more of the pieces) is actually missing from the backup device. Several scenarios can cause this:
• A tape or disk file was accidentally deleted.
• The tape retention policy for a backup set expired (yet the RMAN retention policy has not; we will discuss RMAN retention policy and “obsolete” backups later in this lesson).
• The tape or disk containing the backup piece or set was lost or has become corrupt (i.e. unreadable).
• Missing backup pieces or sets are called “expired” backups. (Don’t confuse “expired” backups with “obsolete” backups, which has to do with retention policy. We will cover obsolete backups later in this lesson.)
• We will use the RMAN CROSSCHECK command to locate expired backups. The command output will identify the expired (missing) files and the command will update the catalog (views) to permanently record the fact the files are missing (if the files are found and the command is re-run, the catalog will be updated to reflect the available status).

Instructor's Notes

(If applicable)

• For this example I used Legato Administrator to manually remove the file from the tape catalog.
• The CROSSCHECK command verify the existence of files. Specifically, it will:
• search disk devices looking for files that make up a backup set
• send requests to the tape server software to check for the existence of a file in the tape catalog (A.K.A. tape index).
• search for archived logs or image copies (also refer to the Oracle9i Recovery Manager Reference for details on the CROSSCHECK ARCHIVELOG or CROSSCHECK COPY commands).
• The CROSSCHECK command output relays the status back to the terminal. It also records the status in the STATUS column of these views:
• RC_BACKUP_SET. “A” indicates all pieces are available. “O” indicates one or more pieces are expired, thus the backup set can not be used in a recovery operation. “D” means that all backup pieces have been deleted.


• Notes continue on the following page…

Instructor's Notes

(If applicable)

• CROSSCHECK notes continued from the previous page:
• RC_BACKUP_PIECE (catalog database) and V$BACKUP_PIECE (target database). “A” means available. “X” means expired. “D” means deleted. “U” means unavailable. A DBA can set a backup set (i.e. all pieces) to a status of unavailable with the CHANGE . . . UNAVAILABLE. This might be done, for example, if a backup piece has become temporarily unavailable due to loss of disk drive. Later in this lesson I will show you how to query the RC_ views.


• Supplemental Notes concerning CROSSCHECK:
• RMAN sends requests to the tape server software (Legato Single Server Version in my environment) to check the availability of files recorded in the RMAN catalog. Legato simply checks its catalog; it does not mount the tape and actually try to read the file. Nor does RMAN attempt to read disk files; it simply checks to see that they exist. (To actually validate/read file(s), use the VALIDATE command, e.g. RMAN> validate backupset 2177;)
• The Oracle9i Recovery Manager Reference states that, if you have tape backups and do not have a default tape channel configured, you must manually allocate a tape channel. I did not find this to be the case. RMAN seems smart enough to allocate a tape channel in response to my CROSSCHECK BACKUP command, even without a tape channel configured with the CONFIGURE command.
• The CROSSCHECK BACKUP command checks the existence of backup sets, pieces and control file autobackups. Use the CROSSCHECK COPY command to check for image copies, control file copies and archive logs.

Instructor's Notes

(If applicable)

• The CROSSCHECK command has many parameters and formats. Refer to the Oracle9i Recovery Manager Reference manual for detailed descriptions of all parameters.

Instructor's Notes

(If applicable)

• The RMAN LIST command, when used with the EXPIRED parameter, will scan the RMAN catalog (or control file if you are not connected to a catalog) looking for expired backups. Remember that the CROSSCHECK command must be run first to search for the backup pieces and update the catalog accordingly.

Instructor's Notes

(If applicable)

• An alternate method of listing backups (and their status) is to query the RMAN catalog.
• The catalog views RC_BACKUP_PIECE and RC_BACKUP_SET record the status of the backup piece. The status will start as “A” for available (it can be used in a restore operation). The CROSSCHECK command will change the status to “X” for expired if the physical file (backup piece) is not found. If the CROSSCHECK changes the status to expired, the query shown above will show the backup piece to be expired:
• 7nf7oa5t_1_1 EXPIRED 11/30/2003 14:22 FULL/LEV 0
• Refer the supplied script “BACKUP_PIECE_STATUS.SQL” for a working example of this code.

Instructor's Notes

(If applicable)

• If you find expired (i.e. missing) backup pieces, and you cannot restore the pieces (or do not want to), you will want to update the RMAN catalog to reflect this. This is accomplished with the DELETE EXPIRED command as shown above. You can also be more specific, i.e. list the exact backupset you wish to delete. For example, the previous example shows that backupset 2177 is expired. I can delete this backupset with this command:
• RMAN> delete backupset 2177;
• using channel ORA_SBT_TAPE_1
• using channel ORA_DISK_1
• . . .
• Note that if you delete a backup but the file is still available, you can re-catalog it in the RMAN catalog with the DBMS_BACKUP_RESTORE procedure. (If it is a tape file, this assumes you can ask the tape server to re-read the tape and re-catalog the file in the tape catalog first.) However, I have not yet found documentation on this procedure – you will have to ask metalink for help on this.

Instructor's Notes

(If applicable)

• This example demonstrates a more complete cross check procedure – crosscheck all backups of datafiles, archived redo logs and control files. Any missing (expired) files are removed from the RMAN and tape media manager catalogs with the DELETE commands.

Instructor's Notes

(If applicable)

• Oracle uses the term “obsolete” backupsets to describe backups that have exceeded the user-specified retention policy.
• Retention Policy Review
• We can configure RMAN so that backups are retained for 30 days:RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;The REPORT OBSOLETE and DELETE OBSOLETE commands will now only report and delete, respectively, backup sets that are older than 30 days.
• Alternatively, you can configure RMAN to retain multiple copies of a backup:RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
• Note that redundancy is mutually exclusive from RECOVERY Window. Recovery Window is usually a better method but redundancy “1” is the default to maintain compatibility with the 8i REPORT OBSOLETE command.

Instructor's Notes

(If applicable)

• When backup sets become obsolete it is common to delete those sets. This is done with the DELETE OBSOLETE command as shown above.
• NOTE: As of Oracle 10g, if backups are stored in the flash recovery area, Oracle will automatically delete obsolete backups when space is needed. Refer to Lesson 13, Backup and Recovery Enhancements, for more information.
• Note that disk files are physically deleted. Tape files are not (at least that is the way Legato works) though they are removed from the tape index (a.k.a. catalog).
• It is notable that when deleting tape backupsets, the DELETE command removes the entry from both the RMAN and tape server (e.g. Legato) catalogs. If the file exists on the tape, it is left intact. Some tape server software, Legato for example, will let you scan the tape and re-catalog files, which can then be re-cataloged with the DBMS_BACKUP_RESTORE procedure. Refer to Oracle’s metalink for help on DBMS_BACKUP_RESTORE (I have been unable to locate docs on this procedure).

Instructor's Notes

(If applicable)

• Since we are discussing obsolete backups and retention policies, let’s take a moment to talk about overriding the configured retention policy.
• There is two ways to affect the retention policy of a backup set:
• KEEP option of the BACKUP command – The KEEP option allows for overriding the configured retention policy for the backup set(s) created by this backup. There are several options:
• KEEP FOREVER [LOGS | NOLOGS] – The backup set(s) never become obsolete. LOGS causes all archive logs necessary to recover this backup to be kept too. NOLOGS is only useful if you want to be able to restore to the point in time at which the backup is taken. The database must not be open to use NOLOGS.
• KEEP UNTIL TIME ‘date’ [LOGS |NOLOGS] – Keep the backup sets until the specified time (e.g. ‘sysdate+1’).


• See notes on the CHANGE command on the next page.

Instructor's Notes

(If applicable)

• The CHANGE command supports the changing of a retention policy. The example above shows how to set a backupset back to the configured retention policy, e.g. 14 days. Several options are available. Here are a couple:
• Change the retention attribute of all backups to the configured policy:
• RMAN> change backup nokeep;
• Change all backups so that they never become obsolete:
• RMAN> change backup keep forever logs;

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• RMAN Maintenance Workshop
• This workshop requires that at least one successful full backup of your database has been taken. Use the REPORT NEED BACKUP command and the LIST BACKUP command to determine if successful backup(s) have been completed and what backup sets are in place.
• If one or more full backups have not been completed, take a full backup before proceeding with this workshop.
• Expired Backups Workshop
  1. Determine if there are any expired backup pieces for your database. (At this point, there should not be any expired pieces. If there are, skip steps 2 and 3.)
  2. Use an OS to command to delete one of the backup set pieces.
  3. Again determine if there are any expired backup pieces for your database.
  4. Delete the expired backup(s).


• Obsolete Backups Workshop
  1. Check if there are any obsolete backups for your database. If there are any, skip step 2.
  2. Take the actions necessary so that an obsolete backup exists.
  3. Delete the obsolete backup.