How to troubleshoot Oracle ORA-00600 6002 errors

This error is often in the database recovery, often encounter the following ORA-600 6002 error, which is a common, can be analyzed to eliminate one of the errors, Next let's look at the solution.

The following are recent errors in user data recovery:

* ** 13:56:04. 178
Ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [6002], [0], [0], [4], [0], [], [], []
----- PL/SQL Call Stack -----
Object line object
Handle number name
57cce5a18 418 package body SYS. DBMS_HA_ALERTS_PRVT
57cce5a18 552 package body SYS. DBMS_HA_ALERTS_PRVT

Incorrect reality: an error occurs when you execute an internal task in the SYS. DBMS_HA_ALERTS_PRVT package.
The ORA-00600 6002 error and index related, the specific content refers:
When Oracle tries to insert an index key value, it first needs to locate the appropriate location and perform relevant verification. The verification content includes the number of index columns and data size. Once inconsistency is found, the 6002 error of the ORA-600 will occur.

The explanations on MOS are as follows:

Oracle was trying to insert a key and key data into a B * tree index.
In order to do this, it had to first find the correct leaf block to do the insert.
Once the correct leaf block is retrieved, Oracle validates the block by checking the data size and number of columns in the key.
If there is a mismatch then ORA-600 [6002] is reported.

The main parameter meanings are as follows:

Arg [a] Number of bytes in keydata
Arg [B] Number of bytes in the index layer of the leaf header
Arg [c] Number of columns in index search key structure
Arg [d] Number of columns in the index layer fo the leaf header

If you encounter this error, you should first find the problematic index object and try to re-create the index to eliminate the impact of this error.
The following error header information is displayed in the tracking file:

Block header dump: 0x00c086ed
Object id on Block? Y
Seg/obj: 0x22a9 csc: 0x00. 1c87691f itc: 12 flg: E typ: 2-INDEX
Brn: 0 bdba: 0xc086e9 ver: 0x01 opc: 0
Inc: 0 exflg: 0
 
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a. 016.00045449 0x0080027f. 4951.02 C --- 0 scn 0x0000. 0b0c478c
0x02 0x0014. 00e. 000 aadce 0x01000b9f. 67ea. 26 C --- 0 scn 0x0000. 1c876660
0x03 0x0014. 01b. 000aada1 0x01000b9f. 67ea. 17 C --- 0 scn 0x0000. 1c87653d
0x04 0x000a. 002.000e628d 0x00800247. 6ebe. 29 C --- 0 scn 0x0000. 1c87691e
0x05 0x0034. 00e. 00000270 0x010061c8. 00c6. 11 ---- 1 fsc 0x0000.00000000
0x06 0x0009. 01d. 0001e6dc 0x0080008a. 8128.37 -- U-1 fsc 0x0031. 1c876923
0x07 0x0008. 017.0001d3c2 0x00800080. 8857.26 C --- 0 scn 0x0000. 1c87613a
0x08 0x0002. 02b. 0001720e 0x00801355. 7ebb. 1f C --- 0 scn 0x0000. 1c87654d
0x09 0x000a. 01f. 000e6254 0x00800245. 6ebe. 0e C --- 0 scn 0x0000. 1c87683c
0x0a 0x0009. 004.0001e89d 0x0080008a. 8128.30 C --- 0 scn 0x0000. 1c876841
0x0b 0x000a. 00a. 000e6279 0x0080023f. 6ebe. 08 C --- 0 scn 0x0000. 1c87666a
0x0c 0x000a. 011.000e6252 0x0080022f. 6ebe. 3e C --- 0 scn 0x0000. 1c87613e

We can see that the problematic object number is 22a9, And the type is the index.
You can find the object name through the database query and solve the problem by re-indexing:

The Code is as follows:	Copy code
SQL> select object_name from dba_objects where object_id = to_number ('22a9 ', 'xxxxx '); OBJECT_NAME Bytes ------------------------------------------------------------------------------------------------------------------------ WRI $ _ ALERT_THRESHOLD_LOG_PK SQL> alter index WRI $ _ ALERT_THRESHOLD_LOG_PK rebuild online; Index altered.

6002 errors need to be analyzed based on different objects, which can be solved through index reconstruction. Sometimes the problem occurs on the IOT object. You can back up the data, TRUNCATE and INSERT the record.

Another article

ORA-600 error 3020, database in fault recovery, application archiving log encountered 3020 error:

ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement:
Alter database recover logfile '/arch/prod1_2711_681480148.arc'
ORA-00283: recovery session canceled due to errors
The ORA-12801: error signaled in parallel query server P011, instance hpdb1: PROD1 (1)
ORA-00600: internal error code, arguments: [3020], [37], [94633], [2], [3659], [604], [16], []
ORA-10567: Redo is inconsistent with data block (file #37, block #94633)
ORA-10564: tablespace pai_undots2
ORA-01110: data file 37

After this error occurs, the service is interrupted and cannot be resumed. The 3020 error is explained on Metalink mainly because:

This is called a 'stuck RECOVERY '.
There is an inconsistency between the information stored in the redo
And the information stored in a database block being recovered.

That is to say, the information recorded in the Redo is found to be inconsistent with the recovered data block information during recovery, resulting in the recovery being unable to continue. For example, if you Update Some record from 3 to 2, it is found that the record is not 3 at all, and the restoration cannot continue.

This error may be caused by the loss of a write Redo operation, which may be caused by abnormal downtime or storage failure.
This error can be reported if any of these updates are lost for some reason.

This error occurs. If there is no backup, the data is not particularly important. You can use some implicit parameters or forcible means to open the database, but data loss will inevitably occur, olive has made such an attempt.
The following proven practices are available:

The Code is as follows:	Copy code
Recover database [using backup controlfile] allow 1 resume uption;

Mark inconsistent blocks as corrupt and then perform further recovery attempts.

In this case, we finally chose Incomplete recovery and record the data. Sometimes it is a recovery if we give up!