Missing Redo Log Files at Oracle Database Startup

Missing Redo Log Files or Redo Log Files Lost at Oracle Database Startup

Redo Logs Contains info regarding the DML Statements that try to make some change in the database. Not every query gets stored in the Redo Log, only the transactions that creates change in the SCN of the database. And that is why these are also one of the important part of Oracle Database lose of which can lead you to database recovery as well as data loss (in instance crash scenarios).

There may be different scenarios to explain this issue but let's just take a simple one this time.

You tried to start your database and a little error popped up i.e.

SQL> startup

ORACLE instance started.

Total System Global Area  235999648 bytes

Fixed Size                   450976 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

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

ORA-00312: online log 1 thread 1: '/home/oracle/oradata/orademo/redo01.log'

Now it is simply showing that it is unable to open redo log 01. After analysis, it was found that I had missed all of my redo logs somehow.
Anyway to get rid of such situation, simply shutdown the database again,

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

and start database at mount stage this time i.e.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999648 bytes

Fixed Size                   450976 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database needs recovery in order to create those lost redo log files. Hence,

SQL> recover database until cancel;

Media recovery complete.

And open database using resetlogs option.

SQL> alter database open resetlogs;

Database altered.

Finally you'll be able to see your lost redo log files.

SQL> select member from v$logfile;

MEMBER

----------------------------------------

/home/oracle/oradata/orademo/redo01.log

/home/oracle/oradata/orademo/redo02.log

/home/oracle/oradata/orademo/redo03.log