Shulou(Shulou.com)06/01 Report--

I. Overview of the problem

1. Database environment:

Oracle Database 11.2.0.3.0 for Oracle Linux Server release 6.4 dint RAC, virtual machine

2. During inspection, a database alert.log log reported ORA-00600 [13011] error frequently, which did not cause database downtime, but affected the business. The error is as follows:

[oracle@NODE1 trace] $grep-I ora-00600 alert*.log | grep 13011 | sort-uORA-00600:: [13011], [321401], [33682485], [24], [33682485], [3], [], []-- Tue Feb 0600: 07:53 error ORA-00600:: [13011], [321401], [33682485], [27], [33682485], [3] [], []

Information in the trace file:

From this, we can see that the DELEDE operation on the XXXXXMIN.XXX_XX_XX_XXX_ OLD table caused the error to occur.

Dump continued from file: / u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_19795.trcORA-00600: [13011], [321401], [33682485], [27], [33682485], [3], [], [] = Dump for incident 49853 (ORA 33682485) = * 2018-02-06 09:37:44.987dbkedDefDump (): 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

Second, problem analysis

1. MOS's description of 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 the articles "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)". The error is caused by a DML operation on a table and a corrupted index corresponding to the table. The solution is to find out the operating table and the damaged index and rebuild the index.

2. Find the error object

Find the error object according to ORA-00600 [13011], [321401], [33682485], [27], [33682485], [3] error codes:

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

Information in the trace file:

BH (0xf60ee308) file#: 8 rdba: 0x0201f435 (8pr 128053) class: 1 ba: 0xf6c96000-- its object XXXXXMIN.XXX_XX_XX_XXXXX_OLD is consistent with the query set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0Magna 25 dbwrid: 0 obj: 321401 objn: 321401 tsn: 8 afn: 8 hint: F hash: [0x13ef9fd78] lru: [0xc900efb0m0xaf13f128] ckptq: [NULL] fileq: [NULL] objq: [0x132d5a950Mag0x132d5a950] objaq: [0x132d5a940 order 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 (8mp 128053)-consistent with query Its object is XXXXXMIN.XXX_XX_XX_XXXXX_OLD scn: 0x0001.084d4f80 seq: 0x01 flg: 0x06 tail: 0x4f800601 frmt: 0x02 chkval: 0x538d type: 0x06=trans dataHex 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 the trace file according to 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 0xlx objn% d\ n" tsn: Tablespace Number where the INDEX is stored .rdba: Relative data block address of the INDEX segment header.

Look for the trace file according to document 1499.1 and did not find the corresponding error. It seems that it is different from the situation described in the document and needs further analysis.

4. According to ROWID analysis

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

Select owner,index_name,index_type from dba_indexes where table_name='XXX_XX_XX_XXXXX_OLD' and owner='XXXXXMIN' The OWNER INDEX_NAME INDEX_TYPE----XXXXXMIN PK_XXX_XX_XX_XXXXX NORMAL-- index creation statement is as follows: DBMS _ METADATA.GET_DDL (UPPER ('INDEX')) UPPER ('PK_XXX_XX_XX_XXXXX') UPPER ('XXXXXMIN'))-CREATE UNIQUE INDEX "XXXXXMIN" . "PK_XXX_XX_XX_XXXXX" ON "XXXXXMIN". "XXX_XX_XX_XXXXX_OLD" ("AX_ID" "BX_ID")

According to the creation statement of the "XXXXXMIN". "PK_XXX_XX_XX_XXXXX" index, the index is a B-tree index, which is based on the binary tree and consists of branch 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 that differ between a full table scan and an index scan:

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 results are as follows:

ROWID RELATIVE_FNO BLOCK--AABOd5AAIAAAfQ1AAP 8 128053AABOd5AAIAAAfQ1AAQ 8 128053AABOd5AAIAAAfQ1AAR 8 128053AABOd5AAIAAAfQ1AAY 8 128053AABOd5AAIAAAfQ1AAZ 8 128053AABOd5AAIAAAfQ1AAa 8 128053AABOd5AAIAAAfQ1AAb 8 128053AABOd5AAIAAAfQ1AAc 8 128053AABOd5AAIAAAfQ1AAd 8 128053AABOd5AAIAAAfQ1AAe 8 128053AABOd5AAIAAAfQ1AAf 8 128053AABOd5AAIAAAfQ1AAg 8 128053AABOd5AAIAAAfQ1AAq 8 128053AABOd5AAIAAAfQ1AAr 8 128053AABOd5AAIAAAfQ1AAs 8 12805315 rows selected.

5. Verify that there are different rows between the full table scan and the index scan of the table

Find out the discrepancy data according to the following statement:

Select e.journal 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 * * * AABOd5AAIAAAzAPAAO15 rows selected.

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

-- SQL execution plan to scan the data queried by index scanning SQL > alter session set statistics_level=all; Session altered.SQL > select e.rownumrecoverrowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.AXNUBXID databases * and e.BXBXID databases * AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID -* AABOd5AAIAAAGcaABRSQL > select * from table (dbms_xplan.display_cursor (null) Null,'allstats last')) PLAN_TABLE_OUTPUT- -SQL_ID cy48jvzrnuv22 Child number 1--select e.paper.rownum Rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.axially identified clients * and e.BX_ID=**Plan hash value: 1022151449 -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |- -- | 0 | SELECT STATEMENT | | 1 | 1 | 00 00.01 | 3 | | 1 | COUNT | | 1 | 1 | 1 | 00 TABLE ACCESS BY INDEX ROWID 00.01 | 3 | 2 | TABLE ACCESS BY INDEX ROWID | XXX_XX_XX_XXX_OLD | 1 | 1 | 1 | 00 INDEX UNIQUE SCAN 00.01 | 3 | * 3 | INDEX UNIQUE SCAN | PK_XXX_XX_XX_XXX | 1 | 1 | 1 | 00:00:00 | .01 | 2 |-Predicate Information (identified by operation id):-3-access ("E". "AX_ID" = * AND "E". "BX_ID" = * *) 21 rows selected.--SQL executes the data SQL > select / * + full (e) * / e.* that is scheduled to be queried through the full table. Rownum,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.axially IDC clients * and e.BX IDPs * AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID -* AABOd5AAIAABFRCACkSQL > select * from table (dbms_xplan.display_cursor (null) Null,'allstats last')) PLAN_TABLE_OUTPUT- -SQL_ID 14vbv6bu472ty Child number 1--select / * + full (e) * / e.recording journal rownum Rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.axially IDC customers * and e.BXY IDPs * Plan hash value: 3364144674 Murray- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |- -- | 0 | SELECT STATEMENT | | 1 | 1 | 00 SELECT STATEMENT 00.01 | 68 | 1 | 1 | COUNT | | 1 | 1 | 00 00.01 | 68 | | * 2 | TABLE ACCESS FULL | XXX_XX_XX_XXX_OLD | 1 | 1 | 1 | 00.01 | 68 | -Predicate Information (identified by operation id):-2-filter (("E". "AX_ID" = * AND "E". "BX_ID" = *)) 20 rows selected.

There are differences between full table scan and index scan by comparison.

SQL > select e.rechargeRownum from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.axially ID cards * and e.BX ID cards * 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.axially IDC clients * and e.BX IDPs * AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWNUM ROWID -* AABOd5AAIAABFRCACk

Summary: when executing delete from "XXXXXMIN". "XXX_XX_XX_XXXXX_OLD" where "AX_ID" =: 1, the execution plan of the SQL is to scan the index, 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). When the value of the variable ": 1" happens to be an abnormal value, it results in an error of ORA 600[ 13011].

III. Solutions

1. Rebuild the XXXXXMIN.PK_XXX_XX_XX_XXXXX index

Because XXXXXMIN.PK_XXX_XX_XX_XXXXX is the joint primary key index of the "AX_ID", "BX_ID" column, and the AX_ID column is the associated foreign key of "XXXXXMIN". "XXX_VX" ("ID"), and the BX_ID column is the associated foreign key of "XXXXXMIN". "XXX_DATAXXXXX" ("ID"). Therefore, in order to avoid the impact on the business, ONLINE online reconstruction is used to rebuild the XXXXXMIN.PK_XXX_XX_XX_XXXXX index.

ALTER INDEX XXXXXMIN.PK_XXX_XX_XX_XXX REBUILD ONLINE

2. Verify the data after re-indexing

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.