ORA-00313 : Lost redo logs file of the database in Oracle

ORA-00313: open failed for members of log group 1 of thread 1

During open the database got the following error:

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/KSDQA/redo01.log'
ORA-27037: unable to obtain file status

Step 1. Start the database at mount state:

Start the database.
startup mount;

Step 2: Check the location of redo log and add new log files

alter database add logfile group 4 '/u02/oradata/KSDQA/redo04.log' size 250M;
alter database add logfile group 5 '/u02/oradata/KSDQA/redo05.log' size 250M;
alter database add logfile group 6 '/u02/oradata/KSDQA/redo06.log' size 250M;

Step 3: Start the database in open mode:

alter database open;
-- IF not then try with resetlog option:
alter database open resetlogs;

Note: If even not start database then go to step 4
 
Step 4 : If database is not up then proceed with following steps:

create pfile from spfile;

Step 5: Take a cold backup of database before proceed:
Note: Take full backup of all datafiles, means copy the current datafiles into new location
 
Step 6: Edit the pfile and add the following parameters:

*.undo_management='MANUAL'
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE

Remove the *.Undo_management='AUTO' 

Step 7: Start database with new pfile

startup mount pfile=location

Step 8: Start the following recover command:

recover database using BACKUP CONTROLFILE until cancel;
Ask for auto,cancel option:
CANCEL

Step 9: Finally open the database with resetlogs:

alter database open resetlogs;

Step 10: Checked the resetlog file are created in location

select * from v$logfile;

Step 11: Shutdown the database:

Shutdown immediate;

Step 12: Start with spfile:

Start with spfile
Startup

Step 13: Remove the extra created group

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.