Thursday, February 15, 2007

If you are familiar with Oracle error messages, you will recognize that the true error message text for the ORA-1578 error is not, in fact, "RMAN to the Rescue". If you look this error up in the Oracle Database Error Messages manual, you'll find the text is:

Action: Try to restore the segment containing the block indicated. This may involve dropping the segment and recreating it. If there is a trace file, report the errors in it to your ORACLE representative.

Uh-oh. This sounds like it could be some bad news indeed. In particular, the statement "This may involve dropping the segment and recreating it." does not have an especially appealing quality to it when the segment in question is, say, a several hundred million row table, just as an example. It is a time like this that RMAN really can come to the rescue.

"But, we don't use RMAN to backup our database. We only perform user managed backups." I can hear you say. Many people are surprised to learn that even if you don't use RMAN to backup your database (even though you really should!) it can still be used to perform what is known as block media recovery. It is just this situation that I will illustrate here - performing block media recovery on a corrupt data block (ORA-1578) using RMAN but without having an RMAN backup.

Before embarking on this adventure, I think it wise to create a new tablespace, and, thus, datafile dedicated to this purpose. I will, naturally, be using SQL*Plus for this activity:

NOTE: If your database is in ARCHIVELOG mode, you do not need to shut it down before copying the data file; however, remember to issue an “alter tablespace TEST begin backup;” before copying the data file and an “alter tablespace TEST end backup;” after the file has been copied. As this database is a “sandbox” database on my laptop, it runs in NOARCHIVELOG mode most of the time.

At this time some weird magic happened and a block belonging to the table has become corrupt. It's funny how these things can happen.

Time to start the database back up and see if the block is corrupted from Oracle’s perspective.

Back in my SQLPLUS session:

startup open;

The database has successfully started back up. Now I try to perform a select against the table:

OK, so the data block belonging to my CTEST table is definitely corrupt. I’d say it is time for that RMAN magic!

Because I did not use RMAN to perform any backup operations on this database – the “backup” was simply copying a single datafile to another directory – I do not have any RMAN Recovery Catalog. Therefore I specify the “nocatalog” option when I start RMAN:

rman target=/ nocatalog

After connecting to the database with RMAN, I need to tell RMAN about the backup file which is also known as the “datafilecopy” in RMAN terms. I refer to this as “registering” the file with RMAN. Here’s how I do that:

RMAN is now “aware” of this file and can use it to perform the block media recovery. Also note that all of the redo that was generated from the time I copied the file up to the present time is also available on my system. To recover the block simply enter:

You may also use the “delete” command if you wish to both remove the registration and delete the physical file in a single command.

One final reminder: the purpose of this was to demonstrate an RMAN capability. If you encounter a corrupt data block in your database and need assistance, please ensure you contact Oracle Support for the proper procedures to recover. Remember this is for demonstration purposes only. Do not perform this in a production server!

About Me

I'm an Oracle Software Developer with DBA experience. I authored the ODP.NET column in Oracle Magazine and the Apress book "Pro .NET Oracle Programming". I'm also a former Oracle ACE Director which means I tend to advocate Oracle and try to help people with it. This site may or may not have (meaningful) content. Sometimes I am a perfectionist. I reserve the right to edit and/or delete comments. Personal blog that does not represent any company. I bet you knew that. Married to a serial tea drinker.

Disclaimer

The content expressed here is my own and does not necessarily reflect that of Oracle Corporation,
its affliates or clients, or anyone else for that matter. The content expressed by
visitors is their own. The content on this site is not guaranteed to be fit for any purpose
and no warranty of any kind is expressed or implied. If you choose to use any content from
this site, it is at your own risk.

Always use a non-production system to assess the
suitability of any content from this site in your environment.