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
Showing posts with label Fixing Corrupt Blocks. Show all posts
Showing posts with label Fixing Corrupt Blocks. Show all posts
Monday 13 May 2024
Reread (file 327, block 339794) found same corrupt data (no logical check) ---Fixing Corrupt Blocks
Subscribe to:
Posts (Atom)