in Oracle

RMAN — 9 : Querying the RMAN Views / Catalog

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.

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 note ?

Question 2 : Can you write a script mapping individual ArchiveLogs with BackupSets ? Please submit your scripts (a minimal listing of columns suffices).

.
.
.