Useful Query

Query 1

To determine which databases are currently registered in the recovery catalog:

SQL> SELECT * FROM RC_DATABASE;

Query 2

To determine which tablespaces are currently stored in the recovery catalog for the target database:

SELECT DB_KEY, DBINC_KEY, DB_NAME, TS#, NAME,

CREATION_CHANGE#, CHANGE#, CREATION_TIME, CRE_DATE

FROM RC_TABLESPACE;

Query 3

To determine which scripts are currently stored in the recovery catalog for the target database:

SELECT * FROM RC_STORED_SCRIPT;

Query 4

To list all backups of files in tablespace tbs_1 that was made since November first:

RMAN> list until time 'Nov 1 1996 00:00:00' backupset of tablespace tbs_1;

Query 5

To list all backups on device type 'sbt_tape':

RMAN> list device type 'sbt_tape' backupset of database;

Query 6

To list all database incarnations registered in the recovery catalog:

RMAN> list incarnation of database;

You can also use the list command to determine which copies and backups can be deleted. For example, if a full backup of the database was created on November 2, and it will not be necessary to recover the database to an earlier point-in-time, then the backup sets listed in the following report can be deleted: 

RMAN> list until time 'Nov 1 1996 00:00:00' backupset of database;

Query 7

To report on all datafiles which need a new backup because they contain unlogged changes that were made after the last full or incremental backup.

RMAN> report unrecoverable database;

Query 8

To report on all datafiles which need a new backup because 3 or more incremental backups have been taken since the last full backup.

RMAN> report need backup incremental 3 database;

Query 9

To report on all datafiles in tablespace tbs_1 which need a new backup  because the last full or incremental backup was taken more than 5 days ago.

RMAN> report need backup days 5 database;