Home > Oracle, RDBMS > Recovery from loss of redo log file.

Recovery from loss of redo log file.

LGWR – logwriter process writes the redo data from the log buffer cache to the redo log files when,

  • a transaction is commited
  • every 3 seconds
  • when redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it – Oracle complains with errors ORA-00313, ORA-00312 and/or ORA-27037. The database halts unable to switch the log file.

1. Recover from loss of active redo log file.
– shutdown the database

SHUTDOWN IMMEDIATE;

– restore the datafiles and proceed for an incomplete recovery until the missing log sequence
– mount the database

STARTUP MOUNT;

– check the status of the missing log file group from v$log view

select group#, sequence#, bytes, first_change#, to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim, status from v$log;

– if STATUS = ACTIVE and ARCHIVED = NO

RECOVER DATABASE UNTIL TIME ‘yyyy-mm-dd:hh24:mi:ss’;

‘ss’ in the seconds should be 1 second less than the first_time of the missing redo log file.

– open the database with resetlogs option

ALTER DATABASE OPEN RESETLOGS;

– take a solid backup

2. Recover from loss of inactive redo log file.
– shutdown the database

SHUTDOWN IMMEDIATE;

– mount the database

STARTUP MOUNT;

– check the status of the missing log file group from v$log view

select * from v$log;

– if STATUS = INACTIVE and ARCHIVED = YES

ALTER DATABASE CLEAR LOGFILE GROUP ;

– if STATUS = INACTIVE and ARCHIVED = NO

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP ;

The CLEAR command will create the missing log files at the OS level. Since the unarchived log file is cleared, there will NOT be an archive log of that sequence. Means, if log of seq#10 is cleared. Then archive log seq#10 will be not be created. It will be like seq#8,seq#9,seq#11,seq#12. So, rollforward using archive logs will not be possible. Also, all uncommitted transactions will be lost.

– open the database

ALTER DATABASE OPEN;

– take a good backup

Hope, this helps.

Advertisements
  1. Dinesh Gunwant
    2012-11-09 at 07:31

    nice

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: