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.

Advertisements