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

mlion

怎么理解，什么原因

swz_19981018

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.

hecm22

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 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     
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 oracle.mk 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 oracle.mk 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                             
driver.  

3)     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.  

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 <ID_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_<ID>;.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_<ID>;' 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                  
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  

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  tool.   

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                                                           variable  
% 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.