# lists backups of all files in database
LIST BACKUP OF DATABASE;
# lists copy of specified datafile
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf';
# lists specified backup set
LIST BACKUPSET 213;
# lists datafile copy
LIST DATAFILECOPY '/tmp/tools01.dbf';
# specify a backup set by tag
LIST BACKUPSET TAG 'weekly_full_db_backup';
# specify a backup or copy by device type
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
# specify a backup by directory or path
LIST COPY LIKE '/tmp/%';
# specify a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2012' AND '17-DEC-2012';
# specify logs backed up at least twice to tape
LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
# specify backup sets backed up at least once to disk
LIST BACKUPSET BACKED UP 1 TIMES TO DISK;
# specify backups of PDB backed up at least twice to sbt
LIST BACKUP OF PLUGGABLE DATABASE my_pdb BACKED UP 2 TIMES TO SBT; REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2; REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1; REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy REPORT NEED BACKUP INCREMENTAL 2; # checks entire database CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK BACKUP DEVICE TYPE sbt; CROSSCHECK BACKUP; REPORT OBSOLETE; REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS; REPORT OBSOLETE REDUNDANCY 1;
Reporting on the Database Schema The REPORT SCHEMA command lists and displays information about the database files, tablespaces, and so on.
To report on the database schema:
If you do not specify FOR DB_UNIQUE_NAME with REPORT SCHEMA, then a recovery catalog connection is optional, but a target database connection is required. In a Data Guard environment, you can specify REPORT SCHEMA FOR DB_UNIQUE_NAME to report the schema for a database in the environment. In this case, an RMAN connection to a target database is not required. You can connect RMAN to the recovery catalog and set the DBID instead.
REPORT SCHEMA; REPORT SCHEMA AT TIME 'SYSDATE-14'; # schema 14 days ago REPORT SCHEMA AT SCN 1000; # schema at scn 1000 REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100 REPORT SCHEMA FOR DB_UNIQUE_NAME standby1; # schema for database standby1
The following query shows the backup job history ordered by session key, which is the primary key for the RMAN session:
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
The following query shows the backup job speed ordered by session key, which is the primary key for the RMAN session. The columns in_sec and out_sec display the data input and output per second.
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
The following query shows the backup job size and throughput ordered by session key, which is the primary key for the RMAN session. The columns in_size and out_size display the data input and output per second.
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
