WARNING: inbound connection timed out (ORA-3136) 解决方法 、ora-03113 的报错，related information :end-of-fil

1、 ORA-03113

[oracle@testos:/home/oracle]$ oerr ora 03113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup.
//          Also, look in the alert.log file for any errors. Finally, test to
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
[oracle@testos:/home/oracle]$

在实际的开发过程中，碰到了ora-03113 的报错，related information :end-of-file had check…

处理：

增加sqlnet.ora 文件：

DIAG_ADR_ENABLED =OFF

Oracle 11g 默认DIAG_ADR_ENABLED =ON，即启用了Automatic Diagnostic Repository特性。

将DIAG_ADR_ENABLED =OFF，即恢复到11g之前的client trace产生的方式，在测试环境网络不稳定的情况下，是有帮助的，但是如果网络稳定的情况下不建议在生产环境中将DIAG_ADR_ENABLED =OFF，因为使用Oracle 11g的adr集中日志和trace管理方式对日后的数据库日常管理是有帮助的。

Understanding Automatic Diagnostic Repository

The automatic diagnostic repository (ADR) is a systemwide tracing and logging central repository. The repository is a file-based hierarchical data store for depositing diagnostic information, including network tracing and logging information.

The ADR home is the unit of the ADR directory that is assigned to an instance of an Oracle product. Each database instance has its own ADR home. Similarly, each listener, Oracle Connection Manager, and client instance has its own ADR home.

The location of an ADR home is given by the following path, which starts at the ADR base directory: diag/product_type/product_id/instance_id

另外的解决方案：

1. the hostname of file: listener.ora should be changed from Upper case to lower case as below:

   (ADDRESS = (PROTOCOL = TCP)(HOST = HGDSFE01)(PORT = 1521))
   #改为
   (ADDRESS = (PROTOCOL = TCP)(HOST = hgdsfe01)(PORT = 1521))
   

   因为 /etc/hosts里面映射的是小写主机名

   #cat /etc/hosts
   43.82.201.185   hgdsfe01
   

2. Do relink operation. And first, we should shut down the Database, then do following command

   #（因为按装的时候是copy安装的，一定要做relink）
   $relink all
   

3. 检查client 的plsql 中的system32 中的host 文件的配置：也要为小写，还有oracle/network/admin/tnsnames.ora 的配置

4. 注意：改这些配置，要关掉database 的，如果是生产机的，要放到周末来做

2、 ORA-03136

[oracle@testosa:/home/oracle]$ oerr ora 3136
03136, 00000, "inbound connection timed out"
// *Cause:  Inbound connection was timed out by the server because
//          user authentication was not completed within the given time
//          specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value
// *Action: 1) Check SQL*NET and RDBMS log for trace of suspicious connections.
//          2) Configure SQL*NET with a proper inbound connect timeout value
//             if necessary.

WARNING: inbound connection timed out (ORA-3136) 解决方法

SQLNET.INBOUND_CONNECT_TIMEOUT：这个参数是指客户端连接数据库服务认证的时间长，即用户连接DB的时间的，单位是秒。 当client 在INBOUND_CONNECT_TIMEOUT指定的时间内没有成功连接上服务器（db 负载大，网络延时等原因）。 那么在服务器的sqlnet.log里就会记录下客户端的IP和ORA-12170:TNS:Connect timeout occurred的错误信息。 同时客户端接收到ORA-12547:TNS:lost contact 或者ORA-12637:Packet receive failed的错误。

在oracle10.2.0.1之前，这个参数默认是0s。即不受连接时间的限制。在这个版本之后，为了防止Dos的攻击，将默认值该成60s.

有三种原因可能导致这个错误：
(1) Server gets a connection request from a malicious client which is not supposed to connect to thedatabase , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet logfile.
–恶意的攻击，可以在sqlnet log里查看客户端的地址信息。
(2) The server receives a validclient connection request but the client takes a long time to authenticate morethan the default 60 seconds.
–服务端正常的接收了客户端的请求，但是客户端用了超过默认60秒的时间来验证。
(3) The DB server is heavily loadeddue to which it cannot finish the client logon within the timeout specified.
–DB Server 负载较大，此时也可能导致连接超时。

解决方法：

1 增加连接时间
一般情况下60s是够用的，如果60秒还报ORA-3136的错误。 Oracle 的解决方法是增加这个时间，用更多的时间来连接。 这个也是Oracle 建议的修改方法。
（1）修改sqlnet.ora：将Timeout 设成180
SQLNET.INBOUND_CONNECT_TIMEOUT= 180
（2） 修改listener.ora

添加参数：  INBOUND_CONNECT_TIMEOUT_<listenername> = 170
加上监听名称，我的例子中的监听是LISTENER. 所以设成：
INBOUND_CONNECT_TIMEOUT_ LISTENER = 170

注意：inbound_connect_timeout_listener_name 参数的值要小于sqlnet.inbound_connect_timeout 的值。
（3）重启监听
这样设置以后，如果clients因为系统或者网络的原因在指定的时间没有连接到DB，那么Oracle会根据需要来增加连接的时间。

2 关闭超时限制
在安全上允许的情况下，设置如下参数为0以关闭连接超时的限制：
（1).listener.ora:
INBOUND_CONNECT_TIMEOUT_LISTENER=0
（2).sqlnet.ora:
SQLNET.INBOUND_CONNECT_TIMEOUT=0
（3).重启实例listener