ORA-03113: end-of-file on communication channel

Posted on 2002-05-01
Last Modified: 2012-02-07
If i run  "sqlplus user/pass@connectstr"  - works fine.
If i am user oracle  and run  "sqlplus user/pass" - works fine.
But if I am another user and run  "sqlplus user/pass" - get error ERROR: ORA-03113: end-of-file on communication channel.

Question by:weinerk
  • 3

Expert Comment

ID: 6983612

"sqlplus user/pass" ... this is an incomplete connection string, you need to reference the @connectstr as in your 1st example.  The @connectstr must have entry in tnsnames.ora file, but you know that.


Accepted Solution

ORACLEtune earned 300 total points
ID: 6983653
excuse me, have reread you 2nd statement:

"If i am user oracle  and run  "sqlplus user/pass" - works fine."

well - this depends on your UNIX environment, or Windows Environment Variable Environment.  Were you using a different OPERATING SYSTEM User Account when this situation occurred -

A lot of factors here.

Below doc may be useful to you:

Solution Description:
   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.  
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  
        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
   continue with this section. If you connect to Oracle successfully and get
   the error on an established connection, please go to the next section
   'An Established Connection'.
   Local Connections
      For local connections check the following:
        1)      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 no_parropt ioracle
                        % exit
                If the above reports any errors Oracle support will need to    
               see the contents of the file /tmp/relink.out.
       2)     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.
       3)     Your 'oracle' executable may be corrupt. Relink it as follows:
                        Log in as the 'oracle' user.
                        % script /tmp/relink.out
                        % cd $ORACLE_HOME/rdbms/lib
                        % rm -f oracle        
                        % make -f ioracle
                        % exit
                If this reports any errors Oracle support will need to see
                the contents of the file /tmp/relink.out .
       4)     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.
       5)     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.
   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)     Which SQL*Net protocol are you using ?
                Table C.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 as follows:
                        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 do not      
               receive any output then:
                        % 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
       3)     Check your /etc/oratab or /var/opt/oracle/oratab file
              is of the form:
                        # Comments begin with a HASH
                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.
       4)     If you have truss available try to truss the Oracle  
              connection. You will normally need the root privilege to do this
              and should use truss on the relevant listener process (see
              Table C1)
                  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.
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
                Eg: To find these log into SQL*DBA or Server Manager and:
                        SQLDBA> show parameter dump
         2)     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_<PID>.trc'.
         3)     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_<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 the procedure. One of the following should work for your  
                    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
                        % exit
         4)     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  
                Eg: Product     Action
                    ~~~~~~~     ~~~~~~
                    SQL*Forms   Use the '-s' statistics option at run time.  
                    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 #2 above. The trace file should give a clue as to what
                SQL was being executed.
         5)     Try to obtain any SQL*Net trace to show what the latest
                operation sent to the oracle process was.  
                For SQL*Net V2 ask for the article [NOTE:16564.1]
                For SQL*Net V1 check which SQL*Net protocol you are using  
                and note the Debug environment variable from table C1 above.
                Then catch SQL*Net trace from the client. Eg: For SQL*Net
                TCP/IP and sqlplus:
                        % setenv OSNTDBUG -1    # Use correct OSN*DBUG        
                        % sh
                        % sqlplus scott/tiger@t:hostname:sid 2>/tmp/net1.out
         6)     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.
        7)     It may be useful to follow the instructions in step (4) above.
               This will produce a lot of output but MAY be useful if no      
              other information is available.

Expert Comment

ID: 6983721
I see:

"But if I am another user and run  "sqlplus user/pass" - get error ERROR: ORA-03113: end-of-file on communication

If you are on UNIX, then copy the .profile from the "oracle" unix account to the Unix Account in which this error occurred.... Sounds like a UNIX ORACLE environment variable problem.



Author Comment

ID: 6984961
I still dont know root cause.
I suspect corrupt DB.
I just did a reinstall.


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Concat multi row values of a field in oracle 6 64
scheduler for Procedure in DB with 3 arguments in 10g 7 33
PL SQL Developer 7 35
migration MS SQL database to Oracle 30 61
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question