{"id":6236,"date":"2021-08-18T22:50:57","date_gmt":"2021-08-18T22:50:57","guid":{"rendered":"https:\/\/swiv.com.br\/restoring-control-file-using-autobackup-when-all-of-them-are-lost\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"restoring-control-file-using-autobackup-when-all-of-them-are-lost","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/08\/18\/restoring-control-file-using-autobackup-when-all-of-them-are-lost\/","title":{"rendered":"Restoring Control File (using AUTOBACKUP) when all of them are lost"},"content":{"rendered":"\n<p>O cen\u00e1rio que vamos simular neste artigo considera que todos os control files foram perdidos, e que:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>O banco est\u00e1 rodando em ARCHIVELOG mode;<\/li><li>O backup do banco est\u00e1 dispon\u00edvel e com a op\u00e7\u00e3o AUTOBACKUP implementada;<\/li><li>O formato do AUTOBACKUP est\u00e1 definido para a FRA;<\/li><li>E os Online Redo Logs est\u00e3o dispon\u00edveis.<\/li><\/ul>\n\n\n\n<p>Reconhecendo o nosso laborat\u00f3rio:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 18 19:30:38 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL&gt; SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nNAME      OPEN_MODE            LOG_MODE\n--------- -------------------- ------------\nRMANDB    READ WRITE           ARCHIVELOG\n \nSQL&gt; SHO PARAMETER CONTROL_FILES;\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncontrol_files                        string      \/oracle\/fra\/RMANDB\/controlfile\n                                                 \/o1_mf_h8nytrty_.ctl, \/oracle\/\n                                                 dados\/RMANDB\/o1_mf_h8nytrhd_.c\n                                                 tl\n<\/pre><\/div>\n\n\n<p>Realizando o backup do banco de dados:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 18 19:31:21 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&gt; BACKUP DATABASE;\n \nStarting backup at 2021-08-18:19:31:26\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=61 device type=DISK\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-08-18:19:31:27\nchannel ORA_DISK_1: finished piece 1 at 2021-08-18:19:32:32\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_18\/o1_mf_nnndf_TAG20210818T193126_jkv2kzq7_.bkp tag=TAG20210818T193126 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05\nFinished backup at 2021-08-18:19:32:32\n \nStarting Control File and SPFILE Autobackup at 2021-08-18:19:32:32\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_18\/o1_mf_s_1080934352_jkv2n19z_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-18:19:32:35\n<\/pre><\/div>\n\n\n<p>Deletando todos os control files:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; host &#039;rm \/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl \/oracle\/dados\/RMANDB\/o1_mf_h8nytrhd_.ctl&#039;;\n \nhost command complete\n \nRMAN&gt; host &#039;ls -lthr \/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl \/oracle\/dados\/RMANDB\/o1_mf_h8nytrhd_.ctl&#039;;\n \nls: cannot access &#039;\/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl&#039;: No such file or directory\nls: cannot access &#039;\/oracle\/dados\/RMANDB\/o1_mf_h8nytrhd_.ctl&#039;: No such file or directory\nhost command complete\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-06135: error executing host command: Additional information: 512\n<\/pre><\/div>\n\n\n<p>Nessa etapa, nossa inst\u00e2ncia j\u00e1 identificou o problema:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT NAME FROM V$DATABASE;\nSELECT NAME FROM V$DATABASE\n                 *\nERROR at line 1:\nORA-00210: cannot open the specified control file\nORA-00202: control file: &#039;\/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl&#039;\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 3\n<\/pre><\/div>\n\n\n<p>E ao tentar logar no RMAN devido a falta de control file:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 18 19:36:49 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-00554: initialization of internal recovery manager package failed\nRMAN-06003: Oracle error from target database:\nORA-00210: cannot open the specified control file\nORA-00202: control file: &#039;\/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl&#039;\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 3\n<\/pre><\/div>\n\n\n<p>Abortando a inst\u00e2ncia (n\u00e3o \u00e9 poss\u00edvel realizar um shutdown \\&#8221;limpo\\&#8221; quando um contro file est\u00e1 indispon\u00edvel):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SHU ABORT;\nORACLE instance shut down.\nSQL&gt;\n<\/pre><\/div>\n\n\n<p>Definindo o DBID do nosso banco e restaurando os control files:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; SET DBID 3825250984;\n \nexecuting command: SET DBID\n \nRMAN&gt; STARTUP NOMOUNT;\n \nOracle instance started\n \nTotal System Global Area    1610612016 bytes\n \nFixed Size                     8658224 bytes\nVariable Size                520093696 bytes\nDatabase Buffers            1073741824 bytes\nRedo Buffers                   8118272 bytes\n \nRMAN&gt; RESTORE CONTROLFILE FROM AUTOBACKUP;\n \nStarting restore at 2021-08-18:19:42:06\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=38 device type=DISK\n \nrecovery area destination: \/oracle\/fra\ndatabase name (or database unique name) used for search: RMANDB\nchannel ORA_DISK_1: AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_18\/o1_mf_s_1080934352_jkv2n19z_.bkp found in the recovery area\nchannel ORA_DISK_1: looking for AUTOBACKUP on day: 20210818\nchannel ORA_DISK_1: restoring control file from AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_18\/o1_mf_s_1080934352_jkv2n19z_.bkp\nchannel ORA_DISK_1: control file restore from AUTOBACKUP complete\noutput file name=\/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl\noutput file name=\/oracle\/dados\/RMANDB\/o1_mf_h8nytrhd_.ctl\nFinished restore at 2021-08-18:19:42:09\n<\/pre><\/div>\n\n\n<p>Ap\u00f3s isso podemos fazer as etapas para o recover:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; SHUTDOWN IMMEDIATE;\n \nOracle instance shut down\n \nRMAN&gt; STARTUP MOUNT;\n \nconnected to target database (not started)\nOracle instance started\ndatabase mounted\n \nTotal System Global Area    1610612016 bytes\n \nFixed Size                     8658224 bytes\nVariable Size                520093696 bytes\nDatabase Buffers            1073741824 bytes\nRedo Buffers                   8118272 bytes\n \nRMAN&gt; RECOVER DATABASE;\n \nStarting recover at 2021-08-18:19:44:49\nStarting implicit crosscheck backup at 2021-08-18:19:44:49\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=44 device type=DISK\nCrosschecked 1 objects\nFinished implicit crosscheck backup at 2021-08-18:19:44:50\n \nStarting implicit crosscheck copy at 2021-08-18:19:44:50\nusing channel ORA_DISK_1\nFinished implicit crosscheck copy at 2021-08-18:19:44:50\n \nsearching for all files in the recovery area\ncataloging files...\ncataloging done\n \nList of Cataloged Files\n=======================\nFile Name: \/oracle\/fra\/RMANDB\/autobackup\/2021_08_18\/o1_mf_s_1080934352_jkv2n19z_.bkp\n \nusing channel ORA_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 12 is already on disk as file \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log\narchived log file name=\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log thread=1 sequence=12\nmedia recovery complete, elapsed time: 00:00:00\nFinished recover at 2021-08-18:19:44:51\n<\/pre><\/div>\n\n\n<p>Abrindo o banco em RESETLOGS:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; ALTER DATABASE OPEN RESETLOGS;\n\nStatement processed\n<\/pre><\/div>\n\n\n<p>Validando ambiente:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRMAN&gt; SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n\nNAME      OPEN_MODE            LOG_MODE\n--------- -------------------- ------------\nRMANDB    READ WRITE           ARCHIVELOG\n<\/pre><\/div>\n\n\n<p>Apenas para constar, uma varia\u00e7\u00e3o desse procedimento \u00e9 caso o nosso banco esteja em NOARCHIVELOG mode. Neste caso, no momento do recover, executamos com a op\u00e7\u00e3o NOREDO, conforme exemplo abaixo:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRECOVER DATABASE NOREDO;\n<\/pre><\/div>\n\n\n<p>E outra varia\u00e7\u00e3o: caso o AUTOBACKUP esteja configurado em uma \u00e1rea fora da FRA, devemos tratar como o exemplo abaixo:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nRUN {\n    SET DBID XXXXX;\n    SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO &#039;\/oracle\/controlfiles\/autobackup_%F&#039;;\n    RESTORE CONTROLFILE FROM AUTOBACKUP;\n}\n<\/pre><\/div>\n\n\n<p>Obs: 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":"<p>O cen\u00e1rio que vamos simular neste artigo considera que todos os control files foram perdidos, e que: O banco est\u00e1 rodando em ARCHIVELOG mode; O backup do banco est\u00e1 dispon\u00edvel e com a op\u00e7\u00e3o AUTOBACKUP implementada; O formato do AUTOBACKUP est\u00e1 definido para a FRA; E os Online Redo Logs est\u00e3o dispon\u00edveis. Reconhecendo o nosso [&hellip;]<\/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-6236","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\/6236","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=6236"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6236\/revisions"}],"predecessor-version":[{"id":9057,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6236\/revisions\/9057"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6236"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6236"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6236"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}