Databases Reference
In-Depth Information
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:
BS Key COMPLETIO BP Name Datafile
------ --------- -------------------------------- ---------------------------------
159 11-JAN-13 /u01/O12C/rman/r16qnv59jj_1_1.bk /u01/dbfile/o12c/inv_data2.dbf
/u01/dbfile/o12c/lob_data01.dbf
/u01/dbfile/o12c/p14_tbsp.dbf
/u01/dbfile/o12c/p15_tbsp.dbf
/u01/dbfile/o12c/p16_tbsp.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
,TO_CHAR(end_time,'dd-mon-yy hh24:mi') as end_time
FROM v$rman_backup_job_details
ORDER BY end_time;
Here is some sample output:
SESSION_RECID COMPRESSION_RATIO TIME_TAKEN_DISPLAY HOURS END_TIME
------------- ----------------- -------------------- -------- ------------------------
15 1 00:05:08 .09 11-jan-13 13:41
27 3.79407176 00:00:09 .00 11-jan-13 13:52
33 1.19992137 00:05:01 .08 11-jan-13 14:07
The contents of
V$RMAN_BACKUP_JOB_DETAILS
are summarized by a session connection to RMAN. Therefore, the
report output is more accurate if you connect to RMAN (establishing a session) and then exit out of RMAN after the
backup job is complete. If you remain connected to RMAN while running multiple backup jobs, the query output
reports on all backup activity while connected (for that session).
