The error message “503 Service Unavailable” from ORDS (Oracle REST Data Services) indicates that ORDS is unable to establish a connection to the database due to connection pool exhaustion. The key part of the error is:
“All connections in the Universal Connection Pool are in use”
This suggests that all available database connections in the ORDS connection pool are currently occupied. Here’s how you can troubleshoot and fix the issue:
1. Check for Active Sessions in the Database
Since the error suggests that no more connections are available, check the number of active database sessions.
Run this SQL query as a privileged user (e.g., SYS or DBA):
SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';
If the number is close to the maximum allowed, you may need to increase the database session limit.
You can check the current limits with:
SHOW PARAMETER sessions;
SHOW PARAMETER processes;
If needed, increase the limits (adjust the value as required):
ALTER SYSTEM SET SESSIONS = 300 SCOPE=BOTH;
ALTER SYSTEM SET PROCESSES = 300 SCOPE=BOTH;
Restart the database for the changes to take effect.
2. Restart ORDS
Below command is to start ORDS.
Logon to server dp0026alli.genworth.net
cd /u01/oracle/ords
nohup java -jar ords.war standalone &
If ORDS has locked up due to exhausted connections, restart it:
ords stop
ords start
or if running as a service:
systemctl restart ords
3. Increase ORDS Connection Pool Size
Modify the connection pool size for ORDS in defaults.xml (or standalone.xml if running standalone).
The file is typically located in:
• /etc/ords/standalone/standalone.xml (for standalone ORDS)
• $ORDS_HOME/config/ords/defaults.xml (for WAR deployments)
Find and update these parameters:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Wed Feb 09 02:29:45 EST 2022</comment>
<!-- Database Connection Settings -->
<entry key="database.api.enabled">true</entry>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">dp00001234</entry>
<entry key="db.port">1521</entry>
<entry key="db.sid">mussaib</entry>
<!-- ORDS Feature Settings -->
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
<!-- Security Settings -->
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
<entry key="security.validationFunctionType">plsql</entry>
<!-- JDBC Settings -->
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InitialLimit">5</entry>
<entry key="jdbc.MinLimit">5</entry>
<entry key="jdbc.MaxLimit">50</entry> <!-- Increased from 50 to 500 -->
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.AbandonedConnectionTimeout">900</entry>
<entry key="jdbc.MaxConnectionReuseCount">50000</entry>
</properties>
Restart ORDS after making changes.
4. Check Long-Running Queries
If connections are stuck due to long-running queries, find them with:
SELECT SID, SERIAL#, STATUS, SQL_ID, MACHINE, PROGRAM
FROM v$session
WHERE STATUS = 'ACTIVE'
ORDER BY LAST_CALL_ET DESC;
Kill a problematic session (use with caution):
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
5. Check for ORDS Leaks or Misconfigurations
• Ensure that ORDS is properly closing connections when requests complete.
• Check ORDS logs (ords.log) for errors related to connection pooling.
6. Check Database Availability
Ensure the database is actually running:
sqlplus / as sysdba
Then, verify that the database is open:
SELECT status FROM v$instance;
If the database is down, start it with:
STARTUP;
Final Thoughts
• If this happens frequently, consider increasing the connection pool size and optimizing database session handling.
• If the issue is due to excessive traffic, consider load balancing ORDS across multiple instances.