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/spfile 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*Plus and issue: SQL> show parameter dump C2) Check in your 'USER_DUMP_DEST' for any Oracle trace files. 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 Windows: 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. It should be named 'alert_<SID>.log'. C4) UNIX only: 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 it's 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 to see how to do this - one of these should work for your platform. Please, refer to Note 1812.1 - TECH: Getting a Stack Trace from a CORE file on Unix 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 file does not show the failing statement 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 a SQL*Net trace may help to show what the latest operation sent to the 'oracle' process was.
C7) Based on the information collected above try to put together 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 raises 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 the O/S level (Eg: kill -9 on UNIX).
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/spfile 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*Plus and issue: SQL> show parameter dump
C2) Check in your 'USER_DUMP_DEST' for any Oracle trace files.
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 Windows:
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.
It should be named 'alert_<SID>.log'.
C4) UNIX only:
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 it's 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
to see how to do this - one of these should work for your
platform.
Please, refer to
Note 1812.1 - TECH: Getting a Stack Trace from a CORE file on Unix
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 file does not show the failing statement
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 a SQL*Net trace may help to show what the latest operation sent
to the 'oracle' process was.
C7) Based on the information collected above try to put together 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 raises 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 the O/S
level (Eg: kill -9 on UNIX).