Database Reference
In-Depth Information
Sometimes, DBAs new to RMAN have a hard time grasping the concept of backups, backup sets, backup pieces,
and data files and how they relate. I find the following query useful when discussing RMAN backup components.
This query will display backup sets, the backup pieces with the set, and the data files that are backed up within the
backup pieces:
SET LINES 132 PAGESIZE 100
BREAK ON REPORT ON bs_key ON completion_time ON bp_name ON file_name
COL bs_key FORM 99999 HEAD "BS Key"
COL bp_name FORM a40 HEAD "BP Name"
COL file_name FORM a40 HEAD "Datafile"
--
SELECT
s.recid bs_key
,TRUNC(s.completion_time) completion_time
,p.handle bp_name
,f.name file_name
FROM v$backup_set s
,v$backup_piece p
,v$backup_datafile d
,v$datafile f
WHERE p.set_stamp = s.set_stamp
AND p.set_count = s.set_count
AND d.set_stamp = s.set_stamp
AND d.set_count = s.set_count
AND d.file# = f.file#
ORDER BY
s.recid
,p.handle
,f.name;
The output here has been shortened to fit on the page:
S Key COMPLETIO BP Name Datafile
------ --------- ---------------------------------------- ------------------------------------
11 28-SEP-14 /u01/O12C/rman/O12C_0cpjkl6h_1_1.bk /u01/dbfile/O12C/inv_mgmt_data01.dbf
/u01/dbfile/O12C/reg_index01.dbf
/u01/dbfile/O12C/tools01.dbf
/u01/dbfile/O12C/undotbs01.dbf
/u01/dbfile/O12C/users01.dbf
Sometimes, it's useful to report on the performance of RMAN backups. The following query reports on the time
taken for an RMAN backup per session.
COL hours FORM 9999.99
COL time_taken_display FORM a20
SET LINESIZE 132
--
SELECT
session_recid
,compression_ratio
,time_taken_display
,(end_time - start_time) * 24 as hours
