How to recover from a redo log file loss.(ORA-00313 ,ORA-00312)

How to recover from a redo log file loss.(ORA-00313 ,ORA-00312).The usual error you get when this happens is ORA-00313 ,ORA-00312. So let's see how can we fix this.

  • 1-startup your database in mount mode.
  • [oracle@DCG023 RCAT]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 9 17:57:40 2013
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup mount;
    ORACLE instance started.
    
    Total System Global Area 1603411968 bytes
    Fixed Size                  2213776 bytes
    Variable Size            1006635120 bytes
    Database Buffers          587202560 bytes
    Redo Buffers                7360512 bytes
    Database mounted.
  • 2-Check the status of your log files and see which one is currently online.
  • SQL> select GROUP#,STATUS from V$LOG;
    
        GROUP# STATUS
    ---------- ----------------
             3 INACTIVE
             2 CURRENT
    		 1 INACTIVE
  • 3-Drop the inactive group(which in our case is creating the error).
  • SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
    Database altered.
  • 4-Create a new log file group .
  • ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oracle/oradata/RCAT/redo04.log') SIZE 50M;
    Database altered.
  • 5-Recover your database and open it with RESETLOGS option.
  • SQL> RECOVER DATABASE UNTIL CANCEL;
    Media recovery complete.
    SQL> ALTER DATABASE OPEN RESETLOGS;
    
    Database altered.