Oracle Faq

Hi,

In 10gR2 RAC on IBM AIX we had a database when starting the database in nomount stage we got the following error

SQL> startup nomount
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 59
ORA-27301: OS failure message: Message too long
ORA-27302: failure occurred at: sskgxpsnd1

However when the root cause of error was improper tcp udp parameter settings which is required in an RAC environment for cache fusion.

The following is the required settings for tcp-udp parameter for RAC environment on IBM-AIX systems

Using the following commands change the values of the following parameters(in AIX):

# no -o tcp_sendspace=262144
# no -o tcp_recvspace=262144
# no -o udp_sendspace=65536
# no -o udp_recvspace=262144

Also the parameter rfc1323 needs to be set to 1 for any TCP/ UDP parameters settings of
over 64K.

no -o rfc1323=1
Adding these entries to /etc/rc.net will ensure that the parameter take effect on every reboot.

Also, the following are the appropriate ulimit settings for RAC environment on IBM AIX systems

As a root user, add the following entries for the root and Oracle users:

root:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

oracle:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
nofiles = -1

This needs to be done on all nodes of the cluster. A server reboot might be necessary to activate updated limits. After you modify the settings, the “ulimit –a” command should display the following:

# ulimit –a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 2097152
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) unlimited

Verify the same for the Oracle user. Next, you need to increase Oracle user processes to 8192. You can increase the processes for the Oracle user via the “smit” utility on AIX-based servers.

Hi All,

Many a times due to corruption of data blocks or associated Index, the execution of query on corrupt data blocks gives Ora-600[12700] which is being logged in alert log .

The follwing is excerpt of alert log

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

The following is the procedure to diagnose the error Ora-600[12700]

1) Create the following procedure as “SYS” user

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) Then execute the procedure as follows

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.