The "data dictionary" for RMAN is in (a) V$ views from the controlfile and, if a Catalog Schema is implemented (b) Catalog Views.
A useful mapping of the two sets is
(for 11.2) at http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcviews001.htm#RCMRF1923
(for 12.1)
at http://docs.oracle.com/database/121/RCMRF/rcviews.htm#i77529
Sometimes (many a times ?) it is better to use these views than the RMAN LIST / REPORT commands. But it might be a tad difficult to understand these views and write the appropriate queries.
So, we extract a list of RMAN Backup Jobs. This V$ view listing spans Instance restarts !
Note, however, the INPUT_TYPE reported is based on the backup command. For example, the 06-Sep backup command was a single "backup as compressed backupset database plus archivelog;" which actually created multiple BackupSets for ArchiveLogs, Database Files, ArchiveLog and Controlfile. All of them appear together as one entry "DB FULL". See my earlier posting "RMAN -- 1 : Backup Job Details" for another example of how you might misinterpret this view.
If it is single command, even the SUBJOB details view doesn't seem to be helpful.
So, be careful when trying to interpret the results of a query on this view if you don't know how the backups are run.
This, below, is the query on the corresponding Recovery Catalog view :
(Remember the first 10-Aug backup doesn't show because it was before a RESETLOGS and the Recovery Catalog doesn't show backups prior a RESETLOGS that was issued befor the first Full Resync into the Recovery Catalog. See my previous post).
What about listing individual datafiles ?
(Note once again, the backup before the RESETLOGS isn't included when querying the Recovery Catalog simply because the RESETLOGS was before the Full Resync).
And Backup Sets ?
Note that the RC_BACKUP_SET view shows the type of controlfile backup (whether a BACKUP or a STANDBY) not whether it is included in the BackupSet. SET_STAMP and SET_COUNT are the Join to V$BACKUP_DATAFILE. I use the Join to show the backup executed earlier today :
So, for the BackupSet 310, I have datafiles 1 to 11 backed up between 21:25 and 21:33 today. Set 312 has a Controlfile (Auto)Backup. You may notice some slight differences in the query the RC_% views versus the V$ views.
What about BackupPieces ?
BackupSets 309 and 311 contain ArchiveLog backups while 312 contains the Controlfile (auto)backup.
In the earlier scripts joining V$BACKUP_SET with V$BACKUP_DATAFILE, I've already identified datafiles in each BackupSet (in this case only 1 BackupSet for datafiles today). You could have your multiple datafiles spread across multiple BackupSets.
Question 1 : Can you map Datafiles to BackupPieces ? Answer : No. Follow-up Question : Why not ?
Question 2 : Can you write a script mapping individual ArchiveLogs with BackupSets ? Please submit your scripts (a minimal listing of columns suffices).
.
.
.
A useful mapping of the two sets is
(for 11.2) at http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcviews001.htm#RCMRF1923
(for 12.1)
at http://docs.oracle.com/database/121/RCMRF/rcviews.htm#i77529
Sometimes (many a times ?) it is better to use these views than the RMAN LIST / REPORT commands. But it might be a tad difficult to understand these views and write the appropriate queries.
SYSTEM>l 1 select to_char(start_time,'DD-MON HH24:MI') StartTime, 2 to_char(end_time,'DD-MON HH24:MI') EndTime, 3 (end_time-start_time)*1440 RunMin, 4 input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status 5 from v$rman_backup_job_details 6 where start_time > sysdate-32 7* order by start_time SYSTEM>/ STARTTIME ENDTIME RUNMIN READ_MB WRITE_MB INPUT_TYPE STATUS --------------------- --------------------- --------- -------- -------- ------------- ----------------------- 10-AUG 15:11 10-AUG 15:16 4.32 2,690 860 DB FULL COMPLETED 10-AUG 19:55 10-AUG 19:59 3.88 2,702 869 DB FULL COMPLETED 30-AUG 16:58 30-AUG 16:59 1.28 851 248 DATAFILE FULL COMPLETED 06-SEP 21:25 06-SEP 21:33 8.07 2,808 902 DB FULL COMPLETED SYSTEM>
So, we extract a list of RMAN Backup Jobs. This V$ view listing spans Instance restarts !
Note, however, the INPUT_TYPE reported is based on the backup command. For example, the 06-Sep backup command was a single "backup as compressed backupset database plus archivelog;" which actually created multiple BackupSets for ArchiveLogs, Database Files, ArchiveLog and Controlfile. All of them appear together as one entry "DB FULL". See my earlier posting "RMAN -- 1 : Backup Job Details" for another example of how you might misinterpret this view.
If it is single command, even the SUBJOB details view doesn't seem to be helpful.
SYSTEM>l 1 select to_char(start_time,'DD-MON HH24:MI') StartTime, 2 to_char(end_time,'DD-MON HH24:MI') EndTime, 3 (end_time-start_time)*1440 RunMin, 4 input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status 5 from v$rman_backup_subjob_details 6 where start_time > sysdate-32 7* order by start_time SYSTEM>/ STARTTIME ENDTIME RUNMIN READ_MB WRITE_MB INPUT_TYPE STATUS --------------------- --------------------- --------- -------- -------- ------------- ----------------------- 10-AUG 15:11 10-AUG 15:16 4.32 2,690 860 DB FULL COMPLETED 10-AUG 19:55 10-AUG 19:59 3.88 2,702 869 DB FULL COMPLETED 30-AUG 16:58 30-AUG 16:59 1.28 851 248 DATAFILE FULL COMPLETED 06-SEP 21:25 06-SEP 21:33 8.07 2,808 902 DB FULL COMPLETED SYSTEM>
So, be careful when trying to interpret the results of a query on this view if you don't know how the backups are run.
This, below, is the query on the corresponding Recovery Catalog view :
RCAT_OWNER>l 1 select to_char(start_time,'DD-MON HH24:MI') StartTime, 2 to_char(end_time,'DD-MON HH24:MI') EndTime, 3 (end_time-start_time)*1440 RunMin, 4 input_bytes/1048576 Read_MB, output_bytes/1048576 Write_MB, input_type, status 5 from RC_RMAN_BACKUP_JOB_DETAILS 6* where start_time > sysdate-32 order by start_time RCAT_OWNER>/ STARTTIME ENDTIME RUNMIN READ_MB WRITE_MB INPUT_TYPE STATUS --------------------- --------------------- ------ ------- -------- ------------- ----------------------- 10-AUG 19:55 10-AUG 19:59 3.88 2,702 869 DB FULL COMPLETED 30-AUG 16:58 30-AUG 16:59 1.28 851 248 DATAFILE FULL COMPLETED 06-SEP 21:25 06-SEP 21:33 8.07 2,808 902 DB FULL COMPLETED RCAT_OWNER>
(Remember the first 10-Aug backup doesn't show because it was before a RESETLOGS and the Recovery Catalog doesn't show backups prior a RESETLOGS that was issued befor the first Full Resync into the Recovery Catalog. See my previous post).
What about listing individual datafiles ?
SYSTEM>l 1 select to_char(completion_time,'DD-MON HH24:MI') Complete_At, checkpoint_change#, resetlogs_change#, 2 trunc(block_size*datafile_blocks/1048576) Total_Size, trunc(block_size*blocks/1048576) Backup_Size 3 from v$backup_datafile 4 where file#=1 5 and completion_time > sysdate-32 6* order by completion_time SYSTEM>/ COMPLETE_AT CHECKPOINT_CHANGE# RESETLOGS_CHANGE# TOTAL_SIZE BACKUP_SIZE --------------------- ------------------ ----------------- ---------- ----------- 10-AUG 15:14 14158847 14082620 841 741 10-AUG 19:58 14186110 14185666 841 741 30-AUG 16:59 14198051 14185666 841 741 06-SEP 21:32 14211304 14185666 841 741 SYSTEM> RCAT_OWNER>l 1 select to_char(completion_time,'DD-MON HH24:MI') Complete_At, checkpoint_change#, resetlogs_change#, 2 trunc(block_size*datafile_blocks/1048576) Total_Size, trunc(block_size*blocks/1048576) Backup_Size 3 from rc_backup_datafile 4 where file#=1 5 and completion_time > sysdate-32 6* order by completion_time RCAT_OWNER>/ COMPLETE_AT CHECKPOINT_CHANGE# RESETLOGS_CHANGE# TOTAL_SIZE BACKUP_SIZE --------------------- ------------------ ----------------- ---------- ----------- 10-AUG 19:58 14186110 14185666 841 741 30-AUG 16:59 14198051 14185666 841 741 06-SEP 21:32 14211304 14185666 841 741 RCAT_OWNER>
(Note once again, the backup before the RESETLOGS isn't included when querying the Recovery Catalog simply because the RESETLOGS was before the Full Resync).
And Backup Sets ?
SYSTEM>l 1 select set_stamp, set_count, 2 decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included 5 from v$backup_set 6 where completion_time > sysdate-32 7* order by completion_time SYSTEM>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CON ---------- ---------- ----------- ---------- --------------------- --------------------- --- 887382703 291 ArchiveLog 1 10-AUG 15:11 10-AUG 15:11 NO 887382704 292 Datafile 1 10-AUG 15:11 10-AUG 15:15 NO 887382910 293 Datafile 1 10-AUG 15:15 10-AUG 15:15 NO 887382945 294 Datafile 1 10-AUG 15:15 10-AUG 15:15 NO 887382961 295 ArchiveLog 1 10-AUG 15:16 10-AUG 15:16 NO 887382962 296 Datafile 1 10-AUG 15:16 10-AUG 15:16 YES 887399620 299 Datafile 1 10-AUG 19:53 10-AUG 19:53 YES 887399709 300 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NO 887399713 301 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NO 887399714 302 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NO 887399715 303 Datafile 1 10-AUG 19:55 10-AUG 19:58 NO 887399941 304 ArchiveLog 1 10-AUG 19:59 10-AUG 19:59 NO 887399943 305 Datafile 1 10-AUG 19:59 10-AUG 19:59 YES 889115340 306 Datafile 1 30-AUG 16:29 30-AUG 16:29 YES 889117084 307 Datafile 1 30-AUG 16:58 30-AUG 16:59 NO 889117160 308 Datafile 1 30-AUG 16:59 30-AUG 16:59 YES 889737940 309 ArchiveLog 1 06-SEP 21:25 06-SEP 21:25 NO 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 889738419 311 ArchiveLog 1 06-SEP 21:33 06-SEP 21:33 NO 889738421 312 Datafile 1 06-SEP 21:33 06-SEP 21:33 YES 20 rows selected. SYSTEM> RCAT_OWNER>l 1 select set_stamp, set_count, 2 decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included 5 from rc_backup_set 6 where completion_time > sysdate-32 7* order by completion_time RCAT_OWNER>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CONTROL ---------- ---------- ----------- ---------- --------------------- --------------------- ------- 887399620 299 Datafile 1 10-AUG 19:53 10-AUG 19:53 BACKUP 887399709 300 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NONE 887399713 301 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NONE 887399714 302 ArchiveLog 1 10-AUG 19:55 10-AUG 19:55 NONE 887399715 303 Datafile 1 10-AUG 19:55 10-AUG 19:58 NONE 887399941 304 ArchiveLog 1 10-AUG 19:59 10-AUG 19:59 NONE 887399943 305 Datafile 1 10-AUG 19:59 10-AUG 19:59 BACKUP 889115340 306 Datafile 1 30-AUG 16:29 30-AUG 16:29 BACKUP 889117084 307 Datafile 1 30-AUG 16:58 30-AUG 16:59 NONE 889117160 308 Datafile 1 30-AUG 16:59 30-AUG 16:59 BACKUP 889737940 309 ArchiveLog 1 06-SEP 21:25 06-SEP 21:25 NONE 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 889738419 311 ArchiveLog 1 06-SEP 21:33 06-SEP 21:33 NONE 889738421 312 Datafile 1 06-SEP 21:33 06-SEP 21:33 BACKUP 14 rows selected. RCAT_OWNER>
Note that the RC_BACKUP_SET view shows the type of controlfile backup (whether a BACKUP or a STANDBY) not whether it is included in the BackupSet. SET_STAMP and SET_COUNT are the Join to V$BACKUP_DATAFILE. I use the Join to show the backup executed earlier today :
SYSTEM>l 1 select s.set_stamp, s.set_count, 2 decode(backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(s.completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included, 5 file#, checkpoint_change# 6 from v$backup_set s, v$backup_datafile d 7 where s.set_stamp=d.set_stamp 8 and s.set_count=d.set_count 9 and s.completion_time > sysdate-1 10* order by s.completion_time, file# SYSTEM>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CON FILE# CHECKPOINT_CHANGE# ---------- ---------- ----------- ---------- --------------------- --------------------- --- ---------- ------------------ 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 1 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 2 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 3 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 4 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 5 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 6 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 7 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 8 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 9 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 10 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 11 14211304 889738421 312 Datafile 1 06-SEP 21:33 06-SEP 21:33 YES 0 14211700 12 rows selected. SYSTEM> RCAT_OWNER>l 1 select s.set_stamp, s.set_count, 2 decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 s.pieces, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(s.completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included, 5 file#, checkpoint_change# 6 from rc_backup_set s, rc_backup_datafile d 7 where s.set_stamp=d.set_stamp 8 and s.set_count=d.set_count 9 and s.completion_time > sysdate-1 10* order by s.completion_time, file# RCAT_OWNER>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CONTROL FILE# CHECKPOINT_CHANGE# ---------- ---------- ----------- ---------- --------------------- --------------------- ------- ---------- ------------------ 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 1 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 2 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 3 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 4 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 5 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 6 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 7 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 8 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 9 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 10 14211304 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 11 14211304 11 rows selected. RCAT_OWNER>
So, for the BackupSet 310, I have datafiles 1 to 11 backed up between 21:25 and 21:33 today. Set 312 has a Controlfile (Auto)Backup. You may notice some slight differences in the query the RC_% views versus the V$ views.
What about BackupPieces ?
SYSTEM>l 1 select s.set_stamp, s.set_count, 2 decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 s.pieces, to_char(p.start_time,'DD-MON HH24:MI') Start_At, to_char(p.completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included, 5 p.piece#, p.handle 6 from v$backup_set s, v$backup_piece p 7 where s.set_stamp=p.set_stamp 8 and s.set_count=p.set_count 9 and s.completion_time > sysdate-1 10* order by s.completion_time, piece# SYSTEM>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CON PIECE# ---------- ---------- ----------- ---------- --------------------- --------------------- --- ---------- HANDLE ------------------------------------------------------------------------------------------------------------------------------------ 889737940 309 ArchiveLog 1 06-SEP 21:25 06-SEP 21:25 NO 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T212539_byrhpncy_.bkp 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NO 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp 889738419 311 ArchiveLog 1 06-SEP 21:33 06-SEP 21:33 NO 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T213338_byrj5mgt_.bkp 889738421 312 Datafile 1 06-SEP 21:33 06-SEP 21:33 YES 1 /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_09_06/o1_mf_n_889738421_byrj5q8h_.bkp SYSTEM> RCAT_OWNER>l 1 select s.set_stamp, s.set_count, 2 decode(s.backup_type,'L','ArchiveLog','D','Datafile','I','Incremental') backup_type, 3 s.pieces, to_char(p.start_time,'DD-MON HH24:MI') Start_At, to_char(p.completion_time,'DD-MON HH24:MI') Completed_At, 4 controlfile_included, 5 p.piece#, p.handle 6 from rc_backup_set s, rc_backup_piece p 7 where s.set_stamp=p.set_stamp 8 and s.set_count=p.set_count 9 and s.completion_time > sysdate-1 10* order by s.completion_time, piece# RCAT_OWNER>/ SET_STAMP SET_COUNT BACKUP_TYPE PIECES START_AT COMPLETED_AT CONTROL PIECE# ---------- ---------- ----------- ---------- --------------------- --------------------- ------- ---------- HANDLE ------------------------------------------------------------------------------------------------------------------------------------ 889737940 309 ArchiveLog 1 06-SEP 21:25 06-SEP 21:25 NONE 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T212539_byrhpncy_.bkp 889737948 310 Datafile 1 06-SEP 21:25 06-SEP 21:33 NONE 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp 889738419 311 ArchiveLog 1 06-SEP 21:33 06-SEP 21:33 NONE 1 /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_annnn_TAG20150906T213338_byrj5mgt_.bkp 889738421 312 Datafile 1 06-SEP 21:33 06-SEP 21:33 BACKUP 1 /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_09_06/o1_mf_n_889738421_byrj5q8h_.bkp RCAT_OWNER>
BackupSets 309 and 311 contain ArchiveLog backups while 312 contains the Controlfile (auto)backup.
In the earlier scripts joining V$BACKUP_SET with V$BACKUP_DATAFILE, I've already identified datafiles in each BackupSet (in this case only 1 BackupSet for datafiles today). You could have your multiple datafiles spread across multiple BackupSets.
Question 1 : Can you map Datafiles to BackupPieces ? Answer : No. Follow-up Question : Why not ?
Question 2 : Can you write a script mapping individual ArchiveLogs with BackupSets ? Please submit your scripts (a minimal listing of columns suffices).
.
.
.
3 comments:
Hi Hemant, wonderful post as usual. Thank you for sharing.
Foued
Thank you, just what I was looking for, really useful.
Ann Marie
Thank you so much
Post a Comment