Oracle DB Repository

Before you set up Oracle DB as the IDM repository, confer with your Oracle DBA to create the database schema, tables, and users. This section assumes that you have configured an Oracle DB with Local Naming Parameters (tnsnames.ora) and a service user for IDM.

Important

IDM supports two connection pools for an Oracle DB:

Hikari Connection Pool (HikariCP), described in the HikariCP GitHub Repository
Oracle Universal Connection Pool (Oracle UCP), described in the Universal Connection Pool for JDBC Developer's Guide

Many steps in this procedure will depend on your connection pool type.

To Set Up Oracle as an IDM Repository

As the appropriate schema owner, import the IDM schema using the data definition language script (/path/to/openidm/db/oracle/scripts/openidm.sql).
Use the Oracle SQL Developer Data Modeler to run the script that creates the tables required by the workflow engine:
```
/path/to/openidm/db/oracle/scripts/flowable.oracle.all.create.sql
```
If you are planning to direct audit logs to this repository, run the script that sets up audit tables.
Use the Oracle SQL Developer Data Modeler to run the following script:
```
/path/to/openidm/db/oracle/scripts/audit.sql
```
Set the host and port of the Oracle DB instance, either in the resolver/boot.properties file or through the OPENIDM_OPTS environment variable.
Set in an IDM Properties File
If you use the resolver/boot.properties file, set values for the following variables:
- openidm.repo.host = localhost
- openidm.repo.port = 1521
Set as an Environment Variable
If you use the OPENIDM_OPTS environment variable, include the JVM memory options when you set the repo host and port. For example:
```
export OPENIDM_OPTS="-Xmx1024m -Xms1024m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=1521"
```
Remove the default DS repository configuration file (repo.ds.json) from your project's conf/ directory. For example:
```
rm /path/to/openidm/my-project/conf/repo.ds.json
```
Copy the Oracle DB repository configuration file (repo.jdbc.json) to your project's configuration directory:
```
cp /path/to/openidm/db/oracle/conf/repo.jdbc.json my-project/conf/
```
For Oracle UCP Only
Edit the repo.jdbc.json file as follows:
```
{
    "dbType" : "ORACLE",
    "useDataSource" : "ucp-oracle",
    ...
}      
```
Copy the connection configuration file to your project's configuration directory and edit the file for your Oracle DB deployment. The connection configuration file depends on the connection pool that you use:
For Hikari CP
1. Copy the following file:
```
cp /path/to/openidm/db/oracle/conf/datasource.jdbc-default.json my-project/conf/
```
  Edit the file to reflect your deployment. The default configuration for a HikariCP connection pool is as follows:
```
{
    "driverClass" : "oracle.jdbc.OracleDriver",
    "jdbcUrl" : "jdbc:oracle:thin:@//&{openidm.repo.host}:&{openidm.repo.port}/DEFAULTCATALOG",
    "databaseName" : "openidm",
    "username" : "openidm",
    "password" : "openidm",
    "connectionTimeout" : 30000,
    "connectionPool" : {
        "type" : "hikari",
        "minimumIdle" : 20,
        "maximumPoolSize" : 50
    }
}
```
  The jdbcUrl corresponds to the URL of the Oracle DB listener, including the service name, based on your configured Local Naming Parameters tnsnames.ora. Set this parameter according to your database environment.
  The DEFAULTCATALOG refers to the SID (system identifier), for example, orcl.
  The username and password correspond to the credentials of the service user that connects from IDM.
For Oracle UCP
1. Copy the following file:
```
cp /path/to/openidm/db/oracle/conf/datasource.jdbc-ucp-oracle.json my-project/conf/
```
  Edit the file to reflect your deployment. The default connection configuration for an Oracle UCP connection pool is as follows:
```
{
    "databaseName" : "openidm",
    "jsonDataSource" : {
        "class" : "oracle.ucp.jdbc.PoolDataSourceImpl",
        "settings" : {
            "connectionFactoryClassName" : "oracle.jdbc.pool.OracleDataSource",
            "url" : "jdbc:oracle:thin:@&{openidm.repo.host}:&{openidm.repo.port}:SID",
            "user" : "openidm",
            "password" : "openidm",
            "connectionTimeout" : "30000",
            "minPoolSize" : 20,
            "maxPoolSize" : 50
        }
    }
}
```
  The url corresponds to the URL of the Oracle DB listener, including the service ID (SID), based on your configured Local Naming Parameters tnsnames.ora. Set this property to the appropriate value for your environment, for example: jdbc:oracle:thin:@localhost:1521:orcl.
  The user and password correspond to the credentials of the service user that connects from IDM.
Create an OSGi bundle for the Oracle DB driver, as follows:
1. Download the JDBC drivers for your Oracle DB version.
  The files that you download depend on your Oracle DB version, and on whether you are using HikariCP or Oracle UCP. Because the version numbers change with minor updates, you must search for the precise corresponding files on oracle.com:
  - Download the ojdbc*.jar file that corresponds to your Oracle DB version.
  - Download the most recent bnd JAR file from https://repo1.maven.org/maven2/biz/aQute/bnd/biz.aQute.bnd/. The bnd utility lets you create OSGi bundles for JDBC libraries that do not yet support OSGi.
    For Oracle UCP Only
    Download the following additional files:
    ucp.jar
    ons.jar
  Copy the downloaded files to the /path/to/openidm/db/oracle/scripts directory.
2. The /path/to/openidm/db/oracle/scripts directory includes an ojdbc8.bnd file that specifies the version information for your JDBC driver.
  Edit the driver version in that file if necessary. The default file is as follows:
```
version=12.2.0.1
Export-Package: *;version=${version}
Bundle-Name: Oracle Database 12.2.0.1 JDBC Driver
Bundle-SymbolicName: oracle.jdbc.OracleDriver
Bundle-Version: ${version}
Import-Package: *;resolution:=optional
```
  Note
  Do not include trailing zeros in the version number. For example, for Oracle 12.2.0.1.0, set the version string to version=12.2.0.1.
  Oracle DB 12cR2 (12.2.0.1) uses the drivers in ojdbc8.jar.
3. From the /path/to/openidm/db/oracle/scripts directory, run the following command to create the OSGi bundle, replacing the * with your Oracle DB driver version:
```
java -jar biz.aQute.bnd-version.jar wrap --properties ojdbc*.bnd --output ojdbc*-osgi.jar ojdbc*.jar
```
  For Oracle UCP Only
  1. Create bnd files for the ucp.jar and ons.jar files. The following examples assume version 12.2.0 Oracle JDBC drivers:
    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
    Save the bnd files in the /path/to/openidm/db/oracle/scripts directory, then run the following commands to create the corresponding OSGi bundles:
    cd /path/to/openidm/db/oracle/scripts java -jar biz.aQute.bnd-version.jar wrap --properties ucp.bnd --output ucp-osgi.jar ucp.jar java -jar biz.aQute.bnd-version.jar wrap --properties ons.bnd --output ons-osgi.jar ons.jar
    You can ignore any private references warnings that are logged when you build these bundles.
4. Move all the OSGi bundle files to the openidm/bundle directory.
When you have set up Oracle DB for use as the internal repository, make sure that the server starts without errors.

Oracle DB Repository

Important

Note