Thursday, October 25, 2018

UCP Connections Fail to Connect to DB in Mount Mode With ORA-12504 TNS:listener was not given the SID in CONNECT_DATA

Trying to connect to a database (non-CDB, CDB or PDB) in mount mode as sysdba using a UCP JDBC connection fails with
Exception in thread "main" java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA
However there's no failure when OracleDataSource is used instead of UCP. Issue with UCP only appears in JDBC Driver versions 18.3.0.0.0 and 12.2.0.1.0.
No issue in connecting to database in mount mode with driver versions 12.1.0.2.0 and 11.2.0.4.0. This appears to be a bug on later version of JDBC drivers. After an SR this is been investigated under bug# 28780778.

Follow Java code could be used to recreate the issue. Change the ojdbc*.jar and ucp.jar as needed to try different drivers.
import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.StringTokenizer;
import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

/**
 *
 * @author Asanga
 */
public class ConnectToMount {

    public static void main(String[] args) throws SQLException {

        String username = "sys";
        String password = "xxxxx";
        boolean isSysdba = true;
        String URL = "jdbc:oracle:thin:@192.168.0.79:1521/pdb";

        System.out.println("Driver Info");
        Connection con = usingODS(username, password, URL, isSysdba);
        printDriverInfo(con);
        
        System.out.println("\nusing ODS");
        runQuery(con);

        System.out.println("\nusing UCP");
        runQuery(usingUCP(username, password, URL, isSysdba));

    }

    static void runQuery(Connection con) throws SQLException {

        PreparedStatement pr = con.prepareStatement("select db_unique_name from v$database");

        ResultSet rs = pr.executeQuery();
        while (rs.next()) {

            System.out.println(rs.getString(1));
        }

        rs.close();
        pr.close();
        con.close();
    }

    static Connection usingUCP(String username, String password, String URL, boolean isSysDBA) throws SQLException {

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setUser(username);
        ds.setPassword(password);
        ds.setURL(URL);

        if (isSysDBA) {
            Properties p = new Properties();
            p.put("internal_logon", "sysdba");

            ds.setConnectionProperties(p);
        }

        return ds.getConnection();

    }

    static Connection usingODS(String username, String password, String URL, boolean isSysDBA) throws SQLException {

        OracleDataSource ds = new OracleDataSource();
        ds.setUser(username);
        ds.setPassword(password);
        ds.setURL(URL);

        if (isSysDBA) {

            Properties p = new Properties();
            p.put("internal_logon", "sysdba");

            ds.setConnectionProperties(p);
        }

        return ds.getConnection();

    }

    static void printDriverInfo(Connection con) throws SQLException {

        DatabaseMetaData meta = con.getMetaData();

        System.out.println("Driver Name " + meta.getDriverName());
        System.out.println("Driver Version " + meta.getDriverVersion());
        System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
        System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
        System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
        System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());

        java.util.Properties props = System.getProperties();
        System.out.println("\nJVM\n===");
        System.out.println(props.getProperty("java.vm.vendor"));
        System.out.println(props.getProperty("java.vm.name"));
        System.out.println(props.getProperty("java.vm.version"));
        System.out.println(props.getProperty("java.version"));
    }
}