{"id":6006,"date":"2021-07-27T22:37:09","date_gmt":"2021-07-27T22:37:09","guid":{"rendered":"https:\/\/swiv.com.br\/performing-complete-recovery-of-a-user-tablespace-to-new-location\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"performing-complete-recovery-of-a-user-tablespace-to-new-location","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/07\/27\/performing-complete-recovery-of-a-user-tablespace-to-new-location\/","title":{"rendered":"Performing Complete Recovery of a User Tablespace to New Location"},"content":{"rendered":"\n

Hoje vamos simular o processo de recupera\u00e7\u00e3o de uma user tablespace para uma localiza\u00e7\u00e3o diferente da original (vamos imaginar que este cen\u00e1rio seja necess\u00e1rio por conta de um Disk Group ou FS indispon\u00edvel). O procedimento que ser\u00e1 demonstrado foi reproduzido em um banco de dados em Archivelog mode, online (ou seja, sem necessidade de downtime do banco).<\/p>\n\n\n\n

Reconhecendo o ambiente de laborat\u00f3rio:<\/p>\n\n\n

\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Tue Jul 27 19:19:27 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 \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\/RESTORE\/system.dbf\n2    120      TS_HIPO_CATALOG      NO      \/oracle\/RESTORE\/ts_hipo_catalog.dbf\n3    730      SYSAUX               NO      \/oracle\/RESTORE\/sysaux.dbf\n4    305      UNDOTBS1             YES     \/oracle\/RESTORE\/undo.tbs\n5    200      TS_CORTEX_CATALOG    NO      \/oracle\/RESTORE\/ts_cortex_catalog.dbf\n7    16       USERS                NO      \/oracle\/RESTORE\/users.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 FULL + archives do ambiente:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE PLUS ARCHIVELOG;\n \n \nStarting backup at 2021-07-27:19:23:03\ncurrent log archived\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=62 device type=DISK\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\ninput archived log thread=1 sequence=28 RECID=187 STAMP=1078950353\ninput archived log thread=1 sequence=29 RECID=188 STAMP=1078951009\ninput archived log thread=1 sequence=30 RECID=189 STAMP=1078953236\ninput archived log thread=1 sequence=31 RECID=190 STAMP=1078953263\ninput archived log thread=1 sequence=32 RECID=191 STAMP=1079032984\nchannel ORA_DISK_1: starting piece 1 at 2021-07-27:19:23:05\nchannel ORA_DISK_1: finished piece 1 at 2021-07-27:19:23:08\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_27\/o1_mf_annnn_TAG20210727T192305_jj11t9d1_.bkp tag=TAG20210727T192305 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nFinished backup at 2021-07-27:19:23:08\n \nStarting backup at 2021-07-27:19:23:08\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\/RESTORE\/system.dbf\ninput datafile file number=00003 name=\/oracle\/RESTORE\/sysaux.dbf\ninput datafile file number=00004 name=\/oracle\/RESTORE\/undo.tbs\ninput datafile file number=00005 name=\/oracle\/RESTORE\/ts_cortex_catalog.dbf\ninput datafile file number=00002 name=\/oracle\/RESTORE\/ts_hipo_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/RESTORE\/users.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-07-27:19:23:08\nchannel ORA_DISK_1: finished piece 1 at 2021-07-27:19:24:03\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_27\/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp tag=TAG20210727T192308 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55\nFinished backup at 2021-07-27:19:24:03\n \nStarting backup at 2021-07-27:19:24:03\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=33 RECID=192 STAMP=1079033043\nchannel ORA_DISK_1: starting piece 1 at 2021-07-27:19:24:04\nchannel ORA_DISK_1: finished piece 1 at 2021-07-27:19:24:05\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_27\/o1_mf_annnn_TAG20210727T192404_jj11w45y_.bkp tag=TAG20210727T192404 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-07-27:19:24:05\n \nStarting Control File and SPFILE Autobackup at 2021-07-27:19:24:05\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_07_27\/o1_mf_s_1079033045_jj11w5rv_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-07-27:19:24:06\n<\/pre><\/div>\n\n\n
A tablespace que usaremos para este teste \u00e9 a USERS, que atualmente est\u00e1 no diret\u00f3rio “\/oracle\/RESTORE\/”. Vamos remover o seu datafile:<\/p>\n\n\n
\nRMAN> host 'rm \/oracle\/RESTORE\/users.dbf';\n \nhost command complete\n<\/pre><\/div>\n\n\n
Identificando o problema:<\/p>\n\n\n
\nRMAN> VALIDATE DATABASE;\n \nStarting validate at 2021-07-27:19:25:37\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00001 name=\/oracle\/RESTORE\/system.dbf\ninput datafile file number=00003 name=\/oracle\/RESTORE\/sysaux.dbf\ninput datafile file number=00004 name=\/oracle\/RESTORE\/undo.tbs\ninput datafile file number=00005 name=\/oracle\/RESTORE\/ts_cortex_catalog.dbf\ninput datafile file number=00002 name=\/oracle\/RESTORE\/ts_hipo_catalog.dbf\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03009: failure of validate command on ORA_DISK_1 channel at 07\/27\/2021 19:25:37\nORA-01122: database file 7 failed verification check\nORA-01110: data file 7: '\/oracle\/RESTORE\/users.dbf'\nORA-01565: error in identifying file '\/oracle\/RESTORE\/users.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
Confirmando que o datafile reportado pertence \u00e0 tablespace USERS:<\/p>\n\n\n
\nRMAN> SELECT NAME FROM V$TABLESPACE WHERE TS# = (SELECT D.TS# FROM V$DATAFILE D WHERE FILE#=7);\n \nNAME\n------------------------------\nUSERS\n<\/pre><\/div>\n\n\n
A partir disso, podemos deixar a tablespace OFFLINE, conforme abaixo:<\/p>\n\n\n
\nRMAN> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;\n\nStatement processed\n<\/pre><\/div>\n\n\n
Com o bloco run a seguir, faremos o restore da tablespace para o novo diret\u00f3rio discriminado, al\u00e9m do switch da sua nova localiza\u00e7\u00e3o\/nome para o control file. Para finalizar, o recover da tablespace:<\/p>\n\n\n
\nrun {\n    SET NEWNAME FOR DATAFILE 7 TO '\/oracle\/dados\/users.dbf';\n    RESTORE TABLESPACE USERS;\n    SWITCH DATAFILE ALL;\n    RECOVER TABLESPACE USERS;\n}\n<\/pre><\/div>\n\n\n
Log da execu\u00e7\u00e3o:<\/p>\n\n\n
\nRMAN> run {\n        SET NEWNAME FOR DATAFILE 7 TO '\/oracle\/dados\/users.dbf';\n        RESTORE TABLESPACE USERS;\n        SWITCH DATAFILE ALL;\n        RECOVER TABLESPACE USERS;\n}2> 3> 4> 5> 6>\n \nexecuting command: SET NEWNAME\n \nStarting restore at 2021-07-27:19:29:59\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 00007 to \/oracle\/dados\/users.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_27\/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_27\/o1_mf_nnndf_TAG20210727T192308_jj11tf15_.bkp tag=TAG20210727T192308\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 2021-07-27:19:30:01\n \ndatafile 7 switched to datafile copy\ninput datafile copy RECID=59 STAMP=1079033401 file name=\/oracle\/dados\/users.dbf\n \nStarting recover at 2021-07-27:19:30:01\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:00\n \nFinished recover at 2021-07-27:19:30:01\n<\/pre><\/div>\n\n\n
Agora bastar alterar a tablespace para ONLINE e validar o banco:<\/p>\n\n\n
\nRMAN> ALTER TABLESPACE USERS ONLINE;\n \nStatement processed\n \nRMAN> VALIDATE DATABASE;\n \nStarting validate at 2021-07-27:19:31:15\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00001 name=\/oracle\/RESTORE\/system.dbf\ninput datafile file number=00003 name=\/oracle\/RESTORE\/sysaux.dbf\ninput datafile file number=00004 name=\/oracle\/RESTORE\/undo.tbs\ninput datafile file number=00005 name=\/oracle\/RESTORE\/ts_cortex_catalog.dbf\ninput datafile file number=00002 name=\/oracle\/RESTORE\/ts_hipo_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/users.dbf\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:15\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n1    OK     0              16529        112644          4154887\n  File Name: \/oracle\/RESTORE\/system.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              74921\n  Index      0              13655\n  Other      0              7535\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n2    OK     0              401          15361           2733896\n  File Name: \/oracle\/RESTORE\/ts_hipo_catalog.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              150\n  Index      0              98\n  Other      0              14711\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n3    OK     0              16502        93497           4154830\n  File Name: \/oracle\/RESTORE\/sysaux.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              11529\n  Index      0              10227\n  Other      0              55182\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n4    OK     0              1            39047           4154891\n  File Name: \/oracle\/RESTORE\/undo.tbs\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              0\n  Index      0              0\n  Other      0              39039\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n5    OK     0              505          25601           2790924\n  File Name: \/oracle\/RESTORE\/ts_cortex_catalog.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              175\n  Index      0              135\n  Other      0              24785\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n7    OK     0              120          2101            3924688\n  File Name: \/oracle\/dados\/users.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              1440\n  Index      0              19\n  Other      0              501\n \nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\nincluding current control file for validation\nincluding current SPFILE in backup set\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nList of Control File and SPFILE\n===============================\nFile Type    Status Blocks Failing Blocks Examined\n------------ ------ -------------- ---------------\nSPFILE       OK     0              2\nControl File OK     0              722\nFinished validate at 2021-07-27:19:31:32\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 o processo de recupera\u00e7\u00e3o de uma user tablespace para uma localiza\u00e7\u00e3o diferente da original (vamos imaginar que este cen\u00e1rio seja necess\u00e1rio por conta de um Disk Group ou FS indispon\u00edvel). O procedimento que ser\u00e1 demonstrado foi reproduzido em um banco de dados em Archivelog mode, online (ou seja, sem necessidade de downtime […]<\/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-6006","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\/6006","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=6006"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6006\/revisions"}],"predecessor-version":[{"id":9072,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6006\/revisions\/9072"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}