Oracle Report Command Options

In the realm of Oracle Database Administration, the Recovery Manager (RMAN) provides a pivotal tool known as the `REPORT` command. This command is indispensable for database administrators (DBAs) who seek to obtain comprehensive information about various aspects of their backup and recovery environment. The `REPORT` command in RMAN is multifaceted, offering several sub-options to cater to different reporting needs. These sub-options include:

1. REPORT SCHEMA: This option presents a snapshot of the database schema at a specific point in time. It can be particularly useful for understanding the database structure during a past backup.
2. REPORT NEED BACKUP: With this option, DBAs can determine which database files require a backup based on specified criteria, such as the number of days since the last backup or the number of log switches since the last backup.
3. REPORT UNRECOVERABLE: This option identifies any database files that contain changes not backed up by RMAN, highlighting potential risks in the database's recoverability.
4. REPORT OBSOLETE: In the context of a retention policy, this option lists all RMAN backups and copies that are obsolete and can be deleted.
5. REPORT BACKUP: This option provides details about backups of the database, offering insights into the backup pieces, their sizes, and other relevant information.

An exemplary use of the `REPORT` command might look like this:

RMAN> REPORT NEED BACKUP REDUNDANCY 2;

This command instructs RMAN to report on any files that would require a backup to maintain a redundancy level of two, meaning two complete and independent copies of the data. The `REPORT` command's utility lies in its ability to furnish DBAs with vital information, aiding in making informed decisions about backup strategies, ensuring data integrity, and optimizing storage. It is a tool that, when wielded with expertise, greatly enhances the DBA's capacity to maintain the health and security of the Oracle database environment.

Recovery Manager provides detailed reporting through the use of either the report or the list command. In this lesson, we will review the options for the report command and identify why you might want to use report.

Below is a list of the more frequent uses of the report command. To obtain a detailed listing, please refer to the Oracle8 documentation.

1. Use report to report on objects that need to be backed up based on incremental or day constraints. The integer in the command line represents the number of incremental backups, days since last full or incremental backup, or a minimum level of redundancy.
   report need backup {incremental, days, redundancy} integer {datafile, database, tablespace};
   1. report need backup days 50 datafile
   2. report need backup incremental 2 database
   3. report need backup redundancy 3
2. Use report to report on which objects might be unrecoverable if your system fails, such as a disk crashing. By running a report command, you can determine which data files are unrecoverable. report unrecoverable {datafile, database, tablespace} name report unrecoverable datafile "C:\oracle8\database\usr1orc1.ora"
3. Use report to report on the current schema of your target database or the state of the schema at a specific point in time, system control number (SCN), or log sequence number:
   

   report schema {
   at time "time", at scn integer, at logseq integer
   }
   

   1. report schema
4. Use report to determine which backups are obsolete while maintaining a certain backup redundancy. These backups can then be deleted.
   report obsolete redundancy=integer
   1. report obsolete redundancy=2

The following series of images demonstrates some examples:

To find out which backups are not needed during the recovery process, namely those commands that are beyond the specified retention policy, use this command:

report obsolete  

For this, list backup of the database, show the current retention policy and do two backups of datafile 4:

RMAN> list backup;
 using target database control file instead of recovery catalog
  
 RMAN> show retention policy;
 RMAN configuration parameters are:
 configure retention policy to redundancy 1; # default
  
 RMAN> backup datafile 4;
 piece handle=../o1_mf_nnndf_tag20090901t164959_59t2m7n0_.bkp 
  
 RMAN> backup datafile 4;
 piece handle=../o1_mf_nnndf_tag20090901t165007_59t2mhj3_.bkp

The REPORT command performs more complex analysis than LIST. Some main options are shown in Table 2.
Table 2: Report Commands

Option	Example	Explanation
NEED BACKUP	REPORT NEED BACKUP DATABASE	Shows which files need backing up under current retention policy. Use optional REDUNDANCY and RECOVERY WINDOW parameters to specify different criteria.
OBSOLETE	REPORT OBSOLETE	Lists backups that are obsolete under the configured backup retention policy. Use the optional REDUNDANCY and RECOVERY WINDOW parameters to override the default.
SCHEMA	REPORT SCHEMA	Reports the tablespaces and data files in the database at the current time (default) or a different time.
UNRECOVERABLE	REPORT UNRECOVERABLE	Lists all data files for which an unrecoverable operation has been performed against an object in the data file since the last backup of the data file.

To generate reports of database files and backups:

1. Start RMAN and connect to a target database.
2. Run the REPORT command at the RMAN prompt.

The following example reports backups that are obsolete according to the currently configured backup retention policy:

REPORT OBSOLETE;

The following example reports the data files and temp files in the database:

REPORT SCHEMA;

In the next lesson, you will learn how to use the list command to display information contained in your recovery catalog.