This article is a practical implementation of recovering missing online redo logs. There are two ways to recover.
1. Recovery of redo logs with the help of hidden parameter _ALLOW_RESETLOGS_CORRUPTION.
Scenario Details:
1. Shutdown Database and delete online redo logs.
2. Startup mount and recover the missing redo logs.
There are many cases wherein, we go for incomplete recovery. This is one of the scenario, wherein online logs are corrupted or missing. In this case, we need to go for incomplete recovery. Using “until cancel”, we will recover and open database in resetlogs.
Proceeding with the scenario.
Shutdown the database, and deleting the online logs.
16:19:02 SYS on 14-JUL-11 at orcl >shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Physically deleted redo logs. On startup, we recieve the below error.
16:19:15 SYS on 14-JUL-11 at orcl >startup ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified.
alert log details:
ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 2 processes Started redo scan Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_ora_2176.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Aborting crash recovery due to error 313 Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_ora_2176.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_ora_2176.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. ORA-313 signalled during: ALTER DATABASE OPEN... Thu Jul 14 16:19:38 2011 Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_m000_4076.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO01.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_m000_4076.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO02.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_m000_4076.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified.
Trying to go for an incomplete recovery.
16:19:38 SYS on 14-JUL-11 at orcl >recover database until cancel;
ORA-00279: change 1039492 generated at 07/11/2011 19:12:52 needed for thread 1
ORA-00289: suggestion : C:\ORACLE112\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_07_14\O1_MF_1_12_%U_.ARC
ORA-00280: change 1039492 for thread 1 is in sequence #12
16:20:22 Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE112\ORADATA\ORCL\SYSTEM01.DBF'
alert log details:
ALTER DATABASE RECOVER database until cancel Media Recovery Start started logmerger process Parallel Media Recovery started with 2 slaves ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ... ALTER DATABASE RECOVER CANCEL Thu Jul 14 16:20:27 2011 Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_pr00_1480.trc: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'C:\ORACLE112\ORADATA\ORCL\SYSTEM01.DBF' ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL
Well, what now. Not a problem lets find some alternate way. We will now go for an hidden parameter _ALLOW_RESETLOGS_CORRUPTION which will allow to open database, even if it is not recovered properly. We will make an entry of this hidden parameter in pfile. Creating a pfile.
16:20:27 SYS on 14-JUL-11 at orcl >create pfile from spfile; File created. Elapsed: 00:00:00.09
Bounce the database. Open database using pfile.
16:20:57 SYS on 14-JUL-11 at orcl >shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Set _ALLOW_RESETLOGS_CORRUPTION=true in pfile.
16:22:09 SYS on 14-JUL-11 at orcl >startup pfile=C:\oracle112\dbhome\database\INITorcl.ORA; ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. 16:22:55 SYS on 14-JUL-11 at orcl >@dt STARTTIME SYSDATETIME DB_NAME LOG_MODE OPEN_MODE HOST_NAME VERSION -------------------- -------------------- --------------- ------------ ------------ --------------- ---------- 14-07-2011 16:22:49 14-07-2011 16:23:59 ORCL ARCHIVELOG MOUNTED RAO-PC 11.2.0.1.0 Elapsed: 00:00:00.04
Recovering database until cancel.
16:23:59 SYS on 14-JUL-11 at orcl >recover database until cancel;
ORA-00279: change 1039492 generated at 07/11/2011 19:12:52 needed for thread 1
ORA-00289: suggestion : C:\ORACLE112\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_07_14\O1_MF_1_12_%U_.ARC
ORA-00280: change 1039492 for thread 1 is in sequence #12
16:25:16 Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE112\ORADATA\ORCL\SYSTEM01.DBF'
As we are performing inconsistent recovery, following will end up with error.
16:25:10 SYS on 14-JUL-11 at orcl >alter database open noresetlogs; alter database open noresetlogs * ERROR at line 1: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: 'C:\ORACLE112\ORADATA\ORCL\REDO03.LOG' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Elapsed: 00:00:00.65
Open database with resetlogs.
16:25:20 SYS on 14-JUL-11 at orcl >alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [1039500], [0], [1039531], [4194432], [], [], [], [], [], [] Process ID: 2328 Session ID: 125 Serial number: 5 Elapsed: 00:00:11.41 16:25:37 SYS on 14-JUL-11 at orcl >
alert log details:
ORA-00600: internal error code, arguments: [2662], [0], [1039500], [0], [1039531], [4194432], [], [], [], [], [], [] Incident details in: c:\oracle112\diag\rdbms\orcl\orcl\incident\incdir_16953\orcl_ora_2328_i16953.trc Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_ora_2328.trc: ORA-00600: internal error code, arguments: [2662], [0], [1039500], [0], [1039531], [4194432], [], [], [], [], [], [] Errors in file c:\oracle112\diag\rdbms\orcl\orcl\trace\orcl_ora_2328.trc: ORA-00600: internal error code, arguments: [2662], [0], [1039500], [0], [1039531], [4194432], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 2328): terminating the instance due to error 600 Thu Jul 14 16:25:37 2011 Instance terminated by USER, pid = 2328
Now this has ended up with ORA-600. The reason being, when we go for _ALLOW_RESETLOGS_CORRUPTION=true the database gets open but instance will crash after open due to undo tablespace corruption. So while making changes in pfile for _ALLOW_RESETLOGS_CORRUPTION=true, also change undo_management to MANUAL to resolve undo tablespace corruption.
Changes made in pfile are,
_ALLOW_RESETLOGS_CORRUPTION=true
undo_management = MANUAL
C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 16:26:32 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. 16:26:32 SYS on 14-JUL-11 at orcl >startup pfile=C:\oracle112\dbhome\database\INITorcl.ORA; ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. Database opened. 16:26:53 SYS on 14-JUL-11 at orcl >@try TSPACE TOT_TS_SIZE USED_TS_SIZE FREE_TS_SIZE FREE_PCT USED_PCT1 ------------------------- ----------- ------------ ------------ ---------- ---------- EXAMPLE 100.000 78.438 21.563 22 78 SHRI 3.000 1.063 1.938 65 35 SYSAUX 490.000 458.438 31.563 6 94 SYSTEM 670.000 669.500 .500 0 100 UNDOTBS1 45.000 14.563 30.438 68 32 USERS 5.000 4.063 .938 19 81 6 rows selected. Elapsed: 00:00:00.13 16:27:53 SYS on 14-JUL-11 at orcl >sho parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1
Now the database is up without any ora error. We need to drop the corrupted undo tablespace and recreate a new undo tablespace, set undo_management to AUTO.
16:28:33 SYS on 14-JUL-11 at orcl >drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. Elapsed: 00:00:01.82 16:29:02 SYS on 14-JUL-11 at orcl >@try TSPACE TOT_TS_SIZE USED_TS_SIZE FREE_TS_SIZE FREE_PCT USED_PCT1 ------------------------- ----------- ------------ ------------ ---------- ---------- EXAMPLE 100.000 78.438 21.563 22 78 SHRI 3.000 1.063 1.938 65 35 SYSAUX 490.000 458.438 31.563 6 94 SYSTEM 670.000 669.500 .500 0 100 USERS 5.000 4.063 .938 19 81 Elapsed: 00:00:00.09 16:40:28 SYS on 14-JUL-11 at orcl >create undo tablespace undotbs1 datafile 'C:\ORACLE112\ORADATA\ORCL\undotbs01.dbf' size 50m; Tablespace created. Elapsed: 00:00:01.20 16:40:49 SYS on 14-JUL-11 at orcl >shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Now set the below parameter in pfile,
undo_management = AUTO
undo_tablespace =
(hash or remove)#_ALLOW_RESETLOGS_CORRUPTION=true
16:41:12 SYS on 14-JUL-11 at orcl >startup pfile=C:\oracle112\dbhome\database\INITorcl.ORA; ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. Database opened. 16:41:36 SYS on 14-JUL-11 at orcl > sho parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 16:42:05 SYS on 14-JUL-11 at orcl >create spfile from pfile; File created. Elapsed: 00:00:00.17
Steps:
1. create a pfile from spfile. Set parameter _ALLOW_RESETLOGS_CORRUPTION = true and UNDO_MANAGEMENT = MANUAL.
2. startup mount pfile=”;
3. recover database until cancel;
4. alter database open resetlogs;
5. drop corrupted undo tablespace and create a new undo tablespace.
6. remove _ALLOW_RESETLOGS_CORRUPTION = true, set UNDO_MANAGEMENT = AUTO and undo_tablespace = in pfile.
7. startup pfile=”;
8. create spfile from pfile;
9. bounce database.
2. Second way of online log file recovery.
The redo logs are missing, but there are times when the following simple steps do help you out.
22:36:40 SYS on 14-JUL-11 at orcl >startup ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. Database opened.
Connecting to shri schema and inserting records in table t1;
22:36:54 SYS on 14-JUL-11 at orcl >conn shri/shri Connected. 22:39:16 SHRI on 14-JUL-11 at orcl >select * from t1; N ---------- 1 Elapsed: 00:00:00.06 22:39:48 SHRI on 14-JUL-11 at orcl >insert into t1 values(2); 1 row created. Elapsed: 00:00:00.03 22:39:52 SHRI on 14-JUL-11 at orcl >commit; Commit complete. Elapsed: 00:00:00.00 22:39:55 SHRI on 14-JUL-11 at orcl >select * from t1; N ---------- 2 1 Elapsed: 00:00:00.01 22:40:02 SHRI on 14-JUL-11 at orcl >conn / as sysdba Connected. 22:40:16 SYS on 14-JUL-11 at orcl >alter system switch logfile; System altered. Elapsed: 00:00:00.06 22:40:28 SYS on 14-JUL-11 at orcl >conn shri/shri Connected. 22:40:33 SHRI on 14-JUL-11 at orcl >insert into t1 values(3); 1 row created. Elapsed: 00:00:00.00 22:40:37 SHRI on 14-JUL-11 at orcl >commit; Commit complete. Elapsed: 00:00:00.00 22:40:41 SHRI on 14-JUL-11 at orcl >select * from t1; N ---------- 2 1 3 Elapsed: 00:00:00.01 22:40:41 SHRI on 14-JUL-11 at orcl >conn / as sysdba Connected. 22:41:00 SYS on 14-JUL-11 at orcl >shu immediate Database closed. Database dismounted. ORACLE instance shut down. 22:41:38 SYS on 14-JUL-11 at orcl >
Deleting redo log files.
22:41:38 SYS on 14-JUL-11 at orcl >startup mount ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 230687184 bytes Database Buffers 297795584 bytes Redo Buffers 5804032 bytes Database mounted. 22:46:52 SYS on 14-JUL-11 at orcl >recover database until cancel; Media recovery complete. 22:48:26 SYS on 14-JUL-11 at orcl >alter database open resetlogs; Database altered. Elapsed: 00:00:20.95 22:48:55 SYS on 14-JUL-11 at orcl >select * from shri.t1; N ---------- 2 1 3 Elapsed: 00:00:00.06 22:48:55 SYS on 14-JUL-11 at orcl >
Note:
Considering 3 online redo logs. Method 2 is applicable, if none of them are ACTIVE. For example out of 3 redo logs only 1 log is current and other 2 are INACTIVE. Database will be recovered with “recover database until cancel”.
Method 1 will help us when, if any of the redo is ACTIVE. At this point of time recovering database with normal recover database until cancel won’t be useful. Going with it will display the error “ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: ‘/oracle112/oradata/system01.dbf'”. Hence need to perform the whole process.
Shrikant's Blog 
Thanks very useful! That hidden parameter worked and allowed me to open the database and switch the log files a few times, after a bounce I was back up and running!
You’re welcome
Oh my God, I have no words to Thank you. You my friend saved me 14hrs of agony, Thank you so……much. I wish google could put your entry on top, anyway thanks a lot again. And keep up the good work.
Thanks
-Vijiay Parmar
You’re most welcome.
Great article. Everybody should notice that this does not magically recover the database. Is database was opened and crashed, it will most probably have old data blocks in the datafiles and you will get an error when selecting data that resides on those blocks.
Redo logs are the first thing written, if you loose something that was not sent to disk (checkpoint), then you will have problems in the future without doubt. But you could be lucky and be able to backup everything important.
Jose
thnx for sharing the information.
thanks for sharing this useful post
You are welcome
Many Many thanks for useful information
you’re welcome.
My Qustion is can we able to recover the normal dbf file which is currupted my system is up and running but i can’t able to get the data with that currupted dbf file its given messages ORA-00376 connot be read at this time ORA -01110 datafile 56:
can you please help me in this regards 14-jan-2013
Yes, It is possible to recover a normal datafile with a clean backup. Just for my reference are you sure its a corruption. There are many causes for this error to occur.
Few of them are:
A. Tablespace or Datafile offline.
B. Datafile does not exist at the OS level.
C. Datafile locked by Backup Software.
D. Incorrectly set ULIMIT on UNIX.
E. Rollback Segment with active transactions is unavailable
F. Possible Other Causes.
Please go through MOS doc id 183327.1 for causes and solution.
very helpful content.
Thanks…
very useful, indeed got my db up n running. Thanks alot!
you’re welcome.