Common listener-related errors in ALERT logs: troubleshooting of ORA-609 errors, alertora-609

Common listener-related errors in ALERT logs: troubleshooting of ORA-609 errors, alertora-609
Reference MOS documents include:
Troubleshooting Guide ORA-609: Opiodr aborting process unknown ospid (Documentation ID 1121357.1)
Alert. log shows ORA-609 with TNS-12537: TNS: connection closed (Document ID 1538717.1)
Fatal NI Connect 12560 'And 'ora-609 Opiodr Aborting Process' Errors In The Alert Log (Document ID 987162.1)
Database ALERT logs often see ORA-609, ORA-3136/ORA-609 TNS-12537 and TNS-12547 or TNS-12170 12170, 'tns-12535 and other related errors, this type of problem is summarized as follows:
1. ORA-609 troubleshooting guide:
The following error message is displayed in the Alert log:
Fatal NI connect error 12537, connecting:
(LOCAL = NO)

Version information:
TNS for Linux: Version 11.2.0.3.0-Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0-Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0-Production
Time: 26-FEB-2013 02:23:51
Tracing not turned on.
Tns error struct:
Ns main err code: 12537

TNS-12537: TNS: connection closed
Ns secondary err code: 12560
Nt main err code: 0
Nt secondary err code: 0
Nt OS err code: 0
Opiodr aborting process unknown ospid (28725) as a result of ORA-609

First an explanation of this kind of errors.

The message
Opiodr aborting process unknown ospid (...) as a result of ORA-609
Is just a configurations that oracle database closed (aborted) a dedicated process because of ORA-609.

ORA-609 means "cocould not attach to incoming connection" so the database process was 'aborted' (closed) because it couldn't attach to the incoming connection passed to it by the listener.
The reason for this is found in the sqlnet error stack, in our case is:
TNS-12537: TNS: connection closed.
Basically the dedicated process didn't have a client connection anymore to work.
This error is similar to a notification that ORACLE terminated a database-specific connection-ospid (28725) because the ORA-609 was disabled ).
ORA-609 error cause: unable to contact the incoming connection, so unable to transfer this connection to the listener, so the database process stops this process.
The error TNS-12537: TNS: connection closed is reported at this time because the client connection is abnormal.

The process of connecting the client to the ORACLE database through the listener:

1. Client initiates a connection to the database so it connects to the listener
2. Listener starts (fork) a dedicated database process that will receive this connection (session)
3. After this dedicated process is started, the listener passes the connection from the client to this process
4. The server process takes the connection from the listener to continue the handshake with the client
5. Server process and client exchange information required for establishing a session (ASO, Two Task Common, User logon)
6. Session is opened
Simply put:
1. Connect the client to the listener
2. Listen to a subprocess derived from fork and convert it to the VPC process dedicated database process
3. After step 2 is completed, the listener transfers the client connection to the VPC process dedicated process.
4. After the server process receives the connection information from the listener, it needs to continue handshake with the client connection.
5. The server process exchanges the information required to establish a session with the client process, such as the user name and password.
6. After the preceding OK statements, the SESSION is OPEN.
In Steps 3 and 4, the client connection is closed. When the dedicated database process communicates with the client, it is found that the client is closed.

###############################
Use tracking to troubleshoot:
Document: Troubleshooting Guide ORA-609: Opiodr aborting process unknown ospid (Document ID 1121357.1)
For troubleshooting of such problems, the use of listener. log or SQLNET tracking is not very good, because there may be many connections per second while SQLNET tracking does not provide more client information.
In this case, you can try to use the OS-level tracking.
For example, 1111 is the listening process and ps-ef | grep tnslsnr is detected.
LINUX: strace-rf-o/tmp/lsnr1.log-p 1111
HP-UX: tusc-T hires-afpo/tmp/lsnr1.log 1111


If TRACE tracing is used:
3. Oracle Net Level 16 Server tracing. Add to server side SQLNET. ORA file
DIAG_ADR_ENABLED = off # Disable ADR if version 11g
TRACE_LEVEL_SERVER = 16 # Enable level 16 trace
TRACE_TIMESTAMP_SERVER = ON # Set timestamp in the trace files
TRACE_DIRECTORY_SERVER = <DIRECTORY> # Control trace file location

TRACE_FILELEN_SERVER = <n> # Control size of trace set in kilobytes eg 20480
TRACE_FILENO_SERVER = <n> # Control number of trace files per process

The Errorstack method is as follows:
4. Errorstack: Setup errorstack to capture failure. This can be particle useful when capturing an Oracle Net client trace is not feasible.
SQL> alter session set events '2017 errorstack (3 )';

Once a few traces have been collected while the error is reproduced:
SQL> alter session set events '2017 off ';
######################################## #######


The solutions to this problem are:
Document: Alert. log shows ORA-609 with TNS-12537: TNS: connection closed (Document ID 1538717.1)
Possible causes:
The client is stuck and crashed; the connection is killed by the firewall; the client timeout settings; the client is closed immediately after connection; the network is unstable;
Check the information in client tnsnames. ora/sqlnet. ora:

Possible timeouts in sqlnet. ora in client oracle home:

Sqlnet. outbound_connect_time
Sqlnet. recv_timeout
Sqlnet. send_timeout
Tcp_connect_timeout

Possible timeout in client connect descriptor (hardcoded in client application or in client tnsnames. ora ):
Connect_timeout

--------------

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.