in Oracle

V$RMAN_BACKUP_JOB_DETAILS, a caveat

Building on a previous blog post (you could read it before or after this post), here’s a quick demo of a caveat or quirk with V$RMAN_BACKUP_JOB_DETAILS.

This in 11.2.0.4

[oracle@ora11204 Desktop]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 9 13:39:44 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1362461976)

RMAN> backup as compressed backupset
2> incremental level 1 database
3> plus archivelog ;


Starting backup at 09-JAN-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=108 RECID=1245 STAMP=928093294
input archived log thread=1 sequence=109 RECID=1246 STAMP=928093719
input archived log thread=1 sequence=110 RECID=1247 STAMP=928093722
input archived log thread=1 sequence=111 RECID=1248 STAMP=928093724
...
...
...
input archived log thread=1 sequence=163 RECID=1318 STAMP=929802055
input archived log thread=1 sequence=164 RECID=1319 STAMP=932823436
input archived log thread=1 sequence=165 RECID=1320 STAMP=932823439
input archived log thread=1 sequence=166 RECID=1321 STAMP=932823606
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134007_d768kr8l_.bkp tag=TAG20170109T134007 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
using channel ORA_DISK_1
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd0_TAG20170109T134123_d768n3v5_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c552qnsh_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_intermed_c552qpc7_.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd1_TAG20170109T134123_d768ojmm_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=167 RECID=1322 STAMP=932823743
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134223_d768ozv9_.bkp tag=TAG20170109T134223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JAN-17

Starting Control File and SPFILE Autobackup at 09-JAN-17
piece handle=/u02/FRA/ORCL/autobackup/2017_01_09/o1_mf_s_932823745_d768p1jo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-17

RMAN>

What does V$RMAN_BACKUP_JOB_DETAILS tell us ?

SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') Start_At,
2 to_char(end_time,'DD-MON HH24:MI') End_At,
3 input_bytes/1048576 Input_MB, output_bytes/1048576 Output_MB,
4 input_type, status
5 from v$rman_backup_job_details
6 where start_time > trunc(sysdate)
7* order by start_time
SQL> /

START_AT END_AT INPUT_MB OUTPUT_MB INPUT_TYPE
--------------------- --------------------- ---------- ---------- -------------
STATUS
-----------------------
09-JAN 13:40 09-JAN 13:42 2917.06055 491.563477 DB INCR
COMPLETED


SQL>

The view does NOT show how much of the input/output was for ArchiveLogs. It clubs ArchiveLogs and the controlfile autobackup under the single entry for “DB INCR”. Anyone reading this row from V$RMAN_BACKUP_JOB_DETAILS would NOT know if ArchiveLogs had been backed-up, would NOT know if a controlfile/spfile autobackup was created.
Furtheremore, if there is a failure (e.g. only the last ArchiveLog backupset failed ?), would you be able to identify what has successfully been backed up. Also see my previous blog post.
.
.
.