Backup Recovery   «Prev  Next»

Lesson 7 Report Backup
Objective Report Backup Sets and Datafile Copies

How to Report Backup Sets and Datafile Copies in Oracle

Reporting "backup sets" and "datafile copies" in Oracle 12c is an essential task for any Oracle DBA to ensure data integrity and successful recovery operations. Here's the process to report these elements:
  1. Use RMAN (Recovery Manager) for Reporting: Oracle's Recovery Manager (RMAN) is the primary tool for managing backup and recovery operations. To report on backup sets and datafile copies, you will utilize RMAN's reporting capabilities.
  2. Connecting to RMAN: First, connect to the target database and, if necessary, the recovery catalog database using RMAN. This can be done via the command line: 
    $ rman TARGET / CATALOG rman/rman@rcat
    Replace `/` with appropriate credentials if needed.
  3. Reporting Backup Sets: To report on backup sets, use the `LIST BACKUP` command. This command provides details about backup sets, including their contents and status. For instance: 
    RMAN> LIST BACKUP;
    This command lists all backup sets. To refine your search, you can use additional options like `BY FILE`, `SUMMARY`, `of database`, etc.
  4. Reporting Datafile Copies: For reporting on datafile copies, use the `LIST COPY` command: 
    RMAN> LIST COPY;
    This command lists all datafile copies. You can refine the output by specifying particular datafiles or using options like `TAG`, `LEVEL`, etc.
  5. Using REPORT Command: The `REPORT` command in RMAN is also useful for reporting on various aspects of backups and datafile copies. For example: 
    RMAN> REPORT NEED BACKUP;
    This command reports which files need backup based on your retention policy.
  6. Check for Obsolete Backups: Regularly use the `REPORT OBSOLETE` command to identify backups that are no longer needed based on the retention policy: 
    RMAN> REPORT OBSOLETE;
  7. Output and Logging: The output of these commands can be viewed on the screen or redirected to a log file for record-keeping. To direct output to a log file, start RMAN with the `LOG` option: 
    $ rman TARGET / LOG /tmp/rman.log
  8. Custom Scripts and Automation:For regular reporting, consider developing custom scripts that encapsulate these RMAN commands. Automation can be set up using cron jobs or other scheduling tools.
  9. Reviewing and Analyzing Reports: Regularly review the reports generated by these commands. Ensure that all required datafiles and backup sets are present and accounted for.
  10. Staying Updated with Oracle Documentation: Always refer to the latest Oracle documentation for any updates or changes in command syntax or features, as Oracle periodically updates its software.

Following these steps will ensure thorough reporting of backup sets and datafile copies, contributing significantly to the database's integrity and availability in Oracle 12c environments.

Reporting on Obsolete Backups

You can report backup sets, backup pieces and datafile copies that are obsolete, those which are not needed to meet a specified retention policy, by specifying the OBSOLETE keyword. To report obsolete backups:
  1. Start RMAN and connect to a target database and recovery catalog (if used).
  2. Execute the CROSSCHECK command to update the status of backups in the repository compared to their status on disk.
    In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands: 
    CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE sbt;
CROSSCHECK BACKUP;
#crosshecks all backups on all devices
  3. Run REPORT OBSOLETE to identify which backups are obsolete because they are no longer needed for recovery.
If you do not specify any other options, then REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy, as shown in the following example: 

RMAN> REPORT OBSOLETE;
Datafile Copy 44 08-FEB-06 
/backup/ora_df549738566_s70_s1
Datafile Copy 45 08-FEB-06 
/backup/ora_df549738567_s71_s1
Datafile Copy 46 08-FEB-06 
/backup/ora_df549738568_s72_s1
Backup Set 26 08-FEB-06
Backup Piece 26 08-FEB-06 
/backup/ora_df549738682_s76_s1

You can also check which backups are obsolete under different recovery window-based or redundancy-based retention policies, by using REPORT OBSOLETE with RECOVERY WINDOW and REDUNDANCY options, as shown in these examples: 
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
REPORT OBSOLETE REDUNDANCY 1;

Oracle Report Commands (Legacy)

REPORT NEED BACKUP DAYS = 7 DATABASE
1) 
REPORT NEED BACKUP DAYS = 7 DATABASE

This report identifies new backup that is required for 7 days worth of archived redo logs for recovery

REPORT NEED BACKUP DAYS = 30 TABLESPACE SYSTEM
2) 
REPORT NEED BACKUP DAYS = 30 TABLESPACE SYSTEM;

This report identifies new backup for SYSTEM tablespace that is required for 3 days worth of archived redo logs for recovery

les, which require new backup to revert to the state as of last 3 days.
3) REPORT NEED BACKUP INCREMENTAL = 5 DATAFILE 'D:\ORACLE\ORADATA\PETS\INDX01.DBF';
This report identifies the datafiles, which require new backup to revert to the state as of last 3 days.

REPORT OBSOLETE REDUNDANCY=2
4) 
REPORT OBSOLETE REDUNDANCY=2;

This report identifies backups or copies that have at least 2 more recent backups or copies.


REPORT OBSOLETE REDUNDNACY = 2 UNTIL TIME SYSDATE-14
5) 
REPORT OBSOLETE REDUNDNACY = 2 UNTIL TIME 'SYSDATE-14';

This report identifies obsolete backups if there are at least 2 copies/backups that are no more than 2 weeks old.

REPORT UNRECOVERABLE DATABASE
6) REPORT UNRECOVERABLE DATABASE;
This report determines the datafiles that have had an unrecoverable operation performed against an object residing within the datafile since its last backup.

REPORT SCHEMA AT TIME SYSDATE-14
7) 
REPORT SCHEMA AT TIME 'SYSDATE-14';

This report identifies all datafiles and tablespaces within the target database at the current time.

The next lesson describes some of the data dictionary views for backup and recovery.
SEMrush Software