Tuesday, 15 November 2016

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


No comments:

Post a comment