RE: NoLogging Corruption Woes

1. What Tim said
2. If I understand you correctly, you have already dropped, recreated, and reloaded the table in question (by import did you mean an Oracle import or datapump, or some other sort of loading data?), that the problem persists in the current location (database, tablespace, and files), but if you load the same data into a different database the problem goes away. This presents *some possibility* that there is a problem with the tablespace metadata and/or the storage on which it resides, making me wonder what happens if, perhaps, you renamed the current table, whacked the indexes, and reloaded the table again so it will definitely use different blocks. If the tablespace metadata is fubar, perhaps a new tablespace rather than merely renaming the current broken table to squat on the currently broken (possibily, see "a) What Tim said") blocks would be useful. Of course if a tablespace is trashed in some way, then any additional contents of said tablespace *may* need to also be handled as well. All this is conjecture and I only write it based on being puzzled by thinking you have already reloaded the table and it was re-corrupted. The last time I personally observed fubar'd tablespace metadata was 8.0 or 8.1 where the parallel index create command neatly assigned blocks from fet$ more than once, leading to hilarity. If it is a storage problem, I'd expect you to see bazillions of errors in the alert log and the OS and/or ASM if you're using ASM. More likely "a) What Tim said."

mostly I'm just curious.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, June 05, 2013 10:38 AM
To: April Sims
Cc: oracle-l_at_freelists.org
Subject: Re: NoLogging Corruption Woes

April,
I like to run procedures like CHECK_OBJECT from the DBMS_REPAIR package over the items listed in the V$xxx_CORRUPTION views before deciding that they are actually corrupt or not. Generally if you are getting ORA-01578 errors then it is pretty definite, but if you're not getting that then perhaps it's negotiable. :-) There are all kinds of causes for corruption and often it is transient for one reason or another.

I've attached a script (attached file 'bcorr.sql") that I've used in the past to first "validate" entries in V$BACKUP_CORRUPTION using DBMS_REPAIR, and if those are validated, then subsequently generate a "suggested" RMAN script to use to actually restore/recover over the actual corruption. By no means is the generated "suggested" RMAN script the absolute best approach to dealing with the problem, but it worked for me at the time I wrote this almost 10 years ago, and I haven't used it much since. If nothing else, please consider the generated "suggested" RMAN script is basically a report confirming that certain blocks are indeed irretrievably corrupted -- by no means should you run it unless you're absolutely certain that it is the right thing to do.
User takes full responsibility, etc...

One other thing is that the DBMS_REPAIR package can be used to "fix" the corrupted blocks by marking them solidly as corrupt (i.e. procedure FIX_CORRUPT_BLOCKS) and then marking the table for skipping over corrupted blocks (i.e. procedure SKIP_CORRUPT_BLOCKS), but that can become complicated by the need to deal with newly-orphaned index entries (i.e. procedure DUMP_ORPHANED_KEYS followed by rebuilding indexes) and rebuilding freelists and bitmaps (i.e. procedure REBUILD_FREELISTS).

Tim Hall does his usual incredible bang-up job describing all this at "http://www.oracle-base.com/articles/misc/detect-and-correct-corruption.php# DBMS_REPAIR". Of course, if the object is an index, then either simply drop/re-create or simply rebuild the index (preference on the latter), but you've already stated that it is a table.

Hope this helps...

Thanks!

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.

   postal   => PO Box 352151, Westminster CO 80035-2151 USA
   email    => Tim_at_EvDBT.com
   mobile   => +1 (303) 885-4526
   blog     => http://www.EvDBT.com/
   LinkedIn => http://www.LinkedIn.com/in/TimGorman
   Twitter  => timothyjgorman
board      -> Rocky Mtn Oracle Users Group (www.RMOUG.org)
board      -> Oracle Developers Tools Users Group (www.ODTUG.com)
board      -> Project SafeGuard (www.PSGHelps.org)
member     -> OakTable Network (www.OakTable.net)

Oracle ACE Director (www.oracle.com/technetwork/community/oracle-ace)

Lost Data? => www.ora600.be/ for info about DUDE...

On 6/5/2013 7:24 AM, April Sims wrote:
> Ok....need some assistance. Open SR with Oracle said to drop table,
> recreate and import the data. Corruption is still being reported. Ran
> DBV, same results.
> DBV-00201: Block, DBA 28211119, marked corrupt for invalid redo
> application
>
> This is a third party application so we don't have knowledge of
> exactly how this happened. Have turned on forced logging to prevent any
in the future.
>
> Cloning the database to another SID, removes the corruption.
> We can select all of the data from the underlying table, the other
> objects in the corruption report is the dependent indexes and PK/FK (s).
>
>
> SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
>
> FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
> ---------- ---------- ---------- ------------------ ---------
> 6 3044768 16 1.1019E+11 NOLOGGING
> 6 2248 16 1.1012E+11 NOLOGGING
> 6 3045250 46 1.1019E+11 NOLOGGING
> 6 2900866 46 1.1022E+11 NOLOGGING
> 6 1180371 5 1.1015E+11 NOLOGGING
> 6 2368 8 1.1012E+11 NOLOGGING
> 8 4106416 16 1.1019E+11 NOLOGGING
> 8 4121248 16 1.1021E+11 NOLOGGING
> 8 4116848 16 1.1021E+11 NOLOGGING
> 8 4116784 16 1.1021E+11 NOLOGGING
> 8 4096368 16 1.1017E+11 NOLOGGING
>
> FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
> ---------- ---------- ---------- ------------------ ---------
> 8 4085808 16 1.1016E+11 NOLOGGING
> 8 4116738 14 1.1021E+11 NOLOGGING
> 8 4106464 32 1.1019E+11 NOLOGGING
> 8 4106336 32 1.1019E+11 NOLOGGING
> 8 4106272 32 1.1019E+11 NOLOGGING
> 8 4106242 14 1.1019E+11 NOLOGGING
> 8 4096288 32 1.1017E+11 NOLOGGING
> 8 4096258 14 1.1017E+11 NOLOGGING
> 8 4085856 32 1.1016E+11 NOLOGGING
> 8 2839931 5 1.1012E+11 NOLOGGING
> 8 2839920 8 1.1012E+11 NOLOGGING
>
> FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
> ---------- ---------- ---------- ------------------ ---------
> 8 2839865 7 1.1012E+11 NOLOGGING
> 8 2839856 8 1.1012E+11 NOLOGGING
> 9 55536 16 1.1021E+11 NOLOGGING
> 9 55328 32 1.1021E+11 NOLOGGING
> 9 55298 14 1.1021E+11 NOLOGGING
> 9 55392 32 1.1021E+11 NOLOGGING
> 9 43744 32 1.1021E+11 NOLOGGING
> 9 43680 32 1.1021E+11 NOLOGGING
>
> 30 rows selected.
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l