Oracle RMAN 11g Backup and Recovery (142 page)
Read Oracle RMAN 11g Backup and Recovery Online
Authors: Robert Freeman
424
Part III: Using RMAN Effectively
ecause everyone wants to know for sure that their databases have been backed up and are currently recoverable, RMAN comes with some good reporting tools. This chapter covers RMAN reporting in some depth. First, we look at the RMAN
list
B
command, followed by the RMAN
report
command. Each of these commands provides facilities for in-depth analysis of the database that you are using RMAN to back up and its backups. These commands are the primary ways of extracting information from RMAN. You will find that lists and reports come in handy not only during recovery, but also when you want to see how RMAN is configured and when you need to perform other administrative tasks (such as determining if a tablespace has been backed up).
The RMAN list Command
The RMAN
list
command is a method of querying either the database control file or the recovery catalog for historical information on backups. Lists provide an array of information, from lists of database incarnations, to lists of backup sets and archive log backups. The bottom line is that if you want to know whether the database was backed up and when, then you want to generate a list. The format of lists initially tends to appear not very reader friendly. Once you have looked at a few lists, though, they seem a little easier to read. So, let’s look at the
list
commands and how they can be interpreted.
Listing Incarnations
The
list incarnation
command provides you a list of each database incarnation for the target database. This list can be used to recover your database to a point in time before your last
resetlogs
command was issued, if this is required (refer to Chapter 14 for more details on this operation). Here is an example of the
list incarnation
command output: RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ROB1 1854903786 PARENT 1 07-SEP-09
2 2 ROB1 1854903786 CURRENT 635384 08-SEP-09
In this listing, we find that our database has had two different incarnations, with each incarnation represented in each row of the report. Each individual incarnation has its own key (Inc Key), which we would use if we wanted to reset the database incarnation (refer to Chapter 14). We also get our database name and ID in this report.
The STATUS column displays the status of the incarnation listed. It indicates whether the incarnation is an older incarnation (PARENT), the current incarnation, or, if a recovery past
resetlogs
has occurred, an orphan incarnation. Finally, the Reset SCN and Reset Time columns basically indicate when the database incarnation was created (which is why the Reset SCN for the first entry is 1). This column helps support recovery through
resetlogs
and also helps support easier recovery to a previous incarnation.
An important point to note is that output generated with a recovery catalog and output generated without a recovery catalog generally look somewhat different. For example, this is the output of the
list incarnation
command while attached to a recovery catalog:
Chapter 17: Monitoring and Reporting on RMAN
425
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2 18 ROB1 1854903786 PARENT 1 07-SEP-09
2 4 ROB1 1854903786 CURRENT 635384 08-SEP-09
Note in this example that both the DB keys and the incarnation keys are different from those reported when using the control file. This leads to an important point: Many reports have keys that identify specific items in the reports. You will use these keys in other RMAN commands (such as in the
reset database
command). Since the values of the keys change depending on whether you are connected to the recovery catalog, you need to be careful about determining which keys you need.
Listing Backups
The
list
command comes with a number of different options that allow you to report on the status of database backups and copies. In this section, we are going to look at several of these reports.
Summarizing Available Backups
Let’s first look at a few ways of getting summary backup information. The
list
command provides a couple of options. The first option is the
list backup summary
report: RMAN> list backup summary;
List of Backups
===============
60 B A A DISK 08 SEP 09 1 2 YES TAG20090908T025311
61 B A A DISK 08 SEP 09 1 2 NO TAG20090908T025326
62 B F A DISK 08 SEP 09 1 2 NO TAG20090908T025328
63 B A A DISK 08 SEP 09 1 2 NO TAG20090908T025355
67 B A A DISK 08 SEP 09 1 2 NO TAG20090908T032531
68 B F A DISK 08 SEP 09 1 1 NO TAG20090908T032815
70 B F A DISK 08 SEP 09 1 1 NO TAG20090908T032850
176 B F A DISK 08 SEP 09 1 1 NO TAG20090908T040309
207 B F A DISK 08 SEP 09 1 1 NO TAG20090908T040315
421 B F A DISK 08 SEP 09 1 1 NO TAG20090908T051140
433 B F A DISK 08 SEP 09 1 1 NO TAG20090908T051144
This report provides us with some nice summary information. The backup set key is listed in the Key column. The TY (type) and the LV (level) columns indicate the type of backup listed (B = backup, F = full, A = archive log, and 0 and 1 = incremental backups). The S column indicates the status of the backup (AVAILABLE, UNAVAILABLE, or EXPIRED). The Device Type column lets us know whether the backup is a tape or disk backup. We also have columns for the date of the backup (Completion Time), the number of pieces (#Pieces) or copies (#Copies) that the backup set consists of, if the backup was compressed, and any tag that was assigned to the backup set (Tag).
Most of the
list
commands will accept the
summary
parameter at the end. For example: list backup of database summary;
list expired backup of archivelog all summary;
list backup of tablespace users summary;
426
Part III: Using RMAN Effectively
Listing Backups by Datafile
Another way to summarize backups is to use the
list backup by file
command to list each backup set and backup set piece. Here is an example of this report (we have removed some output to save a few trees):
RMAN> list backup by file;
List of Datafile Backups
========================
2 62 B F A 635676 08 SEP 09 1 2 NO TAG20090908T025328
4 421 B F A 644956 08 SEP 09 1 1 NO TAG20090908T051140
176 B F A 642222 08 SEP 09 1 1 NO TAG20090908T040309
68 B F A 637442 08 SEP 09 1 1 NO TAG20090908T032815
List of Archived Log Backups
============================
1 2 635574 08 SEP 09 60 A 1 2 YES TAG20090908T025311
1 3 635642 08 SEP 09 61 A 1 2 NO TAG20090908T025326
1 4 635668 08 SEP 09 63 A 1 2 NO TAG20090908T025355
1 7 636872 08 SEP 09 67 A 1 2 NO TAG20090908T032531
1 8 637317 08 SEP 09 67 A 1 2 NO TAG20090908T032531
1 9 637320 08 SEP 09 67 A 1 2 NO TAG20090908T032531
1 10 637324 08 SEP 09 67 A 1 2 NO TAG20090908T032531
List of Control File Backups
============================
644990 08 SEP 09 433 A 1 1 NO TAG20090908T051144
642268 08 SEP 09 207 A 1 1 NO TAG20090908T040315
637490 08 SEP 09 70 A 1 1 NO TAG20090908T032850
List of SPFILE Backups
======================
08 SEP 09 433 A 1 1 NO TAG20090908T051144
08 SEP 09 207 A 1 1 NO TAG20090908T040315
08 SEP 09 70 A 1 1 NO TAG20090908T032850
This report summarizes each backup file that has been created by the type of backup (datafile backup, archived log backup, control file backup, and SPFILE backup) and then by datafile for the datafile backups. In this report, we get the date of the backup and the specific keys associated with the backup file. Depending on the type of backup, we get information that pertains to that type of backup.
Additional Backup Information
If you want as much information reported on your RMAN backups as you can get, then the
list
backup
command is for you. It provides detailed information on the backups that you have taken, including backup sets, archived redo log backups, and control file/SPFILE backups. Let’s look at an example of the results of the execution of the
list backup
command:
