oracle备份数据12170,alert log报12170和TNS-12535 TNS-00505的处理方法

硬件说明：

操作系统版本：ORACLE LINUX

6.3  64位

数据库版本：11.2.0.3

64位

问题说明：

在检查数据库的alert日志的时候，发现大量的12170和TNS-12535的错误；

Fatal NI connect

error 12170.

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:

06-APR-2014 10:46:14

Tracing not

turned on.

Tns error

struct:

ns

main err code: 12535

TNS-12535:

TNS:operation timed out

ns

secondary err code: 12560

nt

main err code: 505

TNS-00505:

Operation timed out

nt

secondary err code: 110

nt OS

err code: 0

Client

address:

(ADDRESS=(PROTOCOL=tcp)(HOST=110.80.1.83)(PORT=50226))

Sun

Apr 06 10:46:24 2014

问题解决：

在metalink平台上面查找，该症状和文档

ID (1628949.1)描述的症状完全一样，根据文档的内容整理如下：

1、出现问题的版本

Oracle Net

Services - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to

12.1]

Information in this document

applies to any platform.

2、出现错误的症状或报错格式如下：

Fatal NI connect

error 12170.

VERSION INFORMATION:

TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version

11.2.0.3.0 - Production

Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version

11.2.0.3.0 - Production

Time: 22-FEB-2014 12:45:09

Tracing not turned on.

Tns error struct:

ns main err code: 12535

TNS-12535: TNS:operation timed out

ns secondary err code: 12560

nt main err code: 505

TNS-00505: Operation timed out

nt secondary err code: 60

nt OS err code: 0

***Client address:

(ADDRESS=(PROTOCOL=tcp)(HOST=121.23.142.141)(PORT=45679))

The PORT field here

is the ephemeral port assigned to the client for this

connection. This does not correspond to the listener port.

3、问题的原因

The alert.log message

indicates that a connection was terminated AFTER it was established

to the instance. In this case, it was terminated 2 hours and 3

minutes after the listener handed the connection to the database.

This would indicate

an issue with a firewall where a maximum idle time setting is in

place.

The connection would

not necessarily be "idle". This issue can arise during a long

running query or when using JDBC Thin connection pooling. If there

is no data 'on the wire' for lengthy

periods of time for

any reason, the firewall might terminate the connection.

4、解决方法：

The following

parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can

resolve this kind of problem. DCD or SQLNET.EXPIRE_TIME can mimic

data transmission between the server and the client during long

periods of idle time. SQLNET.EXPIRE_TIME=n Where is a

non-zero value set in minutes.

进入ORACLE_HOME/network/admin目录下，添加sqlnet.ora文件，增加一行SQLNET.EXPIRE_TIME=10

5、补充说明SQLNET.EXPIRE_TIME

Purpose

To

specify a time interval, in minutes, to send a check to verify that

client/server connections are active. The following usage notes

apply to this parameter:

Setting a value

greater than 0 ensures that connections are not left open

indefinitely, due to an abnormal client termination.

If

the probe finds a terminated connection, or a connection that is no

longer in use, then it returns an error, causing the server process

to exit.

This parameter is

primarily intended for the database server, which typically handles

multiple connections at any one time.

Limitations on using this terminated connection

detection feature are:

It

is not allowed on bequeathed connections.

Though very small,

a probe packet generates additional traffic that may downgrade

network performance.

Depending on which

operating system is in use, the server may need to perform

additional processing to distinguish the connection probing event

from other events that occur. This can also result in degraded

network performanc

6、做完以上操作后，重启数据库的监听；

详细的metalink链接

：https://support.oracle.com/epmos/faces/DocumentDisplay?id=1628949.1

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

ORACLE 数据库运维群367875324

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