About DATABASE

KeepAlive 란 Idle TCP/IP Connection 의 Time out 기능을 말한다.

TCP/IP 기반으로 remote 로 연결된 상태에서 클라이언트의 갑작스런 리부팅이나
스위치 Off 의 경우 해당 연결을 서버에서 Closing 하지 못하게 된다.
이것은 TCP/IP의 제한인 것이지 오라클의 문제가 아니다.
이 때 KeepAlive가 정확하게 configure되어 있다면 좀 더 빨리 Dead connection
을 발견하고 빨리 Closing할 것이다.
TCP/IP 에 관련된 것이므로 KeepAlive를 각 플랫폼 별로 지정하는 방법이 모두
틀리다.

예를들면 유저가 PC 에서 Unix Oracle Server로 연결 후 실수로 다음 쿼리 문을
실행했다.

SQL> select * from bigtable, hugetable, massivetable;

적게는 수십분, 많게는 몇시간이 걸릴 작업이라면 유저는 쿼리를 인터럽트하기
보다 reset 버튼을 누르게 될 것이다. 이때 클라이언트 프로세스는 없어졌지만
백그라운드 서버 프로세스는 계속 running 중일것이다.
이런 현상은 수시로 일어날 수 있다. 또한 당연한 것이다.
유저가 사용을 잘못하는 것이기 때문이다.

하지만 SQL*NET 2.1 이상에서 Dead-Connection-Detection 이라 불리는 기능을
지원한다. 이것은 곧 KeepAlive 를 지정할 수 있는 기능이다.
반드시 서버 사이드의 $ORACLE_HOME/network/admin/sqlnet.ora 파일에 다음의
파라미터를 지정해야 한다. 클라이언트에는 지정해봐야 전혀 소용이 없다.

sqlnet.expire_time = 1 (단위는 분)

Netware를 제외한 모든 Platform, Protocol에서 지원이 된다.

# Windows NT 에서 KeepAlive 를 지정하는 방법.
(타 플랫폼은 제외)

Regedt32 를 실행한다.

레지스트리에서

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Tcpip/Parameters

까지 이동한다.
여기서 키 추가가 아닌 값 추가를 한다.

파라미터는 KeepAliveTime (REG_DWORD) 로 입력한다.

값은 Decimal -> 180000 (for example = 3 minutes)

으로 지정한다.

An ORA-3113 "end of file on communication channel" error is a general error usually reported by a client process connected to an Oracle database. The error basically means 'I cannot communicate with the Oracle shadow process'. As it is such a general error more information must be collected to help determine what has happened - this error by itself does not indicate the cause of the problem. For example, ORA-3113 could be signalled for any of these scenarios:

    Server machine crashed
    Your server process was killed at O/S level
    Network problems
    Oracle internal errors / aborts on the server
    Client incorrectly handling multiple connections
    etc.. etc.. etc.. - a lot of possible causes !!

This short article describes what information to collect for an ORA-3113 error. It is common for this error to be accompanied by other errors such as:

    ORA-1041 internal error. hostdef extension doesn't exist
    ORA-3114 not connected to ORACLE
    ORA-1012 not logged on

These are all symptomatic of being disconnected from Oracle.

Please collect as much information as possible from the items listed below and submit this information to Oracle support. Where a step produces an output file / trace file this may be needed by Oracle Support to help determine the cause of the error. The more information you can present in one go the better your chance of a speedy solution. Note that some sections may not be applicable.

If viewing this article on a Web Browser you can follow the links, otherwise manually go to the relevant section.

 
What Scenario does the ORA-3113 occur in ?

  A. When attempting to startup Oracle ?                    -> Section A
  B. When attempting to make a connection to the database ? -> Section B
  C. Client gets the error running SQL / PLSQL ?            -> Section C
  D. Server trace file reports ORA-3113 ?                   -> Section D

You may find it useful to scan the checklist in Section E at the end of this article. This covers some questions / issues relevant to all problem sections.

NOTE: References to SVRMGR are outdated with Oracle9i and Oracle 10g. If you are on an earlier version of the database, you will use 'svrmgrl' instead of SQL*Plus to connect as the internal user.

e.g. svrmgrl
SVRMGR> connect / as sysdba (or connect internal)
(A) ORA-3113 when attempting to STARTUP Oracle

  
  There are several phases involved in starting up a database. If ORA-3113
  occurs during startup then abort the instance and start up using the
  sequence below. If an error occurs at any step then see the related notes
  below.

    a. Start any required services.        On error see A1
       Eg: On NT start the OracleServiceSID

    b. Connect as a SYSDBA user.          On error see A1
       Eg:  sqlplus /nolog
        SQL> connect / as sysdba

    c. Startup nomount.              On error see A1
       Eg:
        SQL> startup nomount

    d. Mount the database.             On error see A1 and A2
       Eg:
        SQL> alter database mount;

    e. Recover the database         On error see A3
       Eg:
        SQL> recover database

    f. Open the database             On error see A4
       Eg:    
        SQL> alter database open;

    g. Wait 3 minutes then issue a select.   On error see A4
       Eg:    
        SQL> select count(*) from DBA_OBJECTS;



A1) Errors connecting as SYSDBA / Internal OR on startup nomount
   
    There is something fundamental wrong with the software / environment
    if you cannot connect to Server Manager as a DBA user.
    The steps here cover errors such as ORA-3113, ORA-12547: TNS:lost contact
    or similar errors connecting to Oracle or starting the instance NOMOUNT. 
    Check the following items:

    A1.1)    If possible reboot the server disabling any automatic
        startup of Oracle before you do so. This may seem drastic
        but helps make sure you are working from a consistent
        starting point.

    A1.2)     Check your environment points at the expected ORACLE_HOME
        and ORACLE_SID and that TWO_TASK is not set (Unix) or
        LOCAL is not set (NT registry).
                Check the USER_DUMP_DEST and BACKGROUND_DUMP_DEST and default
                trace directories under this environment for any user trace
                files or alert log entries generated. These may help indicate
                the cause of the problem.
                Eg: ORA-600[SKGMINVALID] may indicate a problem with the
                    shared memory Unix parameters on Unix systems.
                Try to show that any trace file / alert log entry you
                find is truely related to the "CONNECT" command by re-issuing
                the "connect" and checking for a new trace file / alert entry
                at the time of the error.

        A1.3)   Unix only:
        Some Unix platforms need LD_LIBRARY_PATH to be set
                correctly to resolve any dynamically linked libraries.
                As the user with the problem:

                        % script /tmp/ldd.out
                        % id
                        % cd $ORACLE_HOME/bin
                        % ldd oracle
                        % exit

                If the 'ldd' command does not exist go to the next step below.
                Check that all lines listed show a full library file. If there
                are any 'not found' lines reported contact Oracle support
                with the output of /tmp/ldd.out .


        A1.4)   Unix only:
        Your 'oracle' executable may be corrupt. Relink it thus:

                        Log in as the 'oracle' user.
                        % script /tmp/relink.out
                        % cd $ORACLE_HOME/rdbms/lib
            % mv $ORACLE_HOME/bin/oracle $ORACLE_HOME/bin/oracle.dd.mon.yy
                        % rm -f ./oracle       
                        % make -f ins_rdbms.mk ioracle
                        % exit

        Prior to Oracle7.3 the relink command was:
            make -f oracle.mk ioracle

                If this reports any errors Oracle support will need to see
                the contents of the file /tmp/relink.out .

   
    A1.5)   Have you installed the Parallel Server Option ?
                ORA-3113 can occur if you have installed the Parallel
        Server Option but do NOT have a Distributed Lock Manager
        installed or running correctly.

                Unix:
          If the Parallel Server Option was installed by accident
          then it can be de-installed by relinking.
          Eg:
                        Shut down any Oracle instances
                        % script /tmp/relink.out
                        % cd $ORACLE_HOME/rdbms/lib
                        # 'oracle' should not exist so delete it if it present
                        % rm -f oracle
                        % make -f ins_rdbms.mk no_parropt ioracle
                        % exit

          NB: Do NOT deinstall the Parallel Server Option if the
              database is using Parallel Server unless both nodes
              are shut down otherwise database corruption could occur.

    A1.6)   If the error is on STARTUP NOMOUNT:

            Check the init.ora file used to start the database.
            This provides the configuration details used
            configure the instance. To help isolate the problem
            it may be useful use a very basic init.ora file
            when starting the instance. If this works then
            parameters can be increased / introduced one at a
            time to see if there is a problem with a particular
            setting.

    A1.7)     Check for server side trace files which may give more
        indication what the underlying problem is.
        See section C for details on how to check
        for server trace files.
       

    A1.8)     Ensure there is free disk space in:

          a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations

          b. Your AUDIT destination (Unix)
            The default is $ORACLE_HOME/rdbms/audit

          c. Your Oracle Trace directory if Oracle Trace is enabled
            See Note:45482.1

        A1.9) Windows 2000 - Only
              If the Server's sqlnet.ora file contains Authnetication services
              which are NOT reachable by Oracle, then an ORA-3113 error will
              result.
              For example, if the sqlnet.ora file contains the parameter:
              SQLNET.AUTHENTICATION_SERVICES = (NTS) and the Oracle database
              is moved from a Windows NT Domain to an Active Directory one,
              or if a Domain Controller is introduced, then an error will
              result trying to start the database.
              Remove the sqlnet.authentication_services line so that Oracle
              does not look for a non-existent KDC (Kerberos Domain Controller).


           
A2) Errors Mounting the database

    Check all the items in A1 first.

    If an error occurs when mounting the database there may be problems
    with the control-files or data files, or with resources required to
    open these files.

    A2.1)     The location of the control files are specified in the
        init.ora file.  Try mounting using each controlfile in
        turn.
        eg: "Shutdown abort",
            edit the init.ora to refer to ONE of the controlfiles only,
            "startup nomount",
            "alter database mount"
        Repeat for each controlfile to see if any controlfile works.
       
    A2.2)    It is possible to re-create the controlfiles if you know the
        location of all datafiles and online logs, or to restore an old
        backup controlfile. Always back up the current controlfiles before
        restoring any backup copies or issuing a CREATE CONTROLFILE
        command.
        The steps for this are not documented here.

        A2.3)   Unix:
                Some unix platforms have a 'truss' command (or 'tusc').
        If available this can be used to help trace how far Oracle
        gets before the error occurs.
                Eg: 
                        % truss -o /tmp/truss.out -f svrmgrl

                Keep the file /tmp/truss.out safe - Oracle Support MAY need to see it.


A3) Errors on RECOVER DATABASE

      ORA-3113 during recover database is often related to a corruption on the
    database or redo stream which causes the shadow process to die. There should
    be a server side trace file produced for this sort of problem.
    See Section C for details on how to locate any trace files
    from both USER_DUMP_DEST and BACKGROUND_DUMP_DEST.

    A3.1)    If the "recover database" fails fairly quickly then it
        may help to collect the redo up to the point of failure as this
        may help identify where the problem is.

        Use the following commands just prior to the RECOVER DATABASE
        command:

          SQL> alter session set max_dump_file_size=unlimited;
          SQL> alter session set events
          2> '10228 trace name context forever, level 10';
          SQL> RECOVER DATABASE
       
        This causes redo information to be written to the user trace
        file. The last items of redo may help determine which file
        has problems.

    A3.2)    If you do not have many datafiles in the database it may be
        as quick to recover each file in turn to see if this narrows
        down the problem.
        Eg:
          SQL> select name from v$datafile;
       
        and then for each file:

          SQL> RECOVER DATAFILE 'full_file_name'

        If this gets to a problem file then back up the file and
        use standard recovery options as if the file was lost.
       

A4) Errors on ALTER DATABASE OPEN

    Database open performs very many operations and so it is necessary
    to collect any trace information before determining the next steps.
    However, the following may help isolate the problem more quickly:

    A4.1)     Move files out of your USER_DUMP_DEST and BACKGROUND_DUMP_DEST
        directory as these steps will generate a lot of trace.

    A4.2)     Edit the init.ora file and add the lines:

            event="10046 trace name context forever, level 12"
            event="10015 trace name context forever, level 1"
            event="10228 trace name context forever, level 1"

        If you already have "EVENT=" lines in the init.ora file
        this MUST directly below the other "Event=" lines.
        These lines will trace:

            SQL and BIND activity during startup
            REDO applied
            Information about transactional rollback required

    A4.3)     Startup the instance as described at the top of this section.
       
        As soon as the error occurs REMOVE the above events from the init.ora
        file and shutdown. Collect together the trace files and alert logs
        as described in Section C

       


(B) ORA-3113 attempting to connect to the database

  
   Which SQL*Net layer are you using ?

    a. SQL*Net2 or Net8  -> Goto B1
       This is the default for Oracle 7.0.15 onwards

    b. SQL*Net 1         -> Goto B2
       Note: This can only be used with Oracle releases before 7.3


B1) Connecting to Oracle using SQL*Net V2 / Net8

   Net8 or SQL*Net2 should report network related errors if a problem
   occurs whilst establishing a connection to a remote Oracle shadow
   process. An ORA-3113 implies that the connection itself has been established
   but then is lost. As such follow the steps in Section C

B2) Connecting to Oracle using SQL*Net V1

   This section is included for historical reasons.


   Is the tool you are trying to connect from

    a. A local connection ?    -> Goto BL
       ie: The client is on the same machine as the database and you are
        not trying to connect over a network connection.

    b. A remote connection ? -> Goto BR


   BL) Local SQL*Net V1 Connections

      For local connections check the following:

        BL1)    Have you installed the Parallel Server Option ?
                ORA-3113 will occur if you have installed the Parallel
                Server Option but do NOT have a Distributed Lock Manager
                installed or running.

                To deinstall the Parallel Server Option:

                        Shut down any Oracle instances
                        % script /tmp/relink.out
                        % cd $ORACLE_HOME/rdbms/lib
                        # 'oracle' should not exist so delete it if it present
                        % rm -f oracle
                        % make -f oracle.mk no_parropt ioracle
                        % exit

                If this reports any errors Oracle support will need to see
                the contents of the file /tmp/relink.out .


        BL2)    Try using the SQL*Net V1 driver for local connections:

                        setenv TWO_TASK P:

                Then try the client tool. If this now works you may have a
                problem with the default SQL*Net driver.


        BL3)    Your 'oracle' executable may be corrupt. Relink it thus:

                        Log in as the 'oracle' user.
                        % script /tmp/relink.out
                        % cd $ORACLE_HOME/rdbms/lib
                        % rm -f oracle       
                        % make -f oracle.mk ioracle
                        % exit

                If this reports any errors Oracle support will need to see
                the contents of the file /tmp/relink.out .


        BL4)    Some Unix platforms need LD_LIBRARY_PATH to be set
                correctly to resolve any dynamically linked libraries.
                As the user with the problem:

                        % script /tmp/ldd.out
                        % id
                        % cd $ORACLE_HOME/bin
                        % ldd oracle
                        % exit

                If the 'ldd' command does not exist go to the next step below.
                Check that all lines listed show a full library file. If there
                are any 'not found' lines reported contact Oracle support
                with the output of /tmp/ldd.out .


        BL5)    If you cannot connect as the Oracle user AND your system has
                the 'truss' command try the following when logged in as
                'oracle' (using the relevant client tool):
               
                        % truss -o /tmp/truss.out -f sqlplus user/password
                        Exit from sqlplus (or the problem tool)

                Keep the file /tmp/truss.out safe - Oracle MAY need to see it.


   BR) Remote SQL*Net v1 Connections

      For remote connections check the following:

        BR1)    Check if you can make LOCAL connections. If not then follow
                the steps above for LOCAL connection problems.


        BR2)    Which SQL*Net protocol are you using ?

                Table B.1
                ~~~~~~~~~
                SYMBOL     SQL*Net V1 Layer  Prefix  Debug       Listener
                ------     ----------------  ------  -----       --------
                osnptt      PIPE Two Task     P:     OSNTTD      none
                osnasy      ASYNC             A:     OSNADBUG    none
                osnttt      TCP/IP Two Task   T:     OSNTDBUG    orasrv
                osntlitcp   TCP/TLI Two Task  TT:    OSNTLIDBUG  tcptlisrv
                osntlispx   SPX Two Task      X:     OSNTLIDBUG  spxsrv
                osndnt      DECNET Two Task   D:     OSNDDBUG    none


                For the protocol you are using check that the 'oracle'
                executable has this linked in thus:

                        Log in as oracle on the server
                        % script /tmp/drivers.out
                        % cd $ORACLE_HOME/bin
                        % drivers oracle
                        % exit

                        Eg: If you are using TCP/IP it should list TCP/IP.

                If the 'drivers' command does not exist on your machine check
                the 'oracle' executable as below substituting the relevant
                symbol from Table C.1 for the word 'SYMBOL'. If you get *NO*
                output the driver is probably *NOT* installed.

                        % script /tmp/symbols.out
                        % cd $ORACLE_HOME/bin
                        % nm oracle | grep -i SYMBOL    # Use relevant SYMBOL
                        % exit

                        Eg: For SQL*Net TCP/IP you would use the command:

                                % nm oracle | grep -i osnttt

                If the required driver is not installed you should:

                        a) Relink Oracle (See step (B3) above).
                        b) Re-check the 'oracle' executable for the
                           relevant driver. If it is still missing then
                           the relevant SQL*Net driver has probably not
                           been installed. Reinstall the required SQL*Net
                           driver.


        BR3)    Check your /etc/oratab or /var/opt/oracle/oratab file
                is of the form:

                        # Comments begin with a HASH
                        SID:/path/to/oracle/home:N

                And confirm:
               
                        [ ] There are no blank lines.
                        [ ] The PATH to ORACLE_HOME is correct and contains
                             no environment variables.
                        [ ] There are no ':'s in the ORACLE_HOME path.
                        [ ] There is NOTHING at the end of the line.
                            The last character on a line should be Y or N.
                            There should NOT be a fourth field.


        BR4)    Unix only:
        If you have 'truss' available try to truss the Oracle
                connection. You will normally need 'root' privilege to do this
                and should use truss on the relevant listener process (see
                Table B1)
                Eg: For TCP/IP the listener is 'orasrv' so enter these commands
                    as 'root':

                        % truss -o /tmp/truss.out -f -eall -p <PID_of_orasrv>
               
                        Attempt the connection to reproduce the ORA-3113 then
                        interrupt this 'truss' session.

                This will produce a LOT of output so keep it but do not send it
                to Oracle initially - just make a note that it is available.


(C) Client sees ORA-3113 running SQL / PLSQL

  
   If the ORA-3113 error occurs AFTER you have connected to Oracle then
   it is most likely that the 'oracle' executable has terminated unexpectedly.

        C1)     Determine which database you were connected to and
                obtain the following init.ora parameter values:
       
                        Parameter               Default
                        ~~~~~~~~~               ~~~~~~~
                        USER_DUMP_DEST          $ORACLE_HOME/rdbms/log
                        BACKGROUND_DUMP_DEST    $ORACLE_HOME/rdbms/log
                        CORE_DUMP_DEST          $ORACLE_HOME/dbs

                Eg: To find these log into Server Manager and:

                        SQL> show parameter dump


        C2)     Check in your 'USER_DUMP_DEST' for any Oracle trace file.
                It is important to find the correct trace file.
        On Unix:
          Use the command 'ls -ltr' to list files in time order with the
                  latest trace files appearing LAST. The trace file will typically
          be of the form '<SID>_ora_<PID>.trc'.
        On NT:
          Click on the "Modified" column in Windows Explorer to sort the
          files by their modified date. Files will typically be of the form
          'ORA<PID>.TRC'.
       
        If you are not sure which trace file may be relevant MOVE all
        the current trace files to a different directory and reproduce
        the problem.

        C3)     Check in your 'BACKGROUND_DUMP_DEST' for your alert log and
        any other trace files produced close to the time of the error.
        On Unix this should be named 'alert_<SID>.log'.
        On NT this should be named '<SID>ALRT.LOG'

        C4)     Unix Only:
        If there is no trace file check for a 'core' dump in the
                CORE_DUMP_DEST. Check thus:

                        % cd $ORACLE_HOME/dbs   # Or your CORE_DUMP_DEST
                        % ls -l core*

                If there is a file called 'core' check its time matches the
                time of the problem. If there are directories called
                'core_<PID>' check for core files in each of these. It is
                IMPORTANT to get the correct core file. Now obtain a stack
                trace from this 'core' file. Check each of the sequences below
                for how to do this - one of these should work for your
                platform:


                    If you have dbx:
                        % script /tmp/core.stack
                        % dbx $ORACLE_HOME/bin/oracle core
                        (dbx) where
                        ...
                        (dbx) quit
                        % exit
                       
                    If you have sdb:
                        % script /tmp/core.stack
                        % sdb $ORACLE_HOME/bin/oracle core
                        * t
                        ...
                        * q
                        % exit
                       
                    If you have xdb:
                        % script /tmp/core.stack
                        % xdb $ORACLE_HOME/bin/oracle core
                        (xdb) t
                        ...
                        (xdb) q
                        % exit


                    If you have adb:
                        % script /tmp/core.stack
                        % adb $ORACLE_HOME/bin/oracle core
                        $c
                        ...
                        $q
                        % exit


        C5)     Try to isolate the SQL command that is executing when
                the error occurs. Eg: Is it a particular SQL statement
                or PL/SQL block that causes the error ?
        In many cases this will be listed in the trace file
        produced under the heading "Current SQL statement", or
         near the middle of the trace file under the cursor referred
        to by the "Current cursor NN" line.

        If the trace does not show the failing statement then
        then SQL_TRACE may be used to help determine this provided
          the problem reproduces. SQL_TRACE can be enabled in most
        client tools:

                Eg: Product     Action
                    ~~~~~~~     ~~~~~~
                    SQL*Plus    Issue 'ALTER SESSION SET SQL_TRACE TRUE;'
                    Pro*        EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;

                This should force a server side SQL trace file as detailed
                in C2 above. The trace file should give a clue as to what
                SQL was being executed.


        C6)     If no trace file can be found and the problem is reproducible
        then SQL*Net trace may help to show what the latest operation sent
        to the 'oracle' process was.
                For SQL*Net V2 / V8 tracing see the article Note:16564.1
   
        Historical note:
                     For SQL*Net V1 check which SQL*Net protocol you are using
                    and note the 'Debug' environment variable from table B1 below.
                    Then catch SQL*Net trace from the client. Eg: For SQL*Net
                    TCP/IP and sqlplus:
   
                            % setenv OSNTDBUG -1    # Use correct OSN*DBUG variable
                            % sh
                            % sqlplus scott/tiger@t:hostname:sid 2>/tmp/net1.out
       

        C7)     Based on information collected above try to determine a small
                test case which will reproduce the problem. This is important
                for two reasons:

                        a) It gives Oracle support a small test case if the
                           problem does not look like a known problem.
               
                        b) It gives you a simple way to check if any patch
                           supplied will fix the problem.



    C8)    If a statement can be isolated which consistently raised an
        ORA-3113 error then it is worth spending some time collecting
        additional information such as:
           
            - An execution plan for the statement
            - Table definitions, column definitions
            - Information on constraints, triggers etc..

        ie: Any additional information about the statement which fails.
            eg: If a SELECT fails then it may succeed if run under a
            different optimizer mode.



    C9)     Check if your server administrator has any scripts which abort
        long running or CPU intensive processes. An ORA-3113 process
        can occur if someone kills your Oracle shadow process at O/S
        level (Eg: kill -9 on Unix).


(D) Server trace reports ORA-3113

    D1)     It is unusual for a server trace to report ORA-3113.
        However, this can occur if the server loses contact with
        the client OR a database link connection.
        Treat this the same as an ORA-3113 in a client process
        and follow the steps in Section C.

    D2)     The following line may be added to the init.ora file
        to help collect maximum information when the error occurs:

            event="3113 trace name errorstack level 3"

        If you already have "EVENT=" lines in the init.ora file
        this MUST directly below the other "Event=" lines.

(E) Additional Checks / Information

        E1)     Is it just this one tool that encounters the error or
                do you get ORA-3113 from any tool doing a similar operation ?
                If the problem reproduces in SQL*Plus use this in all tests
                below.

        E2)     Unix only:
        Check if the problem is just restricted to:

                        [ ] One particular UNIX user,
                        [ ] Any UNIX user
                    or  [ ] Any UNIX user EXCEPT as the Oracle user.


        E3)     Check if the problem is just restricted to:

                        [ ] One particular ORACLE logon
                    or  [ ] Any ORACLE logon that has access to the
                                relevant tables.

        E4)     If this is a client-server set up does this occur from:

                        [ ] Any client
                        [ ] Just one particular client
                    or  [ ] Just one group of clients ?
                            If so what do these clients have in common ?
                            Eg: Software release .
               
        E5)     Do you have a second server or database version where the
                same operation works correctly ?


    E6)     Ensure there is free disk space in:

          a. Your USER_DUMP_DEST and BACKGROUND_DUMP_DEST locations

          b. Your AUDIT destination (Unix)
            The default is $ORACLE_HOME/rdbms/audit

          c. Your Oracle Trace directory if Oracle Trace is enabled
            See Note:45482.1

Applies to:

Oracle Server - Personal Edition
Oracle Server - Enterprise Edition
Oracle Server - Standard Edition
Information in this document applies to any platform.
*** Checked for relevance on 05-Feb-2007 ***
*** Checked for relevance on 27-Jul-2010 ***

Purpose

The ORA-3113 error is a general error reported by Oracle client tools,
which signifies that they cannot communicate with the oracle shadow
process. As it is such a general error more information must be collected
to help determine what has happened.

This short article describes what information to collect for an
ORA-3113 error when the Oracle server is on a Unix platform.

Scope and Application

This article is intended for DBAs at all levels of experience.

DIAGNOSING ORA-3113 ERRORS

General Issues:
===============
1) Is it only one tool that encounters the error or do you get an ORA-3113 from any tool doing a similar operation?  If the problem reproduces in SQL*Plus use this in all tests below.

2) Check if the problem is just restricted to:
     [ ] One particular UNIX user,
     [ ] Any UNIX user
 or [ ] Any UNIX user EXCEPT as the Oracle user.

3) Check if the problem is just restricted to:
     [ ] One particular ORACLE logon
 or [ ] Any ORACLE logon that has access to the relevant tables.

4) If you have a client-server configuration does this occur from:
    [ ] Any client
    [ ] Just one particular client
or [ ] Just one group of clients ?

If so what do these clients have in common ?
Eg: Software release .

5) Do you have a second server or database version where the same operation works correctly?

Connecting to Oracle
================

If the ORA-3113 error occurs when actually connecting to Oracle then follow the section below. If you connect to Oracle successfully and get the error on an established connection, please go to the section 'An Established Connection'.

Local Connections
==============

For local connections check the following:

1) Try using the SQL*Net driver for local connections:

setenv TWO_TASK P:

Then try the client tool. If this now works you may have a problem with the default SQL*Net driver.

2) Your 'oracle' executable may be corrupt. Relink it as follows:

For 7.3.X thru 8.1.7

Log in as the 'oracle' user.
% script /tmp/relink.out
% cd $ORACLE_HOME/rdbms/lib
% rm -f oracle
% make -f oracle.mk ioracle
% exit

For 9.2.X thru 11.2.X use the relink executable
relink
usage:
relink
parameters: all, oracle, network,
client, client_sharedlib interMedia,
ctx, precomp, utilities, oemagent, ldap

If this reports any errors Oracle support will need to see the contents of the file /tmp/relink.out .

3) If you cannot connect as the Oracle user AND your system has system call tracing such as truss, trace the problem connection when logged in as 'oracle' (using the relevant client tool). The example given below is for truss:

% truss -o /tmp/truss.out -f sqlplus user/password

Keep the file /tmp/truss.out safe - Oracle MAY need to see it.

Remote Connections
================

For remote connections check the following:

1) Check if you can make LOCAL connections. If not then follow the steps above for LOCAL connection problems.

2) If you have truss or an equivalent tracing tool available try to trace the Oracle connection. You will normally need the root privilege to do this and should trace the listener process.

Eg: For TCP/IP the listener is 'tnslsnr' so enter these commands as 'root':

% truss -o /tmp/truss.out -f -eall -p <pid of tnslsnr>

Attempt the connection to reproduce the ORA-3113 then interrupt this 'truss' session.

An Established Connection:

===================

If the ORA-3113 error occurs AFTER you have connected to Oracle, then it is most likely that the oracle executable has terminated unexpectedly.

1) Determine which database you were connected to and obtain the following init.ora parameter values:

Parameter                                               Default

USER_DUMP_DEST                            $ORACLE_HOME/rdbms/log
BACKGROUND_DUMP_DEST          $ORACLE_HOME/rdbms/log
CORE_DUMP_DEST                           $ORACLE_HOME/dbs

To find these log into SQL*Plus:

SQL> show parameter dump

2) Check your alert_<SID>.log for errors coinciding with the ORA-3113.

3) Check in your 'USER_DUMP_DEST' for any Oracle trace file. It is important to find the correct trace file. Use the command 'ls -ltr' to list files in time order with the latest trace files appearing LAST. If you are not sure which trace file may be relevant, move all the current trace files to a different directory and reproduce the problem. The trace file will typically be of the form 'ora_<spid>.trc'. If there is an error in the alert.log the relevant trace file name will be referenced.

4) If there is no trace file check for a core dump in the CORE_DUMP_DEST. Check as follows:

% cd $ORACLE_HOME/dbs # Or your CORE_DUMP_DEST
% ls -l core*

If there is a file called core, check that its time matches the time of the problem. If there are directories called 'core_' check for core files in each of these. It is IMPORTANT to get the correct core file. Now obtain a stack trace from this core file using Note 1812.1 'TECH: Getting a Stack Trace from a CORE file'
5) Try to isolate the SQL command that is executing when the error occurs. Eg: Is it a particular SQL statement or PL/SQL block that causes the error? To help establish this turn on SQL_TRACE for the client tool.

Eg: Product     Action
~~~~~~~       ~~~~~~
SQL*Plus       Issue 'ALTER SESSION SET SQL_TRACE TRUE;'
Pro*               EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;

This should force a server side SQL trace file as detailed in #3 above. The trace file should give a clue as to what SQL was being executed.

6) Try to obtain any SQL*Net trace to show what the latest operation sent to the Oracle process was (Note 16564.1 'TECH: SQL*Net V2 on Unix - A Quick Guide to Setting Up Client Side Tracing')

7) Based on information collected above try to create a small test case which will reproduce the problem. This is important for two reasons:

a) It allows Oracle to test in a debug environment if the problem does not look like a known problem.
b) It gives you a simple way to check if any patch supplied will fix the problem.

관련 자료

TECH: SQL*Net V2 on Unix - A Quick Guide to Setting Up Client Side Tracing [ID 16564.1]
		Modified 20-OCT-2005 Type REFERENCE Status PUBLISHED

( For a more detailed discussion of SQL*Net V2 tracing see Note:16658.1 )

Client Tracing
~~~~~~~~~~~~~~
1) Set the environment variable TNS_ADMIN to the directory where the 
   tnsnames.ora and listener.ora files exist.

   The default location is $ORACLE_HOME/network/admin. Set $TNS_ADMIN to this
   if it is not set. This ENSURES you know which files you are using.

2) Start the listener:          lsnrctl
                                > set password <password>
                                > start

   Note any errors. If you do not have a password set then ignore the 
   set password command.

3) If the listener started, start the database.

4) Create a file in $HOME called .sqlnet.ora and add the lines:

        trace_level_client= 16
        trace_file_client=client
        trace_directory_client= /tmp            (or similar)
        trace_unique_client=true

5) Try to connect from SQL*Plus thus:

        sqlplus username/password@alias

   or
        sqlplus username/password

   substituting a suitable alias.

6) If you get an error we may need to see the client trace file
   /tmp/client_<PID>.trc where <PID> is the process ID of the 
   client process (*1).
   This will be quite large so it is best to FAX or EMAIL it.

*1 Note: On earlier versions of SQL*Net the filename may NOT have
         the process ID appended to it.


Listener Tracing:
~~~~~~~~~~~~~~~~~

1) Edit your $TNS_ADMIN/listener.ora file and add the lines:

        TRACE_LEVEL_LISTENER     = 16    
        TRACE_DIRECTORY_LISTENER = /tmp
        TRACE_FILE_LISTENER      = "listener"   

2) Stop and restart the listener:

        lsnrctl stop
        lsnrctl start

   Output should go to /tmp/listener.trc

In this Document
  Goal
  Solution
     Launch the Net Manager utility
     Enabling Oracle Net Client / Server tracing (or both).
      Enabling Oracle SQLNet Listener tracing.
     Cyclic Tracing
     Tracing Kerberos
     Tracing Externel Procedures (Extproc)
     Tnsping Trace
     Enabling Dynamic Listener Tracing
     Only for 11g
  References

---

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.2.0
Information in this document applies to any platform.

Goal

This document covers how to use Oracle Net Manager tool to enable Oracle SQLNet Client, Server, Listener, Kerberos and External Procedure tracing.  It also includes how to control the size and amount of trace files produced and steps to start dynamic listener tracing.

Solution

Launch the Net Manager utility

 

Windows : Go to Start button, select the Oracle Home on which we are going to generate the tracing for. Net Manager is located under 'Configuration and Migration Tools' of the Oracle Home menu.

Unix : The tool can also be started remotely from a Microsoft Windows system with the help a terminal emulation software. You need to ensure this software is up and running first.  Ensure that the $DISPLAY, $ORACLE_HOME, and $PATH environment variables are properly set. The Net Manager tool is started by the command "netmgr".

 

 

Enabling Oracle Net Client / Server tracing (or both).

 

A. Click on Profiles in the left panel. The right panel of Net Manager will now show more details.

B. Select General from drop down menu on the right panel of the Net Manager.

C. Select Tracing tab in the right panel. Client Information and Server Information is now seen.

D. Select the required Trace Level. Oracle Support usually requests level SUPPORT (also known as level 16). It is optional to provide a value for the Trace Directory and Trace File. If you do,  ensure that the directory exists and that the owner of the Oracle software is able to write to that directory. The default location for the trace files is the $ORACLE_HOME/network/trace directory. Default name is CLI.TRC

The Unique Trace File Name option for the Client Information section, will make unique trace files for every connection. Without this option selected, all client tracing goes into the one file.  If running Microsoft Windows, always set this parameter or there is the potential that the trace file with the error will be overwritten.  The Server does not have this parameter available.

E. If tracing is to be done on the Server and the connection model is Shared Server, then the Instance will have to be restarted for the Dispatchers to pick up the changes to the SQLNET.ORA file.

The SQLNET.ORA will now look like the following :

 Enabling Oracle SQLNet Listener tracing.

A. In the left panel of the Net Manager, select Listeners view.

B. Select the Listener you want to trace. Once selected the right hand panel will show more details.

C. Select General Parameters from the drop down button, in the right hand panel.

D. Select Logging & Tracing tab.

E. Click the Tracing Enabled radio button and select Trace Level. SUPPORT level is normally requested by Oracle Support.

Select a directory for the trace to be generated.

G. Listener will need to be restarted or reloaded to enable or disable tracing.
 

 

The LISTENER.ORA will now have the following lines.

Cyclic Tracing

Adding parameters TRACE_FILENO_ and TRACE_FILELEN_ to the SQLNET.ORA or LISTENER.ORA files enables Oracle Net trace files to be limited a certain size and a certain number of files. Following example is for Listener traces of 10 MB, with a maximum of 10 files for a maximum of 100 Mb of trace files. Cyclic tracing can be configured for the Client, Server and Listener

TRACE_FILENO_{CLIENT/SERVER/listener_name}: When this parameter is set it will determine the maximum number of trace files that will be generated. Trace data will continue to generate but in a cyclic fashion. The maximum size of each file is set by configuring the appropriate TRACE_FILELEN parameter.

TRACE_FILELEN_{CLIENT/SERVER/listener_name}: When set determines the maximum size of each trace file before either starting a new trace file (if the TRACE_FILENO parameter is set) or will overwrites an existing file (cyclic tracing). Once the last trace file has been written, up to the size specified by TRACE_FILELEN_<CLIENT/SERVER/listener_name>, tracing will continue by overwriting the first trace file in the cycle.

Tracing Kerberos

Kerberos can be traced using the following parameters in the SQLNET.ORA file.
See Note 185897.1 Kerberos Troubleshooting Guide for more details.

 

Tracing Externel Procedures (Extproc)

External procedure can be traced using the following parameter in the SQLNET.ORA file, where extproc is setup.

 

Tnsping Trace

 

Enabling Dynamic Listener Tracing

There might be situations when you cannot reload or restart the listener to enable the traces.Such times, you can fmake use of the dynamic tracing for the listener .

This would create sqlnet support level tracing on the listener under directory /home/oracle with name listener.trc

Only for 11g

11g has new method of tracing Automatic Diagnostic Repository(ADR)  which will create the trace to the ADR directories
Reference
Note 454927.1Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g

To create the trace files to the desired directory,
For client tracing & server tracing add:


For listener tracing:

Unable to start Net Manager

If Net Manager is unable to start, check that you are able to launch other GUI tools from the same server, where Net Manager is failing. For example the X-Window utility "xclock". If you are not able to launch xclock, you need to address this problem, before attempting to start Oracle GUI tools. If xclock works, check other Oracle GUI tools, like DBCA before reporting the issue to Oracle Support.

References

NOTE:374116.1 - How to Match Oracle Net Client and Server Trace Files
NOTE:454927.1 - Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g

---

Applies to:

Oracle Application Object Library
Information in this document applies to any platform.
Checked for relevance on 15-Feb-2010

Solution

goal: How to Run and View a Listener Trace


fact: Oracle Application Object Library 10.7 G


fact: Oracle Application Object Library 10.7 N


fact: Oracle Application Object Library 11






fix:

1. Set the parameter TRACE_LEVEL_LISTENER=16 in the listener.ora file and
bounce the listener to start the trace.

(or)

At the command line type: 

lsnrctl trace 16

Note: This level of tracing generates a huge amount of information in the trace 
file. Turn it off once enough trace information is obtained.

2. Attempt to view a report from the client. This will produce a very detailed 
trace file of the connection attempt.

3. Set the parameter TRACE_LEVEL_LISTENER=0 in the listener.ora file and
bounce the listener to stop the trace.

(or)

At the command line type: 

lsnrctl trace OFF

4. Navigate to $ORACLE_HOME/bin

5. Run trcasst 

Ex: trcasst filename.trc>[outfile]

Where [outfile] is the name of the output file to contain the modified trace 
file.

6. View the output file

Reference:
See the Net8 Administrator's manual for details on Trace Assistant.

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 7-MAY-2010.

Goal

When attempting to limit the amount of trace generated when TNS listener tracing is enabled, cyclic tracing can be used. But to further restrict the amount of time TNS listener is enabled, tracing can be enabled and disabled, dynamically via the listener control, lsnrctl. This means tracing can be enabled only when required and allows the listener to run without generating trace until required.But when enabled via this method, Time stamp is missing from the trace file(s) generated. The following steps explain how to workaround the time stamp restriction.

Solution

To workaround the current restriction of TNS listener tracing enabled via listener control, generating traces without time stamp, the following steps can be used. Steps 1 to 3 must be inplaced before attempting to capture any problem with tracing.

 1. Backup the Listener.ora file, add the following parameters and save.

TRACE_LEVEL_<Listener Name> = 16
TRACE_FILE_<Listener Name> = <File Name>
TRACE_DIRECTORY_<Listener Name> = <Directory>
TRACE_TIMESTAMP_<Listener Name> = ON
TRACE_FILELEN_<Listener Name> = <Size in KiloBytes>
TRACE_FILENO_<Listener Name> = <Number>

TRACE_FILELEN and TRACE_FILENO are the cyclic tracing parameters.Ensure values used for these are large enough to capture information,without being overwritten.

2.Reload the listener, with command lsnrctl reload <listener name>

With a default listener name of "listener", the listener name can be excluded from commands.

3. Stop listener tracing, run set trc_level 0 via lsnrctl

If your listener name is not using the default name of "listener", then run command "set current_listener <listener name> in listener control, before any other command is used.

4. When TNS listener tracing is required to be re-enabled. run set trc_level 16 via lsnrctl.

 
5. Turn off tracing as soon as the problem / issue has been captured with command "set trc_level 0" from lsnrctl. (As per step 3). Remove or hash out the parameters added to the listener.ora in Step 1 and run reload command. This will stop tracing from being re-activated.

Problem Description:
====================

Established connections fails intermittently.
ORA-03113 is returned to the application.
Application must start a new connection.
No errors are generated in the database alert.log


Solution Description:
=====================

WARNING: Misuse of Windows Registry/Editor can cause serious problems
that may require you to reinstall your operating system.

Increase TcpMaxDataRetransmissions parameter in the Windows Registry


On Windows NT/2000:

HKEY_LOCAL_MACHINE
      SYSTEM       
        CurrentControlSet         
          Services
            Tcpip             
             Parameters

add a key "TcpMaxDataRetransmissions" (REG_DWORD) and set its value to 10.

On the Edit menu, click Add Value, and then add the following:

      Value Name: TcpMaxDataRetransmissions
      Value Type: REG_DWORD - Number
      Valid Range: 0 - 0xFFFFFFFF
      Default Value: 5 Decimal
      New Value: 10 to  Decimal

The value may vary as it is dynamically adjusted.


On Windows 95:

HKEY_LOCAL_MACHINE     
   SYSTEM       
     CurrentControlSet         
       Services
            Winsock

add a key "TcpMaxDataRetransmissions" and set its value to 10.


On the Edit menu, click Add Value, and then add the following:

      Value Name: TcpMaxDataRetransmissions
      Value Type: REG_DWORD - Number
      Valid Range: 0 - 0xFFFFFFFF
      Default Value: 5 Decimal
      New Value: 10  Decimal


Solution Explanation:
=====================

When a TCP/IP packet in a given segment is sent, the sender waits for
an acknowledgment (ACK) before proceeding to the next task.
If an ACK is not received before the retransmission timer expires,
the retransmission timer value is doubled and the packet is resent.

A retrans counter is incremented and the process repeats until the
retrans counter is equal to the TcpMaxDataRetransmissions value.
At this point the network is considered timed-out and the socket is
closed by the Microsoft Operating System.

Oracle SQLNET/Net8 tracing reports this as an ntt2err: SOC xxxx error
as well as several other Oracle network transport errors (NT).

The default initial retransmission timer value is set to 3 seconds.
This value is doubled and it's product is doubled on the next iteration,
until iterations equal the TcpMaxDataRetransmissions value.

The total time to time-out with default values looks like this:
  (3s)+(3s*2)+(3s*4)+(3s*8)+(3s*16)=93s OR 1 min 33 seconds to network
  time-out.

If you see something very close to the following a SQL*Net/Net8 trace
file, the most likely cause would be that the network has timed-out.
The ntt2err SOC error is the real error - all errors appearing after
this are secondary or misleading errors.

ntt2err: soc xxxxx error - operation=5, ntresnt[0]=517, ntresnt[1]=54,
ntresnt[2]=0

Following the appearance of ntt2err, you may expect to see something like the
following. These errors are symptoms of the real problem.  They tell us
that the network or server went down when it only timed-out.

-<ERROR>- osnqrc:  wanted 1 got 0, type 0
-<ERROR>- osnqper:  error from osnqrc
-<ERROR>- osnqper:    nr err code: 0
-<ERROR>- osnqper:    ns main err code: 12547
-<ERROR>- osnqper:    ns (2)  err code: 12560
-<ERROR>- osnqper:    nt main err code: 517
-<ERROR>- osnqper:    nt (2)  err code: 54
-<ERROR>- osnqper:    nt OS   err code: 0
osnqer: entry
osnqer:  incoming err = 12151


A little later on you may see the following:
osnqer:  returning err = 3113

A clear sign of a busy network is:
ntt2err: soc xxxx - operation=5,
ntresnt[0]=517, ntresnt[1]=54, ntresnt[2]=0
This is very consistent.
       
The ORA-3113 tells us:
Text:   end-of-file on communication channel

You can look in the trace but it will be impossible to find an EOF
packet dump. You must review the second meaning of ora-3113 - the
network or server machine went down. Once again somewhat misleading
as the network was only busy and the server is really fine.


Note:
Increasing TcpMaxDataRetransmissions is only a workaround for a bad or over
stressed network. The workaround should gives you some time to address the
underlying network issues.
It is time to get out the network sniffer and measure the degree of the
problem.
It is more than likely that it will get worse over time, particularly with
increased network usage.


Search words:

Tcp Max Data Retransmissions
TcpMaxDataRetransmissions
3113
ntt2err: soc error -
operation=5, ntresnt[0]=517,
ntresnt[1]=54, ntresnt[2]=0


ref: {4950.102}    BUG-561277