Como diagnosticar un 0600 - [12700] [ ] [ ] [ ]
Muchas veces una corrupcion de bloque puede venir asociada a un indice corrupto, y la ejecucion de un bloque corrupto puede dar un ORA 600[12700], el cual lo dice el alert de la base de datos y nos encontramos por ejemplo con este ejemplo (valga la redundancia)
Sat May 5 15:03:12 2007
ARC1: Beginning to archive log 3 thread 1 sequence 49943
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/archive/oradata/ODSORA/1_49943.arc’
ARC1: Completed archiving log 3 thread 1 sequence 49943
Sat May 5 15:35:49 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s003_27559.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:40:25 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s001_2441.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:55:32 2007
Thread 1 advanced to log sequence 49945
Sat May 5 15:55:32 2007
Current log# 5 seq# 49945 mem# 0: /oracle/dbs/oradata/ODSORA/redo05a.log
Current log# 5 seq# 49945 mem# 1: /u20/oradata/ODSORA/redo05b.log
Sat May 5 15:55:32 2007
ARC0: Evaluating archive log 4 thread 1 sequence 49944
Sat May 5 15:55:32 2007
ARC0: Beginning to archive log 4 thread 1 sequence 49944
Para lo cual hacemos lo siguiente:
1) Creamos el siguiente procedure con el user “SYS”
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(513) ;
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;
/
2) Lo ejecutamos:
SQL> set serveroutput on
SQL> execute oerr12700( 90646, 71560692, 9);
The output of the above procedure execution gives the following result
SQL> execute oerr12700( 90646, 71560692, 9);
ORA-600 [12700] [90646],[71560692],[9]
————————————————–
there is an index pointing to a row in PORTAL.PREMIUM_TRANSACTION_DATA
row is slot 9 in file 17 block 257524
one index entry is pointing to ROWID=’AAAWIWAARAAA+30AAJ’
————————————————–
You may want to check the integrity of PORTAL.PREMIUM_TRANSACTION_DATA
executing :
dbv file=/u03/oradata/portals.dbf
blocksize=8192 start=257524 end=257524
————————————————–
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(90646,71560692,9)
——————————————————-
PL/SQL procedure successfully completed.
Now, To verify whether data blocks is corrupted or corruption lies in index use the dbv utility as shown
dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;
If there is no data block corruption the output of the dbv utility will as shown:
bash-2.05$ dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;
DBVERIFY: Release 9.2.0.4.0 - Production on Mon May 7 13:23:42 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u03/oradata/portals.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
The above confirms that data blocks are not corrupted and hence we to verify the index corruption as shown:
1) create the following procedure as SYS use
SQL> 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;
/
2) execute the procedure as note the output
SQL> set serveroutput on
SQL> execute oerr12700diag( 90646, 71560692, 9);
————————————————–
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 AAAWIWAARAAA+30AAJ index is corrupted
…………………………………………..
.
To test PORTAL.PREMIUM_TRANSACTION_DATA indexes
.
.
To test INDEX PREMIUM_TRANSACTION_DATA_PK you run :
.
select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!”
from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID)
where rowid=’AAAWIWAARAAA+30AAJ’;
PL/SQL procedure successfully completed.
Now, we execute the following query
SQL> select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!” from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID) where rowid=’AAAWIWAARAAA+30AAJ’;
The output to above query is
PREMIUM_TRANSACTION_DATA_PK corrupted!
——————————————————-
AAAWIWAARAAA+30AAJ
then it mean that index PREMIUM_TRANSACTION_DATA_PK is corrupted and hence we will have to drop and recreate the index PREMIUM_TRANSACTION_DATA_PK to avoid the error.
If there is any data block corruption them you have raise an SR with Oracle Support and follow appropriate steps.
Sat May 5 15:03:12 2007
ARC1: Beginning to archive log 3 thread 1 sequence 49943
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/archive/oradata/ODSORA/1_49943.arc’
ARC1: Completed archiving log 3 thread 1 sequence 49943
Sat May 5 15:35:49 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s003_27559.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:40:25 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s001_2441.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:55:32 2007
Thread 1 advanced to log sequence 49945
Sat May 5 15:55:32 2007
Current log# 5 seq# 49945 mem# 0: /oracle/dbs/oradata/ODSORA/redo05a.log
Current log# 5 seq# 49945 mem# 1: /u20/oradata/ODSORA/redo05b.log
Sat May 5 15:55:32 2007
ARC0: Evaluating archive log 4 thread 1 sequence 49944
Sat May 5 15:55:32 2007
ARC0: Beginning to archive log 4 thread 1 sequence 49944
Para lo cual hacemos lo siguiente:
1) Creamos el siguiente procedure con el user “SYS”
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(513) ;
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;
/
2) Lo ejecutamos:
SQL> set serveroutput on
SQL> execute oerr12700( 90646, 71560692, 9);
The output of the above procedure execution gives the following result
SQL> execute oerr12700( 90646, 71560692, 9);
ORA-600 [12700] [90646],[71560692],[9]
————————————————–
there is an index pointing to a row in PORTAL.PREMIUM_TRANSACTION_DATA
row is slot 9 in file 17 block 257524
one index entry is pointing to ROWID=’AAAWIWAARAAA+30AAJ’
————————————————–
You may want to check the integrity of PORTAL.PREMIUM_TRANSACTION_DATA
executing :
dbv file=/u03/oradata/portals.dbf
blocksize=8192 start=257524 end=257524
————————————————–
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(90646,71560692,9)
——————————————————-
PL/SQL procedure successfully completed.
Now, To verify whether data blocks is corrupted or corruption lies in index use the dbv utility as shown
dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;
If there is no data block corruption the output of the dbv utility will as shown:
bash-2.05$ dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;
DBVERIFY: Release 9.2.0.4.0 - Production on Mon May 7 13:23:42 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u03/oradata/portals.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
The above confirms that data blocks are not corrupted and hence we to verify the index corruption as shown:
1) create the following procedure as SYS use
SQL> 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;
/
2) execute the procedure as note the output
SQL> set serveroutput on
SQL> execute oerr12700diag( 90646, 71560692, 9);
————————————————–
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 AAAWIWAARAAA+30AAJ index is corrupted
…………………………………………..
.
To test PORTAL.PREMIUM_TRANSACTION_DATA indexes
.
.
To test INDEX PREMIUM_TRANSACTION_DATA_PK you run :
.
select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!”
from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID)
where rowid=’AAAWIWAARAAA+30AAJ’;
PL/SQL procedure successfully completed.
Now, we execute the following query
SQL> select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!” from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID) where rowid=’AAAWIWAARAAA+30AAJ’;
The output to above query is
PREMIUM_TRANSACTION_DATA_PK corrupted!
——————————————————-
AAAWIWAARAAA+30AAJ
then it mean that index PREMIUM_TRANSACTION_DATA_PK is corrupted and hence we will have to drop and recreate the index PREMIUM_TRANSACTION_DATA_PK to avoid the error.
If there is any data block corruption them you have raise an SR with Oracle Support and follow appropriate steps.
Comentarios