ORA-609

ORA-609 opiodr aborting process unknown ospid tips

Para remover este error  ORA-609 , debemos incrementar el valor para INBOUND_CONNECT_TIMEOUT en listener.ora  del servidor y sqlnet.ora.

- sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

- listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Para obtener información adicional para este error ORA-609 , mira  MOSC note 1121357.1


Troubleshooting Guide ORA-609 : Opiodr aborting process unknown ospid (Doc ID 1121357.1)

Este error se presenta para las siguientes versiones de Oracle:
Oracle Net Services - Version 11.1.0.7 to 12.1.0.2 [Release 11.1 to 12.1]
Information in this document applies to any platform.


Revisamos el alert.log de nuestra base:

ORA-609 is being reported in the alert log.  The error is intermittent and may not occur for days at a time. 

Fatal NI connect error 12547, connecting to:

 (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: 16-JUN-2016 09:04:12

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12547

TNS-12547: TNS:lost contact

    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 (8064) as a result of ORA-609

Listener log shows the connection was established with no apparent errors.  This is because the connection
 failed AFTER the listener has handed off the connection to the server process.

12-OCT-2009 10:03:39 * (CONNECT_DATA=(SID=ORCL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=123.456.1.123)(PORT=3158)) * establish * ORCL * 0

12-OCT-2009 10:03:39 * (CONNECT_DATA=(SID=ORCL)) * (ADDRESS=(PROTOCOL=tcp)(HOST=123.456.1.123)(PORT=3159)) * establish * ORCL * 0

Notice in this example Oracle Net Server trace, filename "svr_7464.trc"

Here the problem is seen when receiving the connection packet from client.  Note that the ORA-609 does not appear in the Oracle Net trace.  The ORA-609 is represented by the accompanying ns=12537 in this trace snippet.

[000001 12-OCT-2009 10:03:39:116] nscon: doing connect handshake...

[000001 12-OCT-2009 10:03:39:116] nscon: recving a packet

[000001 12-OCT-2009 10:03:39:116] nsprecv: entry

[000001 12-OCT-2009 10:03:39:116] nsprecv: reading from transport...

[000001 12-OCT-2009 10:03:39:116] nttrd: entry

[000001 12-OCT-2009 10:03:39:163] nttrd: exit

[000001 12-OCT-2009 10:03:39:163] ntt2err: entry

[000001 12-OCT-2009 10:03:39:163] ntt2err: Read unexpected EOF ERROR on 7104

[000001 12-OCT-2009 10:03:39:163] ntt2err: exit

[000001 12-OCT-2009 10:03:39:163] nsprecv: error exit

[000001 12-OCT-2009 10:03:39:163] nserror: entry

[000001 12-OCT-2009 10:03:39:163] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; 

[000001 12-OCT-2009 10:03:39:163] nscon: error exit

[000001 12-OCT-2009 10:03:39:163] nsdo: nsctxrnk=0

[000001 12-OCT-2009 10:03:39:163] nsdo: error exit

[000001 12-OCT-2009 10:03:39:163] nsinh_hoff: error recving request

Other times tracking ORA-609 via Alert and Oracle Net server traces, shows issue further on during handshake.

Alert.log except here:

Mon Dec 21 15:52:15 2009

ORA-609 : opiodr aborting process unknown ospid (21631120_1)

[21-DEC-2009 15:52:15:025] nscon: sending NSPTAC packet

[21-DEC-2009 15:52:15:025] nspsend: entry

[21-DEC-2009 15:52:15:031] ntt2err: Read unexpected EOF ERROR on 14

[21-DEC-2009 15:52:15:031] ntt2err: exit

[21-DEC-2009 15:52:15:031] nsprecv: error exit

[21-DEC-2009 15:52:15:031] nserror: entry

[21-DEC-2009 15:52:15:031] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; 

[21-DEC-2009 15:52:15:031] nsrdr: error exit

[21-DEC-2009 15:52:15:031] nsdo: nsctxrnk=0

[21-DEC-2009 15:52:15:031] nsdo: error exit

[21-DEC-2009 15:52:15:031] nsnareceive: error exit

[21-DEC-2009 15:52:15:031] nserror: entry

[21-DEC-2009 15:52:15:031] nserror: nsres: id=0, op=68, ns=12537, ns2=12532; 

[21-DEC-2009 15:52:15:031] nacomrc: failed with error 12637

[21-DEC-2009 15:52:15:031] nacomrc: exit

[21-DEC-2009 15:52:15:031] na_receive_packet: failed with error 12637

[21-DEC-2009 15:52:15:031] na_receive_packet: exit

[21-DEC-2009 15:52:15:031] na_server: failed with error 12637

It is common to find corresponding errors in the sqlnet.log file that is local to the instance.

Fatal NI connect error 12537, connecting to:

(LOCAL=NO)

VERSION INFORMATION:

TNS for Solaris: Version 11.2.0.2.0 - Production

Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production

TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.2.0 - Production

Time: 21-DEC-2009 15:52:15

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

Matched to an event in the Oracle Net Server Trace

TROUBLESHOOTING STEPS

1. Find the incoming client(s) making the connections from the listener.log.
Alert log will show an ORA-609 error similar to following :

Mon Oct 05 12:41:49 2009

ORA-609 : opiodr aborting process unknown ospid (21131406_1)

Go to the listener.log and find the entry for this connection.  The entry in the listener.log should look similar to the following:

05-OCT-2009 12:41:49 * (CONNECT_DATA=(SID=orcl)) *

(ADDRESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=1234)) * establish * orcl * 0

Notice the client address in our example is "sample.com".   One option is to locate several clients and enable client tracing at those sites.  You might inspect the log file (ORACLE_HOME/network/log) at the client(s) and check specifically for timeout errors that might have occurred at the same timestamp.

2.

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

Cyclic tracing will allow you to control the size of and number of trace files that are produced.

The TRACE_FILELEN parameter is for the size of a trace file.
The TRACE_FILENO parameter is the number of traces per process.

Important Notes:

The SQLNET.ORA file is only read once on creation of a process. RDBMS Background process and shared server dispatchers will need to be restarted for parameter changes in the SQLNET.ORA to be picked up. Once a process has started to be traced, tracing will not stop until that the process stops.

In an environment where both GRID and RDBMS homes are installed, instance would reference a sqlnet.ora file in RDBMS_HOME/network/admin by default.  (Whereas the listener would refer to GRID_HOME/network/admin for its .ora files)

Please note, that enabling Oracle Net server tracing can produce large amounts of trace, in a very short time frame. Even with cyclic tracing, each process will have the TRACE_FILENO_SERVER value amount of traces produced. Optimal tracing workflow should be to enable tracing, reproduce problem and then disable tracing. Thus limiting amount of time tracing is enabled.

Setting TRACE_FILENO_SERVER to 1 and TRACE_FILELEN_SERVER = 20480, could be a solution to lowering the amount of trace generated per process. Remember the trace file will be overwritten and you could lose the important data covering the failure.

4. Errorstack: Setup errorstack to capture failure. This can be particular useful when capturing an Oracle Net client trace is not feasible.

SQL> alter session set events '609 errorstack(3)';

SQL> alter session set events '609 off';

Once you get a failure:

• Review the SQLNET.LOG file on server.

• Find the matching entry in the ALERT. LOG, compare via timestamp.

• From the entry in the SQLNET.LOG file, you will have the Oracle Net server trace name, from the line "Tracing to file". 

• Open the server trace and grep / search for the Connection ID value.

• Then search the clients trace client directory for the same Connection ID value.