Logfile deleted on Oracle database how to re-create it

corruptionlog-filesoracle

For my database assignment we were looking into 'database corruption' and I was asked to delete the second redo log file which I have done with the command: rm log02a.rdo this was in the $HOME/ORADATA/u03 directory.

Now I started up my database using startup pfile=$PFILE nomount then I mounted it using the command alter database mount; now when I try to open it alter database open; it gives me this error:

ORA-03113: end-of-file on communication channel 
Process ID: 22125 
Session ID: 25 Serial number: 1

I am assuming this is because the second redo log file is missing. There is still log01a.rdo, but not the one I have deleted. How can I go about recovering this now so that I can open my database again?

I have looked into the database create scripts, and it specified the log02a.rdo file to be size 10M and part of group 2.

If I do select group#, member from v$logfile; I get:

  1
/oradata/student_db/user06/ORADATA/u03/log01a.rdo

  2
/oradata/student_db/user06/ORADATA/u03/log02a.rdo

  3
/oradata/student_db/user06/ORADATA/u03/log03a.rdo

  4
/oradata/student_db/user06/ORADATA/u03/log04a.rdo

So it is part of group 2. If I try to add the log02a.rdo file again "already part of the database". If I drop group 2 and then add it again with these commands:

ALTER DATABASE 
  ADD LOGFILE GROUP 2 ('$HOME/ORADATA/u03/log02a.rdo')
      SIZE 10M;

Nothing. Supposedly alters the database, but it still won't start up. Any ideas what I can do to re-create this and be able to open my database again?

Best Answer

There is no way to recreate it including the data that was in it, unless you happen to have a backup ofc. You can start the database reset the logs and recreate an empty file

try:

 startup nomount;
 alter database mount; 
 alter database open resetlogs;

more info http://web.njit.edu/info/limpid/DOC/backup.102/b14191/osrecov009.htm

Related Topic