######################################################
How to Fix ORA-600 [12700] Internal Error Step By Step
######################################################
Eg. ORA-600 appears in AlertSID.log file :
ORA-00600: internal error code, arguments: [12700], [18], [416476427], [15], [83969031], [25], [], []
ORA-00600: internal error code, arguments: [12700], [18], [416476597], [50], [83969031], [25], [], []
ORA-00600: internal error code, arguments: [12700], [18], [415794709], [9], [176160842], [25], [], []
Error Format: ORA-600 [12700] [a] [b] [c] [d] [e]
--------------------------------------------------------------
From Oracle 9i, there are up to five additional arguments:
Arg [a] Data Object ID
Arg [b] Table Relative Data Block Address (RDBA)
Arg [c] Row Slot Number of the row in the data block
Arg [d] If present - Index Relative Data Block Address (RDBA)
Arg [e] If present - Index Row Source Class
1. Identify the trace file containing the ora-600 error
------------------------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [12700], [18], [416476427], [15], [83969031], [25], [], []
Sat Jun 22 10:12:15 2019
Errors in file /.../..../diag/rdbms/DB/SID/xxxx_ora_YYYY.trc:
2. Identify the possibly corrupted data object
-----------------------------------------------------------------------------------------------
In the trace file, find ORA-600 Error :
ORA-00600: internal error code, arguments: [12700], [18], [416476597], [50], [83969031], [25], [], []
Current SQL statement for this session:
select u2.name, o2.name from ind$ i, obj$ o1, obj$ o2, user$ u1, user$ u2 where i.bo# = o1.obj# and o1.owner#=u1.user# and i.obj#=o2.obj# and o2.owner#
=u2.user# and u1.name=:1 and o1.name=:2 and i.type#=9
ORA-00600: internal error code, arguments: [12700], [18], [416476427], [15], [83969031], [25], [], []
Current SQL statement for this session:
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an
d linkname is null and subname is null
******************************************************************
In Our case we have both ind$ and obj$ objects that are affected.
******************************************************************
3. Convert the ora-600 Oracle arguments into some meaningful information
----------------------------------------------------------------------------------------------------
SQL> set serveroutput on
SQL> execute oerr12700( 18,416476427,15 )
ORA-600 [12700] [18],[416476427],[15]
--------------------------------------------------
there is an index pointing to a row in SYS.OBJ$
row is slot 20 in file 205 block 860
one index entry is pointing to ROWID='AAAAASADNAAAANcAAU'
--------------------------------------------------
You may want to check the integrity of SYS.OBJ$
executing :
dbv file=/..../..../oradata/system001.dbf
blocksize=8192 start=860 end=860
--------------------------------------------------
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(18,416476427,15)
-------------------------------------------------------
PL/SQL procedure successfully completed.
**************************************************************************
This means there is one index pointing to a non-existent row in SYS.OBJ$
The row is supposed to be in file=205,block=860 , slot=416476427.
Index entry is pointing to ROWID 'AAAAASADNAAAANcAAU'.
**************************************************************************
Another way to identify the possible corrupted data object is to select from
DBA_OBJECTS :
SELECT owner, object_name, object_type, object_id, data_object_id
FROM dba_objects
WHERE data_object_id = 18;
SQL> r
1 SELECT owner, object_name, object_type, object_id, data_object_id
2 FROM dba_objects
3* WHERE data_object_id = 18
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
------------ -------------- ------------ ------------ --------------
SYS OBJ$ TABLE 18 18
4. Check if the data object is corrupted or not
-----------------------------------------------------------------------------------------------
At this point, the way to check if the data object is corrupted or not
is to do an ANALYZE TABLE <table_name> VALIDATE STRUCTURE
SQL> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE
Table analyzed.
SQ
SQL> ANALYZE TABLE sys.ind$ VALIDATE STRUCTURE;
Table analyzed.
If you can not afford to lock the table in exclusive mode, you can
try to check the block reported by the PL/SQL script above using "dbv" :
dbv file=<where-the-data-is> blocksize=<db_block_size> start=<block> end=<block>
Note: <block> is the block number showed by oerr12700 procedure.
If dbv does not return any error message and "Pages Failing" is 0,
then the data is NOT corrupted, so the corruption must be in the indexes.
---------------------------------------------------------------------------------------------------------
$dbv file=/......./oradata/system001.dbf blocksize=8192 start=860 end=860
DBVERIFY: Release xxxxxxx- Production on Fri Jun 21 23:17:04 2019
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /.../system001.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1
Total Pages Processed (Data) : 1
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 10794338932012 (2513.1086117164)
5. Identify the possibly corrupted indexes
-------------------------------------------------------------------------------------------------
At this point you should be confident there is no data corruption.
The problem must be with some corrupted index.
Table identified above can have several indexes.
One way of finding the corrupted indexes is to run the command
"ANALYZE TABLE <tname> VALIDATE STRUCTURE CASCADE" and look at the
trace file it generates when it finds an error.
SQL> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
the ANALYZE TABLE ... command will lock your table in exclusive mode,
and you may want to check the indexes without locking anything.
In this case you can try to identify the corrupted indexes using the
procedure "oerr12700diag".
SQL> execute oerr12700diag( 18,416476427,15)
--------------------------------------------------
IF dbv did not show any corruption, you can try to
find the corrupted indexes using following queries:
-------------------------------------------------------
If a query returns "no rows selected" index is sane
If a query returns AAAAASABjAAEu0LAAP index is corrupted
..................................................
.
To test SYS.OBJ$ indexes
.
.
To test INDEX I_OBJ1 you run :
.
select rowid "I_OBJ1 corrupted!"
from
(SELECT /*+ INDEX_FFS(OBJ$,I_OBJ1) */
OBJ#,rowid from SYS.OBJ$ where OBJ#=OBJ#)
where rowid='AAAAASABjAAEu0LAAP';
.
To test INDEX I_OBJ2 you run :
.
select rowid "I_OBJ2 corrupted!"
from
(SELECT /*+ INDEX_FFS(OBJ$,I_OBJ2) */
OWNER#,rowid from SYS.OBJ$ where OWNER#=OWNER#)
where rowid='AAAAASABjAAEu0LAAP';
.
To test INDEX I_OBJ3 you run :
.
select rowid "I_OBJ3 corrupted!"
from
(SELECT /*+ INDEX_FFS(OBJ$,I_OBJ3) */
OID$,rowid from SYS.OBJ$ where OID$=OID$)
where rowid='AAAAASABjAAEu0LAAP';
PL/SQL procedure successfully completed.
SQL>
SQL>
1 select rowid "I_OBJ1 corrupted!"
2 from
3 (SELECT /*+ INDEX_FFS(OBJ$,I_OBJ1) */
4 OBJ#,rowid from SYS.OBJ$ where OBJ#=OBJ#)
5* where rowid='AAAAASADNAAAANcAAU'
select rowid "I_OBJ1 corrupted!"
*
ERROR at line 1:
ORA-08103: object no longer exists
SQL> select rowid "I_OBJ2 corrupted!"
2 from
3 (SELECT /*+ INDEX_FFS(OBJ$,I_OBJ2) */
4 OWNER#,rowid from SYS.OBJ$ where OWNER#=OWNER#)
5 where rowid='AAAAASADNAAAANcAAU';
no rows selected
SQL> select rowid "I_OBJ3 corrupted!"
from
2 3 (SELECT /*+ INDEX_FFS(OBJ$,I_OBJ3) */
4 OID$,rowid from SYS.OBJ$ where OID$=OID$)
5 where rowid='AAAAASADNAAAANcAAU';
no rows selected
----------------------------------------------------------
select * /*+ full (tablename) */ from tablename
where rowid='AAAAASADNAAAANcAAU'
We can conclude that the corrupted index is I_OBJ1.
To double check, you can see if table contains a row with the ROWID
reported by "oerr12700" procedure :
SQL> select * /*+ full (tablename) */ from tablename
2 where rowid='AAAAASADNAAAANcAAU'
SQL> select * /*+ full (sys.obj$) */ from sys.obj$
2 where rowid='AAAAASADNAAAANcAAU';
no rows selected
Since it's Dictionary Index owned by SYS, You may have an error when trying to drop and recreate index
SQL> drop index sys.I_OBJ1;
drop index sys.I_OBJ1
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
Data Dictionary objects may be not be allowed to be altered by a DDL. Data Dictionary tables stored in
table sys.bootstrap$ are not allowed to be altered with a DDL (drop, alter index, etc) as error ORA-701 is produced.
Since this index is part of sys.bootstrap$ and can not be modified, following are the 2 options suggested by Oracle Support:
A) Supported option is to recreate the database using expdp/create/impdp with or without TTS option
(TTS would take less time due to no physical import of data/creation of indexes).
B) Unsupported option does consist of an update of the DD (DataDictionary) which requires careful testing.
This is an undocumented/unsupported option which does consist of an update of the DD (DataDictionary).
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
