{"id":5946,"date":"2021-07-25T17:48:11","date_gmt":"2021-07-25T17:48:11","guid":{"rendered":"https:\/\/swiv.com.br\/performing-complete-recovery-of-the-whole-non-cdb-database-in-archivelog-mode\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"performing-complete-recovery-of-the-whole-non-cdb-database-in-archivelog-mode","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/07\/25\/performing-complete-recovery-of-the-whole-non-cdb-database-in-archivelog-mode\/","title":{"rendered":"Performing Complete Recovery of the Whole Non-CDB Database in ARCHIVELOG Mode"},"content":{"rendered":"\n

Neste artigo, vamos simular a perda de datafiles do nosso banco non-cdb (system e non-system datafiles) e a recupera\u00e7\u00e3o completa do banco de dados (que est\u00e1 rodando em archivelog mode). Vamos tomar conhecimento do nosso laborat\u00f3rio:<\/p>\n\n\n

\n[oracle@oel8 datafile]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Sat Jul 24 00:07:03 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> SELECT NAME,OPEN_MODE,LOG_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            LOG_MODE     CDB\n--------- -------------------- ------------ ---\nRMANDB    READ WRITE           ARCHIVELOG   NO\n<\/pre><\/div>\n\n\n
Realizando um backup Full do nosso banco de dados:<\/p>\n\n\n
\n[oracle@oel8 datafile]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Sat Jul 24 00:07:39 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> BACKUP DATABASE;\n \nStarting backup at 2021-07-24:00:08:11\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=78 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_jhq037qr_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq037wr_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq037yg_.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_jhq0380t_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-07-24:00:08:12\nchannel ORA_DISK_1: finished piece 1 at 2021-07-24:00:08:47\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35\nFinished backup at 2021-07-24:00:08:47\n \nStarting Control File and SPFILE Autobackup at 2021-07-24:00:08:47\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_07_24\/o1_mf_s_1078704527_jhq11zgw_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-07-24:00:08:48\n<\/pre><\/div>\n\n\n
Criando uma tabela simples ap\u00f3s este backup, e gerando alguns archives:<\/p>\n\n\n
\nRMAN> CREATE TABLE BSS.ASWAN (DESCRICAO VARCHAR2(50));\n \nStatement processed\n \nRMAN> INSERT INTO BSS.ASWAN (DESCRICAO) VALUES ('TESTE DO BRUNO');\n \nStatement processed\n \nRMAN> COMMIT;\n \nStatement processed\n \nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n<\/pre><\/div>\n\n\n
Realizando um backup dos archived redo logs (que ser\u00e3o armazenados em um backupset diferente do backup full). Ap\u00f3s o backup desses arquivos, os mesmos ser\u00e3o removidos:<\/p>\n\n\n
\nRMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;\n \nStarting backup at 2021-07-24:00:11:21\ncurrent log archived\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting archived log backup set\nchannel ORA_DISK_1: specifying archived log(s) in backup set\ninput archived log thread=1 sequence=13 RECID=168 STAMP=1078703629\ninput archived log thread=1 sequence=14 RECID=172 STAMP=1078703629\ninput archived log thread=1 sequence=15 RECID=169 STAMP=1078703629\ninput archived log thread=1 sequence=16 RECID=170 STAMP=1078703629\ninput archived log thread=1 sequence=17 RECID=171 STAMP=1078703629\ninput archived log thread=1 sequence=18 RECID=173 STAMP=1078703724\ninput archived log thread=1 sequence=19 RECID=174 STAMP=1078704602\ninput archived log thread=1 sequence=20 RECID=175 STAMP=1078704605\ninput archived log thread=1 sequence=21 RECID=176 STAMP=1078704609\ninput archived log thread=1 sequence=22 RECID=177 STAMP=1078704682\nchannel ORA_DISK_1: starting piece 1 at 2021-07-24:00:11:22\nchannel ORA_DISK_1: finished piece 1 at 2021-07-24:00:11:23\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp tag=TAG20210724T001122 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nchannel ORA_DISK_1: deleting archived log(s)\narchived log file name=\/oracle\/archives\/1_13_1078693497.dbf RECID=168 STAMP=1078703629\narchived log file name=\/oracle\/archives\/1_14_1078693497.dbf RECID=172 STAMP=1078703629\narchived log file name=\/oracle\/archives\/1_15_1078693497.dbf RECID=169 STAMP=1078703629\narchived log file name=\/oracle\/archives\/1_16_1078693497.dbf RECID=170 STAMP=1078703629\narchived log file name=\/oracle\/archives\/1_17_1078693497.dbf RECID=171 STAMP=1078703629\narchived log file name=\/oracle\/archives\/1_18_1078693497.dbf RECID=173 STAMP=1078703724\narchived log file name=\/oracle\/archives\/1_19_1078693497.dbf RECID=174 STAMP=1078704602\narchived log file name=\/oracle\/archives\/1_20_1078693497.dbf RECID=175 STAMP=1078704605\narchived log file name=\/oracle\/archives\/1_21_1078693497.dbf RECID=176 STAMP=1078704609\narchived log file name=\/oracle\/archives\/1_22_1078693497.dbf RECID=177 STAMP=1078704682\nFinished backup at 2021-07-24:00:11:23\n \nStarting Control File and SPFILE Autobackup at 2021-07-24:00:11:23\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_07_24\/o1_mf_s_1078704683_jhq16w12_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-07-24:00:11:24\n<\/pre><\/div>\n\n\n
Vamos simular a remo\u00e7\u00e3o dos datafiles do banco:<\/p>\n\n\n
\nRMAN> REPORT SCHEMA;\n \nReport of database schema for database with db_unique_name RMANDB\n \nList of Permanent Datafiles\n===========================\nFile Size(MB) Tablespace           RB segs Datafile Name\n---- -------- -------------------- ------- ------------------------\n1    880      SYSTEM               YES     \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq037qr_.dbf\n2    120      TS_HIPO_CATALOG      NO      \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\n3    730      SYSAUX               NO      \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq037wr_.dbf\n4    305      UNDOTBS1             YES     \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq037yg_.dbf\n5    200      TS_CORTEX_CATALOG    NO      \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\n7    16       USERS                NO      \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_jhq0380t_.dbf\n \nList of Temporary Files\n=======================\nFile Size(MB) Tablespace           Maxsize(MB) Tempfile Name\n---- -------- -------------------- ----------- --------------------\n1    69       TEMP                 32767       \/oracle\/dados\/RMANDB\/datafile\/o1_mf_temp_h8nyvt1f_.tmp\n \nRMAN> host 'rm \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq037qr_.dbf \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq037wr_.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq037yg_.dbf \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_jhq0380t_.dbf';\n \nhost command complete\n<\/pre><\/div>\n\n\n
Baixando a inst\u00e2ncia e montando o banco:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nORA-01116: error in opening database file 1\nORA-01110: data file 1: '\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq037qr_.dbf'\nORA-27041: unable to open file\nLinux-x86_64 Error: 2: No such file or directory\nAdditional information: 3\nSQL> SHU ABORT;\nORACLE instance shut down.\nSQL> STARTUP MOUNT;\nORACLE instance started.\n \nTotal System Global Area 1610612016 bytes\nFixed Size                  8658224 bytes\nVariable Size             520093696 bytes\nDatabase Buffers         1073741824 bytes\nRedo Buffers                8118272 bytes\nDatabase mounted.\n<\/pre><\/div>\n\n\n
Antes de disparar o restore do banco, podemos validar a consist\u00eacia do backup que ser\u00e1 usado na opera\u00e7\u00e3o atrav\u00e9s do comando abaixo. Obviamente, quanto maior o banco e seu backup, mais tempo esta valida\u00e7\u00e3o durar\u00e1.<\/p>\n\n\n
\nRMAN> RESTORE DATABASE VALIDATE;\n \nStarting restore at 2021-07-24:00:22:04\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=36 device type=DISK\n \nchannel ORA_DISK_1: starting validation of datafile backup set\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nFinished restore at 2021-07-24:00:22:06\n<\/pre><\/div>\n\n\n
Disparando restore do banco:<\/p>\n\n\n
\nRMAN> RESTORE DATABASE;\n \nStarting restore at 2021-07-24:00:23:54\nusing channel ORA_DISK_1\n \nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00001 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq037qr_.dbf\nchannel ORA_DISK_1: restoring datafile 00002 to \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq037wr_.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq037yg_.dbf\nchannel ORA_DISK_1: restoring datafile 00005 to \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_jhq0380t_.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_nnndf_TAG20210724T000811_jhq10wjb_.bkp tag=TAG20210724T000811\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:35\nFinished restore at 2021-07-24:00:24:29\n<\/pre><\/div>\n\n\n
Disparando comando de recover do banco. O interessante aqui \u00e9 que, como deletamos os nossos archived redo logs, no processo de recover ser\u00e1 realizado o restore desses arquivos para permitir a recupera\u00e7\u00e3o dos datafiles:<\/p>\n\n\n
\nRMAN> RECOVER DATABASE;\n \nStarting recover at 2021-07-24:00:28:21\nusing channel ORA_DISK_1\n \nstarting media recovery\n \nchannel ORA_DISK_1: starting archived log restore to default destination\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=19\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=20\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=21\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=22\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_24\/o1_mf_annnn_TAG20210724T001122_jhq16tdf_.bkp tag=TAG20210724T001122\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\narchived log file name=\/oracle\/archives\/1_19_1078693497.dbf thread=1 sequence=19\narchived log file name=\/oracle\/archives\/1_20_1078693497.dbf thread=1 sequence=20\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 2021-07-24:00:28:23\n<\/pre><\/div>\n\n\n
Abrindo o banco e validando a nossa tabela de teste:<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN;\n \nStatement processed\n \nRMAN> SELECT * FROM BSS.ASWAN;\n \nDESCRICAO\n--------------------------------------------------\nTESTE DO BRUNO\n<\/pre><\/div>\n\n\n
Como n\u00e3o fizemos a abertura do banco em resetlogs, o mesmo permanece com o seu ID incarnation:<\/p>\n\n\n
\nRMAN> SELECT INCARNATION#, RESETLOGS_CHANGE#, PRIOR_RESETLOGS_CHANGE#, STATUS FROM V$DATABASE_INCARNATION ORDER BY 1;\n \nINCARNATION# RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# STATUS\n------------ ----------------- ----------------------- -------\n           1                 1                       0 PARENT\n           2           1477662                       1 PARENT\n           3           3489797                 1477662 CURRENT\n<\/pre><\/div>\n\n\n
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":"
Neste artigo, vamos simular a perda de datafiles do nosso banco non-cdb (system e non-system datafiles) e a recupera\u00e7\u00e3o completa do banco de dados (que est\u00e1 rodando em archivelog mode). Vamos tomar conhecimento do nosso laborat\u00f3rio: Realizando um backup Full do nosso banco de dados: Criando uma tabela simples ap\u00f3s este backup, e gerando alguns […]<\/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-5946","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\/5946","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=5946"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5946\/revisions"}],"predecessor-version":[{"id":9076,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5946\/revisions\/9076"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5946"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5946"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5946"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}