Performing recovery from Loss of an Inactive Redo Log Group Member

Backup & Recovery / 15/07/2022

Hoje vamos simular a perda e recuperação de um membro de Online Redo Log de um grupo com status INACTIVE, onde o banco de dados está operacional, com o recurso de OMF habilitado.

Reconhecendo nosso ambiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@oel8 ~]$ rman target /
  
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Sep 20 20:55:00 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> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
  
using target database control file instead of recovery catalog
NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
RMANDB    READ WRITE           ARCHIVELOG

Realizando um backup FULL do ambiente apenas por prevenção (mas o mesmo não será usado na recuperação):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
RMAN> BACKUP DATABASE;
  
Starting backup at 2021-09-20:20:55: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=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=00002 name=/oracle/dados/RMANDB/datafile/ts_hipo_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-09-20:20:55:35
channel ORA_DISK_1: finished piece 1 at 2021-09-20:20:57:30
piece handle=/oracle/fra/RMANDB/backupset/2021_09_20/o1_mf_nnndf_TAG20210920T205534_jnl7vth5_.bkp tag=TAG20210920T205534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2021-09-20:20:57:30
  
Starting Control File and SPFILE Autobackup at 2021-09-20:20:57:30
piece handle=/oracle/fra/RMANDB/autobackup/2021_09_20/o1_mf_s_1083790650_jnl7zc9h_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-09-20:20:57:33

Para facilitar as consultas que faremos ao longo do artigo, vamos salvar a consulta abaixo em um arquivo.sql:

1
2
3
4
5
6
7
[oracle@oel8 ~]$ cat log.sql
SET LIN 200
col MEMBER for a60
  
SELECT G.GROUP#, G.STATUS G_STATUS, M.STATUS M_STATUS, SUBSTR( MEMBER, INSTR(MEMBER,'o1')) MEMBER
FROM V$LOG G, V$LOGFILE M WHERE G.GROUP#=M.GROUP# ORDER BY 1;
[oracle@oel8 ~]$

Consultando membros:

1
2
3
4
5
6
7
8
9
10
11
12
SQL> @log
 
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 INACTIVE                 o1_mf_2_jjml062p_.log
         2 INACTIVE                 o1_mf_2_jjml0cpd_.log
         3 INACTIVE                 o1_mf_3_jjml0g5l_.log
         3 INACTIVE                 o1_mf_3_jjml0hw9_.log
 
6 rows selected.

Vamos analisar qual é o Grupo que está com o status em INACTIVE. No meu caso, usarei o grupo 3 como referência:

1
2
3
4
5
6
7
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG ORDER BY 1;
  
    GROUP#    MEMBERS STATUS           ARC
---------- ---------- ---------------- ---
         1          2 CURRENT          NO
         2          2 INACTIVE         YES
         3          2 INACTIVE         YES

Consultando os membros do grupo 1:

1
2
3
4
5
6
SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=3;
  
MEMBER
------------------------------------------------------------
/oracle/dados/RMANDB/onlinelog/o1_mf_3_jjml0g5l_.log
/oracle/fra/RMANDB/onlinelog/o1_mf_3_jjml0hw9_.log

Removendo apenas um membro do grupo:

1
2
3
SQL> ! rm -rf /oracle/dados/RMANDB/onlinelog/o1_mf_3_jjml0g5l_.log
  
SQL>

Entretanto, até o momento o alert do banco não reconheceu o evento:

1
2
3
4
5
6
2021-09-20T21:01:15.807596-03:00
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 1 seq# 4 mem# 0: /oracle/dados/RMANDB/onlinelog/o1_mf_1_jjmkzz6r_.log
  Current log# 1 seq# 4 mem# 1: /oracle/fra/RMANDB/onlinelog/o1_mf_1_jjml02kw_.log
2021-09-20T21:01:15.835633-03:00
NET  (PID:3279): Archived Log entry 171 added for T-1.S-3 ID 0xe69fcdee LAD:1

Roteando os redo logs, vemos que por ainda termos um membro preservado, a operação do banco de dados não foi prejudicada:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SQL> @log
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 INACTIVE                 o1_mf_2_jjml062p_.log
         2 INACTIVE                 o1_mf_2_jjml0cpd_.log
         3 INACTIVE                 o1_mf_3_jjml0g5l_.log
         3 INACTIVE                 o1_mf_3_jjml0hw9_.log
  
6 rows selected.
  
SQL> ALTER SYSTEM SWITCH LOGFILE;
  
System altered.
  
SQL> @log
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 ACTIVE                   o1_mf_1_jjmkzz6r_.log
         1 ACTIVE                   o1_mf_1_jjml02kw_.log
         2 CURRENT                  o1_mf_2_jjml062p_.log
         2 CURRENT                  o1_mf_2_jjml0cpd_.log
         3 INACTIVE                 o1_mf_3_jjml0g5l_.log
         3 INACTIVE                 o1_mf_3_jjml0hw9_.log
  
6 rows selected.
  
SQL> ALTER SYSTEM SWITCH LOGFILE;
  
System altered.
  
SQL> @log;
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 ACTIVE                   o1_mf_1_jjmkzz6r_.log
         1 ACTIVE                   o1_mf_1_jjml02kw_.log
         2 ACTIVE                   o1_mf_2_jjml062p_.log
         2 ACTIVE                   o1_mf_2_jjml0cpd_.log
         3 CURRENT                  o1_mf_3_jjml0g5l_.log
         3 CURRENT                  o1_mf_3_jjml0hw9_.log
  
6 rows selected.
  
SQL> ALTER SYSTEM SWITCH LOGFILE;
  
System altered.
  
SQL> @log
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 ACTIVE                   o1_mf_2_jjml062p_.log
         2 ACTIVE                   o1_mf_2_jjml0cpd_.log
         3 ACTIVE                   o1_mf_3_jjml0g5l_.log
         3 ACTIVE                   o1_mf_3_jjml0hw9_.log
  
6 rows selected.

Após o roteamento, o alert do banco de dados identifica o problema:

1
2
3
4
5
6
7
2021-09-20T21:11:48.700810-03:00
Errors in file /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_mz00_5685.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/dados/RMANDB/onlinelog/o1_mf_3_jjml0g5l_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Para realizarmos a recuperação, devemos nos certificar que o grupo em questão esteja no status de INACTIVE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> /
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 INACTIVE                 o1_mf_2_jjml062p_.log
         2 INACTIVE                 o1_mf_2_jjml0cpd_.log
         3 ACTIVE                   o1_mf_3_jjml0g5l_.log
         3 ACTIVE                   o1_mf_3_jjml0hw9_.log
  
6 rows selected.
  
SQL> /
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 INACTIVE                 o1_mf_2_jjml062p_.log
         2 INACTIVE                 o1_mf_2_jjml0cpd_.log
         3 INACTIVE                 o1_mf_3_jjml0g5l_.log
         3 INACTIVE                 o1_mf_3_jjml0hw9_.log
  
6 rows selected.

Com o comando abaixo, nos bastidores o Oracle criará novos membros com destino conforme configuração do OMF:

1
2
3
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
  
Database altered.

Podemos observar que o status do grupo fica como UNUSED, o que é esperado, uma vez que acabou de ser criado e ainda nunca foi utilizado pelo oracle. E outro ponto importante é que no meu caso, os 2 membros criados são novos (podemos comparar os nomes antigos com os novos), inclusive o membro sobrevivente foi substituído por um novo:

1
2
3
4
5
6
7
8
9
10
11
12
SQL> @log
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 CURRENT                  o1_mf_1_jjmkzz6r_.log
         1 CURRENT                  o1_mf_1_jjml02kw_.log
         2 INACTIVE                 o1_mf_2_jjml062p_.log
         2 INACTIVE                 o1_mf_2_jjml0cpd_.log
         3 UNUSED                   o1_mf_3_jnl94jbl_.log
         3 UNUSED                   o1_mf_3_jnl94jgz_.log
  
6 rows selected.

Roteando os redo logs para confirmar que o novo grupo consegue trabalhar normalmente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> ALTER SYSTEM SWITCH LOGFILE;
  
System altered.
  
SQL> /
  
System altered.
  
SQL> @log
  
    GROUP# G_STATUS         M_STATU MEMBER
---------- ---------------- ------- ------------------------------------------------------------
         1 ACTIVE                   o1_mf_1_jjmkzz6r_.log
         1 ACTIVE                   o1_mf_1_jjml02kw_.log
         2 CURRENT                  o1_mf_2_jjml062p_.log
         2 CURRENT                  o1_mf_2_jjml0cpd_.log
         3 ACTIVE                   o1_mf_3_jnl94jbl_.log
         3 ACTIVE                   o1_mf_3_jnl94jgz_.log
  
6 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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish