A partir de algumas consultas SQL, podemos identificar e monitorar as operações do RMAN que estão em execução no ambiente. Para simular este caso, vamos disparar um backup do banco de dados, com 2 canais abertos, o que significa que encontraremos 2 sessões abertas, conforme exposto abaixo:
[oracle@oel8 ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 11 04:40:36 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> run{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
BACKUP DATABASE TAG 'DB_FULL';
}2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=78 device type=DISK
allocated channel: C2
channel C2: SID=64 device type=DISK
Starting backup at 2021-04-11:04:40:57
channel C1: starting full datafile backup set
channel C1: 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=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
channel C1: starting piece 1 at 2021-04-11:04:40:58
channel C2: starting full datafile backup set
channel C2: specifying datafile(s) in backup set
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
channel C2: starting piece 1 at 2021-04-11:04:40:58
Consultando as 2 sessões no banco:
[oracle@oel8 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Apr 11 04:40:39 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> SELECT S.SID, P.SPID, S.SID, S.SERIAL#, S.CLIENT_INFO FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND CLIENT_INFO LIKE 'rman%';
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3846 78 19147
rman channel=C1
64 3847 64 49644
rman channel=C2
SQL>
Além disso, a partir da consulta abaixo, podemos ter um número aproximado da evolução do backup:
SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; 2
SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
64 49644 1 103550 119680 86.52
78 19147 1 93726 137760 68.04
Ao dispararmos um backup no RMAN, também temos a possibilidade de usar o “SET COMMAND ID TO”, que facilita a identificação do RMAN job quando fazemos a consulta SQL. Vejamos o exemplo abaixo:
RMAN> run{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
SET COMMAND ID TO 'BSS';
BACKUP DATABASE TAG 'DB_FULL';
}2> 3> 4> 5>
released channel: ORA_DISK_1
allocated channel: C1
channel C1: SID=78 device type=DISK
executing command: SET COMMAND ID
Starting backup at 2021-04-11:04:55:39
channel C1: starting full datafile backup set
channel C1: 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
Adicionando a string BSS em nossa consulta SQL:
SQL> SELECT S.SID, P.SPID, S.SID, S.SERIAL#, S.CLIENT_INFO FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND CLIENT_INFO LIKE 'rman%'
OR CLIENT_INFO LIKE '%id=BSS%'; 2
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 77 63757
id=BSS
77 3356 77 63757
id=BSS
77 3358 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3360 77 63757
id=BSS
77 3362 77 63757
id=BSS
77 3366 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3509 77 63757
id=BSS
77 3370 77 63757
id=BSS
77 3372 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3372 77 63757
id=BSS
77 3375 77 63757
id=BSS
77 3377 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3377 77 63757
id=BSS
77 3380 77 63757
id=BSS
77 3382 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3384 77 63757
id=BSS
77 3386 77 63757
id=BSS
77 3388 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3390 77 63757
id=BSS
77 3392 77 63757
id=BSS
77 3394 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3396 77 63757
id=BSS
77 3398 77 63757
id=BSS
77 3400 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3402 77 63757
id=BSS
77 3404 77 63757
id=BSS
77 3406 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3408 77 63757
id=BSS
77 3414 77 63757
id=BSS
77 3412 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3416 77 63757
id=BSS
77 3418 77 63757
id=BSS
77 3420 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3813 77 63757
id=BSS
77 3457 77 63757
id=BSS
77 5082 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3464 77 63757
id=BSS
77 3466 77 63757
id=BSS
77 3468 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3470 77 63757
id=BSS
77 3472 77 63757
id=BSS
77 3474 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3487 77 63757
id=BSS
77 3489 77 63757
id=BSS
77 3520 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3522 77 63757
id=BSS
77 3524 77 63757
id=BSS
77 3526 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3845 77 63757
id=BSS
77 3530 77 63757
id=BSS
77 5117 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3534 77 63757
id=BSS
77 3536 77 63757
id=BSS
77 3543 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3846 77 63757
id=BSS
77 3847 77 63757
id=BSS
77 6331 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3815 77 63757
id=BSS
77 3765 77 63757
id=BSS
77 3777 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
77 3779 77 63757
id=BSS
77 3781 77 63757
id=BSS
77 3810 77 63757
id=BSS
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 78 19147
id=BSS,rman channel=C1
78 3356 78 19147
id=BSS,rman channel=C1
78 3358 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3360 78 19147
id=BSS,rman channel=C1
78 3362 78 19147
id=BSS,rman channel=C1
78 3366 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3509 78 19147
id=BSS,rman channel=C1
78 3370 78 19147
id=BSS,rman channel=C1
78 3372 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3372 78 19147
id=BSS,rman channel=C1
78 3375 78 19147
id=BSS,rman channel=C1
78 3377 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3377 78 19147
id=BSS,rman channel=C1
78 3380 78 19147
id=BSS,rman channel=C1
78 3382 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3384 78 19147
id=BSS,rman channel=C1
78 3386 78 19147
id=BSS,rman channel=C1
78 3388 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3390 78 19147
id=BSS,rman channel=C1
78 3392 78 19147
id=BSS,rman channel=C1
78 3394 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3396 78 19147
id=BSS,rman channel=C1
78 3398 78 19147
id=BSS,rman channel=C1
78 3400 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3402 78 19147
id=BSS,rman channel=C1
78 3404 78 19147
id=BSS,rman channel=C1
78 3406 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3408 78 19147
id=BSS,rman channel=C1
78 3414 78 19147
id=BSS,rman channel=C1
78 3412 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3416 78 19147
id=BSS,rman channel=C1
78 3418 78 19147
id=BSS,rman channel=C1
78 3420 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3813 78 19147
id=BSS,rman channel=C1
78 3457 78 19147
id=BSS,rman channel=C1
78 5082 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3464 78 19147
id=BSS,rman channel=C1
78 3466 78 19147
id=BSS,rman channel=C1
78 3468 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3470 78 19147
id=BSS,rman channel=C1
78 3472 78 19147
id=BSS,rman channel=C1
78 3474 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3487 78 19147
id=BSS,rman channel=C1
78 3489 78 19147
id=BSS,rman channel=C1
78 3520 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3522 78 19147
id=BSS,rman channel=C1
78 3524 78 19147
id=BSS,rman channel=C1
78 3526 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3845 78 19147
id=BSS,rman channel=C1
78 3530 78 19147
id=BSS,rman channel=C1
78 5117 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3534 78 19147
id=BSS,rman channel=C1
78 3536 78 19147
id=BSS,rman channel=C1
78 3543 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3846 78 19147
id=BSS,rman channel=C1
78 3847 78 19147
id=BSS,rman channel=C1
78 6331 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3815 78 19147
id=BSS,rman channel=C1
78 3765 78 19147
id=BSS,rman channel=C1
78 3777 78 19147
id=BSS,rman channel=C1
SID SPID SID SERIAL#
---------- ------------------------ ---------- ----------
CLIENT_INFO
----------------------------------------------------------------
78 3779 78 19147
id=BSS,rman channel=C1
78 3781 78 19147
id=BSS,rman channel=C1
78 3810 78 19147
id=BSS,rman channel=C1
126 rows selected.
SQL>
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.
Portuguese
English
Spanish