06 September, 2015

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 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:

Foued said...

Hi Hemant, wonderful post as usual. Thank you for sharing.
Foued

Anonymous said...

Thank you, just what I was looking for, really useful.

Ann Marie

Unknown said...

Thank you so much