Read the Note:155933.1 -Subject: Resolving an ORA-600 [12700] error -Type: HOWTO

We would like to see that our application act according to a specific logic, and would like to reproduce this error.

The problem is how, is there any known way ?

Tried to create an index on a specific tablespace, then shutdown the instance, messed up with the tbs file, but this crashed the whole Oracle.

Idea, anyone ?


Thanks,

Tal (otal@mercury.co.iil)

This will occur when oracle is trying to access a row using its ROWID, which has been obtained from an index.

A mismatch was found between the index rowid and the data block it is
pointing to. The rowid points to a non-existent row in the data block.
The corruption can be in data and/or index blocks.

many times ORA-600 [12700] can also be reported due to a consistent read (CR) problem.

The information dumped to the trace file varies greatly between releases:

- in Oracle 7.3.x it is ORA-600 [12700][a][b] , where
Arg [a] dba (Data Block Address)
Arg [b] slot number (number of the row in the block pointed by the dba)

- in Oracle 8.x and 9.x, it is ORA-600 [12700][a][b][c] , where
Arg [a] dataobj# from sys.obj$
Arg [b] relative dba of the data block
Arg [c] slot number of the row in the data block

The arguments of the ORA-600 [12700] contains information obtained
from the index we are using.


To diagnose this problem, follow the steps below :

1- Identify the trace file containing the ora-600 error
2- Identify the possibly corrupted data object ie Check if the data object is corrupt
3- Identify the possibly corrupted indexes
4- If you find index corruption, you can re-create the corrupted index
5- If you find data corruption, you should follow Note:28814.1
"Handling Oracle Block Corruptions in Oracle7/8/8i"
6- If no data nor index corruption is found, then this can be a
consistent read problem. See suggestions below.
7- If further analysis is required in case of data or index corruption


-1- Identify the trace file containing the ora-600 error

Most probably, your alert.log file will contain an entry like :

Tue Jan 22 12:10:16 2003
Errors in file /users/ora816/rdbms/log/ora_6496.trc:
ORA-00600: internal error code, arguments: [12700], [2989], ....

So the trace file containing the ora-600 dump is named ora_6496.trc

-2- Identify the possibly corrupted data object

The ora-600 trace file will contain something similar to :

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12700], [2997], [16778259],...
Current SQL statement for this session:
select * from t12700bis where a='yyy'

In this case the problem must be with table "t12700bis".

The "Current SQL statement for this session:" may well show a more
complex SQL statement.

When the current SQL statement does not easily point you to the possible
corrupted data object, you can use the PL/SQL Procedure "oerr12700"
to convert the ora-600 Oracle 8.X arguments into some meaningful information :

Note: PL/SQL code for "oerr12700" procedure is at the end of this Note

Example output :

SQL> execute oerr12700( 2989,16777219,4294941081 )
ORA-600 [12700] [2989],[16777219],[4294941081]
--------------------------------------------------
there is an index pointing to a row in SCOTT.T12700
row is slot 4294941081 in file 4 block 3
one index entry is pointing to ROWID='AAAAutAAEAAAAADJmZ'
--------------------------------------------------
You may want to check the integrity of SCOTT.T12700
executing :
dbv file=<file_name> blocksize=<db_block_size> start=3 end=3
--------------------------------------------------
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(2989,16777219,4294941081)
-------------------------------------------------------

This means there is one index pointing to a non-existent row in SCOTT.T12700
The row is supposed to be in file=4,block=3 , slot=4294941081.
Index entry is pointing to ROWID 'AAAAutAAEAAAAADJmZ'.

At this point, the way to check if the data object is corrupted or not
is to do an ANALYZE TABLE <tname> VALIDATE STRUCTURE.

If this command does not return any error, then the data object is sane
and the most probable cause is a corrupted index.

Note that the ANALYZE TABLE command results in an exclusive lock being taken
out on the table for the duration of the command.

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.


-3- 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 in -2- 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.

Again, 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".

Example output :

SQL> execute oerr12700diag(2989,16777219,4294941081)
--------------------------------------------------
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 AAAAutAAEAAAAADJmZ index is corrupted
..................................................
.
To test SCOTT.T12700 indexes
.
.
To test INDEX I1T12700 you run :
.
select rowid "I1T12700 corrupted!" from
(SELECT /*+ INDEX_FFS(T12700,I1T12700) */
N,rowid from SCOTT.T12700 where N=N)
where rowid='AAAAutAAEAAAAADJmZ';


.
To test INDEX IT12700 you run :
.
select rowid "IT12700 corrupted!" from
(SELECT /*+ INDEX_FFS(T12700,IT12700) */
A,rowid from SCOTT.T12700 where A=A)
where rowid='AAAAutAAEAAAAADJmZ';
.

In this particular case , this is the output from the execution of the
above SQL statements :

SQL> r
1 select rowid "I1T12700 corrupted!" from
2 (SELECT /*+ INDEX_FFS(T12700,I1T12700)
3 */ N,rowid from SCOTT.T12700 where N=N)
4* where rowid='AAAAutAAEAAAAADJmZ'

no rows selected
.
This means INDEX I1T12700 does NOT contains any entry pointing to
the non-existent row. The index is sane.
.
SQL> r
1 select rowid "IT12700 corrupted!" from
2 (SELECT /*+ INDEX_FFS(T12700,IT12700) */
3 A,rowid from SCOTT.T12700 where A=A)
4* where rowid='AAAAutAAEAAAAADJmZ'

IT12700 corrupted!
------------------
AAAAutAAEAAAAADJmZ
.

This means INDEX IT12700 contains one entry pointing to the non-existent SCOTT.T12700's row identified by its rowid='AAAAutAAEAAAAADJmZ'. So, INDEX IT12700 is corrupt.

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='AAAAutAAEAAAAADJmZ'

PS:It is possible that the "bad" rowid gets used by an insert after the error (ora-600 [12700]) has occurred.

This could potentially give 2 rows in the index pointing to the same rowid.

For example :
- an index points to slot 3 in a given data block which does not exists.
SELECT statement will return ora-600 [12700] ...
- a new insert results in a row going into slot 3
SELECT does not fail, an UPDATE fails with ora-600 [13013]
The corrupt index will now have 2 rows pointing to the same rowid (slot 3)
A sane index will have 1 row pointing to the same rowid (slot 3)

-4- If you find index corruption, you can re-create the corrupted index

If further analysis is required you must gather some additional
information before dropping the corrupted index. See point -7- .

If you decide to drop the corrupted indexes you should also consider
additional dependencies and be ready to act accordingly.

For example, if the corrupted index is being used by existing integrity
constraints ( Primary Key, Unique, Foreign Key ,...) you will need to drop such constraints before dropping the index.

-5- If you find data corruption

-6- If no data or index corruption is found

Then this could be a consistent read problem.

- 7- If further analysis is required in case of data or index corruption

If further analysis is required in case of data or index corruption
please log the issue with Oracle Support Services.

To find the root cause of the corruption :
- database should be in archivelog mode
- and archived redo log files should be available to dump
the redo for data and index blocks

=============================================
PL/SQL Code for utility procedures "oerr12700" and "oerr12700diag"

Just run procedures which should be created and used by SYS,
both are dependent on the dbms_utility and dbms_rowid packages
provided by Oracle in $ORACLE_HOME/rdbms/admin/dbmsutil.sql


Hope this will help you in analzing your problem
If you are still stucking....write me offline
Sanjit

Did you read the question ?
It was "how do I reproduce this error in a clean env?".

Tal.