Understanding and Diagnosing ORA-00600 [13011] Errors [ID 1392778.1]

rongshiyuan發表於2013-01-22
Understanding and Diagnosing ORA-00600 [13011] Errors [ID 1392778.1]

In this Document


Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Purpose

This article will discuss the common causes of and troubleshooting tips for ORA-00600 [13011].

During a delete operation we are trying to get a consistent set of rows and have exceeded a 5000 pass count when we raise this exception.

The number of arguments and their meaning vary depending on the object types relating to the error.

- 3 Argument format:

This format relates to Oracle 8.0.3 and above for deletes from Views or Index Only Tables (IOT)

Arg [a] Passcount
Arg [b] Object Name
Arg [c] Code

- 5 Argument format:

Arg [a] Data Object ID
Arg [b] Tablespace Decimal Relative DBA (RDBA) of block containing the row to be deleted
Arg [c] Row slot number.
Arg [d] Decimal RDBA of block being updated (Typically same as [b]).
Arg [e] Code

Troubleshooting Steps

Common Causes & Solutions:

1. The most common cause of this error is an index corruption. The first step is to check the indexes for corruption, i.e. run

SQL> ANALYZE INDEX VALIDATE STRUCTURE;

on any indexes referenced in the execution plan. Drop and recreate if needed. To find out the execution plan referenced in the trace file created for ORA-600 [13011], look for the "Plan Table" section following the "Session Cursor Dump" section:

E.g.:

----- Session Cursor Dump -----
Current cursor: 15, pgadep=1

...

============
Plan Table
============
----------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 50 | |
| 1 | MERGE JOIN SEMI | | 41 | 161K | 50 | 00:00:01 |
| 2 | MERGE JOIN | | 41 | 161K | 48 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | WORKFLOW | 2 | 32 | 2 | 00:00:01 |
| 4 | INDEX FULL SCAN | PK_WORKFLOW | 5 | | 1 | 00:00:01 |

...

Note: There are specific actions when the corrupted index is a dictionary index:

E.g.:

- AWR:

Note 1226835.1 Ora-600 [13011] On Delete From Wri$_Adv_Parameters Table

- SMON related table "smon_scn_time":

Note 1361872.1 Instance Terminated With Error ORA-00474: SMON Process Terminated With Error


For a general discussion regarding index corruption review Note 1499.1 OERR: ORA-1499 table/Index Cross Reference Failure - see trace file.

2. If the indexes do not report corruption, further test for corruption the base tables referenced in the execution plan or the statement producing the error:

SQL> ANALYZE TABLE VALIDATE STRUCTURE CASCADE;


Note: The analyze statement will place DML locks during its execution.

3. If no corruption is detected, check whether there is any special type of index referenced in the execution plan (e.g. bitmap indexes). Try to drop the indexes one by one in order to narrow the issue and double-check if the error still reproduces with the index removed. For example, bitmap indexes could cause ORA-600 [13011] or wrong results; see Note 456361.1 ORA-600 [13011] on Delete (Bug 7007924 Wrong results using bitmap index).

4. Check if there are any columns added with DEFAULT value NOT NULL. ORA-600 [13009] / ORA-600 [13011] in COLUMN comparison for a COLUMN previously added with a DEFAULT value NOT NULL:

SQL> ALTER TABLE ADD DEFAULT NOT NULL;


This bug is commonly hit by RMAN catalog with ORA-600 [13011] with code 17 (KDCMPF11) in a DELETE from table CKP caused by the SITE_KEY column comparison. In particular the SITE_KEY column was added via:

SQL> ALTER TABLE ckp ADD site_key NUMBER DEFAULT 0 NOT NULL;


See Note 7336280.8 Bug 7336280 - ORA-600[13009] / ORA-600[13011] on column added with DEFAULT value NOT NULL.

5. If none of the actions suggested above help in solving the issue, please contact Oracle Support. Minimum information required for a new service request related to this error includes the following files:

- alert log
- 2-3 trace files (if available) for different occurrences of ORA-600 [13011]
- for 11g and above, upload the incident package
- the database parameters file
- the list of Oracle patches (i.e. the output from "opatch lsinventory").

References

BUG:7007924 - WRONG RESULT FROM BITMAP INDEX
@ BUG:7336280 - SELECT FOR UPDATE OBY FAILS WITH ORA-600[13009] ON A TABLE WITH DEFAULT COL OPT
@NOTE:1074671.1 - RMAN PARTIAL RESYNC CRASHES WITH ORA-600 [13011]
NOTE:1226835.1 - Ora-600 [13011] On Delete From Wri$_Adv_Parameters Table
NOTE:1361872.1 - Instance Terminated With Error ORA-00474: SMON Process Terminated With Error
NOTE:1499.1 - OERR: ORA-1499 table/Index Cross Reference Failure - see trace file
NOTE:28184.1 - ORA-600 [13011] "Problem occurred when trying to delete a row"
NOTE:456361.1 - ORA-600 [13011] on Delete
NOTE:7007924.8 - Bug 7007924 - Wrong results using bitmap index
NOTE:7336280.8 - Bug 7336280 - ORA-600[13009] / ORA-600[13011] on column added with DEFAULT value NOT NULL
NOTE:745188.1 - ORA-600 [kcbchg1_6] And ORA-600 [13011] After Installation of Patch 5868257
NOTE:863643.1 - ORA-00600 [13011] when delete from WRH$ table

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-753022/,如需轉載,請註明出處,否則將追究法律責任。

相關文章