{"id":5973,"date":"2021-07-26T23:44:02","date_gmt":"2021-07-26T23:44:02","guid":{"rendered":"https:\/\/swiv.com.br\/performing-complete-recovery-of-the-whole-database-to-a-new-location\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"performing-complete-recovery-of-the-whole-database-to-a-new-location","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/07\/26\/performing-complete-recovery-of-the-whole-database-to-a-new-location\/","title":{"rendered":"Performing Complete Recovery of the Whole Database to a New Location"},"content":{"rendered":"\n

Hoje vamos simular uma situa\u00e7\u00e3o onde perdemos os datafiles do nosso banco de dados, e que o local de origem onde os datafiles eram hospedados n\u00e3o pode ser mais utilizado, e por isso, a sua restaura\u00e7\u00e3o e recupera\u00e7\u00e3o dever\u00e1 ocorrer em outro diret\u00f3rio. Neste cen\u00e1rio vamos assumir que o banco est\u00e1 rodando em archivelog mode. Vamos tomar conhecimento do laborat\u00f3rio:<\/p>\n\n\n

\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 26 20:19: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> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nNAME      OPEN_MODE            LOG_MODE\n--------- -------------------- ------------\nRMANDB    READ WRITE           ARCHIVELOG\n \nSQL> SHO PARAMETER db_create_file_dest\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ndb_create_file_dest                  string      \/oracle\/dados\n<\/pre><\/div>\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 20:21:01 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> REPORT SCHEMA;\n \nusing target database control file instead of recovery catalog\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_jhq1ybl0_.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_jhq1yblw_.dbf\n4    305      UNDOTBS1             YES     \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq1ybmg_.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_jhq1ybos_.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<\/pre><\/div>\n\n\n
Realizando um backup do banco de dados:<\/p>\n\n\n
\nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> BACKUP DATABASE PLUS ARCHIVELOG;\n \n \nStarting backup at 2021-07-26:20:24:24\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=26 RECID=185 STAMP=1078950184\ninput archived log thread=1 sequence=27 RECID=186 STAMP=1078950266\nchannel ORA_DISK_1: starting piece 1 at 2021-07-26:20:24:26\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:20:24:27\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_annnn_TAG20210726T202426_jhyk1bpw_.bkp tag=TAG20210726T202426 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-07-26:20:24:27\n \nStarting backup at 2021-07-26:20:24:27\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_jhq1ybl0_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq1yblw_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq1ybmg_.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_jhq1ybos_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-07-26:20:24:27\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:20:25:52\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp tag=TAG20210726T202427 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:25\nFinished backup at 2021-07-26:20:25:52\n \nStarting backup at 2021-07-26:20:25:53\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=28 RECID=187 STAMP=1078950353\nchannel ORA_DISK_1: starting piece 1 at 2021-07-26:20:25:53\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:20:25:54\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_annnn_TAG20210726T202553_jhyk41bh_.bkp tag=TAG20210726T202553 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-07-26:20:25:54\n \nStarting Control File and SPFILE Autobackup at 2021-07-26:20:25:54\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_07_26\/o1_mf_s_1078950354_jhyk42sf_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-07-26:20:25:55\n<\/pre><\/div>\n\n\n
Removendo os datafiles do banco de forma proposital:<\/p>\n\n\n
\nRMAN> host 'rm \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq1ybl0_.dbf \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_jhq1yblw_.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_jhq1ybmg_.dbf \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_jhq1ybos_.dbf';\n \nhost command complete\n \nRMAN> VALIDATE DATABASE;\n \nStarting validate at 2021-07-26:20:27:03\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03009: failure of validate command on ORA_DISK_1 channel at 07\/26\/2021 20:27:03\nORA-01122: database file 1 failed verification check\nORA-01110: data file 1: '\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq1ybl0_.dbf'\nORA-01565: error in identifying file '\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq1ybl0_.dbf'\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\n
Podemos constatar que apenas um tempfile foi preservado:<\/p>\n\n\n
\n[oracle@oel8 datafile]$ ll\ntotal 4276\n-rw-r-----. 1 oracle oinstall 72359936 Jul 26 20:18 o1_mf_temp_h8nyvt1f_.tmp\n[oracle@oel8 datafile]$ pwd\n\/oracle\/dados\/RMANDB\/datafile\n<\/pre><\/div>\n\n\n
Criando um diret\u00f3rio diferente que abrigar\u00e1 os novos datafiles:<\/p>\n\n\n
\n[oracle@oel8 oracle]$ pwd\n\/oracle\n[oracle@oel8 oracle]$ mkdir RESTORE\n[oracle@oel8 oracle]$ cd RESTORE\/\n[oracle@oel8 RESTORE]$ pwd\n\/oracle\/RESTORE\n<\/pre><\/div>\n\n\n
Montando o banco:<\/p>\n\n\n
\n[oracle@oel8 RESTORE]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Jul 26 20:33:22 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> SHU IMMEDIATE;\nORA-01116: error in opening database file 1\nORA-01110: data file 1: '\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_jhq1ybl0_.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
Com o comando abaixo, temos a defini\u00e7\u00e3o do nosso diret\u00f3rio onde os datafiles ser\u00e3o hospedados (atrav\u00e9s do SET NEWNAME), al\u00e9m do comando SWITCH DATAFILE que altera o control file com os novos diret\u00f3rios e nomes de datafiles:<\/p>\n\n\n
\nrun {\n    SET NEWNAME FOR DATAFILE 1 TO '\/oracle\/RESTORE\/system.dbf';\n    SET NEWNAME FOR DATAFILE 2 TO '\/oracle\/RESTORE\/ts_hipo_catalog.dbf';\n    SET NEWNAME FOR DATAFILE 3 TO '\/oracle\/RESTORE\/sysaux.dbf';\n    SET NEWNAME FOR DATAFILE 4 TO '\/oracle\/RESTORE\/undo.tbs';\n    SET NEWNAME FOR DATAFILE 5 TO '\/oracle\/RESTORE\/ts_cortex_catalog.dbf';\n    SET NEWNAME FOR DATAFILE 7 TO '\/oracle\/RESTORE\/users.dbf';\n    RESTORE DATABASE;\n    SWITCH DATAFILE ALL;\n    RECOVER DATABASE;\n}\n<\/pre><\/div>\n\n\n
Log da execu\u00e7\u00e3o:<\/p>\n\n\n
\n[oracle@oel8 RESTORE]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 20:35:13 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, not open)\n \nRMAN> run {\n        SET NEWNAME FOR DATAFILE 1 TO '\/oracle\/RESTORE\/system.dbf';\n        SET NEWNAME FOR DATAFILE 2 TO '\/oracle\/RESTORE\/ts_hipo_catalog.dbf';\n        SET NEWNAME FOR DATAFILE 3 TO '\/oracle\/RESTORE\/sysaux.dbf';\n        SET NEWNAME FOR DATAFILE 4 TO '\/oracle\/RESTORE\/undo.tbs';\n        SET NEWNAME FOR DATAFILE 5 TO '\/oracle\/RESTORE\/ts_cortex_catalog.dbf';\n        SET NEWNAME FOR DATAFILE 7 TO '\/oracle\/RESTORE\/users.dbf';\n        RESTORE DATABASE;\n        SWITCH DATAFILE ALL;\n        RECOVER DATABASE;\n}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nStarting restore at 2021-07-26:20:35:39\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=45 device type=DISK\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\/RESTORE\/system.dbf\nchannel ORA_DISK_1: restoring datafile 00002 to \/oracle\/RESTORE\/ts_hipo_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/oracle\/RESTORE\/sysaux.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/oracle\/RESTORE\/undo.tbs\nchannel ORA_DISK_1: restoring datafile 00005 to \/oracle\/RESTORE\/ts_cortex_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/oracle\/RESTORE\/users.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T202427_jhyk1d8j_.bkp tag=TAG20210726T202427\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:35\nFinished restore at 2021-07-26:20:36:15\n \ndatafile 1 switched to datafile copy\ninput datafile copy RECID=52 STAMP=1078950975 file name=\/oracle\/RESTORE\/system.dbf\ndatafile 2 switched to datafile copy\ninput datafile copy RECID=53 STAMP=1078950975 file name=\/oracle\/RESTORE\/ts_hipo_catalog.dbf\ndatafile 3 switched to datafile copy\ninput datafile copy RECID=54 STAMP=1078950975 file name=\/oracle\/RESTORE\/sysaux.dbf\ndatafile 4 switched to datafile copy\ninput datafile copy RECID=55 STAMP=1078950975 file name=\/oracle\/RESTORE\/undo.tbs\ndatafile 5 switched to datafile copy\ninput datafile copy RECID=56 STAMP=1078950975 file name=\/oracle\/RESTORE\/ts_cortex_catalog.dbf\ndatafile 7 switched to datafile copy\ninput datafile copy RECID=57 STAMP=1078950975 file name=\/oracle\/RESTORE\/users.dbf\n \nStarting recover at 2021-07-26:20:36:15\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:02\n \nFinished recover at 2021-07-26:20:36:17\n<\/pre><\/div>\n\n\n
Abrindo o banco de dados:<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN;\n \nStatement processed\n \nRMAN> 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
\u00c9 importante notarmos que apesar do banco ter sido recuperado em um diret\u00f3rio diferente do original, o par\u00e2metro “db_create_file_dest” n\u00e3o \u00e9 alterado com o novo valor. Ou seja, novos datafiles ser\u00e3o criados no diret\u00f3rio antigo, caso o mesmo esteja dispon\u00edvel:<\/p>\n\n\n
\nSQL> SHOW PARAMETER db_create_file_dest;\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ndb_create_file_dest                  string      \/oracle\/dados\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":"
Hoje vamos simular uma situa\u00e7\u00e3o onde perdemos os datafiles do nosso banco de dados, e que o local de origem onde os datafiles eram hospedados n\u00e3o pode ser mais utilizado, e por isso, a sua restaura\u00e7\u00e3o e recupera\u00e7\u00e3o dever\u00e1 ocorrer em outro diret\u00f3rio. Neste cen\u00e1rio vamos assumir que o banco est\u00e1 rodando em archivelog mode. […]<\/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-5973","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\/5973","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=5973"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5973\/revisions"}],"predecessor-version":[{"id":9074,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5973\/revisions\/9074"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5973"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5973"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5973"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}