Damaged redo log file

[phil cook]

Hello

A particularly nasty sequence of events on an N class i administer has led to the total loss of a single redo log file in an oracle instance. The database will not now start and reports problems with redo log threads etc. Is there ANY way of repairing this without restoring the entire set of datafiles?

Thanks in advance.

Phil Cook

[Leila Maria Rebel]

Is your database open ?

If not, could you tell me the message shown in alert file when you try to open your database ?

Is your database in archivelog mode ?

I need to know if this redo log file is active (current) or not. You have some options, but we should analyze carefully.

[phil cook]

Leila

Hi there. Currently Im restoring from a old tape backup which I can back out of if necessary.

I will try to answer your questions though:

The database will not open. The database is not in archive log mode.

I have a recent backup of the whole database except for the redo log in question.

What do you have in mind - can i supply more information?

Phil

[phil cook]

The error when I tried to start the db was

ORA-00314
00314, 00000, "log %s of thread %s, expected sequence# %s doesn't match %s"
// *Cause: The online log is corrupted or is an old version.
// *Action: Find and install correct version of log or reset logs.

Rgds

[Steve Slade · Accepted solution]

Phil,

Its been a while since I have had to deal this sort of problem but basically, I think your options are :

Start the database to a mounted state - startup mount;

1. If the logfile is not the current log file (check in v$log and v$logfile), you can drop the logfile and re-create it.

2. If it is the current log file then you can issue the command: alter database clear logfile group A; where A is the logfile group file that needs recovery.

3. Depending on how the database was shutdown, you might get the situation where a datafile requires some sort of recovery when the database is started. If this is the case, the following command might not work, as the database may want the current redo log for recovery on that datafile. To get around this you can issue the command:

alter database clear logfile group A unrecoverable datafile; again where A is the group logfile to be cleared.

However, there is a health risk with the above command as it means that the contents of the datafile could be corrupted, as full recovery could not take place. If you can validate the contents of the datafile, then you could be ok. Otherwise, You should look to dropping the tablespace on that datafile and re-creating it and its contents, (if possible), or performing a full restore - which may not be possible.

Anyway once you have done the above, you should issue the following command to open the database and re-initialise everything:

alter database open resetlogs;

Once the database is open, you should close it again and take a full backup, to get yourself a good baseline to start the database from.

If you have the disk space, its worthwhile having two or three redo logfiles per logfile group, (on different disks).

I think the above is correct, however, t'is the season to be jolly, and I am still suffering the after effects of large amount of 'Good' spirit.

[phil cook]

Thanks Steve your solution is perfect. This particular database is a standby for a live system whereby everything is dropped & recreated each day - so the short term loss of data has not been an issue.

Special thanks as you've helped me beat my dba team at their own game.

Regards

[Leila Maria Rebel]

Hello, Phil !

Please, excuse me ! I don't know why, but I couldn't access your question again last friday. But I can see know the perfect answer of Steven and I'm very glad that your problem has been solved.

I hope you have a happy new year !

Leila