Hoje vamos simular a perda e recupera\u00e7\u00e3o de um membro de Online Redo Log de um grupo com status INACTIVE, onde o banco de dados est\u00e1 operacional, com o recurso de OMF habilitado.<\/p>\n\n\n\n
Reconhecendo nosso ambiente:<\/p>\n\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Sep 20 20:55:00 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> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nusing target database control file instead of recovery catalog\nNAME OPEN_MODE LOG_MODE\n--------- -------------------- ------------\nRMANDB READ WRITE ARCHIVELOG\n<\/pre><\/div>\n\n\nRealizando um backup FULL do ambiente apenas por preven\u00e7\u00e3o (mas o mesmo n\u00e3o ser\u00e1 usado na recupera\u00e7\u00e3o):<\/p>\n\n\n
\nRMAN> BACKUP DATABASE;\n \nStarting backup at 2021-09-20:20:55: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=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=00002 name=\/oracle\/dados\/RMANDB\/datafile\/ts_hipo_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-09-20:20:55:35\nchannel ORA_DISK_1: finished piece 1 at 2021-09-20:20:57:30\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_09_20\/o1_mf_nnndf_TAG20210920T205534_jnl7vth5_.bkp tag=TAG20210920T205534 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:55\nFinished backup at 2021-09-20:20:57:30\n \nStarting Control File and SPFILE Autobackup at 2021-09-20:20:57:30\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_09_20\/o1_mf_s_1083790650_jnl7zc9h_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-09-20:20:57:33\n<\/pre><\/div>\n\n\nPara facilitar as consultas que faremos ao longo do artigo, vamos salvar a consulta abaixo em um arquivo.sql:<\/p>\n\n\n
\n[oracle@oel8 ~]$ cat log.sql\nSET LIN 200\ncol MEMBER for a60\n \nSELECT G.GROUP#, G.STATUS G_STATUS, M.STATUS M_STATUS, SUBSTR( MEMBER, INSTR(MEMBER,'o1')) MEMBER\nFROM V$LOG G, V$LOGFILE M WHERE G.GROUP#=M.GROUP# ORDER BY 1;\n[oracle@oel8 ~]$\n<\/pre><\/div>\n\n\nConsultando membros:<\/p>\n\n\n
\nSQL> @log\n\n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 INACTIVE o1_mf_2_jjml062p_.log\n 2 INACTIVE o1_mf_2_jjml0cpd_.log\n 3 INACTIVE o1_mf_3_jjml0g5l_.log\n 3 INACTIVE o1_mf_3_jjml0hw9_.log\n\n6 rows selected.\n<\/pre><\/div>\n\n\nVamos analisar qual \u00e9 o Grupo que est\u00e1 com o status em INACTIVE. No meu caso, usarei o grupo 3 como refer\u00eancia:<\/p>\n\n\n
\nSQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG ORDER BY 1;\n \n GROUP# MEMBERS STATUS ARC\n---------- ---------- ---------------- ---\n 1 2 CURRENT NO\n 2 2 INACTIVE YES\n 3 2 INACTIVE YES\n<\/pre><\/div>\n\n\nConsultando os membros do grupo 1:<\/p>\n\n\n
\nSQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=3;\n \nMEMBER\n------------------------------------------------------------\n\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log\n\/oracle\/fra\/RMANDB\/onlinelog\/o1_mf_3_jjml0hw9_.log\n<\/pre><\/div>\n\n\nRemovendo apenas um membro do grupo:<\/p>\n\n\n
\nSQL> ! rm -rf \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log\n \nSQL>\n<\/pre><\/div>\n\n\nEntretanto, at\u00e9 o momento o alert do banco n\u00e3o reconheceu o evento:<\/p>\n\n\n
\n2021-09-20T21:01:15.807596-03:00\nThread 1 advanced to log sequence 4 (LGWR switch)\n Current log# 1 seq# 4 mem# 0: \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_1_jjmkzz6r_.log\n Current log# 1 seq# 4 mem# 1: \/oracle\/fra\/RMANDB\/onlinelog\/o1_mf_1_jjml02kw_.log\n2021-09-20T21:01:15.835633-03:00\nNET (PID:3279): Archived Log entry 171 added for T-1.S-3 ID 0xe69fcdee LAD:1\n<\/pre><\/div>\n\n\nRoteando os redo logs, vemos que por ainda termos um membro preservado, a opera\u00e7\u00e3o do banco de dados n\u00e3o foi prejudicada:<\/p>\n\n\n
\nSQL> @log\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 INACTIVE o1_mf_2_jjml062p_.log\n 2 INACTIVE o1_mf_2_jjml0cpd_.log\n 3 INACTIVE o1_mf_3_jjml0g5l_.log\n 3 INACTIVE o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> @log\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 ACTIVE o1_mf_1_jjmkzz6r_.log\n 1 ACTIVE o1_mf_1_jjml02kw_.log\n 2 CURRENT o1_mf_2_jjml062p_.log\n 2 CURRENT o1_mf_2_jjml0cpd_.log\n 3 INACTIVE o1_mf_3_jjml0g5l_.log\n 3 INACTIVE o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> @log;\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 ACTIVE o1_mf_1_jjmkzz6r_.log\n 1 ACTIVE o1_mf_1_jjml02kw_.log\n 2 ACTIVE o1_mf_2_jjml062p_.log\n 2 ACTIVE o1_mf_2_jjml0cpd_.log\n 3 CURRENT o1_mf_3_jjml0g5l_.log\n 3 CURRENT o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> @log\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 ACTIVE o1_mf_2_jjml062p_.log\n 2 ACTIVE o1_mf_2_jjml0cpd_.log\n 3 ACTIVE o1_mf_3_jjml0g5l_.log\n 3 ACTIVE o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n<\/pre><\/div>\n\n\nAp\u00f3s o roteamento, o alert do banco de dados identifica o problema:<\/p>\n\n\n
\n2021-09-20T21:11:48.700810-03:00\nErrors in file \/oracle\/18.0.0\/base\/diag\/rdbms\/rmandb\/RMANDB\/trace\/RMANDB_mz00_5685.trc:\nORA-00313: open failed for members of log group 3 of thread 1\nORA-00312: online log 3 thread 1: '\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log'\nORA-27037: unable to obtain file status\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 7\n<\/pre><\/div>\n\n\nPara realizarmos a recupera\u00e7\u00e3o, devemos nos certificar que o grupo em quest\u00e3o esteja no status de INACTIVE:<\/p>\n\n\n
\nSQL> \/\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 INACTIVE o1_mf_2_jjml062p_.log\n 2 INACTIVE o1_mf_2_jjml0cpd_.log\n 3 ACTIVE o1_mf_3_jjml0g5l_.log\n 3 ACTIVE o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n \nSQL> \/\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 INACTIVE o1_mf_2_jjml062p_.log\n 2 INACTIVE o1_mf_2_jjml0cpd_.log\n 3 INACTIVE o1_mf_3_jjml0g5l_.log\n 3 INACTIVE o1_mf_3_jjml0hw9_.log\n \n6 rows selected.\n<\/pre><\/div>\n\n\nCom o comando abaixo, nos bastidores o Oracle criar\u00e1 novos membros com destino conforme configura\u00e7\u00e3o do OMF:<\/p>\n\n\n
\nSQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nPodemos observar que o status do grupo fica como UNUSED, o que \u00e9 esperado, uma vez que acabou de ser criado e ainda nunca foi utilizado pelo oracle. E outro ponto importante \u00e9 que no meu caso, os 2 membros criados s\u00e3o novos (podemos comparar os nomes antigos com os novos), inclusive o membro sobrevivente foi substitu\u00eddo por um novo:<\/p>\n\n\n
\nSQL> @log\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 CURRENT o1_mf_1_jjmkzz6r_.log\n 1 CURRENT o1_mf_1_jjml02kw_.log\n 2 INACTIVE o1_mf_2_jjml062p_.log\n 2 INACTIVE o1_mf_2_jjml0cpd_.log\n 3 UNUSED o1_mf_3_jnl94jbl_.log\n 3 UNUSED o1_mf_3_jnl94jgz_.log\n \n6 rows selected.\n<\/pre><\/div>\n\n\nRoteando os redo logs para confirmar que o novo grupo consegue trabalhar normalmente:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL> @log\n \n GROUP# G_STATUS M_STATU MEMBER\n---------- ---------------- ------- ------------------------------------------------------------\n 1 ACTIVE o1_mf_1_jjmkzz6r_.log\n 1 ACTIVE o1_mf_1_jjml02kw_.log\n 2 CURRENT o1_mf_2_jjml062p_.log\n 2 CURRENT o1_mf_2_jjml0cpd_.log\n 3 ACTIVE o1_mf_3_jnl94jbl_.log\n 3 ACTIVE o1_mf_3_jnl94jgz_.log\n \n6 rows selected.\n \nSQL>\n<\/pre><\/div>\n\n\nObs: 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":"
Hoje vamos simular a perda e recupera\u00e7\u00e3o de um membro de Online Redo Log de um grupo com status INACTIVE, onde o banco de dados est\u00e1 operacional, com o recurso de OMF habilitado. Reconhecendo nosso ambiente: Realizando um backup FULL do ambiente apenas por preven\u00e7\u00e3o (mas o mesmo n\u00e3o ser\u00e1 usado na recupera\u00e7\u00e3o): Para facilitar […]<\/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-6486","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\/6486","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=6486"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6486\/revisions"}],"predecessor-version":[{"id":9043,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6486\/revisions\/9043"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}