Neste artigo vamos simular uma situa\u00e7\u00e3o extrema, onde precisamos recuperar um ambiente onde todos os control files foram perdidos, e n\u00e3o possu\u00edmos um backup dos mesmos.<\/p>\n\n\n\n
Reconhecendo o ambiente:<\/p>\n\n\n
\n[oracle@oel7 ~]$ rman target \/\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 6 19:19:50 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nconnected to target database: CORTEX (DBID=548968087)\n \nRMAN> SELECT NAME,OPEN_MODE FROM V$DATABASE;\n \nusing target database control file instead of recovery catalog\nNAME OPEN_MODE\n--------- --------------------\nCORTEX READ WRITE\n \nRMAN> REPORT SCHEMA;\n \nReport of database schema for database with db_unique_name CORTEX\n \nList of Permanent Datafiles\n===========================\nFile Size(MB) Tablespace RB segs Datafile Name\n---- -------- -------------------- ------- ------------------------\n1 910 SYSTEM YES +DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445\n3 600 SYSAUX NO +DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519\n4 340 UNDOTBS1 YES +DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555\n5 824 SOE NO +DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205\n7 5 USERS NO +DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555\n \nList of Temporary Files\n=======================\nFile Size(MB) Tablespace Maxsize(MB) Tempfile Name\n---- -------- -------------------- ----------- --------------------\n1 32 TEMP 32767 +DG_DATA\/CORTEX\/TEMPFILE\/temp.264.1039033669\n<\/pre><\/div>\n\n\n[oracle@oel7 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 6 19:26:26 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SHO PARAMETER CONTROL_FILES;\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ncontrol_files string +DG_DATA\/CORTEX\/CONTROLFILE\/cu\n rrent.260.1039033623, +DG_FRA\/\n CORTEX\/CONTROLFILE\/current.256\n .1039033625\n<\/pre><\/div>\n\n\nDeletando todos os control files:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n<\/pre><\/div>\n\n\n[grid@oel7 ~]$ asmcmd\nASMCMD> rm +DG_DATA\/CORTEX\/CONTROLFILE\/current.260.1039033623\nASMCMD> rm +DG_FRA\/CORTEX\/CONTROLFILE\/current.256.1039033625\nASMCMD>\n<\/pre><\/div>\n\n\nAo tentar abrir o banco, os control files n\u00e3o s\u00e3o identificados:<\/p>\n\n\n
\nSQL> STARTUP;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\nORA-00205: error in identifying control file, check alert log for more info\n<\/pre><\/div>\n\n\n2021-09-06 19:44:01.869000 -03:00\nErrors in file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortex\/cortex\/trace\/cortex_mz00_6520.trc:\nORA-00202: control file: '+DG_FRA\/CORTEX\/CONTROLFILE\/current.256.1039033625'\nORA-17503: ksfdopn:2 Failed to open file +DG_FRA\/CORTEX\/CONTROLFILE\/current.256.1039033625\nORA-15012: ASM file '+DG_FRA\/CORTEX\/CONTROLFILE\/current.256.1039033625' does not exist\nORA-00210: cannot open the specified control file\nORA-00202: control file: '+DG_DATA\/CORTEX\/CONTROLFILE\/current.260.1039033623'\nORA-17503: ksfdopn:2 Failed to open file +DG_DATA\/CORTEX\/CONTROLFILE\/current.260.1039033623\nORA-15012: ASM file '+DG_DATA\/CORTEX\/CONTROLFILE\/current.260.1039033623' does not exist\nChecker run found 2 new persistent data failures\n<\/pre><\/div>\n\n\nPreparando o ambiente para a cria\u00e7\u00e3o do control file:<\/p>\n\n\n
\nSQL> SHU ABORT;\nORACLE instance shut down.\nSQL> STARTUP NOMOUNT;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\n<\/pre><\/div>\n\n\nPodemos criar o control file manualmente com o script abaixo. Em algumas situa\u00e7\u00f5es, talvez n\u00e3o tenhamos toda a lista dos datafiles. Para simular isso, vou criar o arquivo sem um dos datafiles para vermos o que ocorrer\u00e1:<\/p>\n\n\n
\nCREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS ARCHIVELOG\n MAXLOGFILES 16\n MAXLOGMEMBERS 3\n MAXDATAFILES 100\n MAXINSTANCES 8\n MAXLOGHISTORY 292\nLOGFILE\n GROUP 1 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_1.261.1039033629',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_1.257.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 2 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_2.262.1039033629',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_2.258.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 3 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_3.263.1039033647',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_3.259.1039033651'\n ) SIZE 200M BLOCKSIZE 512\n-- STANDBY LOGFILE\nDATAFILE\n '+DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445',\n '+DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519',\n '+DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555',\n '+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205'\nCHARACTER SET AL32UTF8\n;\n<\/pre><\/div>\n\n\nApesar do datafile USERS n\u00e3o ter sido informado, o control file foi criado:<\/p>\n\n\n
\nSQL> CREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS ARCHIVELOG\n MAXLOGFILES 16\n MAXLOGMEMBERS 3\n MAXDATAFILES 100\n MAXINSTANCES 8\n 2 MAXLOGHISTORY 292\n 3 4 5 6 7 LOGFILE\n 8 GROUP 1 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_1.261.1039033629',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_1.257.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 2 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_2.262.1039033629',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_2.258.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 3 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_3.263.1039033647',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_3.259.1039033651'\n ) SIZE 200M BLOCKSIZE 512\n-- STANDBY LOGFILE\nDATAFILE\n '+DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445',\n '+DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519',\n '+DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555',\n 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 '+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205'\nCHARACTER SET AL32UTF8\n; 26 27\n \nControl file created.\n<\/pre><\/div>\n\n\nAbrindo o banco:<\/p>\n\n\n
\nSQL> RECOVER DATABASE;\nORA-00283: recovery session canceled due to errors\nORA-00264: no recovery required\n \n \nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nComo o datafile n\u00e3o informado no control file n\u00e3o era de sistema (system,sysaux, etc), o banco est\u00e1 operando, e apenas olhando o seu alert.log ou fazendo o validate para percebermos a falta do datafile:<\/p>\n\n\n
\nRMAN> VALIDATE DATABASE;\n \nStarting validate at 06-SEP-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=19 device type=DISK\nRMAN-06169: could not read file header for datafile 7 error reason 1\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of validate command at 09\/06\/2021 20:04:01\nRMAN-06056: could not access datafile 7\n<\/pre><\/div>\n\n\nSQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=7;\n \nFILE_NAME\n--------------------------------------------------------------------------------\n\/oracle\/19.3.0\/product\/dbs\/MISSING00007\n<\/pre><\/div>\n\n\nVou recriar o control file, agora com todos os datafiles:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> STARTUP NOMOUNT;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\nSQL> CREATE CONTROLFILE REUSE DATABASE "CORTEX" NORESETLOGS ARCHIVELOG\n MAXLOGFILES 16\n MAXLOGMEMBERS 3\n MAXDATAFILES 100\n MAXINSTANCES 8\n MAXLOGHISTORY 292\nLOGFILE\n GROUP 1 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_1.261.1039033 2 629',\n 3 4 5 '+DG_FRA\/CORTEX\/ONLINELOG\/group_1.257.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 2 (\n 6 '+DG_DATA\/CORTEX\/ONLINELOG\/group_2.262.1039033629',\n '+DG_FRA\/CORTEX\/ONLINELOG\/group_2.258.1039033637'\n ) SIZE 200M BLOCKSIZE 512,\n GROUP 3 (\n '+DG_DATA\/CORTEX\/ONLINELOG\/group_3.263.1039033647',\n 7 8 9 10 '+DG_FRA\/CORTEX\/ONLINELOG\/group_3.259.1039033651'\n ) SIZE 200M BLOCKSIZE 512\n-- 11 STANDBY LOGFILE\nDATAFILE\n '+DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445',\n '+DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519',\n '+DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555',\n '+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205',\n '+DG_DATA\/ 12 CORTEX\/DATAFILE\/users.259.1039033555'\n 13 14 15 CHARACTER SET AL32UTF8\n; 16 17 18 19 20 21 22 23 24 25 26 27 28\n \nControl file created.\n \nSQL> ALTER DATABASE OPEN;\nALTER DATABASE OPEN\n*\nERROR at line 1:\nORA-01113: file 7 needs media recovery\nORA-01110: data file 7: '+DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555'\n \n \nSQL> RECOVER DATABASE;\nMedia recovery complete.\nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n \nSQL>\n<\/pre><\/div>\n\n\nAmbiente validado:<\/p>\n\n\n
\n[oracle@oel7 trace]$ rman target \/\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 6 20:09:28 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nconnected to target database: CORTEX (DBID=548968087)\n \nRMAN> VALIDATE DATABASE;\n \nStarting validate at 06-SEP-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=25 device type=DISK\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00001 name=+DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445\ninput datafile file number=00005 name=+DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205\ninput datafile file number=00003 name=+DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519\ninput datafile file number=00004 name=+DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555\ninput datafile file number=00007 name=+DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555\nchannel ORA_DISK_1: validation complete, elapsed time: 00:01:15\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n1 OK 0 17624 116488 4661726\n File Name: +DG_DATA\/CORTEX\/DATAFILE\/system.256.1039033445\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 80690\n Index 0 13083\n Other 0 5083\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n3 OK 0 21920 76802 4660992\n File Name: +DG_DATA\/CORTEX\/DATAFILE\/sysaux.257.1039033519\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 7698\n Index 0 5342\n Other 0 41840\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n4 OK 0 129 43520 4661726\n File Name: +DG_DATA\/CORTEX\/DATAFILE\/undotbs1.258.1039033555\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 0\n Index 0 0\n Other 0 43391\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n5 OK 0 8726 105472 3828442\n File Name: +DG_DATA\/CORTEX\/DATAFILE\/soe.266.1065506205\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 59629\n Index 0 34967\n Other 0 2150\n \nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n7 OK 0 101 641 1252497\n File Name: +DG_DATA\/CORTEX\/DATAFILE\/users.259.1039033555\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 60\n Index 0 15\n Other 0 464\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 666\nFinished validate at 06-SEP-21\n \nRMAN>\n<\/pre><\/div>\n\n\nObs: 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 uma situa\u00e7\u00e3o extrema, onde precisamos recuperar um ambiente onde todos os control files foram perdidos, e n\u00e3o possu\u00edmos um backup dos mesmos. Reconhecendo o ambiente: Deletando todos os control files: Ao tentar abrir o banco, os control files n\u00e3o s\u00e3o identificados: Preparando o ambiente para a cria\u00e7\u00e3o do control file: Podemos […]<\/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-6252","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\/6252","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=6252"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6252\/revisions"}],"predecessor-version":[{"id":9053,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6252\/revisions\/9053"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6252"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}