V$Views for using Rman Jobs

In some cases V$views supply information that is not available through use of the LIST and Report commands

To view query details of past and current Rman jobs:

-Rman Backup Job is the set of backup commands executed in one RMAN job

-the Views V$Rman_backup_job_details and V$RMAN_BACKUP_SUBJOB_DETAILS ll provide details of RMAN backup jobs

Queries for using past and current Rman jobs:

Note: the V$RMAN_BACKUP_JOB_DETAILS view for information about the backup type, status, and start and end time.

To view Job history in RMAN:


SQL> COL STATUS FORMAT a9
SQL> COL hrs    FORMAT 999.99
SQL> SELECT SESSION_KEY, INPUT_TYPE, STATUS,
  2         TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
  3         TO_CHAR(END_TIME,'mm/dd/yy hh24:mi')   end_time,
  4         ELAPSED_SECONDS/3600                   hrs
  5  FROM V$RMAN_BACKUP_JOB_DETAILS
  6  ORDER BY SESSION_KEY;

SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
        200 DB FULL       FAILED    02/08/17 15:06 02/08/17 15:06     .00
        203 DB FULL       FAILED    02/08/17 16:14 02/08/17 17:12     .97
        225 DB INCR       COMPLETED 02/10/17 11:54 02/10/17 15:00    3.11
        251 DB INCR       RUNNING   05/02/17 16:16 05/02/17 16:17     .01
        256 DB FULL       COMPLETED 05/02/17 16:20 05/02/17 16:22     .05
        261 DB INCR       COMPLETED 05/02/17 16:37 05/02/17 17:06     .49
        298 DB FULL       COMPLETED 05/08/17 15:04 05/08/17 15:15     .19

7 rows selected.

To view the speed of backup jobs:


SQL> COL in_sec FORMAT a10
SQL> COL out_sec FORMAT a10
SQL> COL TIME_TAKEN_DISPLAY FORMAT a10
SQL> SELECT SESSION_KEY,
  2         OPTIMIZED,
  3         COMPRESSION_RATIO,
  4         INPUT_BYTES_PER_SEC_DISPLAY in_sec,
  5         OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
  6         TIME_TAKEN_DISPLAY
  7  FROM   V$RMAN_BACKUP_JOB_DETAILS
  8  ORDER BY SESSION_KEY;

SESSION_KEY OPT COMPRESSION_RATIO IN_SEC     OUT_SEC    TIME_TAKEN
----------- --- ----------------- ---------- ---------- ----------
        200 NO                  1     0.00K      0.00K  00:00:01
        203 NO         1.26316532     5.12M      4.05M  00:58:19
        225 NO         1.27060047   447.62K    352.29K  03:06:22
        251 NO                  1    15.82M     15.82M  00:00:36
        256 NO         1.28293063    29.05M     22.64M  00:02:50
        261 NO         3.74179062     8.42M      2.25M  00:29:21
        298 NO                  1    14.82M      0.00K  00:11:13

7 rows selected.

To view The size of backup in Rman


SQL> COL in_size  FORMAT a10
SQL> COL out_size FORMAT a10
SQL> SELECT SESSION_KEY,
  2         INPUT_TYPE,
  3         COMPRESSION_RATIO,
  4         INPUT_BYTES_DISPLAY in_size,
  5         OUTPUT_BYTES_DISPLAY out_size
  6  FROM   V$RMAN_BACKUP_JOB_DETAILS
  7  ORDER BY SESSION_KEY;

SESSION_KEY INPUT_TYPE    COMPRESSION_RATIO IN_SIZE    OUT_SIZE
----------- ------------- ----------------- ---------- ----------
        200 DB FULL                       1     0.00K      0.00K
        203 DB FULL              1.26316532    17.50G     13.85G
        225 DB INCR              1.27060047     4.77G      3.76G
        251 DB INCR                       1   569.39M    569.40M
        256 DB FULL              1.28293063     4.82G      3.76G
        261 DB INCR              3.74179062    14.49G      3.87G
        298 DB FULL                       1     9.74G      0.00K

7 rows selected.

To View determine the encryption status of backup pieces:

The ENCRYPTED column of the V$BACKUP_PIECE and V$RMAN_BACKUP_PIECE views indicates whether a backup piece is encrypted (YES) or unencrypted (NO).
 

SQL> COL BS_REC    FORMAT 99999
SQL> COL BP_REC    FORMAT 99999
SQL> COL MB        FORMAT 9999999
SQL> COL ENCRYPTED FORMAT A7
SQL> COL TAG       FORMAT A25
SQL>
SQL> SELECT S.RECID AS "BS_REC", P.RECID AS "BP_REC", P.ENCRYPTED,
  2         P.TAG, P.HANDLE AS "MEDIA_HANDLE"
  3  FROM   V$BACKUP_PIECE P, V$BACKUP_SET S
  4  WHERE  P.SET_STAMP = S.SET_STAMP
  5  AND    P.SET_COUNT = S.SET_COUNT;

BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------

    75     75 NO      TAG20170210T115438


    78     78 NO      TAG20170210T150054
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\BACKUPSET\2017_02_10\O1_MF_ANNNN

_TAG20170210T150054_D9V22KNB_.BKP

    80     80 NO      TAG20170220T151249
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\AUTOBACKUP\2017_02_20\O1_MF_S_93


BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------

6457969_DBOGJTHZ_.BKP

    82     82 NO      TAG20170502T161640
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\BACKUPSET\2017_05_02\O1_MF_ANNNN

_TAG20170502T161640_DJJRWNXZ_.BKP

    83     83 NO      TAG20170502T161640
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\BACKUPSET\2017_05_02\O1_MF_ANNNN

_TAG20170502T161640_DJJRWRSV_.BKP

BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------


    84     84 NO      TAG20170502T162002
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\BACKUPSET\2017_05_02\O1_MF_NNNDF

_TAG20170502T162002_DJJS2VG6_.BKP

    85     85 NO      TAG20170502T162248
C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\DHONI\AUTOBACKUP\2017_05_02\O1_MF_S_94

2942168_DJJS81O9_.BKP


BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------

    86     86 NO      TAG20170502T163738
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_39S38AQI_1_1_DHONI_9429

43058_105_1

    87     87 NO      TAG20170502T163739
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3AS38AQK_1_1_DHONI_9429

43060_106_1

    88     88 NO      TAG20170502T163739

BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------

C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3BS38AT9_1_1_DHONI_9429

43145_107_1

    89     89 NO      TAG20170502T163908
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3CS38ATC_1_1_DHONI_9429

43148_108_1

    90     90 NO      INCRE_BACKUP_1
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3DS38B0S_1_1_DHONI_9429


BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------

43260_109_1

    91     91 NO      INCRE_BACKUP_1
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3ES38C3D_1_1_DHONI_9429

44365_110_1

    92     92 NO      INCRE_BACKUP_1
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3FS38C3K_1_1_DHONI_9429

44372_111_1

BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------


    93     93 NO      INCRE_CULMUL
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3GS38CF7_1_1_DHONI_9429

44743_112_1

    94     94 NO      INCRE_CULMUL
C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\DH_3HS38CH9_1_1_DHONI_9429

44809_113_1


16 rows selected.

SQL>

Source: Oracle,Google