RE: NoLogging Corruption Woes
Date: Wed, 5 Jun 2013 15:43:25 +0000
Message-ID: <004e9522ae1545ff836936ca991bb0ec_at_BLUPR08MB119.namprd08.prod.outlook.com>
In addition to Tim's excellent reply, I would just add that if you're really seeing corruption due to NOLOGGING, then, in the same error stack where you see ORA-01578, you should also see ORA-26040 "Data block was loaded using the NOLOGGING option". If you see ORA-01578 *without* an accompanying ORA-26040 in the same error stack, then the corruption is *not* due to NOLOGGING.
If you *do* see ORA-26040, that just means that someone did a recovery from a backup restored from a point in time before a NOLOGGING data load was done, and rolled forward through the point in time of the NOLOGGING load, thereby invalidating the blocks that were initially loaded with NOLOGGING operations.
Hope that helps,
-Mark
-----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-lReceived on Wed Jun 05 2013 - 17:43:25 CEST