Oracle UCP的配置需要澄清

Question

要求:创建一个多租户应用程序，它应该根据请求中的租户id将每个租户数据插入到各自的PDB中。换句话说，每个租户或客户都将在CDB中拥有自己的PDB，所有PDB都将具有相同的模式，然后根据请求中的租户Id选择数据源，并将数据插入到该PDB中。

堆栈- spring引导2.3.0. universal，Oracle 18c，连接池- Oracle共享通用连接池

UCP连接：

<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数据源配置类：

@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;
    }

上面的配置不起作用，当我用租户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.

但是，如果我取消了上述类中的以下行注释，并从UCP文件中删除用户名和密码，它就能工作：

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

所以我的问题是：

1. 为什么会发生这种情况？
2. UCP配置xmls xsd有一个用户和密码字段，我们如何使用它?本页面描述共享池https://docs.oracle.com/middleware/12213/wls/JDBCA/shared_pooling_ds.htm#JDBCA-GUID-4B7DA858-327E-4CEA-A68C-376792D4A466这有一行：“这个公共用户必须存在于所有连接到共享数据源的PDBs中”这是什么意思？

Answer 1

为了使用ucp共享池特性，数据库用户必须对共享公共连接池的所有数据源保持相同。因此，您不应该在数据源元素下使用用户和密码。

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

如果您需要为每个pdb使用不同的用户，那么共享池就不是一个选项。在这种情况下，您应该在XML中定义两个不同的池，每个PDB都定义一个池，这意味着不应该在连接池元素中设置shared=true。另外，对于非共享池，不需要在池级别使用公共用户，您可以直接使用池元素下的pdb用户、密码和服务名称。

<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>