This discussion is archived
3 Replies Latest reply: Mar 22, 2012 3:08 AM by joey_p RSS

Help Fix 26473 Corrupt blocks in system09.dbf

joey_p Newbie
Currently Being Moderated
How to Format Corrupted Block Not Part of Any Segment [ID 336133.1]

I'm following the above doc to fix 26K corrupt blocks in system09.dbf

in Step 7 of the Doc it says:

+" First find the *extent size* by querying dba_free_space "+

+65536 = If its 64 K+
+1048576 = 1M+

I'm getting 891559936 with the following query:

SQL> Select bytes from dba_free_space where file_id= 352 and 192128 between block_id and block_id + blocks -1;

BYTES
----------
891559936



What will be my extent size?
I'm lost on how to proceed with the following steps:


BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table apps.s allocate extent (DATAFILE '||'''/oracle/KPSO/db/apps_st/data/system09.dbf''' ||'SIZE 64K) ';
end loop;
end ;
+/+

Note you need to change accordingly the size (1M ,64K or 128k) and the value of for loop
can be changed as per requirement.



Thanks.

-Joey-
  • 1. Re: Help Fix 26473 Corrupt blocks in system09.dbf
    Paulie Pro
    Currently Being Moderated
    >

    I'm following the above doc to fix 26K corrupt blocks in system09.dbf
    Are these system data or applcation data?

    The reason I ask is that if you haven't made any DDL changes to your system since last backup,
    you could possibly just restore the .dbf if it's just system (unchanged) data.


    Paul...

    -Joey-
  • 2. Re: Help Fix 26473 Corrupt blocks in system09.dbf
    joey_p Newbie
    Currently Being Moderated
    Are these system data or applcation data?
    The system09.dbf datafile belongs to SYSTEM tablespace.
    The reason I ask is that if you haven't made any DDL changes to your system since last backup,
    you could possibly just restore the .dbf if it's just system (unchanged) data.
    The system is on RMAN backup, but the corruption started sometimes 14-Jan-2012
    and due to that block corruption errors the backup runs failed, and was only recently noticed.


    I proceeded with using the 1MB extent size

    alter table apps.s allocate extent (DATAFILE '/oracle/KPSO/db/apps_st/data/system09.dbf'  SIZE 1M);

    i finished the entire steps 1-9 and was left with 237+ corrupt blocks out of previous 26,473

    currently doing another round of steps 5-9 to see if it will wipe out all the corrupt blocks.
  • 3. Re: Help Fix 26473 Corrupt blocks in system09.dbf
    joey_p Newbie
    Currently Being Moderated
    >
    currently doing another round of steps 5-9 to see if it will wipe out all the corrupt blocks.
    Just want to give an update:

    Datafile was completely free of corruption after 2 more passes ..




    $ dbv file=/oracle/KPSO/db/apps_st/data/system09.dbf blocksize=8192

    DBVERIFY: Release 11.1.0.7.0 - Production on Thu Mar 22 05:08:56 2012

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    DBVERIFY - Verification starting : FILE = /oracle/KPSO/db/apps_st/data/system09.dbf


    DBVERIFY - Verification complete

    Total Pages Examined : 192128
    Total Pages Processed (Data) : 160979
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 26455
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 2124
    Total Pages Processed (Seg) : 0
    Total Pages Failing (Seg) : 0
    Total Pages Empty : 2570
    Total Pages Marked Corrupt : 0
    Total Pages Influx : 0
    Total Pages Encrypted : 0
    Highest block SCN : 3757005752 (1388.3757005752)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points