3 Replies Latest reply: Mar 22, 2012 5:08 AM by joey_p RSS

    Help Fix 26473 Corrupt blocks in system09.dbf

    joey_p
      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
          >

          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
            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
              >
              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)