How to resolve ORA-00600: [ksnpost:ksnigb]

Today i got this error ORA-00600: internal error code, arguments: [ksnpost:ksnigb], [], [], [], [], [], [], [] in one of our shakedown instance (pre go live instance).

In alert log there were many errors like

Errors in file /fsd1/oracle/OROD/oroddb/10.2.0/admin/OROD_admin/udump/orod_ora_6911.trc: ORA-00600: internal error code, arguments: [ksnpost:ksnigb], [], [], [], [], [], [], []
ORA-00609: could not attach to incoming connection
ORA-06403: Unable to allocate memory.
Fri Jul 31 13:55:37 2009
Incremental checkpoint up to RBA [0x1a.21bf9.0], current log tail at RBA [0x1a.234ae.0]
Fri Jul 31 14:00:23 2009
Process startup failed, error stack:
Fri Jul 31 14:00:23 2009
Errors in file /fsd1/oracle/OROD/oroddb/10.2.0/admin/OROD_admin/udump/orod_psp0_18212.trc: ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Fri Jul 31 14:00:24 2009

trace file orod_psp0_18212.trc had error messages

Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
*** 2009-07-31 14:01:24.304

In orod.log (listener log file) there were many error messages like

TNS-12518: TNS:listener could not hand off client connection
TNS-12549: TNS:operating system resource quota exceeded
TNS-12560: TNS:protocol adapter error
TNS-00519: Operating system resource quota exceeded
HPUX Error: 12: Not enough space

as you can see above messages clearly states that system is running out of resources. OS was totally out of physical memory as per swapinfo -tam command



problem was we had only 3GB memory allocated to SGA but processes parameter was set to 1000 and this cause an issue in the instance and to solve this we had to reduce Processes parameter to lower value around 400 to make it work.

How to Find out Product Installations and Patch Set level in Oracle Applications 11i

Use Following Query to find out Product Installation


SELECT application_name "Application Name",
SUBSTR (application_short_name, 1, 10) "Short Name",
RPAD (DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared Install', 'N', 'Not Installed' ), 14, ' ' ) "Install Status", SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath"
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;

How to Find out File version in Oracle Applications 11i E-Business Suite

     
    Some times you need to find out the file version in oracle application for some TAR or for some development work. You can use SQL command to find out file version, package version or you can use OS commands to find out the version.

Use following SQL command to find out file version, patch number.

SELECT af.app_short_name "Application", af.filename "File Name",
afv.VERSION "File Version",
NVL (aap.patch_name, 'Default Installation') "Patch Number",
'$' || fa.basepath || '/' || af.subdir "Location"
FROM apps.ad_files af,
apps.ad_patch_run_bug_actions apa,
apps.ad_patch_run_bugs aprb,
apps.ad_patch_runs apr,
apps.ad_patch_drivers apd,
apps.ad_applied_patches aap,
apps.ad_file_versions afv,
apps.fnd_application fa
WHERE af.filename = 'IGSEN010.fmb'
AND af.file_id = apa.file_id(+)
AND apa.common_action_id(+) = 4042
AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)
AND aprb.patch_run_id = apr.patch_run_id(+)
AND apr.patch_driver_id = apd.patch_driver_id(+)
AND apd.applied_patch_id = aap.applied_patch_id(+)
AND af.file_id = afv.file_id
AND af.app_short_name = fa.application_short_name
GROUP BY aap.patch_name,
af.filename,
afv.VERSION,
af.app_short_name,
af.file_id,
af.subdir,
fa.basepath



At OS level go to $Product_TOP/patch/115/Object_Type

Example
$pwd
/u01/oracle/TESTORCL/testorclappl/igs/11.5.0/forms/US


$ident IGSEN010.fmx | grep IGSEN010.fmb | grep Header
$Header: IGSEN010.fmb 115.24 2003/10/06 06:32 svanukur ship


If you know DB object name you can use following SQL Statement to find out Package,Procedure or Function version.

select text from dba_source where name='FND_OAM_DSCRAM_DMLS_PKG' and line=2

Useful Oracle Apps DBA related SQL Scripts

How to find out blocking locks,sessions.

SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username '.' SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment' ) ) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session' ) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID

How to find out long running Concurrent Requests.


SELECT gv.inst_id, gv.SID, fcr.request_id, fcr.program, fcr.phase,
fcr.status, fcr.actual_start_date "start",
fcr.actual_completion_date "end",
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE) - fcr.actual_start_date )
* 60 * 24, 2 ) TIME, fcqtl.user_concurrent_queue_name "Concurrent Manager", fcr.user_name, fcr.argument_text text, fcr.concurrent_program_id, gvw.event
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE phase_code = 'R'
AND fcr.oracle_session_id = gv.audsid(+)
AND gv.SID = gvw.SID(+)
AND gv.inst_id = gvw.inst_id(+)
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
ORDER BY 9 DESC

How to find out Concurrent Manager Process SID and SPID

SELECT fcq.concurrent_queue_name "Concurrent manager", fcp.os_process_id "Manager PID", LOWER (SUBSTR (fcp.node_name, 1)) "CM Node", ses.SID, proc.spid, SUBSTR (ins.host_name, 1) "DB Node", ins.instance_name "Instance"
FROM apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
gv$process proc,
gv$session ses,
gv$instance ins
WHERE fcp.process_status_code = 'A'
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcp.oracle_process_id = proc.pid
AND ses.paddr = proc.addr
AND proc.inst_id = ses.inst_id
AND ins.instance_number = ses.inst_id
AND ins.instance_number = proc.inst_id
AND fcq.instance_number = ins.instance_number
AND fcp.instance_number = ins.instance_number
ORDER BY fcq.concurrent_queue_name

How to resolve ORA-00600: [qertbFetchByRowID]

In this post i am going to discuss how to resolve ORA-00600: internal error code, arguments: [qertbFetchByRowID]
ORA-00600: internal error code, arguments: [qertbFetchByRowID] error represents that an index and a table entries are out of sync or underlying index is corrupt.

In this case just check the trace file for current SQL statement for that session. Once we get the SQL statement and table name from the session trace file we can easily find out the index name as shown in this example.

Try to validate the table structure using command.

SQL> analyze table gl.gl_interface validate structure cascade;
analyze table gl.gl_interface validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Check the trace file create by above statement in udump folder and search for following text

Table/Index row count mismatch
table 48068 : index 239744, 191029
Index root = tsn: 4 rdba: 0x0d00438b

In above example rdba value is in HEXADECIMAL, Convert rdba: 0x0d00438b to decimal value like 0x0d00438b is equivalent to 218121099.

Now use statement given below to find out Header_file and Header_block.

SQL> select dbms_utility.data_block_address_file(218121099) "Rfile#"
,dbms_utility.data_block_address_block(218121099) "Block#"
from dual;

Rfile# Block#
---------- ----------
52 17291

Once you get the Header_file and Header_Block use the SQL statement to find out the segment name and segment type.

SQL> select owner, segment_name, segment_type
from dba_segments
where header_file = 52
and header_block = 17291;

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------------------------------------------
GL GL_INTERFACE_N1 INDEX

Once you get the index name, you can rebuild index or drop it and recreate it.

SQL> drop index GL.GL_INTERFACE_N1;

CREATE INDEX GL.GL_INTERFACE_N1 ON GL.GL_INTERFACE
(USER_JE_SOURCE_NAME, SET_OF_BOOKS_ID, GROUP_ID)
NOLOGGING
TABLESPACE APPS_TS_INTERFACE
PCTFREE 0
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

SQL> analyze table gl.gl_interface validate structure cascade;

Table analyzed.

Now you wouldn't get the ORA-00600 error anymore.