Understanding how V$RMAN_BACKUP_JOB_DETAILS represents its data

Backup & Recovery / 14/07/2022

O Oracle nos fornece mais essa view, com informações sobre os Jobs RMAN, e neste artigo vamos tentar simular o seu uso. Executando a query abaixo temos os detalhes das últimas 24 horas, para diminuirmos a massa de dados:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Apr 11 05:13:56 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
 
SQL> col S_KEY format a4
col STATUS format a11
SELECT 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,
ELAPSED_SECONDS/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2
 
S_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS
---- ------------- ----------- -------------- -------------- ----------
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15

Executando um backup de tablespace:

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 11 05:15:25 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> BACKUP TABLESPACE USERS TAG 'BSS';
 
Starting backup at 2021-04-11:05:15:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-11:05:15:36
channel ORA_DISK_1: finished piece 1 at 2021-04-11:05:15:37
piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d187z_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-04-11:05:15:37
 
Starting Control File and SPFILE Autobackup at 2021-04-11:05:15:37
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564537_j75d19pm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-11:05:15:38
 
RMAN>

Um novo registro é exibido na consulta da view:

SQL> col S_KEY format a4
col STATUS format a11
SELECT 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,
ELAPSED_SECONDS/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2
 
S_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS
---- ------------- ----------- -------------- -------------- ----------
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
452  DATAFILE FULL COMPLETED   11/04/21 05:15 11/04/21 05:15        .05

Após alguns momentos, realizamos novamente um backup da tablespace USERS:

RMAN> BACKUP TABLESPACE USERS TAG 'BSS';
 
Starting backup at 2021-04-11:05:18:34
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-11:05:18:34
channel ORA_DISK_1: finished piece 1 at 2021-04-11:05:18:35
piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d6t76_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-04-11:05:18:35
 
Starting Control File and SPFILE Autobackup at 2021-04-11:05:18:35
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564715_j75d6vsn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-11:05:18:36

Observamos agora em nossa consulta que nenhum registro é adicionado, mas sim que o último registro foi alterado. Isso significa que um “Backup Job” nesta view não é um comando de backup, mas sim um grupo de comandos de backup do mesmo tipo (seja full ou incremental) disparado pelo RMAN. Até podemos ver que o ELAPSED_SECONDS, que foi incrementado, representa o tempo entre o início da primeira execução do backup até o final da segunda execução.

SQL> col S_KEY format a4
SQL> col STATUS format a11
SELECT 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,
ELAPSED_SECONDS/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL>   2
 
S_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS
---- ------------- ----------- -------------- -------------- ----------
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
452  DATAFILE FULL COMPLETED   11/04/21 05:15 11/04/21 05:18 3.01666667

Executando um backup incremental:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR_0';
 
Starting backup at 2021-04-11:05:24:21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-11:05:24:22
channel ORA_DISK_1: finished piece 1 at 2021-04-11:05:24:37
piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp tag=INCR_0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2021-04-11:05:24:37
 
Starting Control File and SPFILE Autobackup at 2021-04-11:05:24:37
piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069565077_j75dl5nf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-04-11:05:24:38

Observando as informações da view:

SQL> col S_KEY format a4
col STATUS format a11
SELECT 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,
ELAPSED_SECONDS/60 MINS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE START_TIME > SYSDATE-1;SQL> SQL>   2
 
S_KE INPUT_TYPE    STATUS      START_TIME     END_TIME             MINS
---- ------------- ----------- -------------- -------------- ----------
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
436  DB FULL       COMPLETED   11/04/21 04:40 11/04/21 04:56      15.15
452  DB INCR       COMPLETED   11/04/21 05:15 11/04/21 05:24       9.05

Vou deletar os backups realizados até o momento para simular outras situações:

RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
190     190     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d187z_.bkp
191     191     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564537_j75d19pm_.bkp
192     192     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d6t76_.bkp
193     193     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564715_j75d6vsn_.bkp
194     194     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp
195     195     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069565077_j75dl5nf_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d187z_.bkp RECID=190 STAMP=1069564536
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564537_j75d19pm_.bkp RECID=191 STAMP=1069564537
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnndf_BSS_j75d6t76_.bkp RECID=192 STAMP=1069564714
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069564715_j75d6vsn_.bkp RECID=193 STAMP=1069564715
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_04_11/o1_mf_nnnd0_INCR_0_j75dkp7p_.bkp RECID=194 STAMP=1069565062
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_04_11/o1_mf_s_1069565077_j75dl5nf_.bkp RECID=195 STAMP=1069565077
Deleted 6 objects

Realizando um novo backup FULL:

RMAN> BACKUP DATABASE TAG 'BSS';
 
Starting backup at 2021-04-11:05:30:00
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-04-11:05:30:00

Em paralelo, podemos encontrar com a consulta abaixo informações valiosas sobre o backup em execução:

SQL> col SESSION_KEY for 999
col COMP for 99.9999
col IN_SPEED for a10
col OUT_SPEED for a10
col TIME_TAKEN_DISPLAY for a15
SELECT SESSION_KEY, OPTIMIZED, round(COMPRESSION_RATIO,4) COMP, INPUT_BYTES_PER_SEC_DISPLAY IN_SPEED, OUTPUT_BYTES_PER_SEC_DISPLAY OUT_SPEED,
TIME_TAKEN_DISPLAY FROM V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='RUNNING' ORDER BY SESSION_KEY;SQL> SQL> SQL> SQL> SQL>   2
 
SESSION_KEY OPT     COMP IN_SPEED   OUT_SPEED  TIME_TAKEN_DISP
----------- --- -------- ---------- ---------- ---------------
        452 NO    1.2169     4.48M      3.68M  00:14:27

Também podemos usar o SQL abaixo para outros detalhes importantes:

SQL> col SESSION_KEY for 999
col IN_SIZE for a15
col OUT_SIZE for a15
SELECT SESSION_KEY, INPUT_TYPE, ROUND(COMPRESSION_RATIO,5) COMP_R, INPUT_BYTES_DISPLAY IN_SIZE, OUTPUT_BYTES_DISPLAY OUT_SIZE
FROM V$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='RUNNING' ORDER BY SESSION_KEY;SQL> SQL> SQL>   2
 
SESSION_KEY INPUT_TYPE        COMP_R IN_SIZE         OUT_SIZE
----------- ------------- ---------- --------------- ---------------
        452 DB FULL           1.2126     5.34G           4.40G

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish