ORA-00333: redo log read error block XXXX count XXXX

John

11 years ago

ORA-00333: redo log read error block XXXX count XXXX

I encountered this Oracle error when connecting to my database after my laptop lost power and subsequently had some severe Blue Screen issues.
According to Oracle, ORA-00333 is caused by an IO error while reading the log described in the accompanying error. The resolution is to restore accessibility to the file, or get another copy of the file.
This certainly ties in with my scenario.
The steps found to recover from this are fine for a test or development environment where data loss is not an issue. You wouldn’t want to follow these for a production environment though as you could well lose data!
So, what does it look like in practise?

We can see that after connecting to the database my instance was idle (diagram 1), this may not be the case with yours, so I issued startup. This then throws the ORA-00333 error.
The first thing to do is shutdown the database with shutdown immediate (diagram 2).

Next, bring up and mount the database – we need the database mounted (with restricted access) to alter the system parameter. If we try and do this with the database shutdown we’ll get ORA-01034: ORACLE not available.
After setting the “_allow_resetlogs_corruption” parameter we then shutdown the database again (diagram 3).

The database is started up so that the altered system parameter is read from the spfile.

Next we tell Oracle to recover the database. When prompted to specify a log I hit <return> which threw a few extra messages, you could alternatively type CANCEL if which case it should skip to the line ORA-01547: warning.

After recovery from an incomplete media set, or using a backup control file, it is required to reset the redo log. Further details on what happens during this process can be found here:
http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/backup.102/b14191/osrecov009.htm