Oracle database not responding

Question

We have a Java application that uses a connection pool with Oracle database, during the week these conections are frequently refreshed (closed and opened again), the problem is at some point (after two or three days) the application is unable to acquire new connections, it get stuck on that line:

Runner004" prio=10 tid=0x00007f4b2c119800 nid=0x12b6 runnable [0x00007f4b00151000]
   java.lang.Thread.State: RUNNABLE
        at oracle.jdbc.driver.T2CConnection.t2cCreateState(Native Method)
        at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:530)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
        at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:54)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at TesteDB.getConnection(TesteDB.java:238)
        at TesteDB$Runner.run(TesteDB.java:285)

Version information:

• Oracle Instant Client 12.1.0.2.0 on Linux.
• The database version is 11.2.0 or 10.2.0.

Troubleshooting we did, when the database stopped to respond:

• Connect through SQLPLUS doesn't respond anything. It just waits forever.
• TNSPING doesn't respond anything.
• lsnrctl status doesn't respond anything.
• Nothing on alert.log
• We rebooted the listener, same problem.
• We rebooted the database service, it started to work.

We have absolutely no idea what we should look into, any suggestions?

Answer 1

I had a similar issue with an off the shelf three tier application where the app server would make a connection to the database when the user logged on. The user activity was typically to log on in the morning and leave the app open on the desktop all day even if they did nothing for long periods.

The app server was not configured to use a shared pool and would commonly hit the maximum number of sessions after two or three days. As you found the only resolution was to restart the database every night which is crazy unless the app owners say you can't change anything but you can restart it. You may wish to verify if you are using shared connections (pooling) and re examine your code to make sure connections are being dropped.

Here are some things you can check to see if this is a similar situation. This answer here is also helpful.

Log onto the database and run this from sqlplus with a privileged account --this gives the maximum amount of sessions

SELECT name, value 
  FROM v$parameter
 WHERE name = 'sessions'

--number of users currently connected even if they are not doing anything

SELECT COUNT(*)
  FROM v$session;

I created this procedure on the database and scheduled it for every five or ten minutes. Then you can examine the file and see if you are hitting the maximum number of sessions. To use this you must also create a directory PROCESS_LOG_DIR where the user running this has at least write permissions on.

CREATE OR REPLACE PROCEDURE COUNT_PROCESSES
IS
   f                    UTL_FILE.file_type;
   v_processes          NUMBER (10);
   v_current_logons     NUMBER (10);
   v_current_sessions   NUMBER (10);
   v_highwater          NUMBER (10);
BEGIN
   SELECT   COUNT ( * ) INTO v_processes FROM v$process;

   SELECT   VALUE
     INTO   v_current_logons
     FROM   v$sysstat
    WHERE   name = 'logons current';

   SELECT   sessions_current, sessions_highwater
     INTO   v_current_sessions, v_highwater
     FROM   v$license;

   f := UTL_FILE.fopen ('PROCESS_LOG_DIR', 'Process.txt', 'a');

   UTL_FILE.put_line (
      f,
         TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
      || ','
      || v_processes
      || ','
      || v_current_logons
      || ','
      || v_current_sessions
      || ','
      || v_highwater
   );
   UTL_FILE.fclose (f);
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (
         -20000,
         'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.'
      );
END;