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?

• topshot: If there is nothing showing in the alert log or trace files then I'd start leaning toward an OS or networking issue. As a last resport before heading that direction you could turn on more verbose tracing in various spots. Though perhaps a deadlock is causing this??? stackoverflow.com/questions/1102359/… can get you started
• Kris Johnston: Could be a database level trigger: SELECT * FROM dba_triggers WHERE rtrim(base_object_type) = 'DATABASE' AND owner NOT IN ('SYS', 'XDB', 'MDSYS', 'SVCO', 'SYSMAN')and status = 'ENABLED'; Pay special attention to any triggers that occur on database login.

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;