Wednesday, February 25, 2009

ORA-01578 and related heart attacks

Holy ****! We get this heart attack inducing error message in our production system:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1510490)
ORA-01110: data file 4: '/u16/app/oracle/oradata/database/main_effin.dbf
ORA-26040: Data block was loaded using the NOLOGGING option
Within seconds we are hitting Metalink and Google and Oracle forums and Burleson Consulting's sites and opening up service requests. It's not looking good. We call friends. "I've never seen that one before!" F***! We're so f*****. We consider running dbv. We consider preparing for a full restore. We data pump the table into a file on a different file system. We drop the table. We recreate the table. S**!, error when trying to create the table from the import. Drop the table again. Import the table. Both of us have to pee so bad we could extinguish an active volcano.

The Oracle finally calls back.

No problem. We see this a number of times each month.

Here's the deal.

The table and index are set to full redo logging but the CLOB in that table is not. A couple months ago we had to do a full restore of our production database. This part gets confusing because it's being related second hand but the block got marked as LOGGING because of the restore even though the LOB options are set to not generate full REDO logging. When someone tried to update that table Oracle as a safety measure marked the block as read only until measures were taken to check it. The "corruption" in this case wasn't corruption as in your data is fooggegated and there is some crap lost somewhere. The best thing to do is data pump export, drop, data pump import (what we were trying to do while freaking out before calm, cool, collected Mr. Oracle called). He also explained that generating REDO for LOBs is painfully expensive so there is a case for NOT generating REDO for them if the data are insignificant and/or can be recreated.

So here I am, bladder emptied. It felt so good it feels like I'm still taking that leak. My heart rate has returned to close to normal.

I really need to win the lottery...