急,ORA-03113错误?

显示全部楼层 · 2005-10-30 17:05:33

OS: HP-UX 10.20
DB: Oracle 7.3.2
我们用PB开发的一个生产管理系统用了一年多一直没有问题,但昨天突然有一个汇总报表一运行就出现ORA-03113错误,然后该系统就与DB就无法连接了,只有重新启动该系统才能再次使用.
分析原因:应该是该报表的SQL引起的,但我们用了这么就一直都没有问题,不知道是不是系统原因.
报表的SQL:
SELECT "POP06TBL"."PROD_COD" ,
"POP06TBL"."WORK_COD" ,
"POP06TBL"."WORK_QTY" ,
"POP06TBL"."YIELD_QTY",
"POP07TBL"."SCRAP_COD",
"POP07TBL"."SCRAP_NAME",
"POP07TBL"."SCRAP_QTY"
FROM
(

SELECT"TEMPTBL"."PROD_COD" "PROD_COD",

"TEMPTBL"."WORK_COD" "WORK_COD",

SUM("TEMPTBL"."WORK_QTY&quot

"WORK_QTY",

SUM("TEMPTBL"."YIELD_QTY&quot

"YIELD_QTY"
FROM
(

SELECT "POP06TBL"."PROD_COD""PROD_COD",

"POP06TBL"."WORK_COD""WORK_COD",

SUM("POP06TBL"."WORK_QTY&quot

"WORK_QTY",

SUM("POP06TBL"."YIELD_QTY&quot

"YIELD_QTY"
FROM "POP06TBL"
WHERE"POP06TBL"."ED_TIME" BETWEEN '2002-05-10 07:00:01' and '2002-05-12 07:00:01'
GROUP BY "POP06TBL"."PROD_COD" ,"POP06TBL"."WORK_COD"
UNION ALL
SELECT "POP24TBL"."PROD_COD""PROD_COD",

"POP24TBL"."WORK_COD""WORK_COD",

SUM("POP24TBL"."WORK_QTY&quot

"WORK_QTY",

SUM("POP24TBL"."YIELD_QTY&quot

"YIELD_QTY"
FROM "POP24TBL"
WHERE"POP24TBL"."ED_TIME" BETWEEN '2002-05-10 07:00:01' and '2002-05-12 07:00:01'
GROUP BY "POP24TBL"."PROD_COD","POP24TBL"."WORK_COD"
) "TEMPTBL"
GROUP BY "TEMPTBL"."PROD_COD","TEMPTBL"."WORK_COD"
)"POP06TBL" ,
(
SELECT "TMPTBL"."PROD_COD" "PROD_COD",

"TMPTBL"."WORK_COD" "WORK_COD",

"TMPTBL"."SCRAP_COD""SCRAP_COD",

"TMPTBL"."SCRAP_NAME" "SCRAP_NAME",

SUM("TMPTBL"."SCRAP_QTY&quot

"SCRAP_QTY"
FROM
(
SELECT "POP06TBL"."PROD_COD" "PROD_COD",

"POP06TBL"."WORK_COD" "WORK_COD",

"POP07TBL"."SCRAP_COD""SCRAP_COD",

"BADCODETBL"."BAD_NAME" "SCRAP_NAME",

SUM("POP07TBL"."SCRAP_QTY&quot

"SCRAP_QTY"
FROM"POP06TBL",

"POP07TBL",

"BADCODETBL"
WHERE( "POP07TBL"."LOT_NO" = "POP06TBL"."LOT_NO&quot

AND( "POP07TBL"."WORK_COD" = "POP06TBL"."WORK_COD&quot

AND( "BADCODETBL"."WORK_COD" = "POP06TBL"."WORK_COD")
AND( "BADCODETBL"."BAD_CODE" = "POP07TBL"."SCRAP_COD")
AND"POP06TBL"."ED_TIME" BETWEEN '2002-05-10 07:00:01' and '2002-05-12 07:00:01'
GROUP BY"POP06TBL"."PROD_COD",

"POP06TBL"."WORK_COD",

"POP07TBL"."SCRAP_COD",

"BADCODETBL"."BAD_NAME"
UNION ALL
SELECT"POP24TBL"."PROD_COD""PROD_COD",

"POP24TBL"."WORK_COD""WORK_COD",

"POP25TBL"."SCRAP_COD" "SCRAP_COD",

"BADCODETBL"."BAD_NAME""SCRAP_NAME",

SUM("POP25TBL"."SCRAP_QTY")"SCRAP_QTY"
FROM"POP24TBL" ,"POP25TBL" ,"BADCODETBL"
WHERE ( "POP24TBL"."LOT_NO" = "POP25TBL"."LOT_NO")
AND ( "POP24TBL"."WORK_COD" = "POP25TBL"."WORK_COD")
AND ( "BADCODETBL"."WORK_COD" = "POP24TBL"."WORK_COD")
AND ( "BADCODETBL"."BAD_CODE" = "POP25TBL"."SCRAP_COD")
AND "POP24TBL"."ED_TIME" BETWEEN '2002-05-10 07:00:01' and '2002-05-12 07:00:01'
GROUP BY"POP24TBL"."PROD_COD",

"POP24TBL"."WORK_COD",

"POP25TBL"."SCRAP_COD",

"BADCODETBL"."BAD_NAME"
)"TMPTBL"
GROUP BY"TMPTBL"."PROD_COD" ,

"TMPTBL"."WORK_COD" ,

"TMPTBL"."SCRAP_COD" ,

"TMPTBL"."SCRAP_NAME"

) "POP07TBL"

WHERE ("POP06TBL"."PROD_COD" = "POP07TBL"."PROD_COD" (+)) AND ("POP06TBL"."WORK_COD" = "POP07TBL"."WORK_COD" (+))
alter_ORA7.log 的内容:
Fri May 24 10:02:48 2002
Errors in file /mnt1/disk1/oracle7/app/oracle/admin/ORA7/udump/ora_3845.trc:
ORA-07445: exception encountered: core dump [10] [0] [0] [0] [] []
Fri May 24 10:03:05 2002
Thread 1 advanced to log sequence 10495
Current log# 3 seq# 10495 mem# 0: /mnt1/disk1/oracle7/app/oracle/oradata/ORA7/redoORA703.log
Fri May 24 10:32:49 2002
Thread 1 advanced to log sequence 10496
Current log# 2 seq# 10496 mem# 0: /mnt1/disk1/oracle7/app/oracle/oradata/ORA7/redoORA702.log
ora_3845.trc由于太多了,我上传上来
sqlnet.log 的内容:
Fatal OSN connect error 12547, connecting to:
(LOCAL=NO)
VERSION INFORMATION:

TNS for HPUX: Version 2.3.2.1.0 - Production

Oracle Bequeath NT Protocol Adapter for HPUX: Version 2.3.2.1.0 - Production

TCP/IP NT Protocol Adapter for HPUX: Version 2.3.2.1.0 - Production
Time: 13-MAY-02 08:21:18
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12547
TNS-12547: TNS:lost contact
ns secondary err code: 12560
nt main err code: 517
TNS-00517: Lost contact
nt secondary err code: 32
nt OS err code: 0
我打电话给ORACLE,Oracle工程师说是由于我的SQL使用了外连接造成的,建议我重新编写,但我已经使用了一年多都没有问题,我想这不是根本的错误原因,
请各位高手帮忙!

千问 · 2005-10-30 17:05:33

我安装的时候就出现这个错误，我用oerr查错误代码的意思，但这东东没用，老是语法不对，后来我发现是连不上数据库，改了initxxx.ora中的能打开的进程数（改小），建库就顺利进行

千问 · 2005-10-30 17:05:33

最初由雪莱发布
[B]我安装的时候就出现这个错误，我用oerr查错误代码的意思，但这东东没用，老是语法不对，后来我发现是连不上数据库，改了initxxx.ora中的能打开的进程数（改小），建库就顺利进行 [/B]
我现在的是

rocesses = 200 需不需要修改

千问 · 2005-10-30 17:05:33

这个应该是网络问题。
将你的查询语句在服务上执行，看是不是还出现这个问题。
如果没有问题，改成存储过程调用看看。
这个问题，我也说不清楚，瞎指点了；（

千问 · 2005-10-30 17:05:33

ORA-03113
end-of-file on communication channel
Cause
An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8, two task, software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down.
Action
If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of customer support.