[DB-SIG] ORA-03113: end-of-file on communication channel\012 error

[Wong Wah Meng-R32813]

Hello guys,

I would like to post a question on an issue that I have encountered on
an AIX 5.3 box. I intermittently encountered this issue when my
application access to database. Based on my troubleshooting with Oracle
DBA, I think I can rule out the cause of this is in the Oracle, as the
SQL does pass through Oracle PARSE, EXECUTE and FETCH phase. There is no
way I can verify this at network level as both my application and Oracle
database reside on the same server. 

I think the rootcause of this issue might be related to the stability of
the oracledb object that my team built on a AIX 4 box on python 1.5.2
binary back in year 2000. I also suspected this issue happens when there
is simultaneous access to database from multiple threads spawned off in
my application. 

Here is a little more background. I have been running the python binary
and oracledb binary without much issue on another AIX 5.2 box. Oracle
version is 9i on both AIX servers. We are using lib32 of ORACLE since
the oracledb cannot run when I pointed the LIBPATH for Oracle to lib
(default on Oracle 9 is 64 bit). 

Does anyone has encountered this issue in the similar environment like
mine before? Due to lack of knowledge in recompiling python on the UNIX
platform, I didn't try out recompiling, though I do not rule out this
might be one of the root causes why I encounter this error.  

Can anyone share your input if you have encountered this before and how
to further verify the problem or how to resolve this problem? I will
really appreciate it. Below is the trace log of my application and the
trace log in Oracle. 

Thanks!


Regards,
Wah Meng

zmy16aix06:/efm/raesta/Product/Log>grep nonPPTLotClass debug.log.1
Mar 19 17:06:57 DbAccessMgr      ('Thread #7', 'Reconnecting to Oracle
due to Oracle error: PARSE caused a ORA-03113: end-of-file on
communication channel\012', ("select PARM_VALUE from
MES_PARAMETER_VALUES where PARM_OWNER_TYPE = 'APPL' and PARM_OWNER =
'MaterialMgr' and PARM_NAME = 'nonPPTLotClass' and rownum = 1", None)) 
zmy16aix06:/tmp> cat prefmol7_ora_1781858.txt 
 
select PARM_VALUE
from
MES_PARAMETER_VALUES where PARM_OWNER_TYPE = 'APPL' and PARM_OWNER =
'MaterialMgr' and PARM_NAME = 'nonPPTLotClass' and rownum = 1 
 

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.01       0.72          0          0          0
0
Execute      2      0.00       0.01          0          0          0
0
Fetch        1      0.00       0.00          0          4          0
1
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4      0.01       0.74          0          4          0
1 
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 75 
 
czmy16aix06:/efm/raesta/Product/Log>d /tmp
zmy16aix06:/tmp>cat prefmol7_ora_1781858.trc
/opt/oracle/product/9.2/admin/PREFMOL7/udump/prefmol7_ora_1781858.trc
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2
System name:    AIX
Node name:      zmy16aix06
Release:        3
Version:        5
Machine:        000CA28CD600
Instance name: PREFMOL7
Redo thread mounted by this instance: 1
Oracle process number: 302
Unix process pid: 1781858, image: oracle at zmy16aix06 (TNS V1-V3) 
 
*** 2009-03-19 17:07:21.705
*** SESSION ID:(227.12465) 2009-03-19 17:07:21.369
APPNAME mod='python at zmy16aix06 (TNS V1-V3)' mh=0 act='' ah=0
=====================
PARSING IN CURSOR #2 len=153 dep=0 uid=75 oct=3 lid=75
tim=68684146214852 hv=762
989930 ad='34dc15b0'
select PARM_VALUE from MES_PARAMETER_VALUES where PARM_OWNER_TYPE =
'APPL' and P
ARM_OWNER = 'MaterialMgr' and PARM_NAME = 'nonPPTLotClass' and rownum =
1
END OF STMT
PARSE
#2:c=10000,e=723600,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=6868414588686
7
EXEC
#2:c=0,e=13118,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=68684146328635
EXEC #2:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=68684146333105
FETCH
#2:c=0,e=6781,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=4,tim=68684146339922
zmy16aix06:/tmp>