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:
[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):
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:
[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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.