{"id":5614,"date":"2016-03-31T13:11:13","date_gmt":"2016-03-31T05:11:13","guid":{"rendered":"http:\/\/rmohan.com\/?p=5614"},"modified":"2016-03-31T13:11:13","modified_gmt":"2016-03-31T05:11:13","slug":"recover-database-without-control-files-and-redolog-files","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=5614","title":{"rendered":"Recover database without control files and redolog files."},"content":{"rendered":"

\u00a0Recover database without control files and redolog files.<\/h3>\n
<\/div>\n
\n
Scenario:- We are deleting controlfiles from database and would recover database after dropping them.<\/p>\n

Database version :- Oracle 11g R2
\nOS: Rhel 6
\nDatabase sid: orcl
\nArchivelog mode: enabled<\/p>\n

Step1:-
\n$cd \/u01\/app\/oracle\/oradata\/orcl
\n$rm -rf *.ctl<\/p>\n

$sqlplus \/ as sysdba
\nSQL>startup
\nORACLE instance started.<\/p>\n

Total System Global Area \u00a0413372416 bytes
\nFixed Size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a02213896 bytes
\nVariable Size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 402655224 bytes
\nDatabase Buffers \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a04194304 bytes
\nRedo Buffers \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a04308992 bytes
\nORA-00205: error in identifying control file, check alert log for more info<\/p>\n

Step2:-Start the database in nomount stage
\nstartup nomount
\nORACLE instance started.<\/p>\n

Total System Global Area \u00a0209715200 bytes
\nFixed Size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01248140 bytes
\nVariable Size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a075498612 bytes
\nDatabase Buffers \u00a0 \u00a0 \u00a0 \u00a0 \u00a0130023424 bytes
\nRedo Buffers \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a02945024 bytes<\/p>\n

Step3:-Recreate the control file:-<\/p>\n

CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS NOARCHIVELOG
\nMAXLOGFILES 16
\nMAXLOGMEMBERS 3
\nMAXDATAFILES 100
\nMAXINSTANCES 8
\nMAXLOGHISTORY 292
\nLOGFILE
\nGROUP 1 ‘\/u01\/app\/oracle\/oradata\/orcl\/REDO01.log’ \u00a0SIZE 50M,
\nGROUP 2 ‘\/u01\/app\/oracle\/oradata\/orcl\/REDO02.log’ \u00a0SIZE 50M,
\nGROUP 3 ‘\/u01\/app\/oracle\/oradata\/orcl\/REDO03.log’ \u00a0SIZE 50M
\nDATAFILE
\n‘\/u01\/app\/oracle\/oradata\/orcl\/system01.dbf’,
\n‘\/u01\/app\/oracle\/oradata\/orcl\/sysaux01.dbf’,
\n‘\/u01\/app\/oracle\/oradata\/orcl\/users01.dbf’,
\n‘\/u01\/app\/oracle\/oradata\/orcl\/example01.dbf’,
\n‘\/u01\/app\/oracle\/oradata\/orcl\/undotbs01.dbf’
\nCHARACTER SET WE8MSWIN1252;<\/p>\n

Now after creating the control file, the database has been mounted.<\/p>\n

Step 4.
\nSQL> alter database open resetlogs;
\nalter database open resetlogs
\n*
\nERROR at line 1:
\nORA-01194: file 1 needs more recovery to be consistent
\nORA-01110: data file 1: ‘\/u01\/app\/oracle\/oradata\/prim\/system01.dbf’<\/p>\n

So in this case we have to do the recovery \u00a0using the online redolog files.
\nSQL> \u00a0select * from v$log;<\/p>\n

GROUP# \u00a0 \u00a0THREAD# \u00a0SEQUENCE# \u00a0 \u00a0 \u00a0BYTES \u00a0BLOCKSIZE \u00a0 \u00a0MEMBERS ARC STATUS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
\n———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
\n1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00 \u00a0 52428800 \u00a0 \u00a0 \u00a0 \u00a0512 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 YES UNUSED \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00
\n3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00 \u00a0 52428800 \u00a0 \u00a0 \u00a0 \u00a0512 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 YES CURRENT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00
\n2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00 \u00a0 52428800 \u00a0 \u00a0 \u00a0 \u00a0512 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 YES UNUSED \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00<\/p>\n

SQL> recover database until cancel using backup controlfile;
\nORA-00279: change 1030644 generated at 01\/08\/2015 22:25:57 needed for thread 1
\nORA-00289: suggestion :
\n\/u01\/app\/oracle\/flash_recovery_area\/PRIM\/archivelog\/2015_01_08\/o1_mf_1_1_%u_.arc
\nORA-00280: change 1030644 for thread 1 is in sequence #1<\/p>\n

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
\n\/u01\/app\/oracle\/flash_recovery_area\/PRIM\/archivelog\/2015_01_08\/o1_mf_1_1_%u_.arc
\nORA-00308: cannot open archived log
\n‘\/u01\/app\/oracle\/flash_recovery_area\/PRIM\/archivelog\/2015_01_08\/o1_mf_1_1_%u_.ar
\nc’
\nORA-27037: unable to obtain file status
\nLinux-x86_64 Error: 2: No such file or directory
\nAdditional information: 3<\/p>\n

<\/div>\n
\n
SQL> recover database until cancel using backup controlfile;<\/div>\n
ORA-00279: change 1030644 generated at 01\/08\/2015 22:25:57 needed for thread 1<\/div>\n
ORA-00289: suggestion :<\/div>\n
\/u01\/app\/oracle\/flash_recovery_area\/PRIM\/archivelog\/2015_01_08\/o1_mf_1_1_%u_.arc<\/div>\n
ORA-00280: change 1030644 for thread 1 is in sequence #1<\/div>\n
<\/div>\n
<\/div>\n
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}<\/div>\n
\/u01\/app\/oracle\/oradata\/prim\/REDO03.log<\/div>\n
Log applied.<\/div>\n
Media recovery complete.<\/div>\n<\/div>\n

SQL> alter database open resetlogs;<\/p>\n

Database altered.<\/p>\n

SQL> select open_mode from v$database;<\/p>\n

OPEN_MODE
\n——————–
\nREAD WRITE<\/p><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Recover database without control files and redolog files. Scenario:- We are deleting controlfiles from database and would recover database after dropping them.<\/p>\n

Database version :- Oracle 11g R2 OS: Rhel 6 Database sid: orcl Archivelog mode: enabled<\/p>\n

Step1:- $cd \/u01\/app\/oracle\/oradata\/orcl $rm -rf *.ctl<\/p>\n

$sqlplus \/ as sysdba SQL>startup ORACLE instance started.<\/p>\n

Total System Global […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5614"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5614"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5614\/revisions"}],"predecessor-version":[{"id":5615,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5614\/revisions\/5615"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}