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

Vamos explorar o processo que \u00e9 poss\u00edvel adotar quando temos a necessidade de recuperar alguma(s) tablespace(s) que n\u00e3o seja(m) de sistema, com o banco rodando em Archivelog Mode, e com a inst\u00e2ncia dispon\u00edvel (ou seja, sem causar indisponibilidade).<\/p>\n\n\n\n

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

\n[oracle@oel8 RESTORE]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Jul 26 21:10:17 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 do banco de dados + archives:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE PLUS ARCHIVELOG;\n \n \nStarting backup at 2021-07-26:21:13:55\ncurrent log archived\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=81 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\nchannel ORA_DISK_1: starting piece 1 at 2021-07-26:21:13:57\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:21:13:58\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_annnn_TAG20210726T211357_jhymy58s_.bkp tag=TAG20210726T211357 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-07-26:21:13:58\n \nStarting backup at 2021-07-26:21:13:58\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-26:21:13:58\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:21:14:23\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25\nFinished backup at 2021-07-26:21:14:23\n \nStarting backup at 2021-07-26:21:14:23\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=31 RECID=190 STAMP=1078953263\nchannel ORA_DISK_1: starting piece 1 at 2021-07-26:21:14:23\nchannel ORA_DISK_1: finished piece 1 at 2021-07-26:21:14:24\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_annnn_TAG20210726T211423_jhymyzr3_.bkp tag=TAG20210726T211423 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-07-26:21:14:24\n \nStarting Control File and SPFILE Autobackup at 2021-07-26:21:14:24\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_07_26\/o1_mf_s_1078953264_jhymz199_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-07-26:21:14:25\n<\/pre><\/div>\n\n\n
Nesta etapa, vou remover o datafile conforme exposto abaixo:<\/p>\n\n\n
\nRMAN> host 'rm \/oracle\/RESTORE\/users.dbf';\n \nhost command complete\n<\/pre><\/div>\n\n\n
Vamos imaginar que n\u00f3s, como DBAs, somos avisados de algum erro no banco de dados atrav\u00e9s de monitora\u00e7\u00e3o ou at\u00e9 mesmo do pr\u00f3prio usu\u00e1rio afinal. Conseguimos identificar o problema com o VALIDATE DATABASE conforme abaixo:<\/p>\n\n\n
\nRMAN> VALIDATE DATABASE;\n \nStarting validate at 2021-07-26:21:16:16\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\/26\/2021 21:16:17\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
E atrav\u00e9s do script do exemplo a seguir, podemos identificar qual \u00e9 a tablespace afetada:<\/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
Como n\u00e3o se trata de uma tablespace de sistema, a inst\u00e2ncia se mant\u00e9m operacional:<\/p>\n\n\n
\nRMAN> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n\nINSTANCE_NAME    STATUS\n---------------- ------------\nRMANDB           OPEN\n<\/pre><\/div>\n\n\n
O processo em si \u00e9 simples. Colocando a tablespace em quest\u00e3o em modo OFFLINE:<\/p>\n\n\n
\nRMAN> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;\n\nStatement processed\n<\/pre><\/div>\n\n\n
Realizando o restore e recover da tablespace:<\/p>\n\n\n
\nRMAN> RESTORE TABLESPACE USERS;\n \nStarting restore at 2021-07-26:21:19:07\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\/RESTORE\/users.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 2021-07-26:21:19:08\n \nRMAN> RECOVER TABLESPACE USERS;\n \nStarting recover at 2021-07-26:21:19:25\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:00\n \nFinished recover at 2021-07-26:21:19:25\n<\/pre><\/div>\n\n\n
Deixando a tablespace ONLINE e validando o ambiente:<\/p>\n\n\n
\nRMAN> ALTER TABLESPACE USERS ONLINE;\n\nStatement processed\n<\/pre><\/div>\n\n
\nRMAN> RESTORE TABLESPACE USERS;\n \nStarting restore at 2021-07-26:21:19:07\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\/RESTORE\/users.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_07_26\/o1_mf_nnndf_TAG20210726T211358_jhymy6p6_.bkp tag=TAG20210726T211358\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 2021-07-26:21:19:08\n \nRMAN> RECOVER TABLESPACE USERS;\n \nStarting recover at 2021-07-26:21:19:25\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:00\n \nFinished recover at 2021-07-26:21:19:25\n \nRMAN> ALTER TABLESPACE USERS ONLINE;\n \nStatement processed\n \nRMAN> VALIDATE DATABASE;\n \nStarting validate at 2021-07-26:21:20:04\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\/RESTORE\/users.dbf\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n1    OK     0              16529        112644          4152773\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        93502           4152729\n  File Name: \/oracle\/RESTORE\/sysaux.dbf\n  Block Type Blocks Failing Blocks Processed\n  ---------- -------------- ----------------\n  Data       0              11458\n  Index      0              10273\n  Other      0              55207\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n4    OK     0              1            39047           4152777\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\/RESTORE\/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-26:21:20:07\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
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":"
Vamos explorar o processo que \u00e9 poss\u00edvel adotar quando temos a necessidade de recuperar alguma(s) tablespace(s) que n\u00e3o seja(m) de sistema, com o banco rodando em Archivelog Mode, e com a inst\u00e2ncia dispon\u00edvel (ou seja, sem causar indisponibilidade). Reconhecendo o laborat\u00f3rio: Realizando um backup FULL do banco de dados + archives: Nesta etapa, vou remover […]<\/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-5995","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\/5995","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=5995"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5995\/revisions"}],"predecessor-version":[{"id":9073,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5995\/revisions\/9073"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5995"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5995"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5995"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}