Oracle RMAN 11g Backup and Recovery (144 page)

430
Part III: Using RMAN Effectively

Listing Expired Backups

Using the
list backup
command shows you both available and expired backup sets. If you want to see only expired backups, then you can use the
expired
keyword, as shown in this example: RMAN> list expired backup;

List of Backup Sets

BS Key Size Device Type Elapsed Time Completion Time

1025 489.00K DISK 00:00:01 08 SEP 09

BP Key: 1028 Status: EXPIRED Compressed: NO Tag: TAG20090908T203418

Piece Name: /oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08/

o1 mf annnn TAG20090908T203418 5bg51c2c .bkp

List of Archived Logs in backup set 1025

Thrd Seq Low SCN Low Time Next SCN Next Time

1 18 679230 08 SEP 09 679716 08 SEP 09

This command will display all expired backup sets. With the
list expired backup
command, you can also get a list of expired tablespace and datafile backups and lists of expired archive log backups and control file/SPFILE autobackups by inserting the correct keyword, such as
list
expired backup of datafile 3
or
list expired backup of archivelog all
.

Listing Backups by Tablespace Name and Datafile Number

The output of the
list backup of tablespace
or
list backup of datafile
command is very similar to the
list backup
output. These two
list backup
commands allow you to list output specific for a tablespace or a datafile, as shown in this example:

RMAN> list backup of tablespace users;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

713 Incr 0 243.27M DISK 00:01:10 08 SEP 09

BP Key: 715 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202601

Piece Name:

oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf nnnd0 TAG20090908T202601 5bg4ktk1 .bkp

List of Datafiles in backup set 713

File LV Type Ckp SCN Ckp Time Name

4 0 Incr 678900 08 SEP 09 /ora01/oracle/rob1/rob1/users01.dbf

Chapter 17: Monitoring and Reporting on RMAN
431

BS Key Type LV Size Device Type Elapsed Time Completion Time

857 Incr 1 1000.00K DISK 00:00:27 08 SEP 09

BP Key: 861 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202840

Piece Name: /oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf nnnd1 TAG20090908T202840 5bg4psxw .bkp

List of Datafiles in backup set 857

File LV Type Ckp SCN Ckp Time Name

4 1 Incr 679212 08 SEP 09 /ora01/oracle/rob1/rob1/users01.dbf

Note in this example that this backup has expired, which might be of particular interest to us, especially if this were the only backup of the USERS tablespace available! Again, you can use the
expired
keyword to only list expired backups (
list expired backup of tablespace
).

In much the same way, you can list the backups of a specific datafile with the
list backup of
datafile
command:

RMAN> list backup of datafile 3;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

713 Incr 0 243.27M DISK 00:01:10 08 SEP 09

BP Key: 715 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202601

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf nnnd0 TAG20090908T202601 5bg4ktk1 .bkp

List of Datafiles in backup set 713

File LV Type Ckp SCN Ckp Time Name

3 0 Incr 678900 08 SEP 09 /ora01/oracle/rob1/rob1/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

857 Incr 1 1000.00K DISK 00:00:27 08 SEP 09

BP Key: 861 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202840

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf nnnd1 TAG20090908T202840 5bg4psxw .bk

List of Datafiles in backup set 857

File LV Type Ckp SCN Ckp Time Name

3 1 Incr 679212 08 SEP 09 /ora01/oracle/rob1/rob1/undotbs01.dbf

One place where the
list
command can be helpful is if you are trying to do a point-in-time restore and you are getting errors that indicate no backup or copy is found. In this case, try a
list

432
Part III: Using RMAN Effectively

command using the same
until
clause to see if it lists any available backups. Doing this can help reveal any problems with your
until
clause, and you can adjust the
until
clause to determine what point-in-time recovery is truly available from.

Listing Archive Log Backups

Several options exist for listing archive log backups in RMAN. To obtain a complete summary of archive logs currently on disk (this does not mean that they have been backed up), the
list
archivelog all
command is perfect, as shown here:

RMAN> list archivelog all;

List of Archived Log Copies for database with db unique name ROB1

Key Thrd Seq S Low Time

------- ---- ------- - ---------

54 1 9 X 08-SEP-09

Name:

/oracle/app/oracle/flash recovery area/ROB1/archivelog/2009 09 08

/o1 mf 1 9 5bd8qv45 .arc

1170 1 19 A 08-SEP-09

Name:

/oracle/app/oracle/flash recovery area/ROB1/archivelog/2009 09 08

/o1 mf 1 19 5bg61l4l .arc

Here, we find a list of each archived redo log that Oracle has created that is waiting to be backed up, along with the thread number and the sequence number of that archived redo log.

To get a report of those archive logs that we have backed up, we use the
list backup of
archivelog all
report as seen here:

RMAN> list backup of archivelog all;

List of Backup Sets

BS Key Size Device Type Elapsed Time Completion Time

712 1.53M DISK 00:00:00 08 SEP 09

BP Key: 714 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202600

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T202600 5bg4kr90 .bkp

List of Archived Logs in backup set 712

Thrd Seq Low SCN Low Time Next SCN Next Time

1 14 676364 08 SEP 09 678853 08 SEP 09

BS Key Size Device Type Elapsed Time Completion Time

744 61.50K DISK 00:00:00 08 SEP 09

Chapter 17: Monitoring and Reporting on RMAN
433

BP Key: 749 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202721

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T202721 5bg4n9qy .bkp

List of Archived Logs in backup set 744

Thrd Seq Low SCN Low Time Next SCN Next Time

1 15 678853 08 SEP 09 678935 08 SEP 09

BS Key Size Device Type Elapsed Time Completion Time

856 370.50K DISK 00:00:00 08 SEP 09

BP Key: 860 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202838

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T202838 5bg4pq6x .bkp

List of Archived Logs in backup set 856

Thrd Seq Low SCN Low Time Next SCN Next Time

1 16 678935 08 SEP 09 679163 08 SEP 09

BS Key Size Device Type Elapsed Time Completion Time

894 63.00K DISK 00:00:01 08 SEP 09

BP Key: 899 Status: AVAILABLE Compressed: YES Tag: TAG20090908T202920

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T202920 5bg4r0y0 .bkp

List of Archived Logs in backup set 894

Thrd Seq Low SCN Low Time Next SCN Next Time

1 17 679163 08 SEP 09 679230 08 SEP 09

BS Key Size Device Type Elapsed Time Completion Time

1025 489.00K DISK 00:00:01 08 SEP 09

BP Key: 1028 Status: EXPIRED Compressed: NO Tag: TAG20090908T203418

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/backupset/2009 09 08

/o1 mf annnn TAG20090908T203418 5bg51c2c .bkp

List of Archived Logs in backup set 1025

Thrd Seq Low SCN Low Time Next SCN Next Time

1 18 679230 08 SEP 09 679716 08 SEP 09

Note that the last archive log backup set in this report has an EXPIRED status, while the others have an AVAILABLE status. Thus, all the archived redo log backup sets are available for RMAN

recoveries, while the last is not. If you want to look at expired backup sets only, add the
expired
keyword, as in
list expired backup of archivelog all
.

434
Part III: Using RMAN Effectively

Listing Control File and SPFILE Backups

As you might expect, you can also list control file and SPFILE backups. The
list backup of controlfile
command provides you with a list of control file backups, and the
list backup of spfile
command provides output for SPFILE backups. Here is an example of each command and its results: RMAN> list backup of controlfile;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

774 Full 9.67M DISK 00:00:01 08 SEP 09

BP Key: 776 Status: AVAILABLE Compressed: NO Tag: TAG20090908T202724

Piece Name:

/oracle/app/oracle/flash recovery area/ROB1/autobackup/2009 09 08

/o1 mf s 697062444 5bg4ndym .bkp

Control File Included: Ckp SCN: 679035 Ckp time: 08 SEP 09