ORA600 [13011] logical error analysis and solution for tables and index data

ORA600 [13011] logical error analysis and solution for tables and index data

I. Problem Overview

1. database environment:

Oracle Database 11.2.0.3.0 for Oracle Linux Server release 6.4, RAC, Virtual Machine

2, inspection found a Database alert. log reported ORA-00600 [13011] error, frequent error, although did not cause database downtime, but has affected the business, the error is as follows:

[Oracle @ NODE1 trace] $ grep-I ora-00600 alert *. log | grep 13011 | sort-u
ORA-00600: [13011], [321401], [33682485], [24], [33682485], [3], [], [], [], [], [], [], [] -- error reported starting at 00:07:53 Tue Feb 06
ORA-00600: [13011], [321401], [33682485], [27], [33682485], [3], [], [], [], [], [], [], []

Trace file information:

This error occurs when DELEDE is performed on the XXXXXMIN. XXX_XX_XX_XXX_OLD table.

Dump continued from file:/u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_19795.trc
ORA-00600: [13011], [321401], [33682485], [27], [33682485], [3], [], [], [], [], [], [], []
========= Dump for incident 49853 (ORA 600 [13011]) ========
* ** 09:37:44. 987
DbkedDefDump (): Starting incident default dumps (flags = 0x2, level = 3, mask = 0x0)
----- Current SQL Statement for this session (SQL _id = b6nmg0fpy3smf )-----
Delete from "XXXXXMIN". "XXX_XX_XX_XXX_OLD" where "AX_ID" =: 1

Ii. Problem Analysis

1. Description of MOS on ORA 600 [13011:

Format: ORA-600 [13013] [a] [B] {c} [d] [e] [f] Arg
[A] Passcount Arg
[B] Data Object number Arg
{C} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated Arg
[D] Row Slot number Arg
[E] Decimal RDBA of block being updated (Typically same as {c}) Arg
[F] Code

Refer to New and Improved: ORA-600 [13013] "Unable to get a Stable set of Records" (Document ID 1438920.1) and ORA-600 [13013] "Unable to get a Stable set of Records" (Document ID 28185.1. This error is caused by a DML operation on a table, and the corresponding index of the table is damaged. The solution is to find the operated table and the damaged index, and re-create the index.

2. Find the error object

According to the ORA-00600 [13011], [321401], [33682485], [27], [33682485], [3] error code, find the error object:

Select dbms_utility.data_block_address_file (33682485) rfile, dbms_utility.data_block_address_block (33682485) blocks from dual;
 
RFILE BLOCKS
--------------------
8 128053
Select owner, segment_name, segment_type, tablespace_name, a. partition_name from dba_extents a where file_id = 8 and 128053 between block_id and block_id + blocks-1;
 
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME PARTITION_NAME
------------------------------------------------------------------------------------
XXXXXMIN XXX_XX_XX_XXX_OLD TABLE XXX

Trace file information:

BH (0xf60ee308) file #: 8 rdba: 0x0201f435 (8/128053) class: 1 ba: 0xf6c96000 -- the object XXXXXMIN. XXX_XX_XX_XXXXX_OLD is consistent with the query
Set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 25
Dbwrid: 0 obj: 321401 objn: 321401 tsn: 8 afn: 8 hint: f
Hash: [0x13ef9fd78, 0x13ef9fd78] lru: [0xc900efb0, 0xaf13f128]
Ckptq: [NULL] fileq: [NULL] objq: [0x132d5a950, 0x132d5a950] objaq: [0x132d5a940, 0x132d5a940]
St: XCURRENT md: NULL fpin: 'kddwh01: kdddel 'tch: 1 le: 0xcb0e3ee8
Flags: remote_transfered
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [65535]
Buffer tsn: 8 rdba: 0x0201f435 (8/128053) -- same as query, whose object is XXXXXMIN. XXX_XX_XX_XXXXX_OLD
Scn: 0x0001. 084d4f80 seq: 0x01 flg: 0x06 tail: 0x4f800601
Frmt: 0x02 chkval: 0x538d type: 0x06 = trans data
Hex dump of block: st = 0, typ_found = 1

3. Analyze the exception table

Analyze table xxxxxmin. xxx_xx_xx_xxxxx_old validate structure cascade;
ERROR at line 1:
ORA-01499: table/index cross reference failure-see trace file -- find trace files by document 1499.1

OERR: ORA-1499 table/Index Cross Reference Failure-see trace file [ID 1499.1]

Error ORA-1499 is produced by statement "analize table | cluster validate structure cascade" to report an inconsistency between a table or a cluster and its index where an index key value is not found in the index or vice versa.
The content of the trace file has:
: Tsn: rdba:
Description:
"Row not found in index"
"Table/Index row count mismatch"
"Row mismatch in index dba"
"Table row count/Bitmap index bit count mismatch"
"Kdavls: kdcchk returns % d when checking cluster dba 0x % 08lx objn % d \ n"
Tsn: Tablespace Number where the INDEX is stored.
Rdba: Relative data block address of the INDEX segment header.

No error is found in the trace file according to document 1499.1. It seems that the situation is different from that described in the document. Further analysis is required.

4. ROWID-based Analysis

Through the previous analysis we know that ORA-600 [13013] This error is caused by inconsistent logical data between the table and the index. Query the indexes that are explicitly associated:

Select owner, index_name, index_type from dba_indexes where table_name = 'xxx _ XX_XX_XXXXX_OLD 'and owner = 'xxxxxmin ';
 
OWNER INDEX_NAME INDEX_TYPE
----------------------------------------------------------------------
XXXXXMIN PK_XXX_XX_XX_XXXXX NORMAL
 
-- The index creation statement is as follows:
 
DBMS_METADATA.GET_DDL (UPPER ('index'), UPPER ('pk _ XXX_XX_XX_XXXXX '), UPPER ('xxxxxmin '))
Bytes -----------------------------------------------------------------------------------------------------------
Create unique index "XXXXXMIN". "PK_XXX_XX_XX_XXXXX" ON "XXXXXMIN". "XXX_XX_XX_XXXXX_OLD" ("AX_ID", "BX_ID ")

According to "XXXXXMIN ". "PK_XXX_XX_XX_XXXXX" index creation statement. The index is a B-tree index. It is based on binary trees and consists of branch blocks and leaf blocks, including the value of each index column and the ROWID corresponding to the row.

Use the following statement to query the rows with differences between full table scan and index scan:

1 select/* + INDEX_FFS (t pk_xxx_xx_xx_xxx) */rowid,
2 dbms_rowid.ROWID_RELATIVE_FNO (rowid) relative_fno,
3 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) block
4 from XXXXXMIN. XXX_XX_XX_XXXXX_OLD t where (t. AX_ID is not null or BX_ID is not null)
5 minus
6 select/* + FULL (t1) */rowid,
7 dbms_rowid.ROWID_RELATIVE_FNO (rowid) relative_fno,
8 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) block from XXXXXMIN. XXX_XX_XX_XXXXX_OLD t1;

The query result is as follows:

ROWID RELATIVE_FNO BLOCK
----------------------------------------
AABOd5AAIAAAfQ1AAP 8 128053
AABOd5AAIAAAfQ1AAQ 8 128053
AABOd5AAIAAAfQ1AAR 8 128053
AABOd5AAIAAAfQ1AAY 8 128053
Aabmax5aaiaaafq1aaz 8 128053
AABOd5AAIAAAfQ1AAa 8 128053
AABOd5AAIAAAfQ1AAb 8 128053
AABOd5AAIAAAfQ1AAc 8 128053
AABOd5AAIAAAfQ1AAd 8 128053
AABOd5AAIAAAfQ1AAe 8 128053
Aabmax5aaiaaafq1aaf 8 128053
AABOd5AAIAAAfQ1AAg 8 128053
AABOd5AAIAAAfQ1AAq 8 128053
AABOd5AAIAAAfQ1AAr 8 128053
AABOd5AAIAAAfQ1AAs 8 128053
15 rows selected.

5. Verify that the full table scan and index scan of the table have different rows.

Use the following statement to find the different data:

Select e. *, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. rowid> (select min (x. rowid) from XXXXXMIN. XXX_XX_XX_XXX_OLD x where x. AX_ID = e. AX_ID and x. BX_ID = e. BX_ID );
 
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWID
-------------------------------------------------------------------------------------------
**************************************** * ********* AABOd5AAIAAAzAPAAM
**************************************** * ********* AABOd5AAIAAAzAPAAN
**************************************** * ********* AABOd5AAIAAAzAPAAP
**************************************** * ********* AABOd5AAIAAAzAPAAL
**************************************** * ********* AABOd5AAIAAAzAPAAQ
**************************************** * ********* AABOd5AAIAABFRCACA
**************************************** * ********* AABOd5AAIAABFRCACl
**************************************** * ********* AABOd5AAIAABFRCACk
**************************************** * ********* AABOd5AAIAAAzAPAAB
**************************************** * ********* AABOd5AAIAAAzAPAAE
**************************************** * ********* AABOd5AAIAABFRCACC
**************************************** * ********* AABOd5AAIAABFRCACm
**************************************** * ********* AABOd5AAIAAAzAPAAD
**************************************** * ********* AABOd5AAIAABFRCACB
**************************************** * ********* AABOd5AAIAAAzAPAAO
15 rows selected.

Take one of the data to verify the difference between full table scan and index scan.

-- SQL Execution Plan: Data queried by index Scan
 
SQL> alter session set statistics_level = all;
Session altered.
SQL> select e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = ***** and e. BX_ID = **;
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID
-----------------------------------------------------------------------------------------------------
* *************************** AABOd5AAIAAAGcaABR
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID cy48jvzrnuv22, child number 1
-------------------------------------
Select e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = ***** and e. BX_ID = **
Plan hash value: 1022151449
Bytes --------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes --------------------------------------------------------------------------------------------------------------
| 0 | select statement | 1 || 1 | 00:00:00. 01 | 3 |
| 1 | COUNT | 1 | 1 | 00:00:00. 01 | 3 |
| 2 | table access by index rowid | XXX_XX_XX_XXX_OLD | 1 | 1 | 00:00:00. 01 | 3 |
| * 3 | index unique scan | PK_XXX_XX_XX_XXX | 1 | 1 | 00:00:00. 01 | 2 |
Bytes --------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("E". "AX_ID" = ***** AND "E". "BX_ID" = **)
21 rows selected.
 
-- Data queried through the full table in the SQL Execution Plan
 
SQL> select/* + full (e) */e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = *** and e. BX_ID = **;
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID
-----------------------------------------------------------------------------------------------------
* *************************** AABOd5AAIAABFRCACk
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 14vbv6bu472ty, child number 1
-------------------------------------
Select/* + full (e) */e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = *** and e. BX_ID = **

Plan hash value: 3364144674
Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ----------------------------------------------------------------------------------------------------
| 0 | select statement | 1 || 1 | 00:00:00. 01 | 68 |
| 1 | COUNT | 1 | 1 | 00:00:00. 01 | 68 |
| * 2 | table access full | XXX_XX_XX_XXX_OLD | 1 | 1 | 1 | 00:00:00. 01 | 68 |
Bytes ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("E". "AX_ID" = ***** AND "E". "BX_ID" = **))
20 rows selected.

Differences between full table scan and index Scan

SQL> select e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = ***** and e. BX_ID = **;
 
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID
-----------------------------------------------------------------------------------------------------
* *************************** AABOd5AAIAAAGcaABR

SQL> select/* + full (e) */e. *, rownum, rowid from XXXXXMIN. XXX_XX_XX_XXX_OLD e where e. AX_ID = *** and e. BX_ID = **;
 
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID
-----------------------------------------------------------------------------------------------------
* *************************** AABOd5AAIAABFRCACk

Summary: Execute delete from "XXXXXMIN ". "XXX_XX_XX_XXXXX_OLD" where "AX_ID" =: 1, the SQL Execution Plan is to perform index scanning, because the logical data between the table and the index is inconsistent (the index data composed of the value of the index column and the ROWID corresponding to the row is inconsistent with the table data), in the variable ": when the value of 1 "happens to be an abnormal value, the ORA 600 [13011] error is reported.

Iii. Solutions

1. Rebuild the index XXXXXMIN. PK_XXX_XX_XX_XXXXX.

Because XXXXXMIN. PK_XXX_XX_XX_XXXXX is the Union primary key index of the "AX_ID", "BX_ID" column, and the AX_ID column is used as "XXXXXMIN ". the associated foreign key of "XXX_VX" ("ID"). The BX_ID column is used as "XXXXXMIN ". the associated foreign key of "XXX_DATAXXXXX" ("ID. To avoid service impact, use ONLINE reconstruction to reconstruct the XXXXXMIN. PK_XXX_XX_XX_XXXXX index.

Alter index xxxxxmin. PK_XXX_XX_XX_XXX rebuild online;

2. Verify the re-indexing data

1 select/* + INDEX_FFS (t pk_xxx_xx_xx_xxx) */rowid,
2 dbms_rowid.ROWID_RELATIVE_FNO (rowid) relative_fno,
3 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) block
4 from XXXXXMIN. XXX_XX_XX_XXXXX_OLD t where (t. AX_ID is not null or BX_ID is not null)
5 minus
6 select/* + FULL (t1) */rowid,
7 dbms_rowid.ROWID_RELATIVE_FNO (rowid) relative_fno,
8 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) block from XXXXXMIN. XXX_XX_XX_XXXXX_OLD t1;

No rows selected

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151013.htm