Need Clarification on configuration of Oracle UCP

Question

Requirement : Create a multi-tenant Application which should insert each tenants data into their respective PDBs based on the tenant id in the request. In other words each tenant or customer will have there own PDB in a CDB, all PDBs will have the same schema and based on then tenant Id in the request a datasource will be selected and the data would be inserted into that PDB.

Stack - spring boot 2.3.0.RELEASE, Oracle 18c, Connection pool - Oracle shared universal connection pool

UCP connection :

<ucp-properties>
    <connection-pool
        connection-pool-name="pool1"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=orcl.accounts.intern)))"
        user="C##commonuser"
        password="commonuser"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        connection-repurpose-threshold="13"
        sql-for-validate-connection="select 1 from dual"
        shared="true"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
 
        <data-source data-source-name="pdbcust1" service="pdbcust1.accounts.intern" user="cust1" password="password"/>
        <data-source data-source-name="pdbcust2" service="pdbcust2.accounts.intern" user="cust2" password="password"/>
    </connection-pool>
</ucp-properties>

Spring datasource config class :

@Bean
    public DataSource dataSource() throws SQLException {
        System.setProperty("oracle.ucp.jdbc.xmlConfigFile", "file:/" + dbConfigProperties.getUcpConfigFile());
        
        final AbstractRoutingDataSource dataSource = new MultitenantRoutingDataSource();

        targetDataSources = new ConcurrentHashMap<>();

        final PoolDataSource tenantDataSource1 = getTenantDataSource("pdbcust1", "cust1", "password");
        final PoolDataSource tenantDataSource2 = getTenantDataSource("pdbcust2", "cust2", "password");

        
        targetDataSources.put("pdbcust1", tenantDataSource1 );
        targetDataSources.put("pdbcust2", tenantDataSource2 );

        dataSource.setDefaultTargetDataSource(lTenantDataSource2);
    

        lDataSource.setTargetDataSources(lTargetDataSources);

        lDataSource.afterPropertiesSet();

        return lDataSource;
    }

    private static PoolDataSource getTenantDataSource(final String tenantId, String username, String password) {
        try {
            PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(tenantId);
            
            Properties prop = new Properties();
//          prop.setProperty("user", username);
//          prop.setProperty("password", password);
            
            //pds.reconfigureDataSource(prop);
            
            return pds;

        } catch (final Exception e) {
            e.printStackTrace();
        }
        
        return null;
    }

Above configuration does not work and throws the following error when I fire a request with a tenant id :

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8-19.3.0.0.jar:19.3.0.

However if I uncomment the following lines in the above class and remove the username and password from the UCP file, it works:

prop.setProperty("user", username);
prop.setProperty("password", password);
pds.reconfigureDataSource(prop);

So my questions are :

Why does this happen?
The UCP config xmls xsd has a user and password field, how do we use it?
This page describes share pooling https://docs.oracle.com/middleware/12213/wls/JDBCA/shared_pooling_ds.htm#JDBCA-GUID-4B7DA858-327E-4CEA-A68C-376792D4A466 This has line : "This common user must exist in all PDBs that are connected to the sharing data sources" What does it mean?

Saurabh Verma Saurabh Verma · Accepted Answer · 2020-06-29T09:01:11

In order to use ucp shared pool feature, the database user must be same for all the datasources sharing a common pool of connection. So you should not use user and password under datasource element.

<data-source data-source-name="pdbcust1" service="pdbcust1.accounts.intern" />
<data-source data-source-name="pdbcust2" service="pdbcust2.accounts.intern"/>

If you have a requirement to use a different user for each pdb then shared pool is not an option. In that case you should define two different pools in the XML, one for each PDB, that means you should not set the shared=true in connection-pool element. Also, for non shared pools there is no need of using a common user at pool level, you can directly use pdb user, password and service name under pool element.

<ucp-properties>
    <connection-pool
        connection-pool-name="pool1"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=cust1.accounts.intern)))"
        user="cust1"
        password="cust1password"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        sql-for-validate-connection="select 1 from dual"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
        <data-source data-source-name="pdbcust1" />
    </connection-pool>
    
    <connection-pool
        connection-pool-name="pool2"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdbcust2.accounts.intern)))"
        user="cust2"
        password="cust2password"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        sql-for-validate-connection="select 1 from dual"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
        
        <data-source data-source-name="pdbcust2" />
    </connection-pool>
</ucp-properties>

Need Clarification on configuration of Oracle UCP

1 Answers