Skip to content

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

23/09/2013

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?

ORA-00333 screen 1

Diagram 1

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).

Diagram 2

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).

 

Diagram 3

Diagram 3

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

Diagram 4

Diagram 4

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.

Diagram 5

Diagram 5

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

Diagram 6

Diagram 6

Bring the database back up.

Diagram 7

Diagram 7

Let’s not forget the system parameter is still set to true.

Diagram 8

Diagram 8

The last thing to do is to set the parameter to false and restart the database and confirm that the parameter is now set to false.

Diagram 9

Diagram 9

 

 

 

 

 

 

Advertisements

From → ORA Errors, Oracle

13 Comments
  1. Gyan permalink

    Nice and very use full. I could recover my db using this blog thanks a ton

  2. david permalink

    thank you very much for this blog. Very helpfull….

  3. Jacob permalink

    Great article. Thanks

  4. venkatrao permalink

    Nice job for this article, it is very useful to me..thank u so much….good job.

  5. Great help!!! Thanks

  6. dimple permalink

    thanks a ton !!

  7. Kavya permalink

    Thank you…..The above solution worked!!! 🙂

    • You’re very welcome Kavya, I’m glad it helped. It’s always nice to hear back from someone who finds this useful 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: