Oracle Database Discussions

Dear Friends ,

I am using ORacle 9i in AIX 5.3 server as our production server . Recently I got a problem and that is , sometimes our client cannot login into my database as their application . It also generates an error in 'alert' log file and that is :


>Tue Jan 12 11:27:30 2010 And the trace file output is :
Dump file /database/hodb/udump/hodb_ora_2662472.trc
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /oracle
System name: AIX
Node name: KIRK_DC
Release: 3
Version: 5
Machine: 0000FCA9D600
Instance name: hodb
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 2662472, image: oracle@KIRK_DC (TNS V1-V3)

*** SESSION ID:(30.10065) 2010-01-14 15:02:05.763

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
kksefrdy+009c ? 00000000
kkscls+01d0 bl kksefrdy 110329998 ? 000000000 ?
opicca+00f0 bl kkscls FFFFFFFFFFFB1A0 ? 110274A28 ?
opiosq0+03b8 bl opicca FFFFFFFFFFFAA90 ?
kpooprx+0174 bl opiosq0 FFFFFFFFFFFAEC0 ?
4824244800000000 ?
10019903C ? 11029F2D0 ?
kpoal8+033c bl kpooprx FFFFFFFFFFFC914 ? 1103E0938 ?
CE900000CE9 ? 100000001 ?
000000000 ? 24000000000024 ?
080000000 ? 000007FFF ?
opiodr+08c8 bl _ptrgl
ttcpip+0c5c bl _ptrgl
opitsk+0c58 bl ttcpip 11000CE70 ? 00000003A ?
000000001 ? 000000000 ?
000000000 ?
8080808080808080 ?
000000000 ? 000000000 ?
opiino+0798 bl opitsk 000000000 ? 000000000 ?
opiodr+08c8 bl _ptrgl
opidrv+032c bl opiodr 3C00000018 ? 4101F6EF8 ?
FFFFFFFFFFFFA00 ? 0A073CAE0 ?
sou2o+0028 bl opidrv 3C0C000000 ? 4A0784578 ?
FFFFFFFFFFFFA00 ?
main+0138 bl 01FD6EC8
__start+0098 bl main 000000000 ? 000000000 ?

--------------------- Binary Stack Dump ---------------------

========== FRAME [1] (kksefrdy+009c -> 00000000) ==========
Dump of memory from 0x0FFFFFFFFFFF9FE0 to 0x0FFFFFFFFFFFA3E0
FFFFFFFFFFF9FE0 0FFFFFFF FFFFA150 44224481 00000041 [.......PD"D....A]
FFFFFFFFFFF9FF0 00000001 028CA10C 07000001 FF95ADB0 [................]
FFFFFFFFFFFA000 0FFFFFFF FFFFA0D0 00000001 101F6EF8 [..............n.]
FFFFFFFFFFFA010 00000001 030EE018 00000000 0000001F [................]
FFFFFFFFFFFA020 00000200 00000000 00000000 00000020 [............... ]
FFFFFFFFFFFA030 00000000 00000020 04294E42 442E025E [....... .)NBD..^]
FFFFFFFFFFFA040 12E0BE82 6D694B2F 00000000 382C2000 [....miK/....8, .]
FFFFFFFFFFFA050 00000000 101F6EF8 0FFFFFFF FFFFA0B8 [......n.........]
FFFFFFFFFFFA060 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
FFFFFFFFFFFA080 0FFFFFFF FFFFA730 00000001 10324288 [.......0.....2B.]
FFFFFFFFFFFA090 00000001 008FC028 00000000 00000000 [.......(........]
Repeat 3 times
FFFFFFFFFFFA110 00000001 00000000 00000000 00000000 [................]
FFFFFFFFFFFA120 F0000000 2FF47600 F1000100 516EF000 [..../.v.....Qn..]
FFFFFFFFFFFA130 00017952 FDD7F3F8 00000000 00000000 [..yR............]
Repeat 2 times
FFFFFFFFFFFA1F0 00000000 00010000 0FFFFFFF FFFFA6B0 [................]
FFFFFFFFFFFA200 00000001 101F6EF8 00000000 00000001 [......n.........]
FFFFFFFFFFFA210 00000000 00000008 00000000 00000018 [................]
FFFFFFFFFFFA220 07000001 F9F8AD20 00000000 00000000 [....... ........]
FFFFFFFFFFFA230 00000000 008E2D28 00000000 008E4340 [......-(......C@]
FFFFFFFFFFFA240 00000000 00000000 00000000 00000000 [................]
FFFFFFFFFFFA250 0FFFFFFF FFFFA740 00000001 1023F100 [.......@.....#..]
FFFFFFFFFFFA260 00000001 10002518 00000000 00000020 [......%........ ]
FFFFFFFFFFFA270 00000000 00000000 00000000 00000001 [................]
FFFFFFFFFFFA280 00000000 00000000 00000000 00000000 [................]
FFFFFFFFFFFA290 00000001 10320570 07000000 00027A58 [.....2.p......zX]
FFFFFFFFFFFA2A0 00000000 00000CE9 00000001 103E0938 [.............>.8]
FFFFFFFFFFFA2B0 00000000 00000003 00000000 00000006 [................]
FFFFFFFFFFFA2C0 00000000 00000008 00000001 10329998 [.............2..]

Repeat 3 times
========== FRAME [2] (kkscls+01d0 -> kksefrdy) ==========
Dump of memory from 0x0FFFFFFFFFFFA730 to 0x0FFFFFFFFFFFA820
FFFFFFFFFFFA730 0FFFFFFF FFFFA820 00000000 FFFFFFF8 [....... ........]
FFFFFFFFFFFA740 00000001 008F5230 00000001 101F6EF8 [......R0......n.]
FFFFFFFFFFFA750 07000001 F73E1678 07000001 F735CA18 [.....>.x.....5..]
FFFFFFFFFFFA760 00000001 10329998 00000000 00000000 [.....2..........]


-------------------------------------
INSTANTIATION OBJECT: object=110326288
type="cursor"[2] lock=7000001fa710680 handle=7000001fd698740 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=110326320 size=736
07000001 ff21d5a0 07000001 fa61d180 00000000 00000000 00000000 00000000
00000001 103256e0 00000001 10326250 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00110424 05200008 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000001 10325610 00000001 1032b5d8
00000002 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000001 103205c0 00000006 06000000 00000000 00000000
00000000 00000000 00000001 10357ab0 00000240 00000000 00000000 00000000
00000001 10325788 00000000 00000000 00000000 00000000 00000000 00000000
00020202 00000000 00000000 00000000 00000228 62696e64 20766172 20686561
70000000 7fff7fff 7fff7fff 00000000 00000000 00000000 00000000 00000218
00000001 103257a8 00000001 103257a8 00000000 00000418 00000001 103264b8
00000001 103264b8 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000016 00000016 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000008 00000000 00000000
00000000 00000000 00000000 00000000 00000001 10325928 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=110329998
type="cursor"[2] lock=7000001fa707348 handle=7000001fe47e370 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=110329a30 size=736
07000001 ff672060 07000002 00a51758 07000001 fa6183d0 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000001 00000000 00000001 10326738 00000000 00000000 00000000 00000000
00000001 00000000 00100024 04200409 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000001 00000001 1032b520 00000000 00000000
00000000 00000000 00000200 00000000 00000001 1033f238 00000001 10326200
00000002 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000001 10320570 00000006 03000000 00000000 00000000
00000000 00000000 00000001 10357ab0 00000240 00000000 00000000 00000000
00000001 1033fb48 00000000 00000000 00000000 00000000 00000000 00000000
00020202 00000000 00000000 00000000 00000208 62696e64 20766172 20686561
70000000 7fff7fff 7fff7fff 00000000 00000000 00000000 00000000 00000218
00000001 1033fb68 00000001 1033fb68 00000000 00000418 00000001 10329bc8
00000001 10329bc8 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000016 00000016 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000008 00000000 00000000
00000000 00000000 00000000 00000000 00000001 1033fcc8 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=11033f690
type="cursor"[2] lock=7000001fa857650 handle=7000001ffd9fea8 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=11033f728 size=736
07000001 fd2ed040 07000002 00389d58 00000000 00000000 00000000 00000000
00000001 1032b728 00000001 10329f48 00000000 00000000 00000000 00000000
0000000d 00000000 00000001 1032b058 00000000 00000000 00000000 00000000
0000000d 00000000 00110424 05200009 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000001 00000001 1033fdc8 00000000 00000000
00000000 00000000 00000000 00000000 00000001 1032a7a8 00000001 10325ae8
00000002 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000001 10320430 00000006 03000000 00000000 00000000
00000000 00000000 00000001 10357ab0 00000240 00000000 00000000 00000000
00000001 1032bb50 00000000 00000000 00000000 00000000 00000000 00000000
00020202 00000000 00000000 00000000 00000228 62696e64 20766172 20686561
70000000 7fff7fff 7fff7fff 00000000 00000000 00000000 00000000 00000218
00000001 1032bb70 00000001 1032bb70 00000000 00000418 00000001 1033f8c0
00000001 1033f8c0 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000016 00000016 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000008 00000000 00000000
00000000 00000000 00000000 00000000 00000001 1032bcf0 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=110324408
type="cursor"[2] lock=7000002002b24c8 handle=7000001fddadac0 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=1103244a0 size=736
07000001 fb99b970 07000001 ffd9b290 00000000 00000000 00000000 00000000
00000001 10324030 00000001 10329d98 00000000 00000000 00000000 00000000
00000001 00000000 00000001 10326670 00000000 00000000 00000000 00000000
00000001 00000000 00110424 05200009 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000001 00000001 1033f280 00000000 00000000
00000000 00000000 00000000 00000000 00000001 10325aa0 00000001 10326098
00000002 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000001 10320480 00000006 03000000 00000000 00000000
00000000 00000000 00000001 10357ab0 00000240 00000000 00000000 00000000
00000001 10325ba8 00000000 00000000 00000000 00000000 00000000 00000000
00020202 00000000 00000000 00000000 00000228 62696e64 20766172 20686561
70000000 7fff7fff 7fff7fff 00000000 00000000 00000000 00000000 00000218
00000001 10325bc8 00000001 10325bc8 00000000 00000418 00000001 10324638
00000001 10324638 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000016 00000016 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000008 00000000 00000000
00000000 00000000 00000000 00000000 00000001 10325d48 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=110324078
type="cursor"[2] lock=7000001fa8fbb00 handle=7000001fc5b80d0 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=110324110 size=736
07000001 feae6840 07000001 f8011050 00000000 00000000 00000000 00000000
00000001 10326708 00000001 10336bd8 00000000 00000000 00000000 00000000
00000001 00000000 00000001 10326868 00000000 00000000 00000000 00000000
00000001 00000000 00110424 05200009 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000001 00000001 10323ed0 00000000 00000000
00000000 00000000 00000000 00000000 00000001 10325578 00000001 10326040
00000002 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000001 10320520 00000006 03000000 00000000 00000000
00000000 00000000 00000001 10357ab0 00000240 00000000 00000000 00000000
00000001 1032ae18 00000000 00000000 00000000 00000000 00000000 00000000
00020202 00000000 00000000 00000000 00000228 62696e64 20766172 20686561
70000000 7fff7fff 7fff7fff 00000000 00000000 00000000 00000000 00000218
00000001 1032ae38 00000001 1032ae38 00000000 00000418 00000001 103242a8
00000001 103242a8 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000016 00000016 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000008 00000000 00000000
00000000 00000000 00000000 00000000 00000001 1032afb8 00000000 00000000
******************** Cursor Dump ************************
Current cursor: 8, pgadep: 0
pgactx: 7000001fb563580 ctxcbk: 0 ctxqbc: 0 ctxrws: 7000001fef658d8
Explain plan:
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 0 | 0 | 11 | | | | | |
| SORT AGGREGATE | | 1 | 13 | 0 | | | | | |
| TABLE ACCESS FULL | TXNAUTHORISATIONQ | 1 | 13 | 11 | | | | | |
-------------------------------------------------------------------------------------------------------------------------
Cursor Dump:
----------------------------------------
Cursor 1 (110320340): CURBOUND curiob: 11033f300
curflg: 44 curpar: 0 curusr: 0 curses 7000001f735ca18
cursor name: insert into TXNAUTHORISATIONQ(ID,BANK_CODE,BRANCH_CODE,GROUP_CODE,QUEUE_ID,TXN_REF,AUTH_SEQ,ACCESSOR_ID,ACCESS_DT,AUTH_FLAG,STATUS,IP_ADDRESS,TERMINAL_ID,BIS_DATE) values (:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,SYSDATE,:b9,:b10,:b11,:b12,:b13)
child pin: 0, child lock: 7000001fe4e08a8, parent lock: 7000001fa623628
xscflg: 110424, parent handle: 7000001fef82f28, xscfl2: 5600008
bhp size: 160/600
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=07 oacflg=00 oacfl2=1 size=392 offset=0
No bind buffers allocated
bind 1: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=24
No bind buffers allocated
bind 2: dty=1 mxl=32(06) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=56
No bind buffers allocated
bind 3: dty=1 mxl=32(04) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=88
No bind buffers allocated
bind 4: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=120
No bind buffers allocated
bind 5: dty=2 mxl=22(22) mal=00 scl=00 pre=12 oacflg=00 oacfl2=1 size=0 offset=152
No bind buffers allocated
bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=03 oacflg=00 oacfl2=1 size=0 offset=176
No bind buffers allocated
bind 7: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=200
No bind buffers allocated
bind 8: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=232
No bind buffers allocated
bind 9: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=264
No bind buffers allocated
bind 10: dty=1 mxl=32(16) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=296
No bind buffers allocated
bind 11: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=328
No bind buffers allocated
bind 12: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=360
No bind buffers allocated
----------------------------------------
Cursor 2 (110320390): CURBOUND curiob: 110326b30
curflg: 46 curpar: 0 curusr: 0 curses 7000001f735ca18
cursor name: select distinct TX_CODE into :b1 from TXNTRACKER where ((TXN_REF=:b2 and OPCODE=9800) and TXN_DATE=:b3)
child pin: 0, child lock: 7000001fa854488, parent lock: 7000001fd015ab0
xscflg: 110424, parent handle: 7000001fe475468, xscfl2: 5200009
Dumping Literal Information
Bind Pos: 0, Bind Len: 2, Bind Val:
Bind Pos: 1, Bind Len: 8, Bind Val:
bhp size: 160/600
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=12 oacflg=00 oacfl2=1 size=56 offset=0
No bind buffers allocated
bind 1: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=24
No bind buffers allocated
----------------------------------------
Cursor 3 (1103203e0): CURBOUND curiob: 11032a2e8
curflg: 46 curpar: 0 curusr: 0 curses 7000001f735ca18
cursor name:
select ID ,BANK_CODE ,BRANCH_CODE ,TXN_REF ,IP_ADDRESS ,TERMINAL_ID ,USER_ID ,CREATE_DT ,TX_CODE ,TX_TYPE ,APRV_TYPE ,APRV_SEQ ,TXN_AMOUNT ,TXN_CCY ,ACCOUNT_NO ,CIF ,CIF_NAME ,NOTES ,STATUS ,INPUT_CDATA into :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20 from TXN4APPROVAL where ((TXN_REF=:b4 and BRANCH_CODE=:b21) and BIS_DATE=:b22)
child pin: 0, child lock: 7000001fa761bb8, parent lock: 7000001fa6210f0
xscflg: 110424, parent handle: 7000001ffae14c0, xscfl2: 5200009
Dumping Literal Information
Bind Pos: 0, Bind Len: 3, Bind Val:
Bind Pos: 1, Bind Len: 6, Bind Val:
Bind Pos: 2, Bind Len: 8, Bind Val:
bhp size: 280/600
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=12 oacflg=00 oacfl2=1 size=88 offset=0
No bind buffers allocated
bind 1: dty=1 mxl=32(06) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=24
No bind buffers allocated
bind 2: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=56
No bind buffers allocated

End of cursor dump
***************** dump of cursor xsc=110329998 **********************
------------- dump of ephemeral frame ------------------
Shadow_Core_Dump = PARTIAL It's a huge output and I cannot determine the problem . Would anybody help me to resolve this prob ...?

Waiting for kind reply ... ...