Upgrade failed to Confluence 5.8.2 with oracle 11g, Datasource

Steps to replicate

1. Install confluence 5.7.4 Standalone, with Oracle, datasource
2. Shutdown Confluence
3. Unzip Confluence 5.8.2 standalone
4. Reconfigured/placed below files to 5.8.2
   • Reconfigured D:\atlassian-confluence-5.7.4\atlassian-confluence-5.8.2\conf\ server.xml
   • Reconfigured D:\atlassian-confluence-5.7.4\atlassian-confluence-5.8.2\confluence\WEB-INF\ web.xml
   • Placed D:\atlassian-confluence-5.7.4\atlassian-confluence-5.8.2\lib\ ojdbc6.jar
5. Change JDK to 1.8.45
6. Upgrade to Confluence 5.8.2, using standalone

this issue also affecting Confluence connected to Oracle 12c with ojdbc7

Expected Behavior

Upgrade successfully and confluence up and running

Actual Behavior

From atlassian-confluence.log

2015-06-11 18:25:00,910 ERROR [localhost-startStop-1] [atlassian.config.lifecycle.LifecycleManager] panicAndShutdown Unable to start up Confluence. Fatal error during startup sequence: confluence.lifecycle.core:mananagedjobs (Start and stop the Managed Scheduled Jobs) - org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection

Resolutions

Option 1: Add property to data source

1. First, restore your pre-upgrade backups. The upgrade has likely failed, and your data may be in an inconsistent state that could cause problems further down the track.
2. Add accessToUnderlyingConnectionAllowed="true" to the data source configuration in server.xml (this worked with ojdbc6.jar). Eg:

            <Resource
            name="jdbc/confluence"
            auth="Container"
            type="javax.sql.DataSource"
            driverClassName="oracle.jdbc.OracleDriver"
            url="jdbc:oracle:thin:@//hostname:port/sid"
            username="username"
            password="password"
            connectionProperties="SetBigStringTryClob=true"
            accessToUnderlyingConnectionAllowed="true"
            maxActive="25"
            maxIdle="5"
            maxWait="10000"
            />

3. Run the upgrade again

This fix works for the error above, and the error I described in the comments below.

If you're interested in the technical detail about this parameter and what it does:

• http://tomee.apache.org/datasource-config.html#accessToUnderlyingConnectionAllowed
• https://community.oracle.com/thread/281238
• https://cyntech.wordpress.com/2009/02/05/cant-get-that-underlying-connection/

We have confirmed that we are closing the connections in the correct way in the Confluence source, and all of our automated testing has passed, so we are confident this change is low risk.

Option 2: Use Direct JDBC instead of a Data Source

This may not be feasible in all cases, but you can configure Confluence to use a Direct JDBC resource rather than a data source.

1. First, restore your pre-upgrade backups. The upgrade has likely failed, and your data may be in an inconsistent state that could cause problems further down the track.
2. Set up Direct JDBC on the pre-upgrade instance
3. Run the upgrade again