Repair the Block Corruption

Repair the Block Corruption

ERROR1:

Corrupt Block Found
         TSN = 381, TSNAME = APPS_TS_TX_DATA
         RFN = 402, BLK = 160267, RDBA = 1686270475
         OBJN = 158628, OBJD = 262427, OBJECT = AD_SNAPSHOT_FILES, SUBOBJECT =
         SEGMENT OWNER = APPLSYS, SEGMENT TYPE = Table Segment

ERROR2:
Corrupt Block Found
         TSN = 383, TSNAME = APPS_TS_SEED
         RFN = 407, BLK = 138012, RDBA = 1707219740
         OBJN = 240862, OBJD = 261266, OBJECT = JDR_PATHS_N1, SUBOBJECT =
         SEGMENT OWNER = APPLSYS, SEGMENT TYPE = Index Segment


SOLUTION ERROR-1:


STEP1: Create Scripts of block1.sql,block2.sql,block3.sql,block4.sql,block5.sql,block6.sql,block7.sql

Script-1:
[oracle@ebs1 block]$ vi block1.sql
BEGIN
  DBMS_REPAIR.ADMIN_TABLES
  (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'APPS_TS_SEED'
  );
END;
/

Script-2:
[oracle@ebs1 block]$ vi block2.sql

BEGIN
  DBMS_REPAIR.ADMIN_TABLES
  (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
     ACTION     => DBMS_REPAIR.CREATE_ACTION,
     TABLESPACE => 'APPS_TS_SEED'
  );
END;
/


Script-3:
[oracle@ebs1 block]$ vi block3.sql

SET SERVEROUTPUT ON
DECLARE
  vCorruptBlocks INT := 0;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT
  (
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME => 'AD_SNAPSHOT_FILES',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  vCorruptBlocks
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
END;
/
~


Script-4:
[oracle@ebs1 block]$ vi block4.sql

column object_name format a10
column repair_description format a28
set lines 10000
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;


Script-5:
[oracle@ebs1 block]$ vi block5.sql

SET SERVEROUTPUT ON
DECLARE
  vBlocksFixed INT := 0;
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS
  (
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME=> 'AD_SNAPSHOT_FILES',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT => vBlocksFixed
  );
  DBMS_OUTPUT.PUT_LINE('Number of blocks fixed: ' || TO_CHAR(vBlocksFixed));
END;
/
~
~

Script-6:
[oracle@ebs1 block]$ vi block6.sql

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
  (
     SCHEMA_NAME => 'APPLSYS',
     OBJECT_NAME => 'AD_SNAPSHOT_FILES',
     OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
     FLAGS => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

Script-7:
[oracle@ebs1 block]$ vi block7.sql

select owner , table_name , skip_corrupt
from   dba_tables
where  table_name = 'AD_SNAPSHOT_FILES';

STEP 2: Execute the Scripts

SQL> @/oracle/block/block1.sql

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block2.sql

PL/SQL procedure successfully completed.

SQL> @/oracle/block/block3.sql
Number of blocks corrupted: 1

PL/SQL procedure successfully completed.

SQL>  @/oracle/block/block4.sql

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ----------------------------
AD_SNAPSHO     160267         6148 TRUE       mark block software corrupt
T_FILES


SQL>  @/oracle/block/block5.sql
Number of blocks fixed: 0

PL/SQL procedure successfully completed.

SQL>  @/oracle/block/block6.sql

PL/SQL procedure successfully completed.

SQL>  @/oracle/block/block7.sql

OWNER                          TABLE_NAME                     SKIP_COR
------------------------------ ------------------------------ --------
APPLSYS                        AD_SNAPSHOT_FILES              ENABLED

SQL> conn applsys/apps
Connected.
SQL>  create table AD_SNAPSHOT_FILES_BK as select * from AD_SNAPSHOT_FILES;

Table created.

SQL> select count(*) from AD_SNAPSHOT_FILES_BK;

  COUNT(*)
----------
   1735127

SQL> drop table AD_SNAPSHOT_FILES;

Table dropped.

SQL> create table AD_SNAPSHOT_FILES as select * from AD_SNAPSHOT_FILES_BK;

Table created.

SQL> select count(*) from AD_SNAPSHOT_FILES;

  COUNT(*)
----------
   1735127

SQL>  conn sys/sys as sysdba
Connected.
SQL> @/oracle/block/block3.sql
Number of blocks corrupted: 0

PL/SQL procedure successfully completed.


SOLUTION ERROR-2:

SQL> conn applsys/apps
Connected.
SQL>  alter index JDR_PATHS_N1 rebuild online;

Index altered.