ORA-3136 troubleshooting

ORA-3136 error solved 1. parameter description: SQLNET. INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has toprovide the necessary authentication information to a database. INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client hasto complete its connect request to the listener after the network connection has been established. 2. parameter meaning: in oracle 10 In Versions later than. 2, SQLNET. INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername are set to 60 s by default. in earlier versions, this function is disabled by default ). These two parameters are used to prevent malicious clients from attacking dbserver Note: This timeout restriction was introduced to combat Denial of Service (DoS) through the network) attack wherebymalicious clients attempt to flood database servers with connect requests that consumes resources. 3. the reason for this warning is as follows:. the server receives a connection request from a malicious client. In this case, the error thrown is expected and desirable. B. The server receives a valid client connection request. However, it takes a long time for the client to verify that the request exceeds the default value of 60 seconds. (Client network latency) the c. DB server has a high load, which makes it unable to complete client login requests within the specified time. (High database server load) 4. solution:. verify whether the local connection is successful on the db server (whether the listener is available) B. verify whether the local connection is delayed (whether the local network is efficient and available) c. verify the database performance (whether the database server is highly loaded) d. whether the alarm log has reported a ORA-600 or ORA-7445e before. check whether a client has repeatedly tried to log on to the password (this is the most likely reason Currently) f. disable this function (set parameter 2 to 0, and set method to step 5). You can also set parameter 2 to an appropriate value. 5. the two parameters can be modified as follows: Edit the server side sqlnet. ora file and add this parameter: SQLNET. INBOUND_CONNECT_TIMEOUT = <n> Where <n> is the value in seconds. e. g.: SQLNET. INBOUND_CONNECT_TIMEOUT = 120 Edit the listener. ora file and add this parameter: INBOUND_CONNECT_TIMEOUT _ <listenername >=< n> Again, where <n> is the timeout value in seconds. for example if the listener name is LISTENER then use: INBOUND_CONNE CT_TIMEOUT_LISTENER = 110 6. summary: This warning prompt is very likely due to a client's repeated attempts to connect to the database or login delay without knowing the password. I personally think this can be ignored, restarting the listener does not solve this problem. It only clears the listener's alarm information temporarily. You can also observe or capture the performance analysis report for a period of time, check whether the alarm is caused by high load and performance degradation.: ORA-3136 error resolution: ORA-3136 error resolution 1. First, the following error was found through the background alter log: Mon May 20 12:44:19 2013 WARNING: inbound connection timed out (ORA-3136) mon May 20 12:44:19 2013 WARNING: inbound connection timed out (ORA-3136) Mon May 20 12:44:19 2013 WARNING: inbound connection timed out (ORA-3136) 2. Observe sqlnet. the following error is recorded in the log: Fatal NI connect error 12514, connecting to: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = XXXXX) (PORT = 1521) (CONN ECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXXXXXXX) (INSTANCE_NAME = XXXX) (CID = (PROGRAM = XXXXXX) (HOST = XXXXXX) (USER = oracle )))) version information: TNS for Linux: Version 10.2.0.1.0-Production TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0-Production Time:-2008 14:10:42 Tracing not turned on. tns error struct: ns main err code: 12564 TNS-12564: TNS: connection refused ns secondary er R code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 3. reload Listner: [oracle @ XXX ~] $ LsnrctlLSNRCTL for Linux: Version 10.2.0.4.0-Production on-2013 12: 54: 56 Copyright (c) 1991,200 7, Oracle. all rights reserved. welcome to LSNRCTL, type "help" for information. LSNRCTL> reloadConnecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521) The command completed successfully LSNRCTL> servicesConnecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521) Services Summary... service "+ ASM" ha S 1 instance (s ). instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service... handler (s): "DEDICATED" established: 0 refused: 0 state: ready LOCAL SERVERService "+ ASM_XPT" has 1 instance (s ). instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service... handler (s): "DEDICATED" established: 0 refused: 0 state: ready LOCAL SERVERService "PLSExtProc" has 1 instance (s ). instance "PLSExtPro C ", status UNKNOWN, has 1 handler (s) for this service... handler (s): "DEDICATED" established: 0 refused: 0 LOCAL SERVERService "XXXXXXXX" has 2 instance (s ). instance "XXXXXX", status READY, has 1 handler (s) for this service... handler (s): "DEDICATED" established: 16 refused: 0 state: ready remote server (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXXXX) (PORT = 1521 )) instance "XXXXXX", status READY, has 2 handler (s) For this service... handler (s): "DEDICATED" established: 0 refused: 0 state: ready remote server (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXX) (PORT = 1521 )) "DEDICATED" established: 31 refused: 0 state: ready LOCAL SERVERService "XXXXXXXXX" has 2 instance (s ). instance "XXXXXX", status READY, has 1 handler (s) for this service... handler (s): "D000" established: 0 refused: 0 current: 0 max: 972 state: ready DISPATCHER <m Achine: XXXXXXX, pid: 28579> (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXX) (PORT = 6661) Instance "XXXXXX", status READY, has 1 handler (s) for this service... handler (s): "D000" established: 0 refused: 0 current: 0 max: 1022 state: ready DISPATCHER <machine: XXXX, pid: 28800> (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXX) (PORT = 51924) Service "XXXXXXXX" has 2 instance (s ). instance "XXXXXXX", status READY, has 1 handler (s) fo R this service... handler (s): "DEDICATED" established: 16 refused: 0 state: ready remote server (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXX) (PORT = 1521 )) instance "XXXXXXX", status READY, has 2 handler (s) for this service... handler (s): "DEDICATED" established: 0 refused: 0 state: ready remote server (ADDRESS = (PROTOCOL = tcp) (HOST = XXXXXXX) (PORT = 1521 )) "DEDICATED" established: 31 refused: 0 state: ready LOCAL SERVERThe Command completed successfullyLSNRCTL> show inbound_connect_timeoutConnecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 )) LISTENER parameter "inbound_connect_timeout" set to 60The command completed successfully 4. After modification, observe that the system has recovered to normal for a period of time.