Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem Description: ORA-00600 via db links
On DB1, we have package estr.TR_TIC_GATEWAY,
this package is inserting data into synonym estr.TR_TIC_GATEWAY_IN_QUEUE
This synonym is built over a database link to a table tr.TR_TIC_GATEWAY_IN_QUEUE@gdp.ae on DB2
any time an insert is taken place,ora-600 is fired on DB1 and file system is filling up rapidly.
Alert Log File:
Mon Mar 15 13:05:12 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
Mon Mar 15 13:05:16 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
...
Mon Mar 15 14:09:34 2010
Errors in file /orasys/ipay/udump/ipay_ora_1294.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
Trace file shows:
*** 2010-06-28 11:27:10.158
*** SERVICE NAME:(SYS$USERS) 2010-06-28 11:27:10.158
*** CLIENT ID:() 2010-06-28 11:27:10.158
*** SESSION ID:(104.42096) 2010-06-28 11:27:10.158
LIBRARY OBJECT HANDLE: handle=3a8ca3cc8 mtx=3a8ca3df8(0) cdp=32768
name=
INSERT INTO TR_TIC_GATEWAY_IN_QUEUE ( TIQ_SERIAL_NO, TIQ_TIC_SOURCE_CODE, TIQ_TIC_SOURCE_IN, TIQ_TIC_NO, TIQ_TIC_DATE, TIQ_TRANSACTION_TYPE, TIQ_LOG_SERIAL, TIQ_FLS_CODE, TIQ_TIC_TYPE, TIQ_TIC_TYPE_IN, TIQ_TIC_AMOUNT, TIQ_TIC_LOCATION, TIQ_LOCAL_AUTHORITY, TIQ_IS_PAYABLE, TIQ_IS_PAYABLE_IN, TIQ_LIC_NO, TIQ_LIC_SOURCE_CODE, TIQ_LIC_SOURCE_IN, TIQ_PNO, TIQ_PNO_ORG, TIQ_PCOLOR_CODE, TIQ_PKIND_CODE, TIQ_PTYPE_CODE, TIQ_PSOURCE_CODE, TIQ_PCOLOR_IN, TIQ_PKIND_IN, TIQ_PSOURCE_IN, TIQ_VIOLATION_DESC_A, TIQ_VIOLATIO
hash=7649571625c71326d46805477b7157a4 timestamp=06-27-2010 06:02:32
namespace=CRSR flags=RON/KGHP/TIM/OBS/PN0/DBN/[10410040]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=b8e8 hlc=b8e8
lwt=3a8ca3d70[3a8ca3d70,3a8ca3d70] ltm=3a8ca3d80[3a8ca3d80,3a8ca3d80]
pwt=3a8ca3d38[3a8ca3d38,3a8ca3d38] ptm=3a8ca3d48[3a8ca3d48,3a8ca3d48]
ref=3a8ca3da0[3a8ca3da0,3a8ca3da0] lnd=3a8ca3db8[3ba8c8328,4043271e8]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
3b8e99e70 49b1254e8 49b1254e8 1 N [00]
485979d38 49b1c6ed8 49b1c6ed8 1 N [00]
3b081a1d0 41c11c418 41c11c418 1 N [00]
3af961a88 41c149828 41c149828 1 N [00]
LIBRARY OBJECT: object=46a241340
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=32768
child# table reference handle
------ -------- --------- --------
0 49933a578 49933a1e8 3a87cb658
...
32767 400e3d888 3a20fa2d8 39dc0c408
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 39d70a2c0 46a241458 I/P/A/-/- 0 NONE 00
*** 2010-06-28 11:27:10.596
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17059], [0x46A241340], [], [], [], [], [], []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
object line object
handle number name
3ea1099f0 24 ESTR.INSERT_ORG_TABLE
3ba684c30 119 package body ESTR.XML_API
3ba684c30 176 package body ESTR.XML_API
3bbd495c0 1522 package body ESTR.TR_TIC_GATEWAY
39db9df30 1 anonymous block
----- Call Stack Trace -----
ksedmp kgeriv kgesiv kgesic1 kgltba kglhdgc kglget kxsGetLookupLock kksfbc kkspbd0 kksParseCursor opiosq0 opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxtexe kxtex1 rpiswu2 kxtexe insbrp insrow insdrv inscovexe insExecStmtExecIniEngine insexe opiexe opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXIM pfrinstr_EXIM pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main _start
----- End of Call Stack Trace -----
The process state dump shows:
Process global information:
process: 49b024988, call: 49d3eb1c0, xact: 49cd8c2b8, curses: 49b1254e8, usrses: 49b1254e8
----------------------------------------
SO: 49b024988, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=150, calls cur/top: 49d3eb1c0/49d3f9360, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
...
O/S info: user: oracle, term: UNKNOWN, ospid: 26993
OSD pid info: Unix process pid: 26993, image: oracleipay@Gateway2.auh-police
...
SO: 49b1254e8, type: 4, owner: 49b024988, flag: INIT/-/-/0x00
(session) sid: 104 trans: 49cd8c2b8, creator: 49b024988, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-0096-005D6F52, short-term DID: 0000-0000-00000000
txn branch: 49ce44a18
oct: 2, prv: 0, sql: 404c63f90, psql: 3ba74b450, user: 171/ESTR
O/S info: user: NETWORK?SERVICE, term: ACMPWEBSERVER, ospid: 8176:3372, machine: TEMPWORKGROUP\ACMPWEBSERVER
program: w3wp.exe
application name: w3wp.exe, hash value=0
last wait for 'latch: library cache' blocking sess=0x0 seq=239 wait_time=2052878 seconds since wait started=2
address=3b9f53c98, number=d6, tries=0
...
SO: 49d3eb1c0, type: 3, owner: 49d3e7208, flag: INIT/-/-/0x00
(call) sess: cur 49b1254e8, rec 0, usr 49b1254e8; depth: 2
----------------------------------------
SO: 3aa4ac650, type: 59, owner: 49d3eb1c0, flag: INIT/-/-/0x00
cursor enqueue
child: 39ef80bd0, flag: 37, number: 32769
parent: 46a241458
----------------------------------------
SO: 3af8166a0, type: 54, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=3af8166a0 handle=489613408 mode=S lock=4086ada58
user=49b1254e8 session=49b1254e8 count=1 mask=0005 savepoint=0x18a flags=[00]
----------------------------------------
SO: 4086ada58, type: 53, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=4086ada58 handle=489613408 mode=S
call pin=3af8166a0 session pin=0 hpc=0000 hlc=0000
htl=4086adad8[4086d2a60,48558d318] htb=48558d318 ssga=48558c860
user=49b1254e8 session=49b1254e8 count=1 flags=PNC/[0400] savepoint=0x18a
LIBRARY OBJECT HANDLE: handle=489613408 mtx=489613538(0) cdp=0
name=SYS.XMLTYPE_LIB
Justification:
Encountering ORA-600 [17059] errors when using SESSION_CACHED_CURSORS.
Solution:
This looks to be due to bug:5177766 fixed in 11.1 and 10.2.0.4 given that SESSION_CACHED_CURSORS is being used.
Upgrade to 10.2.0.5 or back out merge patch number 7007477 and apply merge patch number 8645328 instead which contains all of the fixes in merge patch number 7007477 plus the following extra fixes:
Bug:5177766 - OERI[17059] with SESSION_CACHED_CURSORS
Bug:6085625 - Wrong child cursor may be executed which has mismatching bind information
Bug:5254759 - ORA-12801/ORA-1008 occurs on a parallel query with bind variables
Bug:6452485 - SGA memory corruption / OERI [17182] with fix for bug 6085625
To clear the problem temporarily in the instance, you can flush the shared pool using:
connect / as sysdba
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
exit
On DB1, we have package estr.TR_TIC_GATEWAY,
this package is inserting data into synonym estr.TR_TIC_GATEWAY_IN_QUEUE
This synonym is built over a database link to a table tr.TR_TIC_GATEWAY_IN_QUEUE@gdp.ae on DB2
any time an insert is taken place,ora-600 is fired on DB1 and file system is filling up rapidly.
Alert Log File:
Mon Mar 15 13:05:12 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
Mon Mar 15 13:05:16 2010
Errors in file /orasys/ipay/udump/ipay_ora_29388.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
...
Mon Mar 15 14:09:34 2010
Errors in file /orasys/ipay/udump/ipay_ora_1294.trc:
ORA-00600: internal error code, arguments: [17059], [0x499C400A8], [], [], [], [], [], []
Trace file shows:
*** 2010-06-28 11:27:10.158
*** SERVICE NAME:(SYS$USERS) 2010-06-28 11:27:10.158
*** CLIENT ID:() 2010-06-28 11:27:10.158
*** SESSION ID:(104.42096) 2010-06-28 11:27:10.158
LIBRARY OBJECT HANDLE: handle=3a8ca3cc8 mtx=3a8ca3df8(0) cdp=32768
name=
INSERT INTO TR_TIC_GATEWAY_IN_QUEUE ( TIQ_SERIAL_NO, TIQ_TIC_SOURCE_CODE, TIQ_TIC_SOURCE_IN, TIQ_TIC_NO, TIQ_TIC_DATE, TIQ_TRANSACTION_TYPE, TIQ_LOG_SERIAL, TIQ_FLS_CODE, TIQ_TIC_TYPE, TIQ_TIC_TYPE_IN, TIQ_TIC_AMOUNT, TIQ_TIC_LOCATION, TIQ_LOCAL_AUTHORITY, TIQ_IS_PAYABLE, TIQ_IS_PAYABLE_IN, TIQ_LIC_NO, TIQ_LIC_SOURCE_CODE, TIQ_LIC_SOURCE_IN, TIQ_PNO, TIQ_PNO_ORG, TIQ_PCOLOR_CODE, TIQ_PKIND_CODE, TIQ_PTYPE_CODE, TIQ_PSOURCE_CODE, TIQ_PCOLOR_IN, TIQ_PKIND_IN, TIQ_PSOURCE_IN, TIQ_VIOLATION_DESC_A, TIQ_VIOLATIO
hash=7649571625c71326d46805477b7157a4 timestamp=06-27-2010 06:02:32
namespace=CRSR flags=RON/KGHP/TIM/OBS/PN0/DBN/[10410040]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=6 hpc=b8e8 hlc=b8e8
lwt=3a8ca3d70[3a8ca3d70,3a8ca3d70] ltm=3a8ca3d80[3a8ca3d80,3a8ca3d80]
pwt=3a8ca3d38[3a8ca3d38,3a8ca3d38] ptm=3a8ca3d48[3a8ca3d48,3a8ca3d48]
ref=3a8ca3da0[3a8ca3da0,3a8ca3da0] lnd=3a8ca3db8[3ba8c8328,4043271e8]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
3b8e99e70 49b1254e8 49b1254e8 1 N [00]
485979d38 49b1c6ed8 49b1c6ed8 1 N [00]
3b081a1d0 41c11c418 41c11c418 1 N [00]
3af961a88 41c149828 41c149828 1 N [00]
LIBRARY OBJECT: object=46a241340
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=32768
child# table reference handle
------ -------- --------- --------
0 49933a578 49933a1e8 3a87cb658
...
32767 400e3d888 3a20fa2d8 39dc0c408
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 39d70a2c0 46a241458 I/P/A/-/- 0 NONE 00
*** 2010-06-28 11:27:10.596
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17059], [0x46A241340], [], [], [], [], [], []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
object line object
handle number name
3ea1099f0 24 ESTR.INSERT_ORG_TABLE
3ba684c30 119 package body ESTR.XML_API
3ba684c30 176 package body ESTR.XML_API
3bbd495c0 1522 package body ESTR.TR_TIC_GATEWAY
39db9df30 1 anonymous block
----- Call Stack Trace -----
ksedmp kgeriv kgesiv kgesic1 kgltba kglhdgc kglget kxsGetLookupLock kksfbc kkspbd0 kksParseCursor opiosq0 opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxtexe kxtex1 rpiswu2 kxtexe insbrp insrow insdrv inscovexe insExecStmtExecIniEngine insexe opiexe opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXIM pfrinstr_EXIM pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main _start
----- End of Call Stack Trace -----
The process state dump shows:
Process global information:
process: 49b024988, call: 49d3eb1c0, xact: 49cd8c2b8, curses: 49b1254e8, usrses: 49b1254e8
----------------------------------------
SO: 49b024988, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=150, calls cur/top: 49d3eb1c0/49d3f9360, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
...
O/S info: user: oracle, term: UNKNOWN, ospid: 26993
OSD pid info: Unix process pid: 26993, image: oracleipay@Gateway2.auh-police
...
SO: 49b1254e8, type: 4, owner: 49b024988, flag: INIT/-/-/0x00
(session) sid: 104 trans: 49cd8c2b8, creator: 49b024988, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-0096-005D6F52, short-term DID: 0000-0000-00000000
txn branch: 49ce44a18
oct: 2, prv: 0, sql: 404c63f90, psql: 3ba74b450, user: 171/ESTR
O/S info: user: NETWORK?SERVICE, term: ACMPWEBSERVER, ospid: 8176:3372, machine: TEMPWORKGROUP\ACMPWEBSERVER
program: w3wp.exe
application name: w3wp.exe, hash value=0
last wait for 'latch: library cache' blocking sess=0x0 seq=239 wait_time=2052878 seconds since wait started=2
address=3b9f53c98, number=d6, tries=0
...
SO: 49d3eb1c0, type: 3, owner: 49d3e7208, flag: INIT/-/-/0x00
(call) sess: cur 49b1254e8, rec 0, usr 49b1254e8; depth: 2
----------------------------------------
SO: 3aa4ac650, type: 59, owner: 49d3eb1c0, flag: INIT/-/-/0x00
cursor enqueue
child: 39ef80bd0, flag: 37, number: 32769
parent: 46a241458
----------------------------------------
SO: 3af8166a0, type: 54, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=3af8166a0 handle=489613408 mode=S lock=4086ada58
user=49b1254e8 session=49b1254e8 count=1 mask=0005 savepoint=0x18a flags=[00]
----------------------------------------
SO: 4086ada58, type: 53, owner: 49d3e7208, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=4086ada58 handle=489613408 mode=S
call pin=3af8166a0 session pin=0 hpc=0000 hlc=0000
htl=4086adad8[4086d2a60,48558d318] htb=48558d318 ssga=48558c860
user=49b1254e8 session=49b1254e8 count=1 flags=PNC/[0400] savepoint=0x18a
LIBRARY OBJECT HANDLE: handle=489613408 mtx=489613538(0) cdp=0
name=SYS.XMLTYPE_LIB
Justification:
Encountering ORA-600 [17059] errors when using SESSION_CACHED_CURSORS.
Solution:
This looks to be due to bug:5177766 fixed in 11.1 and 10.2.0.4 given that SESSION_CACHED_CURSORS is being used.
Upgrade to 10.2.0.5 or back out merge patch number 7007477 and apply merge patch number 8645328 instead which contains all of the fixes in merge patch number 7007477 plus the following extra fixes:
Bug:5177766 - OERI[17059] with SESSION_CACHED_CURSORS
Bug:6085625 - Wrong child cursor may be executed which has mismatching bind information
Bug:5254759 - ORA-12801/ORA-1008 occurs on a parallel query with bind variables
Bug:6452485 - SGA memory corruption / OERI [17182] with fix for bug 6085625
To clear the problem temporarily in the instance, you can flush the shared pool using:
connect / as sysdba
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
exit
No comments:
Post a Comment