Backup Recovery   «Prev  Next»

Lesson 6 Reporting
Objective Create reports and lists about backups.

Oracle Database Backup Reports

Recovery Manager, or RMAN, can create useful lists and reports of backups and image copies. RMAN obtains the information from your metadata repository, either the control file or the recovery catalog. Use the report and list commands to determine what you have, or need to back up or copy. The information, which is available whether or not you use a recovery catalog, is extremely helpful in developing an effective backup strategy. The list command shows the contents of RMAN's metadata repository, all RMAN backups (both backup sets and proxy copies) and image copies. The report command performs a more detailed analysis. For example, it can generate a report on the datafiles that need a backup when the backup pieces are obsolete. The report command can list backup sets and datafile copies that can be deleted either because they are redundant or because they are unrecoverable. RMAN writes the output from these commands to the screen or to a log file. The list command queries the recovery catalog or control file and produces a record of its contents. Use it to list:
  1. Backups of a specified list of datafiles
  2. Image copies of a specified list of datafiles
  3. Backups of any datafile that is a member of a specified list of tablespaces
  4. Image copies of any datafile that is a member of a specified list of tablespaces
  5. Backups of any archived redo logs with a specified name and/or within a specified range
  6. Image copies of any archived redo log with a specified name and/or within a specified range
  7. Incarnations of a specified database

RMAN Reports

RMAN reports are intended to provide analysis of your backup and recovery situation. An RMAN report can answer questions such as:
  1. Which datafiles need a backup?
  2. Which datafiles have not been backed up recently?
  3. Which datafiles need to be backed up since less than n number of backups or copies are available?
  4. Which backups and copies can be deleted?
  5. Which datafiles are not recoverable because of unrecoverable operations performed on them?
  6. What is the current physical schema of the database or what was it at some previous time?
  7. Which backups are orphaned, that is, unusable within a restore operation, because they belong to incarnations of the database that are not direct predecessors of the current incarnation?

LIST BACKUP

Within Oracle, the output of the LIST BACKUP command is significantly improved. It now prints the list of backups belonging to a backup set. This is contained within a separate section of the report, from the list of data files or archived logs included within the backup set. A new command, REPORT NEED BACKUP REDUNDANCY, has been implemented. When fewer than a user-specified number of backups of a data file exist, this report alerts the user that a new backup is required. In the following simulation, we will connect to RMAN and run one LIST and REPORT command, and review the results displayed by it.

Writing Oracle LIST and REPORT Commands

  1. You have connected to the RMAN command line by using RMAN as the User Name, RMAN as the Password, and BACKUP as the Host String. You have also connected to the target database. Now, issue a LIST command to display all the backups performed on the database. Then click Enter to continue.
  2. RMAN now displays all the backups performed on the database. But what if there are obsolete backups? Press Enter to learn how to access that report.
  3. Issue a REPORT command to display all the obsolete backups performed on the database, which can be deleted. Approve the entries.
  4. RMAN now displays all the obsolete backups performed on the database, which can be deleted. This is the end of the simulation. Click Exit.

LIST Purpose

Use the LIST command to display backups and information about other objects recorded in the RMAN repository.

Additional Topics

  1. Prerequisites
  2. Usage Notes
  3. Syntax
  4. Semantics
  5. LIST Command Output
  6. Examples
Prerequisites
Execute LIST only at the RMAN prompt. Either of the following conditions must be met:
  1. RMAN must be connected to a target database. If RMAN is not connected to a recovery catalog, and if you are not executing the LIST FAILURE command, then the target database must be mounted or open. If RMAN is connected to a recovery catalog, then the target database instance must be started.
  2. RMAN must be connected to a recovery catalog and SET DBID must have been run.

Usage Notes for LIST command

Except for LIST FAILURE, the LIST command displays the backups and copies against which you can run CROSSCHECK and DELETE commands. The LIST FAILURE command displays failures against which you can run the ADVISE FAILURE and REPAIR FAILURE commands. "RMAN Backups in a Data Guard Environment" explains how RMAN handles backups in a Data Guard environment. In general, RMAN considers tape backups created on one database in the environment as accessible to all databases in the environment, whereas disk backups are accessible only to the database that created them. In a Data Guard environment, LIST displays those files that are accessible to the connected target database. RMAN prints the LIST output to either standard output or the message log, but not to both at the same time.

For REPORT commands, the following series of images lists some of the new commands for the LIST and REPORT feature within RMAN.

LIST commands in Oracle

1) List Copy of Database ARCHIVELOG ALL; The command displays all the archive log files and datafiles that have been backed up
1) LIST COPY OF DATABASE ARCHIVELOG ALL;
The command displays all the archive log files and datafiles that have been backed up.

2) LIST BACKUP; This command displays information about backup set and pieces (partial backups), the control files and the datafiles included within it.
2) LIST BACKUP;
This command displays information about backup set and pieces (partial backups), the control files and the datafiles included within it.

3) LIST BACKUP OF DATABASE; This command displays backups of all files within the database.
3) LIST BACKUP OF DATABASE;
This command displays backups of all files within the database.

4) LIST COPY OF DATAFILE D:\ORACLE\ORADATA\PETS\INDX01.DBF; This command lists copy of the specified datafile.
4) 
LIST COPY OF DATAFILE 'D:\ORACLE\ORADATA\PETS\INDX01.DBF';
This command lists copy of the specified datafile.

5) LIST BACKUP OF TABLESPACE SYSTEM; This command lists all backups of the SYSTEM tablespace.
5) LIST BACKUP OF TABLESPACE SYSTEM;
This command lists all backups of the SYSTEM tablespace.

6) LIST COPY OF ARCHIVELOG ALL; This command lists all archived redo logs and copies of logs.
6) LIST COPY OF ARCHIVELOG ALL;
This command lists all archived redo logs and copies of logs.

7) LIST BACKUP OF CONTROLFILE; This command lists all control file backups
7) LIST BACKUP OF CONTROLFILE;
This command lists all control file backups.

8) LIST BACKUP TAG WEEKLY_FULL_DB_BACKUP; This command list all the backups by tag name.
8) LIST BACKUP TAG 'WEEKLY_FULL_DB_BACKUP';
This command list all the backups by tag name.

9) LIST COPY LIKE 'D:\ORACLE\BACKUPS\OCB%'; This command lists all backups based on the filename patterns.
9) LIST COPY LIKE 'D:\ORACLE\BACKUPS\OCB%';
This command lists all backups based on the filename patterns.

10) LIST BACKUP OF ARCHIVELOG UNITL TIME SYSDATE-30
10) LIST BACKUP OF ARCHIVELOG UNITL TIME 'SYSDATE-30';
This command lists all backups by time.

11) LLIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-MAR-2000' AND '17-MAR-2000'; This command lists all backups for a data range.
11) LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-MAR-2000' AND '17-MAR-2000';
This command lists all backups for a data range.


The next lesson describes how to desing report backups.
Ad Oracle Backup Recovery