This was a very interesting discovery for me. Customer complained to me that he is unable to connect to a certain user due to below error,

Status : Failure -Test failed: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory

Then I used v$resource_limit view and was able to find out processes and sessions parameters current utilization has come closer to its maximum values. When I took a look at the database alert log I was able to find a lot of TNS timeout errors as below,

Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 19-MAY-2022 15:29:10 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-12535: TNS:operation timed out 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.99.91)(PORT=56424)) WARNING: inbound connection timed out (ORA-3136) Thu May 19 15:29:20 2022 WARNING: inbound connection timed out (ORA-3136)

Below is the behavior shown in the OEM ASH analytics,




After that I decided to increase processes and sessions up to 300 but the issue was not able to be resolved. Then I started asking some questions from the customer and was able to find out he had changed the password of that certain user without changing it at the database level. I asked him immediately to shut the application down on the server whose IP address is showing in the alert log as TNS errors. As soon as I shut the app down on that server Issue got resolved. After researching some time I was able to find out this is the default behavior of a 11g database if a lot of connections come with a wrong password related to a certain user.