ORACLE 11.2.0.4 报警文件中报 ORA-600 [13011]处理

郑全

问题现象：

每一个小时报一次，具体语句为：

----- Current SQL Statement for this session (sql_id=11p815z8hkfms) -----
DELETE MGMT_SYSTEM_PERFORMANCE_LOG WHERE TIME < :B2 AND ROWNUM <= :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0000000459813A70      1321  package body SYSMAN.EMD_LOADER
0000000459813A70       200  package body SYSMAN.EMD_LOADER
0000000457EC6770      1125  package body SYSMAN.EMD_MAINTENANCE
0000000457E3A040         1  anonymous block

原因：    相关索引损坏。

解决：
    处理按Doc ID 2227027.1 文档处理，找出有问题索引，重建一下即可。
select index_name from dba_indexes where table_name='MGMT_SYSTEM_PERFORMANCE_LOG';

ALTER  INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_01 REBUILD ONLINE;
ALTER  INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_02 REBUILD ONLINE;

附：

ORA-00600 [13011] when delete from MGMT_SYSTEM_PERFORMANCE_LOG table (Doc ID 2227027.1)

In this Document
Symptoms
Cause
Solution
References

APPLIES TO:
Oracle Database - Standard Edition - Version 11.2.0.4 and later
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
DML Delete on MGMT_SYSTEM_PERFORMANCE_LOG returns and slow performance reported:
ORA-00600: internal error code, arguments: [13011], [67720], [8465722], [3], [8421715], [0], [], [], [], [], [], []

----- Current SQL Statement for this session (sql_id=11p815z8hkfms) -----
DELETE MGMT_SYSTEM_PERFORMANCE_LOG WHERE TIME < :B2 AND ROWNUM <= :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x919523d0 1321 package body SYSMAN.EMD_LOADER
0x919523d0 200 package body SYSMAN.EMD_LOADER
0x9236da30 1125 package body SYSMAN.EMD_MAINTENANCE
0x91a818e0 1 anonymous block

----- Call Stack Trace -----

delexe opiexe opipls opiodr

'analyze' on the above table my fail to indicate a corruption.
CAUSE
++ Invalid or corrupted associated index
(i.e) Indexes on MGMT_SYSTEM_PERFORMANCE_LOG tables are corrupted or need to be rebuild.

SOLUTION
++ Recreating the corrupted index will fix this issue.

Example :

To check the associated indexes you can use the following command

SQL> select index_name from dba_indexes where table_name='MGMT_SYSTEM_PERFORMANCE_LOG';

Then validate the indexes one by one.

-- Connect as sysdba
SQL> Connect / as sysdba

SQL> oradebug setmypid
SQL> oradebug unlimit

SQL> ANALYZE TABLE SYSMAN.MGMT_SYSTEM_PERFORMANCE_LOG VALIDATE STRUCTURE CASCADE;

SQL> ANALYZE INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_01 VALIDATE STRUCTURE;

SQL> ANALYZE INDEX SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_02 VALIDATE STRUCTURE;

oradebug tracefile_name

-- This last command will tell the name of the trace and its location.
-- Please send us the trace.



++ Then recreate only the indexes involved in the above failing query.



SQL> alter index <index_name> rebuild online;

To Get the Index DDL, Use


set long 100000
set linesize 200 trimspool on
set heading off pagesize 0
spool cridx.sql
select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&OWNER') from dual;

spool off

REFERENCES
NOTE:17806696.8 - Bug 17806696 - Corruption / ORA-8102 / ORA-600 from update of chained row in OLTP compressed table

NOTE:1638904.1 - SRDC - ORA-600 / ORA-700 / ORA-7445: Checklist of Evidence to Supply for Version 11g and Above
NOTE:1562139.1 - 11.2.0.4 Patch Set - Availability and Known Issues
NOTE:10314054.8 - Bug 10314054 - ORA-600 [13001] or similar from DELETE/UPDATE/MERGE SQL with non-deterministic WHERE clause
NOTE:14150255.8 - Bug 14150255 - Diagnostic enhancement for compare column failures (such as ORA-600 [13011] errors)