DIAGNOSING ORA-3113 ERRORS [ID 1020463.6]

DIAGNOSING ORA-3113 ERRORS [ID 1020463.6]

[@more@]

DIAGNOSING ORA-3113 ERRORS [ID 1020463.6]
	Modified21-OCT-2010 TypeBULLETIN StatusPUBLISHED

Applies to:

- 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

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