ora600[12700]解决方法

Resolving an ORA-600 [12700] error in Oracle 8 and above[@more@]

ora600[12700]解决方法［转自metalink］

===========================================================

作者: brotherxiao(http://brotherxiao.itpub.net)
发表于: 2006.09.27 15:44
分类: Oracle技术讨论
出处: http://brotherxiao.itpub.net/post/3712/213680
---------------------------------------------------------------

一次碰到ora600[12700]，在metalink上找到了解决办法。

还遇到N多600错误，其中有些Metalink上都查不到
一个ora-600[12700]的诊断和解决方法（转贴metalink）Resolving an ORA-600 [12700] error in Oracle 8 and above
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PURPOSE:

This article discusses possible actions for handling the Oracle internal
error “ORA-600 [12700]”.

The information here is only applicable to the versions listed and is
provided only for guidance.

See Note 28229.1 for a description of ORA-600 [12700] internal error.

SUGGESTIONS: ” Handling ORA-600 [12700] ”
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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
2.1- 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 May 29 13:10:16 2001
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 :

*** 2001-05-29 18:59:53.735
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> set serveroutput on
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= blocksize= 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 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= blocksize= start= end=

Note: 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 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”.

Note: PL/SQL code for “oerr12700diag” procedure is at the end of this Note

“oerr12700diag” procedure will give you a list of SQL statements intended
to find if one index has an index entry pointing to the problematic ROWID.
.

Example output :

SQL> set serveroutput on
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’

Note:
~~~~~
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, you should follow Note 28814.1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

6 If no data or index corruption is found
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Then this could be a consistent read problem.

If you can reproduce the error using SQL*Plus, set Event 10226 before
reproducing the problem :

SQL>alter session set events ‘10226 trace name context forever, level 10’ ;
SQL> ... execute whatever SQL statements to reproduce the problem

If the error occurs executing some kind of application ( Forms, Pro*C ,...)
try to set event 10226 in the application before reproducing the problem .

If you can’t modify the application code to set event 10226, shutdown the
database, and add this line to your init.ora file :

event=”10226 trace name context forever, level 10”

startup the database and reproduce the problem.
Shutdown the database, remove the event from your init.ora, and startup again.

In all cases, gather the alert.log and ALL the trace files generated in
user_dump_dest and background_dump_dest during the test and send them all
to Oracle Support.

– 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”
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Note: this procedures 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

CREATE OR REPLACE PROCEDURE oerr12700( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;
fname VARCHAR2 ;
dbs number ;
dbs_x varchar2(129);
x number;

BEGIN

x:= dbms_utility.get_parameter_value(‘db_block_size’,dbs,dbs_x);

nfile:=dbms_utility.data_block_address_file(b);
select FILE_NAME into fname from dba_data_files
where RELATIVE_FNO = nfile ;

nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(’ ORA-600 [12700] [‘||a||’],[‘||b||’],[‘||c||’]’);
dbms_output.put_line(‘—————————————————————————’);
dbms_output.put_line(‘there is an index pointing to a row in ‘||un||’.’||tn);
dbms_output.put_line(‘row is slot ‘||c||’ in file ‘||nfile||’ block ‘||nblock);
dbms_output.put_line(‘one index entry is pointing to ROWID=’‘’|| trowid||’‘’‘);
dbms_output.put_line(‘—————————————————————————’);
dbms_output.put_line(‘You may want to check the integrity of ‘||un||’.’||tn);
dbms_output.put_line(‘executing :’);
dbms_output.put_line(‘dbv file=’||fname||’
blocksize=’||dbs||’ start=’|| nblock||’ end=’||nblock);
dbms_output.put_line(‘—————————————————————————’);
—dbms_output.put_line(‘IF dbv does not show any corruption, you can try to’);
dbms_output.put_line(‘find the corrupted indexes using the queries proposed’);
dbms_output.put_line(‘by the procedure oerr12700diag(‘||a||’,’||b||’,’||c||’)’);
dbms_output.put_line(‘———————————————————————————-’);
END;
/

CREATE OR REPLACE PROCEDURE oerr12700diag( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;

cursor pindexes(towner varchar2, tname varchar2) is
select C.INDEX_NAME,COLUMN_NAME from dba_ind_columns C, dba_indexes I
where c.INDEX_NAME=i.INDEX_NAME
and I.INDEX_TYPE <> ‘DOMAIN’
and C.TABLE_OWNER=towner and C.TABLE_NAME=tname
and C.COLUMN_POSITION=1 ;

rpindexes pindexes%rowtype;

BEGIN
nfile:=dbms_utility.data_block_address_file(b);
nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(‘—————————————————————————’);
dbms_output.put_line(‘IF dbv did not show any corruption, you can try to’);
dbms_output.put_line(‘find the corrupted indexes using following queries:’);
dbms_output.put_line(‘———————————————————————————-’);
dbms_output.put_line(‘If a query returns “no rows selected” index is sane’);
dbms_output.put_line(‘If a query returns ‘||trowid||’ index is corrupted’);
dbms_output.put_line(‘..................................................’);

dbms_output.put_line(‘.’);
dbms_output.put_line(‘To test ‘||un||’.’||tn||’ indexes ‘) ;
dbms_output.put_line(‘.’);
for rpindexes in pindexes(un,tn) loop
dbms_output.put_line(‘.’);
dbms_output.put_line(‘To test INDEX ‘||rpindexes.INDEX_NAME||’ you run :’ );
dbms_output.put_line(‘.’);
dbms_output.put_line(‘select rowid “’||rpindexes.INDEX_NAME||’ corrupted!”
from ‘);
dbms_output.put_line(
‘(SELECT /*+ INDEX_FFS(‘||tn||’,’||rpindexes.INDEX_NAME||’) */ ‘);
dbms_output.put_line(
rpindexes.COLUMN_NAME||’,rowid from ‘||
un||’.’||tn||’ where ‘||
rpindexes.COLUMN_NAME||’=’||rpindexes.COLUMN_NAME||’) ’ );
dbms_output.put_line( ‘where rowid=’‘’||trowid||’‘’;’||’ ‘);
end loop ;
END;
/

－－转贴至metalink

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/330796/viewspace-867641/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/330796/viewspace-867641/