This discussion is archived
0 Replies Latest reply: Dec 3, 2012 7:27 AM by GiedriusDeveikis RSS

Listener EA2: database connection pool and connection revalidation

GiedriusDeveikis Newbie
Currently Being Moderated
Hi all,
As one can expect from early adopter release there could be some bugs but I can't find any references in forum to my situation:
* My 11g XE database and listener are starting as windows services when server boots operating system (Windows Server 2003 R2).
* I configured my web server (unsupported Jetty 9.0.0.M1) to start as windows service when operating system starts.
* Apex Listener 2.0.0.268.17.05 configured to connect with XE using JDBC thin driver with default settings (initial pool size 3, max statements 10, min connections 1, max connections 10, inactivity timeout 1800, abandoned connection timeout 900)
* Because web server starts a bit faster than Oracle database when apex connects first time it gets "ORA-12528, TNS:listener: all appropriate instances are blocking new connections" (could be that database still starting but already registered service with listener)
* From listener.log file I can see that all further connections made from Apex listener succeeds
* When I try to open any apex page with browser I am getting 404 error and apex listener logs error (*time is 2 days after system startup*):
2012-11-30 3:56:02 PM oracle.dbtools.common.config.db.DatabaseConfig badConfiguration
SEVERE: The pool named: apex is not correctly configured, error: Listener refused the connection with the following error:
-----
ORA-12528, TNS:listener: all appropriate instances are blocking new connections

ConnectionPoolException [error=BAD_CONFIGURATION]
     at oracle.dbtools.common.jdbc.ConnectionPoolException.badConfiguration(ConnectionPoolException.java:62)
     at oracle.dbtools.common.config.db.DatabaseConfig.badConfiguration(DatabaseConfig.java:146)
     at oracle.dbtools.common.config.db.DatabaseConfig.createPool(DatabaseConfig.java:168)
     at oracle.dbtools.common.config.db.DatabaseConfig.getConnection(DatabaseConfig.java:68)
     at oracle.dbtools.common.jdbc.ora.OraPrincipal.connection(OraPrincipal.java:25)
     at oracle.dbtools.apex.ModApexContext.getConnection(ModApexContext.java:320)
     at oracle.dbtools.apex.Procedure.getProcedure(Procedure.java:166)
     at oracle.dbtools.apex.OWA.validateProcedure(OWA.java:384)
     at oracle.dbtools.apex.security.Security.isValidRequest(Security.java:171)
     at oracle.dbtools.apex.ModApex.validateRequest(ModApex.java:233)
     at oracle.dbtools.apex.ModApex.doGet(ModApex.java:79)
     at oracle.dbtools.apex.ModApex.service(ModApex.java:263)
     at oracle.dbtools.rt.web.HttpEndpointBase.modApex(HttpEndpointBase.java:288)
     at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:127)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
     at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:665)
     <... Jetty web server stack ...>
     at java.lang.Thread.run(Unknown Source)

2012-11-30 3:56:02 PM oracle.dbtools.rt.web.HttpEndpointBase modApex
-----
* Oracle listener log for same time (no errors here):
-----
30-NOV-2012 15:56:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))(SERVICE_NAME=xe)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1078)) * establish * xe * 0
30-NOV-2012 15:56:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))(SERVICE_NAME=xe)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1079)) * establish * xe * 0
30-NOV-2012 15:56:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))(SERVICE_NAME=xe)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1080)) * establish * xe * 0
30-NOV-2012 15:56:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))(SERVICE_NAME=xe)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1081)) * establish * xe * 0
-----
* For some reason apex listener keeps first connection status and won't try to establish new connection if first attempt finished with ORA-12528
* The same scenario is valid when at time of web server start oracle database/listener is not available - even if database and listener starts and apex successfully establishes further connections all apex pages gets 404 error
* If I restart web server windows service (while oracle db and listener still running) browser opens apex pages without errors and apex listener does not log any errors
* I know that I can avoid this error delaying start of web server windows service but it would be nice to have production release 2.x without such bugs