How do I recreate a deleted logfile on an Oracle database?

Question

For my database assignment we were looking into "database corruption".

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 start up my database using startup pfile=$PFILE nomount. Then I mount it using the command alter database mount;

When I try to open it with alter database open; it gives me the 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 of course the file I deleted is missing.

How can I go about recovering the delete filed 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 the missing file was part of group 2. If I try to add the log02a.rdo file again I get "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?

Answer 1

See the Oracle Database Backup and Recovery Users Guide. In particular the section titled Recovering After Losing of All Members of an Online Redo Log Group. Your course of action will depend on other factors you can read about in the documentation.

Answer 2

I'm not 100% sure about your scenario, but I think you need to add the RESETLOGS option to you OPEN command. See the documentation for ALTER DATABASE, and the notes on RESETLOGS.

However, you really need to understand what you are trying to do, and what the command will do, and not dive in blindly. Hopefully this option will have come up on your course - if not then you might be supposed to be looking for something else