Querying the RMAN Repository

The List Command
List command is used for listing backups and database incarnations
Output is used for those files operated on by CHANGE, CROSSCHECK and DELETE commands.
(default is by backup)

RMAN> list backup;

RMAN> list backup by file;

Restricting the result set with the list command.
Show just the datafiles

RMAN> list backup of database;

Show just a datafile

RMAN> list backup of datafile
'+DATA/prod/datafile/system.256.653866897';

A specific tag

RMAN> list backup tag 'TAG20080824T200435';

List backups between specific dates

RMAN> list backup completed between
'24-AUG-08' and '25-AUG-08';

Listing Database Incarnations:

Each time an 'open resetlogs' is performed on a database, this creates a new incarnation of the database

RMAN> list incarnation;

List for a specific database if using a repository catalog for several databases.

RMAN> list incarnation of database prod;

The Report command
Report comand is used for finding out which files need a backup, or which are no longer needed, which files are in the schema...

Reporting database files that need a backup:

--First we should run a crosscheck
RMAN> crosscheck backup;

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- ----------------------------------------

Or we can override the retention policy and use number of days of archivelogs that need applying.

RMAN> report need backup days =2;

Report of files whose recovery needs more than 2 days
of archived logs
File Days Name
---- ----- ------------------------------------------
1 14 +DATA/prod/datafile/system.256.653866897
2 14 +DATA/prod/datafile/sysaux.257.653866897
3 14 +DATA/prod/datafile/undotbs1.258.653866897
4 14 +DATA/prod/datafile/users.259.653866897
5 14 +DATA/prod/datafile/example.269.653867209

Reporting Obsolete backups:

Show superfluous backups based on the retention policy.

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found

Also we can use the delete obsolete command to list and optionally remove the superfluous backups.

RMAN> delete obsolete;

******************************************
keywords: list report
******************************************
rdbms version: 10g - 11g
******************************************