###################alter_oraSID.log####################

Fatal NI connect error 12537, connecting to:

(LOCAL=NO)

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

Time: 18-DEC-2013 08:25:58

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

Wed Dec 18 08:28:25 2013

Thread 1 advanced to log sequence 22477 (LGWR switch)

#######################################################

#######################MOS################################

It can be somewhat challenging to determine the origin of the client that is causing the error.

For that reason, we often recommend increasing the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure. If the problem is due to connection timeouts, an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

These settings are in seconds. Again, the default is 60. A setting of 120 seconds should resolve most if not all ORA-609 errors.

As explained in the Cause section, The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process, so ORA-609 and TNS- errors are expected while shutting down the database - as client connection will fail to complete or aborted the connection process before the connection/authentication process was complete. So please ignore the ORA-609 and TNS- errors, if these are appear while database instance is shutting down.

If the issue persists and inbound connect does not have any effect, the following steps are intended to help locate the client that may be causing the errors.

1) Suppress the TNS errors in the alert.log by setting the following sqlnet.ora file parameter:

sqlnet.ora file (on the server): DIAG_ADR_ENABLED = OFF

Reload the listener following this change.

This will cause the TNS errors to be posted to the ORACLE_HOME/network/log/sqlnet.log file that is local to the database and may yield useful information about the client's address.

For example, here's a snippet from a server side sqlnet.log where client address info was posted:

Production Time: 15-FEB-2010 07:15:01

Fatal NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=yourhost)(Port=1521))(CONNECT_DATA=(SID=PROD1DR)(CID=(PROGRAM=sqlplus)(HOST=client_host)(USER=client))))

Observe the PROGRAM and HOST fields on the last line. This is where the connection originated.
Be sure to match timestamps in the sqlnet.log with the timestamps of the alert.log errors. Once you've located the offending client, you can enable client tracing to try and determine the cause:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<dir location>
TRACE_TIMESTAMP_CLIENT=TRUE
DIAG_ADR_ENABLED=off <<<
If you need assistance with client or server tracing, please open an SR with Global Customer Support.

2) Check the listener.log for client connections that were logged at timestamps that match the ORA-609 timestamps as they appear in the alert.log. The client information is recorded in each listener.log entry. Since this error occurs AFTER the listener has handled the connection, do not expect to see errors in the listener.log.

Here's an example snippet of an incoming client connection that was posted to the listener.log:

20-JAN-2009 17:08:45 (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\Db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=myclient)

Note that the exact timestamp, program name and client host will often be recorded. Again, once you've located the offending client, enable tracing (see above) to try to capture the connection failure.

3) Enable server side Oracle Net tracing and capture the TNS error along with the incoming connection.
Match the PID that accompanies the ORA-609 to the server trace label. e.g.

ORA-609 : opiodr aborting process unknown ospid (4799_1) *Note the PID

This PID would correspond to server trace labeled: svr_4799.trc. Check the server trace for either TNS error (the 609 will not appear) and try to locate the originating client address. If assistance is needed for this investigation, please open an SR with Oracle Support.

See below for instuctions on enabling Oracle Net server tracing.

The following details the discovery of the source of an ORA-609 for a real case:

The alert.log reports the following messages intermittently but frequently:

Mon Nov 16 22:39:22 2009

ORA-609 : opiodr aborting process unknown ospid (nnnn)

Enabled Oracle Net server tracing:

TRACE_LEVEL_SERVER=16

TRACE_DIRECTORY_SERVER=<dir location>

TRACE_TIMESTAMP_SERVER=TRUE

DIAG_ADR_ENABLED=off

Reloaded listener and wait for error to appear again.:

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

In this particular case, there was no information about the client in the trace. This is atypical for a server trace. It may be that the client aborted before all the client information was posted to the file. However, there was post in the listener.log for an emagent connection that was established at the same point in time.

Here's an excerpt from a listener.log entry where an emagent establishes a connection:

PROGRAM=D:\oracle\product\10.1.0\Db_1\bin\emagent.exe)

Checked the EM Agent traces and logs and discovered the following entry:

Fatal NI connect error 12547, connecting to:

(LOCAL=NO)

VERSION INFORMATION:

TNS for Solaris: Version 11.1.0.7.0 - Production

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

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

Time: 16-NOV-2009 22:39:22

****Tracing to file: /backup/sid_traces/sqlnetlog/svr_5233.trc

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

****Note the name of the server trace which contains the PID: svr_5233.trc

Also, the timestamp of the agent event matches the timestamp of the alert.log error.

#######################################################

1.

oracle 11G bug

2.

將/etc/resolv.conf配置檔案中的nameserver註釋。

3.

8I及之前版本，在 listener.ora中配置CONNECT_TIMEOUT_<listener_name> and make it a higher value.

4.

9I及之後版本，CONNECT_TIMEOUT_<listener_name> parameter is obsoleted.

5.

11G中此錯誤會出現在ALERT日誌中。這個錯誤對應用基本沒有影響，通常可以忽略。

ORACLE就給了一個損招，不讓監聽超時錯誤出現在告警日誌裡，回到10G的形式儲存在監聽的LOG中。。

Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log (文件 ID 1286376.1)

方法1： server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

方法2：

Also, to back out the ADR diag for the Listener component, server's listener.ora:

DIAG_ADR_ENABLED_LISTENER = OFF

此時出現監聽超時錯誤只出現在監聽日誌，注意ADR_BASE_LISTENER = /orabase --刪除此條目。

對sqlnet檔案的修改是要重新註冊監聽才能生效的。

6.

11G中還可以設定sqlnet.ora--文件 ID 1628949.1

SQLNET.EXPIRE_TIME=n Where <n> is a non-zero value set in minutes.

Oracle11g ORA-609 TNS 12537 TNS 12560

相關文章