Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-11556

Document support for Oracle UCP connection pooling

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.5.0
    • Fix Version/s: 6.5.0
    • Component/s: documentation
    • Labels:
    • Target Version/s:
    • Story Points:
      2
    • Sprint:
      OpenIDM Sprint 6.5-8

      Description

      Based on OPENIDM-8272. .

      To fully take advantage of Oracle's "fast connection failover (FCF)" and "application continuity" features, we need to use Oracle UCP connection pool (Universal Connection Pool).

      Unknown at this time – will we support it beyond Oracle DB (I hope not).

      From the PR —

      This PR adds the ability to generically configure datasource's via a JSON config.

      Below are some instructions that show setup for Oracle's UCP to Oracle RAC, plus some other alternatives.

      Some of these instructions assume you have Oracle RAC setup by following these instructions.
      https://oracle-base.com/articles/12c/oracle-db-12cr1-rac-installation-on-oracle-linux-6-using-virtualbox#Top

      Preparing for utilizing Oracle's Universal Connection Pool (UCP).

      From your downloaded Oracle Client software you should be able to find the `ucp.jar`, `ons.jar`, and `ojdbc8_g.jar` files.

      These jars need to be converted to OSGI bundles. Use the BND tool with the following config files.

      ucp.bnd
      ```
      version=12.2.0
      Export-Package: oracle.ucp.*;version=${version}
      Bundle-Name: Oracle Universal Connection Pool
      Bundle-SymbolicName: oracle.ucp
      Bundle-Version: ${version}
      Import-Package: *;resolution:=optional
      DynamicImport-Package: *
      ```

      ons.bnd
      ```
      version=12.2.0
      Export-Package: *;version=${version}
      Bundle-Name: Oracle ONS
      Bundle-SymbolicName: oracle.ons
      Bundle-Version: ${version}
      Import-Package: *;resolution:=optional
      ```

      ojdbc8_g.bnd
      ```
      version=12.2.0
      Export-Package: *;version=${version}
      Bundle-Name: Oracle JDBC
      Bundle-SymbolicName: oracle.jdbc
      Bundle-Version: ${version}
      Import-Package: *;resolution:=optional
      ```

      Run the bnd tool for each jar:
      java -jar bnd-2.4.0.jar wrap --properties ucp.bnd --output ucp-osgi.jar ucp.jar
      java -jar bnd-2.4.0.jar wrap --properties ons.bnd --output ons-osgi.jar ons.jar
      java -jar bnd-2.4.0.jar wrap --properties ojdbc8_g.bnd --output ojdbc8_g-osgi.jar ojdbc8_g.jar

      Copy the generated jars to the bundle diretory of IDM.

      Copy the oracle repo config file to the conf directory of IDM.
      `cp db/oracle/conf/repo.jdbc.json conf/`
      Remove the DS repo config file.
      `rm conf/repo.ds.json`

      Copy the ucp-oracle datasource config file into the conf directory of IDM.
      `cp db/oracle/conf/datasource.jdbc-ucp-oracle.json conf/datasource.jdbc-default.json`

      Modify the datasource file to match your configuration.
      ```
      {
      "databaseName" : "openidm",
      "jsonDataSource" : {
      "class" : "oracle.ucp.jdbc.PoolDataSourceImpl",
      "settings" : {
      "connectionFactoryClassName" : "oracle.jdbc.pool.OracleDataSource",
      "url" : "jdbc:oracle:thin:@//&

      {openidm.repo.host}:&{openidm.repo.port}/cdbrac",
      "user" : "openidm",
      "password" : "openidm",
      "connectionTimeout" : "30000",
      "minPoolSize" : 20,
      "maxPoolSize" : 50
      }
      }
      }
      ```

      `databaseName` is the default schema for the openidm database. It is used to substitute ${_dbSchema} within the generated SQL.
      `jsonDataSource` will hold the config of the datasource's connection pool.
      `class` should be the full classname to your connection pool. For UCP that should be `oracle.ucp.jdbc.PoolDataSourceImpl`.
      `settings` should hold the attributes settings you wish to apply to your datasource. The setter method for each field will be invoked to configure the DataSource instance. Reference your Connection Pool api to know what setter methods are available. For example for UCP, `https://docs.oracle.com/cd/E16338_01/java.112/e12826/oracle/ucp/jdbc/PoolDataSourceImpl.html`

      Here is a sample datasource config that would utilize Oracle's UCP with the MysqlDB:
      ```
      {
      "databaseName" : "openidm",
      "jsonDataSource" : {
      "class" : "oracle.ucp.jdbc.PoolDataSourceImpl",
      "settings" : {
      "connectionFactoryClassName" : "com.mysql.jdbc.jdbc2.optional.MysqlDataSource",
      "url" : "jdbc:mysql://&{openidm.repo.host}

      :&

      {openidm.repo.port}/openidm?useSSL=false&allowMultiQueries=true&characterEncoding=utf8",
      "user" : "openidm",
      "password" : "openidm",
      "connectionTimeout" : "30000",
      "minPoolSize" : 20,
      "maxPoolSize" : 50
      }
      }
      }
      ```

      The JSON Datasource config file is generic. Any connection pool could be configured for any DB.
      Here is a sample for Hikari to a MySql DB. In this you only need to add the mysql drivers to the bundle directory.
      ```
      {
      "databaseName" : "openidm",
      "jsonDataSource" : {
      "class" : "com.zaxxer.hikari.HikariDataSource",
      "settings" : {
      "driverClassName" : "com.mysql.jdbc.Driver",
      "jdbcUrl" : "jdbc:mysql://&{openidm.repo.host}:&{openidm.repo.port}

      /openidm?useSSL=false&allowMultiQueries=true&characterEncoding=utf8",
      "username" : "openidm",
      "password" : "openidm",
      "connectionTimeout" : "30000",
      "minimumIdle" : 20,
      "maximumPoolSize" : 50
      }
      }
      }
      ```

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Mike2 Mike Jang
                Reporter:
                Mike2 Mike Jang
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: