Reread (file 327, block 339794) found same corrupt data (no logical check) ---Fixing Corrupt Blocks

 

Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure


------------Error From Alert Log--------------

Hex dump of (file 327, block 339794) in trace file /u01/app/oracle/diag/rdbms/ncbsstd/baappcdb/trace/baappcdb_ora_75038.trc

Corrupt block relative dba: 0x51c52f52 (file 327, block 339794)
Fractured block found during multiblock buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x51c52f52
 last change scn: 0x1eaf.7467dde7 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1adc0601
 check value in block header: 0x3201
 computed block checksum: 0x6b71

Reading datafile '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' for corruption at rdba: 0x51c52f52 (file 327, block 339794)
Reread (file 327, block 339794) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 327, block# 339794)
Automatic block media recovery failed for (file# 327, block# 339794)
[request flood controlled]
Mon May 13 15:00:07 2024
Corrupt Block Found
         CONT = 7, TSN = 4, TSNAME = IBANKTBS
         RFN = 327, BLK = 339794, RDBA = 1371877202
         OBJN = 92301, OBJD = 92301, OBJECT = STMAILID, SUBOBJECT =
         SEGMENT OWNER = IBANKING, SEGMENT TYPE = Table Segment
Mon May 13 15:00:08 2024
Hex dump of (file 327, block 339794) in trace file /u01/app/oracle/diag/rdbms/ncbsstd/baappcdb/trace/baappcdb_ora_178931.trc

Corrupt block relative dba: 0x51c52f52 (file 327, block 339794)
Fractured block found during multiblock buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x51c52f52
 last change scn: 0x1eaf.7467dde7 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1adc0601
 check value in block header: 0x3201
 computed block checksum: 0x6b71
 
 ------------Diagnosis-----------
 
 SQL> create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID;
create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID
                                                                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'


SQL> ALTER TABLE IBANKING.STMAILID
 DROP PRIMARY KEY CASCADE;  2  

Table altered.

ALTER TABLE IBANKING.STMAILID ADD (
  CONSTRAINT PK_STMAILID
  PRIMARY KEY
  (MAILID)
  USING INDEX IBANKING.PK_STMAILID
  ENABLE VALIDATE);
  SQL>   2    3    4    5    6  ALTER TABLE IBANKING.STMAILID ADD (
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'

SQL> analyze table IBANKING.STMAILID validate structure cascade online;
analyze table IBANKING.STMAILID validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'

-----------Execute RMAN command to check corruption-----------

[oracle@ncbsstd ~]$ rman target/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon May 13 14:49:50 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BAAPPCDB (DBID=4236564156)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
83022      HIGH     OPEN      13-MAY-24     Datafile 327: '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' contains one or more corrupt blocks

RMAN>

RMAN>

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
83022      HIGH     OPEN      13-MAY-24     Datafile 327: '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10462 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. No backup of block 339794 in file 327 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
1. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command.  Then perform a Data Guard role change (failover).  Available standbys: ncbsdg.

Automated Repair Options
========================
no automatic repair options available

RMAN>        





SQL>
SQL>
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/
  2    3    4    5    6    7    8  
PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
DECLARE num_fix INT;
SQL>   2  BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     SCHEMA_NAME => 'IBANKING',
     OBJECT_NAME=> 'STMAILID',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
  3    4    5    6    7    8    9   10  DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/ 11   12  
num fix: 0

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
     FROM REPAIR_TABLE;  2  

no rows selected

SQL> CREATE UNIQUE INDEX IBANKING.PK_STMAILID ON IBANKING.STMAILID
  2  (MAILID)
NOLOGGING
TABLESPACE IBANKTBS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL ( DEGREE 63 INSTANCES 1 );  3    4    5    6    7    8    9   10   11   12   13   14   15   16  

Index created.

SQL> ALTER TABLE IBANKING.STMAILID ADD (
  CONSTRAINT PK_STMAILID
  PRIMARY KEY
  (MAILID)
  USING INDEX IBANKING.PK_STMAILID
  ENABLE VALIDATE);  2    3    4    5    6  

Table altered.


SQL> create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID;

Table created.

SQL> select count(1) from IBANKING.STMAILID;

  COUNT(1)
----------
    164784

SQL> select count(1) from IBANKING.STMAILID_13052024;

  COUNT(1)
----------
    164784

SQL>


Reference: https://docs.oracle.com/cd/B13789_01/server.101/b10739/repair.htm