Getting Error : "[Oracle JDBC Driver]Object has been closed."

Report · Sep 22, 2016

I have been looking at this issue for several weeks off and on. Right now I can tell you 70 things it is not. And, I have tried many things. So, please forgive the long posting.

Description

On the Development, Test, Training, and Production instances I have an intermittent connection issue with accessing one table SITE. Everytime I try to access said table via a <cfquery> tag it throws an error.

struct

Cause

struct
ErrorCode	0
Message	[Macromedia][Oracle JDBC Driver]Object has been closed.
SQLState	HY000

Database Environment

The Oracle server is on a different shared server with LAN. It is running Oracle 10G.

CF Environment

The Development, Test, And Training instances are on one shared server. A Linux box: x86_64 x86_64 x86_64 GNU/Linux. I can not update the

Server Details
Server Product	ColdFusion
Version	10,0,21,300068
Edition	Enterprise
Operating System	UNIX
OS Version	3.0.76-0.11-default
Update Level	/local_apps/cf10/Redmond/lib/updates/chf10000021.jar
Adobe Driver Version	4.1 (Build 0001)

JVM Details

Java Version	1.6.0_29
Java Vendor	Sun Microsystems Inc.
Java Vendor URL	http://java.sun.com/

Note:

I cannot upgrade the JRE because of community authentication process will not work.

Data & Services

Database Data Sources
redmond
CF data source name	redmond
Description
Driver	Oracle
JDBC URL	jdbc:macromedia:oracle://10.10.0.118:1529;SID=redmond;AuthenticationMethod=userIDPasswor d;sendStringParametersAsUnicode=false;querytimeout=60;MaxPooledStatements=2000;SpyAttributes=(log=(f ile)/local_apps/cf10/redmond_dev/logs/db.log; linelimit=80;logTName=yes;timestamp=yes)
Username	redmond
Login timeout	30 seconds
Long text buffer size	64000
Timeout	600 seconds
Maintain connections	Yes
Interval	420 seconds
Restricted SQL operations
Disable connections	No

Things I have tried

I turned on Database logging to this JDBC connection.
I dropped the JDBC connection and created a new one.
I saw a posting about this issue on another site and changed MaxPooledStatements=2000. Because one person recommended it.
Increased the Query Timeout (seconds) to 60.
Decreased the Timeout (min) to 7
Enabled CLOB and BLOB just in case.
Enabled Oracle linked servers support.
I created a similar Database connection on a Windows 2008 R2 server running ColdFusion 10 Update 21 with only Java 1.8.0_101 (64 bit) running on it. That worked fine for 10 days. Then quit after 11 days. For one period I hit the SITE table every 6 second for 3 hours with no issues.
Thought reserved word with the table name SITE. So, I had the DBA create a new table called SITEX and I copied the data over to it, That worked until I restarted the ColdFusion instance. Then, SITEX started have the same connection issues as SITE .

Database Connection Error from CF Logging

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.083)>> Connection[39].getAutoCommit()

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.083)>> OK (true)

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.083)>> Connection[39].setAutoCommit(boolean autoCommit)

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.083)>> autoCommit = false

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.083)>> java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Object has been closed. ErrorCode=0 SQLState=HY000

java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Object has been closed.

at macromedia.jdbc.oraclebase.ddcr.b(Unknown Source)

at macromedia.jdbc.oraclebase.ddcr.a(Unknown Source)

at macromedia.jdbc.oraclebase.ddcq.b(Unknown Source)

at macromedia.jdbc.oraclebase.ddcq.a(Unknown Source)

at macromedia.jdbc.oraclebase.BaseConnection.setAutoCommit(Unknown Source)

at macromedia.jdbcspyoracle.SpyConnection.setAutoCommit(Unknown Source)

at coldfusion.server.j2ee.sql.JRunConnection.setAutoCommit(JRunConnection.java:436)

at coldfusion.server.j2ee.sql.JRunConnectionHandle.setAutoCommit(JRunConnectionHandle.java:108)

at coldfusion.tagext.sql.TransactionTag.verifyDataSrc(TransactionTag.java:331)

at coldfusion.tagext.sql.QueryTag.validate(QueryTag.java:501)

at coldfusion.tagext.sql.QueryTag.doValidate(QueryTag.java:632)

at coldfusion.tagext.sql.QueryTag.doAfterBody(QueryTag.java:623)

at cfbase2ecfc543454569$funcINVOKETAG.runFunction(/local_apps/cf10/redmond/CustomTags/com/adobe/coldfusion/base.cfc:445)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)

at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)

at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)

at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)

at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220)

at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2655)

at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2451)

at cfquery2ecfc242559435$funcEXECUTE.runFunction(/local_apps/cf10/redmond/CustomTags/com/adobe/coldfusion/query.cfc:641)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)

at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47)

at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)

at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)

at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)

at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220)

at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220)

at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:655)

at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:444)

at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:414)

at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2428)

at cfsites22ecfc1527081306$funcGETSITE.runFunction(/local_apps/redmond/dev/source/doc/actions/sites2.cfc:94)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)

at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)

at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)

at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)

at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:518)

at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:660)

at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:469)

at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2369)

at cfsites2dtest2ecfm1991642881._factor7(/local_apps/redmond/dev/source/doc/sites-test.cfm:42)

at cfsites2dtest2ecfm1991642881.runPage(/local_apps/redmond/dev/source/doc/sites-test.cfm:1)

at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244)

at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:446)

at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)

at coldfusion.filter.IpFilter.invoke(IpFilter.java:64)

at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:430)

at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)

at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)

at coldfusion.filter.PathFilter.invoke(PathFilter.java:112)

at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94)

at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:79)

at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)

at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)

at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:58)

at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)

at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)

at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)

at coldfusion.CfmServlet.service(CfmServlet.java:219)

at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)

at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:450)

at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:197)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:662)

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.084)>> Connection[39].close()

spy(ajp-bio-8015-exec-6)(2016/09/22 10:45:30.084)>> OK

Report · Oct 12, 2016

Seems the issue is related to the version of the JVM that is in use. The ColdFusion generic Oracle drive does not seem to be able to persistently connect to Oracle 10g with JVM 1.6

To fix this issue I found some documentation about how to replace/add a new JDBC driver.

I downloaded the ojdbc6.jar fromhttp://www.oracle.com/technetwork/database/enterprise-edition/jdbc-111060-084321.html. If you have Java Specification Version 1.7 you will need ojdbc7.jar

To apply ojdbc6.jar to the server I uploaded the file to the ColdFusion instance's /wwwroot/WEB-INF/lib/ folder.
Restart the ColdFusion instance to apply the ojdbc driver.
Once restarted I logged into the ColdFusion Administrator for this instance.
In the left menu column I opened Data & Services menu item
Select the Data Sources sub-menu item.
In the right window locate the sub-section Add New Data Sources
In the field labeled Data Source Name add you data source name
The use the pull-down for the field labeled Driver and select Other
Click the Add button for this sub-section.
On the new page in the form field JDBC URL add the following connection string:
- jdbc:oracle:thin:@{DATABASE SERVER URL OR IP ADDRESS}:1521:{DATABASE NAME}
For the field Driver Class write oracle.jdbc.OracleDriver
For the field Driver Name write Oracle Thin Driver
In the field labeled User name add the user name that you are going to connect to the database with.
In the field labeled Password add the password that you are going to connect to the database with.
If done click the button labeled Submit.

I do recommend clicking the Show Advanced Settings button to limit the permissions of what SQL items the connection is allowed to run even if you Database Administrator has done that for this connection's user account.

Adobe Community

Getting Error : "[Oracle JDBC Driver]Object has been closed."