Pages

Thursday, July 12, 2018

ORA-01578: ORACLE data block corrupted

====================================
General
====================================
A lot of large trace file are generated with error:
ORA-01578: ORACLE data block corrupted

====================================
Evidences
====================================
find /mount/point/ -type f -printf '%s %p\n'sort -nr | head -20| grep trc | awk '{print $2}' | xargs ls -ltr

-rw-r----- 1 oracle dba 149925888 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8390.trc
-rw-r----- 1 oracle dba 150188032 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8415.trc
-rw-r----- 1 oracle dba 149954560 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8394.trc
-rw-r----- 1 oracle dba 150085632 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8392.trc
-rw-r----- 1 oracle dba 149856256 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8404.trc
-rw-r----- 1 oracle dba 149983232 Jul 11 21:52 ./diag/rdbms/igt/igt/trace/igt_ora_8402.trc

Looking into one of these trace files:

Trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_8390.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name:    Linux
Node name:      lux-tan-2-aps-1
Release:        2.6.18-53.el5
Version:        #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine:        x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 8390, image: oracle@lux-tan-2-aps-1


*** 2018-06-21 09:11:41.991
*** SESSION ID:(300.2) 2018-06-21 09:11:41.991
*** CLIENT ID:() 2018-06-21 09:11:41.991
*** SERVICE NAME:(SYS$USERS) 2018-06-21 09:11:41.991
*** MODULE NAME:(JDBC Thin Client) 2018-06-21 09:11:41.991
*** ACTION NAME:() 2018-06-21 09:11:41.991

DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
Incident 838993 created, dump file: /software/oracle/diag/rdbms/igt/igt/incident/incdir_838993/igt_ora_8390_i838993.trc
ORA-01578: ORACLE data block corrupted (file # 5, block # 110573)
ORA-01110: data file 5: '/oracle_db/db1/db_igt/ora_igt_index_01.dbf'

DDE rules only execution for: ORA 1110

*** 2018-06-21 09:33:41.670
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
DDE: Problem Key 'ORA 1578' was flood controlled (0x6) (incident: 838994)
ORA-01578: ORACLE data block corrupted (file # 5, block # 110573)
ORA-01110: data file 5: '/oracle_db/db1/db_igt/ora_igt_index_01.dbf'
Action (ID=34340890) was flood controlled by a FC Qualifier

*** 2018-06-21 09:37:12.986
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
DDE: Problem Key 'ORA 1578' was flood controlled (0x6) (incident: 838995)
ORA-01578: ORACLE data block corrupted (file # 5, block # 110573)
ORA-01110: data file 5: '/oracle_db/db1/db_igt/ora_igt_index_01.dbf'
Action (ID=34340890) was flood controlled by a FC Qualifier


====================================
Solution
====================================
Corrupted index can be dropped and re-created.
For some reason, the corruption was only temporary, and corrupted index no longer exists.

SELECT segment_name, segment_type
FROM DBA_EXTENTS 
WHERE FILE_ID = 5 
  AND block_id = 110573;

no rows selected

No comments:

Post a Comment