Search My Oracle Blog

Custom Search

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

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

.
.
.


1 comment:

Foued said...

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016