RE: NoLogging Corruption Woes
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, April Sims <aprilcsims@xxxxxxxxx>
- Date: Wed, 5 Jun 2013 15:43:25 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Wednesday, June 05, 2013 10:38 AM
To: April Sims
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
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.
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
