14 回覆 最後回覆日期:Nov 16, 2012 8:54 AM,建立者:Feng Gao -Oracle

    ora-03113的问题深入发掘

    957417
      环境:linux oracle11Gr1 11.1.0.7 通过dblink访问 oracle11gR1 11.2.0.2,出现ora-03113错误,后来设置事件跟踪
      alter system set events '3113 trace name errorstack level 3';
      发现错误日志中有如下日志,查了一下好像是一个bug,还请刘大指导一下。如果是bug应该下载哪个补丁

      Trace file /oracle/app/oracle/diag/rdbms/misdb/misdb1/trace/misdb1_ora_13653.trc
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      ORACLE_HOME = /oracle/app/oracle/product/11.1.0
      System name:     Linux
      Node name:     mis-db1
      Release:     2.6.18-92.el5
      Version:     #1 SMP Tue Apr 29 13:16:15 EDT 2008
      Machine:     x86_64
      Instance name: misdb1
      Redo thread mounted by this instance: 1
      Oracle process number: 74
      Unix process pid: 13653, image: oracle@mis-db1


      *** 2012-11-11 18:05:50.195
      *** SESSION ID:(2104.42089) 2012-11-11 18:05:50.195
      *** CLIENT ID:() 2012-11-11 18:05:50.195
      *** SERVICE NAME:(SYS$USERS) 2012-11-11 18:05:50.195
      *** MODULE NAME:(JDBC Thin Client) 2012-11-11 18:05:50.195
      *** ACTION NAME:() 2012-11-11 18:05:50.195

      WARNING:Could not set the asynch I/O limit to 4097 for SQL direct I/O. It is set to 4096

      *** 2012-11-11 18:06:39.835
      WARNING:Could not set the asynch I/O limit to 4097 for SQL direct I/O. It is set to 4096
      WARNING:Could not set the asynch I/O limit to 8193 for SQL direct I/O. It is set to 4096

      *** 2012-11-13 11:51:29.390
      ----- Error Stack Dump -----
      ORA-03113: 通信通道的文件结束
      ORA-02063: 紧接着 line (起自 ETLDB)
      ----- Current SQL Statement for this session (sql_id=3xaxryrzk02x1) -----
      UPDATE DAILY_DATA_ETL_CHECK NOLOGGING SET BUSI_DATE = :B1 ,FLAG = '0',BEG_DATE=NULL,END_DATE=NULL WHERE TABLE_SOURCE = '0' AND FLAG='3' AND BEG_DATE IS NOT NULL
        • 1. Re: ora-03113的问题深入发掘
          LiuMaclean(刘相兵)
          请把TRACE发给我
          • 2. Re: ora-03113的问题深入发掘
            957417
            刘大trace已经发了,请查收
            • 3. Re: ora-03113的问题深入发掘
              LiuMaclean(刘相兵)
              ----- Error Stack Dump -----
              ORA-03113: 通信通道的文件结束
              ORA-02063: 紧接着 line (起自 ETLDB)
              ----- Current SQL Statement for this session (sql_id=3xaxryrzk02x1) -----
              UPDATE DAILY_DATA_ETL_CHECK NOLOGGING SET BUSI_DATE = :B1 ,FLAG = '0',BEG_DATE=NULL,END_DATE=NULL WHERE TABLE_SOURCE = '0' AND FLAG='3' AND BEG_DATE IS NOT NULL
              ----- PL/SQL Stack -----
              ----- PL/SQL Call Stack -----
                object      line  object
                handle    number  name
              0x696b71360      1426  package body MIS_WH.PKG_TAT_DEPOSIT
              0x6964bd988         1  anonymous block
              
                 SO: 0x69dee1908, type: 4, owner: 0x69cffdf40, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
                   proc=0x69cffdf40, name=session, file=ksu.h LINE:10719, pg=0
                  (session) sid: 2104 ser: 42089 trans: 0x683d1e108, creator: 0x69cffdf40
                            flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
                            flags2: (0x40009) -/-/INC
                            DID: , short-term DID: 
                            txn branch: 0x683da19d0
                            oct: 6, prv: 0, sql: 0x69b17e198, psql: 0x69a28b6a0, user: 87/MIS_WH
                  ksuxds FALSE at location: 0
                  service name: SYS$USERS
                  client details:
                    O/S info: user: SYSTEM, term: unknown, ospid: 1234
                    machine: mis-manage program: JDBC Thin Client
                    application name: JDBC Thin Client, hash value=2546894660
                  Current Wait Stack:
                    Not in wait; last wait ended 0.824144 sec ago 
                  Wait State:
                    auto_close=0 flags=0x21 boundary=(nil)/-1
                  Session Wait History:
                   0: waited for 'SQL*Net break/reset to dblink'
                      driver id=28444553, break?=0, =0
                      wait_id=1556598 seq_num=28725 snap_id=1
                      wait times: snap=0.000005 sec, exc=0.000005 sec, total=0.000005 sec
                      wait times: max=infinite
                      wait counts: calls=0 os=0
                      occurred after 0.000005 sec of elapsed time
                   1: waited for 'SQL*Net break/reset to dblink'
                      driver id=28444553, break?=1, =0
                      wait_id=1556597 seq_num=28724 snap_id=1
                      wait times: snap=0.000021 sec, exc=0.000021 sec, total=0.000021 sec
                      wait times: max=infinite
                      wait counts: calls=0 os=0
                      occurred after 0.000002 sec of elapsed time
              你这个update 用了DBLINK 吗?

              DAILY_DATA_ETL_CHECK 是什么对象 ,请列出该对象的DDL
              再重复一次 NOLOGGING 这样用是无效的, 该给开发人员上上课了!
              • 4. Re: ora-03113的问题深入发掘
                957417
                DAILY_DATA_ETL_CHECK 是一个同义词,用了dblink
                • 5. Re: ora-03113的问题深入发掘
                  957417
                  linux oracle11Gr1 11.1.0.7 通过dblink访问 oracle11gR1 11.2.0.2,
                  • 6. Re: ora-03113的问题深入发掘
                    957417
                    linuxel5 oracle11Gr1 11.1.0.7.10 通过dblink访问 linux el5 oracle11gR2 11.2.0.2.0,
                    • 7. Re: ora-03113的问题深入发掘
                      Feng Gao -Oracle
                      ++++以下的trace跟ora-3113应该没有关系:
                      WARNING:Could not set the asynch I/O limit to 8193 for SQL direct I/O. It is set to 4096

                      ++++ora-3113错误是因为收到了reset packet,不管是从另一端应用,或者防火墙。

                      如果发生不频繁,那么忽略。
                      如果经常发生,那么上传两边的sqlnet.ora,然后检查防火墙/网络最近的改动。

                      如果实在是没有思路,在能够立刻重现的情况下,开SQLNET TRACE或/和开TCPDUMP --- 这些操作对性能有冲击,请在Oracle Support支持下设置。

                      Thank you, Feng
                      • 8. Re: ora-03113的问题深入发掘
                        957417
                        1)sqlnet.ora
                        SQLNET.INBOUND_CONNECT_TIMEOUT=0
                        SQLNET.SEND_TIMEOUT=0
                        SQLNET.RECV_TIMEOUT=0
                        2)这种情况是不定时发生,但是只要发生了,前台应用程序就报错了。
                        • 9. Re: ora-03113的问题深入发掘
                          有教无类
                          那是否闲置一定时间后发生
                          • 10. Re: ora-03113的问题深入发掘
                            Feng Gao -Oracle
                            兄弟,下面两个参数很奇怪,不知道设成0是什么意思。
                            但是根据我的经验(我处理过3个类似的问题,就是错误的设上了下面两个参数),最好删掉下面的两个参数。
                            SQLNET.SEND_TIMEOUT=0
                            SQLNET.RECV_TIMEOUT=0


                            Thank you, Feng Gao
                            • 11. Re: ora-03113的问题深入发掘
                              957417
                              设置在指定的时间间隔内必须有数据接收/发送,为了防止长时间的等待
                              SQLNET.RECV_TIMEOUT,SQLNET.SEND_TIMEOUT 都设置为0 ,应该是不设置超时的意思把
                              • 12. Re: ora-03113的问题深入发掘
                                Feng Gao -Oracle
                                嗯,你可以删掉他俩试一下
                                • 13. Re: ora-03113的问题深入发掘
                                  957417
                                  请教一下:你当时出现ora-12170的时候有啥现象吗,后来是如何处理的
                                  • 14. Re: ora-03113的问题深入发掘
                                    Feng Gao -Oracle
                                    SQLNET.INBOUND_CONNECT_TIMEOUT设为0