ORA-3136 in oracle, ORA-609

This article introduced in detail about oracle ORA-3136, ORA-609 method, there is a need for friends can refer to this article Oh.

Fatal NI connect error 12170.

Version information:
TNS for 64-bit Windows: Version 11.2.0.1.0-Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0-Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0-Production
Time:-2011 16:46:57
Tracing not turned on.
Tns error struct:
Ns main err code: 12535

TNS-12535: TNS: Operation timeout
Ns secondary err code: 12606
Nt main err code: 0
Nt secondary err code: 0
Nt OS err code: 0
Client address: (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.3.53) (PORT = 2950 ))
WARNING: inbound connection timed out (ORA-3136)

 

Fatal NI connect error 12560, connecting:
(LOCAL = NO)

Version information:
TNS for 64-bit Windows: Version 11.2.0.1.0-Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0-Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0-Production
Time:-2011 11:22:32
Tracing not turned on.
Tns error struct:
Ns main err code: 12560

TNS-12560: TNS: protocol adapter error.
Ns secondary err code: 0
Nt main err code: 0
Nt secondary err code: 0
Nt OS err code: 0
Opiodr aborting process unknown ospid (21388) as a result of ORA-609
Wed Nov 09 11:22:49 2011

This ora-3136 error, in most cases, we can ignore, because this error is generally caused by the client due to the correct password, the connection timeout. For a very simple example, we use sqlplus user/password @ tnsname, but the entered password is wrong, oracle prompt: ORA-01017: invalid username/password; logon denied, after, do not do anything. The connection is hung there. After one minute, you can see this error in alertlog.

Therefore, one possibility of an ora-3136 error is the client usage error password login, but later did not exit the connection.

But the ora-3136 error is not only this kind of possibility, but also when receiving a connection from a malicious client, such as Dos attack, in addition, when the database load is heavy, there will also be such an error. You can see three possibilities described in metalink Troubleshooting ORA-3136 WARNING Inbound Connection Timed Out [ID 465043.1:

There can be three main reasons for this error-
 
1. server gets a connection request from a malicious client which is not supposed to connect to the database, in which case the error thrown is the correct behavior. you can get the client address for which the error was thrown via sqlnet log file.
2. The server has es a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
According to my understanding, in short, the oracle listener receives a request from the client. When fork is sent to the server process, if an unexpected occurs during this process, such as a wrong password, if the database load is too heavy, will be the parameter ora-3136 error.

 

It may be because the database server is under high pressure or the application server is under high pressure. If the authentication process is not completed within the past 60 seconds, this error will be reported.

Run awr and find that the shared pool is relatively large. library cache latch and shared pool latch are replaced with swap, and page in increases cpu wio, the phenomenon is exactly the same as the situations in our system. After several optimization operations, we mainly analyze the process as follows: 1. Analyze the business connection, mainly through monitoring logs and switch firewalls, determine the number of connections per second, determine which services are connected, and confirm that there is a problem with the business connection pool. After being crawled by other sites, the pressure is very high, limit the connection pool, and solve the business connection problem; analyze whether there are no Bound variables. After analysis, the system code is good and all bind variables are used. 3. Analyze the shared pool configuration and check whether automatic memory configuration is used, 10 Gb of memory cannot be solved. After automatic configuration, a bug will cause the pool to increase constantly and change it to manual configuration. 4. Analyze the machine memory parameter configuration and find that the remaining memory is small, set memory retention to ensure that the system can still allocate memory when the system is under high pressure, instead of using the memory as cache. Instead, to get the address from the swap caused the system to no response caused by the error ORA-01017, analysis of OS Memory Allocation mainly look at the shared memory and the memory of each process, found that the memory consumption is mainly shared pool support is too large, reduced, the OS cache consumption is too high. After it is set to directio, the cache Usage is reduced, and this situation does not occur again. As performance said, you have to check what memory is consumed in the machine, and pin sga is not the most critical. If the memory is enough, the probability of sga being transferred to swap is very low and will return soon, so I think it is still necessary to analyze where the memory is used.

Swap severe

Replace with swap, page in increases-> hdisk0 busy 100%-> overall system load high-> fork server process failed-> ora-3136 Error
Replace with swap, page in increases-> hdisk0 busy 100%-> overall system load high-> library cache latch and shared pool latch

However, the reason why swap is high is not found in this case.