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 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: SVRMGRL> 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 ' 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 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.