{"id":3703,"date":"2021-04-11T08:33:46","date_gmt":"2021-04-11T08:33:46","guid":{"rendered":"https:\/\/swiv.com.br\/understanding-how-vrman_backup_job_details-represents-its-data\/"},"modified":"2026-05-27T20:02:33","modified_gmt":"2026-05-27T19:02:33","slug":"understanding-how-vrman_backup_job_details-represents-its-data","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/11\/understanding-how-vrman_backup_job_details-represents-its-data\/","title":{"rendered":"Understanding how V$RMAN_BACKUP_JOB_DETAILS represents its data"},"content":{"rendered":"\n

O Oracle nos fornece mais essa view, com informa\u00e7\u00f5es sobre os Jobs RMAN, e neste artigo vamos tentar simular o seu uso. Executando a query abaixo temos os detalhes das \u00faltimas 24 horas, para diminuirmos a massa de dados:<\/p>\n\n\n

\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Sun Apr 11 05:13:56 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> col S_KEY format a4\ncol STATUS format a11\nSELECT TO_CHAR(SESSION_KEY) S_KEY, INPUT_TYPE, STATUS, TO_CHAR(START_TIME,'dd\/mm\/yy hh24:mi') START_TIME, TO_CHAR(END_TIME,'dd\/mm\/yy hh24:mi') END_TIME,\nELAPSED_SECONDS\/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2\n \nS_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS\n---- ------------- ----------- -------------- -------------- ----------\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n<\/pre><\/div>\n\n\n
Executando um backup de tablespace:<\/p>\n\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 11 05:15:25 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> BACKUP TABLESPACE USERS TAG 'BSS';\n \nStarting backup at 2021-04-11:05:15:35\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=71 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-04-11:05:15:36\nchannel ORA_DISK_1: finished piece 1 at 2021-04-11:05:15:37\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d187z_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-04-11:05:15:37\n \nStarting Control File and SPFILE Autobackup at 2021-04-11:05:15:37\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564537_j75d19pm_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-04-11:05:15:38\n \nRMAN>\n<\/pre><\/div>\n\n\n
Um novo registro \u00e9 exibido na consulta da view:<\/p>\n\n\n
\nSQL> col S_KEY format a4\ncol STATUS format a11\nSELECT TO_CHAR(SESSION_KEY) S_KEY, INPUT_TYPE, STATUS, TO_CHAR(START_TIME,'dd\/mm\/yy hh24:mi') START_TIME, TO_CHAR(END_TIME,'dd\/mm\/yy hh24:mi') END_TIME,\nELAPSED_SECONDS\/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2\n \nS_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS\n---- ------------- ----------- -------------- -------------- ----------\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n452  DATAFILE FULL COMPLETED   11\/04\/21 05:15 11\/04\/21 05:15        .05\n<\/pre><\/div>\n\n\n
Ap\u00f3s alguns momentos, realizamos novamente um backup da tablespace USERS:<\/p>\n\n\n
\nRMAN> BACKUP TABLESPACE USERS TAG 'BSS';\n \nStarting backup at 2021-04-11:05:18:34\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-04-11:05:18:34\nchannel ORA_DISK_1: finished piece 1 at 2021-04-11:05:18:35\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d6t76_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-04-11:05:18:35\n \nStarting Control File and SPFILE Autobackup at 2021-04-11:05:18:35\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564715_j75d6vsn_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-04-11:05:18:36\n<\/pre><\/div>\n\n\n
Observamos agora em nossa consulta que nenhum registro \u00e9 adicionado, mas sim que o \u00faltimo registro foi alterado. Isso significa que um “Backup Job” nesta view n\u00e3o \u00e9 um comando de backup, mas sim um grupo de comandos de backup do mesmo tipo (seja full ou incremental) disparado pelo RMAN. At\u00e9 podemos ver que o ELAPSED_SECONDS, que foi incrementado, representa o tempo entre o in\u00edcio da primeira execu\u00e7\u00e3o do backup at\u00e9 o final da segunda execu\u00e7\u00e3o.<\/p>\n\n\n
\nSQL> col S_KEY format a4\nSQL> col STATUS format a11\nSELECT TO_CHAR(SESSION_KEY) S_KEY, INPUT_TYPE, STATUS, TO_CHAR(START_TIME,'dd\/mm\/yy hh24:mi') START_TIME, TO_CHAR(END_TIME,'dd\/mm\/yy hh24:mi') END_TIME,\nELAPSED_SECONDS\/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL>   2\n \nS_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS\n---- ------------- ----------- -------------- -------------- ----------\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n452  DATAFILE FULL COMPLETED   11\/04\/21 05:15 11\/04\/21 05:18 3.01666667\n<\/pre><\/div>\n\n\n
Executando um backup incremental:<\/p>\n\n\n
\nRMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR_0';\n \nStarting backup at 2021-04-11:05:24:21\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting incremental level 0 datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-04-11:05:24:22\nchannel ORA_DISK_1: finished piece 1 at 2021-04-11:05:24:37\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp tag=INCR_0 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15\nFinished backup at 2021-04-11:05:24:37\n \nStarting Control File and SPFILE Autobackup at 2021-04-11:05:24:37\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069565077_j75dl5nf_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-04-11:05:24:38\n<\/pre><\/div>\n\n\n
Observando as informa\u00e7\u00f5es da view:<\/p>\n\n\n
\nSQL> col S_KEY format a4\ncol STATUS format a11\nSELECT TO_CHAR(SESSION_KEY) S_KEY, INPUT_TYPE, STATUS, TO_CHAR(START_TIME,'dd\/mm\/yy hh24:mi') START_TIME, TO_CHAR(END_TIME,'dd\/mm\/yy hh24:mi') END_TIME,\nELAPSED_SECONDS\/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2\n \nS_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS\n---- ------------- ----------- -------------- -------------- ----------\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n436  DB FULL       COMPLETED   11\/04\/21 04:40 11\/04\/21 04:56      15.15\n452  DB INCR       COMPLETED   11\/04\/21 05:15 11\/04\/21 05:24       9.05\n<\/pre><\/div>\n\n\n
Vou deletar os backups realizados at\u00e9 o momento para simular outras situa\u00e7\u00f5es:<\/p>\n\n\n
\nRMAN> DELETE BACKUPSET;\n \nusing channel ORA_DISK_1\n \nList of Backup Pieces\nBP Key  BS Key  Pc# Cp# Status      Device Type Piece Name\n------- ------- --- --- ----------- ----------- ----------\n190     190     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d187z_.bkp\n191     191     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564537_j75d19pm_.bkp\n192     192     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d6t76_.bkp\n193     193     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564715_j75d6vsn_.bkp\n194     194     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp\n195     195     1   1   AVAILABLE   DISK        \/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069565077_j75dl5nf_.bkp\n \nDo you really want to delete the above objects (enter YES or NO)? Y\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d187z_.bkp RECID=190 STAMP=1069564536\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564537_j75d19pm_.bkp RECID=191 STAMP=1069564537\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnndf_BSS_j75d6t76_.bkp RECID=192 STAMP=1069564714\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069564715_j75d6vsn_.bkp RECID=193 STAMP=1069564715\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_04_11\/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp RECID=194 STAMP=1069565062\ndeleted backup piece\nbackup piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_04_11\/o1_mf_s_1069565077_j75dl5nf_.bkp RECID=195 STAMP=1069565077\nDeleted 6 objects\n<\/pre><\/div>\n\n\n
Realizando um novo backup FULL:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE TAG 'BSS';\n \nStarting backup at 2021-04-11:05:30:00\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-04-11:05:30:00\n<\/pre><\/div>\n\n\n
Em paralelo, podemos encontrar com a consulta abaixo informa\u00e7\u00f5es valiosas sobre o backup em execu\u00e7\u00e3o:<\/p>\n\n\n
\nSQL> col SESSION_KEY for 999\ncol COMP for 99.9999\ncol IN_SPEED for a10\ncol OUT_SPEED for a10\ncol TIME_TAKEN_DISPLAY for a15\nSELECT SESSION_KEY, OPTIMIZED, round(COMPRESSION_RATIO,4) COMP, INPUT_BYTES_PER_SEC_DISPLAY IN_SPEED, OUTPUT_BYTES_PER_SEC_DISPLAY OUT_SPEED,\nTIME_TAKEN_DISPLAY FROM V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='RUNNING' ORDER BY SESSION_KEY;SQL> SQL> SQL> SQL> SQL>   2\n \nSESSION_KEY OPT     COMP IN_SPEED   OUT_SPEED  TIME_TAKEN_DISP\n----------- --- -------- ---------- ---------- ---------------\n        452 NO    1.2169     4.48M      3.68M  00:14:27\n<\/pre><\/div>\n\n\n
Tamb\u00e9m podemos usar o SQL abaixo para outros detalhes importantes:<\/p>\n\n\n
\nSQL> col SESSION_KEY for 999\ncol IN_SIZE for a15\ncol OUT_SIZE for a15\nSELECT SESSION_KEY, INPUT_TYPE, ROUND(COMPRESSION_RATIO,5) COMP_R, INPUT_BYTES_DISPLAY IN_SIZE, OUTPUT_BYTES_DISPLAY OUT_SIZE\nFROM V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='RUNNING' ORDER BY SESSION_KEY;SQL> SQL> SQL>   2\n \nSESSION_KEY INPUT_TYPE        COMP_R IN_SIZE         OUT_SIZE\n----------- ------------- ---------- --------------- ---------------\n        452 DB FULL           1.2126     5.34G           4.40G\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
O Oracle nos fornece mais essa view, com informa\u00e7\u00f5es sobre os Jobs RMAN, e neste artigo vamos tentar simular o seu uso. Executando a query abaixo temos os detalhes das \u00faltimas 24 horas, para diminuirmos a massa de dados: Executando um backup de tablespace: Um novo registro \u00e9 exibido na consulta da view: Ap\u00f3s alguns […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3703","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3703"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3703\/revisions"}],"predecessor-version":[{"id":9177,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3703\/revisions\/9177"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}