Master Note: Troubleshooting Oracle User (Client) Processes (Doc ID 1512275.1)

Master Note: Troubleshooting Oracle User (Client) Processes (Doc ID 1512275.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Net Services - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

PURPOSE

This document is intended to assist Database Administrators resolve issues encountered involving User (Client) Processes.

TROUBLESHOOTING STEPS

Concepts

A client (user) process executes the application or Oracle tool code. When users run client applications such as SQL*Plus, the operating system creates client processes to run the applications.

For example, assume that a user on a client host starts SQL*Plus and connects over the network to database exdb on a different host (the database instance is not started):

SQL> CONNECT SYS@exdb AS SYSDBA
Enter password: *********
Connected to an idle instance. <<<< database instance is not started

On the client host, a search of the processes for either sqlplus or exdb shows only the sqlplus client process:

% ps -ef | grep -e exdb -e sqlplus | grep -v grep
clientuser 29437 29436  0 15:40 pts/1    00:00:00 sqlplus           as sysdba

Client processes differ in important ways from the Oracle processes interacting directly with the instance. The Oracle processes servicing the client process can read from and write to the SGA, whereas the client process cannot. A client process can run on a host other than the database host, whereas Oracle processes cannot.

Oracle Database creates server processes to handle the requests of client processes connected to the instance. A client process always communicates with a database through a separate server process. Although the client application and database can run on the same computer, greater efficiency is often achieved when the client portions and server portion are run by different computers connected through a network. More details in Note 1504268.1

Issues Involving Client (User) Processes

Many of the issues involving client/user processes involve compatibility of versions between client and server.

Client / Server / Interoperability

Whether you are using Oracle tools like SQL*Plus or a Third Party tool/application, the client and server version should be interoperable. The following Note is a comprehensive document outlining the support for interoperability between Oracle client and server versions and should be checked first when looking at possible issues.

Note 207303.1 - Client / Server / Interoperability Support Matrix For Different Oracle Versions

Issues involving Client Connections

The following is a list of some of the known issues connecting from specific client versions:

Note 785291.1 - Unable To Connect To 11g Db From LINUX 11g Client via SQLPUS as non-Oracle user Hang
Note 3437884.8 - Bug 3437884 - 10g client cannot connect to 8.1.7.0 - 8.1.7.3 server
Note 3564573.8 - Bug 3564573 - ORA-1017 when 10g client connects to 8i/9i server with EBCDIC ASCII connection
Note 4511371.8 - Bug 4511371 ORA-6544 / ORA-4052 using PLSQL between 10g and 11g
Note 389713.1 - Understanding and Diagnosing ORA-00600 [12333] / ORA-3137 [12333] Errors
Note 207319.1 - ALERT: Connections from Oracle 9.2 to Oracle7 are Not Supported

Issues Involving User Processes

Note 3835429.8 - Bug 3835429 - OERI[kqrfrpo] / DB hang after killing a user process
Note 10325142.8 - Bug 10325142 - Client process stack size gets reset by OCIEnvCreate() call
Note 7348613.8 - Bug 7348613 - ORA-600 [17281] during process cleanup for failed client
Note 1196373.1 - ORA-600 [17280] When Client Side Process Dies Or Breaks The Connection Due to Inbound Connection Timed Out
Note 1448953.1 - User Process Aborts Due To ORA-24761 Error
Note 316916.1 - Processes Remain In V$Process Without A Related Session ORA-00020

Helpful Articles on Client / User Processes

Note 1506805.1 - Master Note: Troubleshooting ORA-03113
Note 119706.1 - Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors
Note 951892.1 - Why does a server process continue to run after its client process has been terminated?
Note 387077.1 - How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed?
Note 601605.1 - A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
Note 206007.1 - How To Automate Cleanup Of Dead Connections And INACTIVE Sessions
Note 219968.1 - Client side tracing? SQL*Net & Oracle Net Services - Tracing and Logging at a Glance
Note 164768.1 - Troubleshooting: High CPU Utilization
Note 1019526.6 - Script: To Obtain Session Information
Note 1501987.1 - Master Note: Overview of Database Resident Connection Pooling (DRCP)

Helpful Articles on Connecting as SYSDBA

Note 233223.1 - Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues

Further Diagnostics

If you were not able to resolve the issue with the details provided in this document, please raise a Service Request for further assistance from Oracle Support.

REFERENCES

NOTE:207303.1 - Client / Server Interoperability Support Matrix for Different Oracle Versions
NOTE:1504268.1 - Master Note: Overview of User (Client) Processes
Troubleshooting Session Administration (Doc ID 805586.1) ============================================================
A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes (Doc ID 601605.1)

Note--> This information collected from Oracle and all rights reserved for Oracle. knowledge purpose we are using this note not for commercial use.

==========================================================