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;
Responses