--- title: Oracle DB repository description: 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. component: pingidm version: 8.1 page_id: pingidm:install-guide:repository-oracledb canonical_url: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-oracledb.html keywords: ["Installation", "OracleDB"] section_ids: set_up_oracle_as_an_idm_repository: Set up Oracle as an IDM repository --- # 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) ](http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm)and a service user for IDM. | | | | - | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | IDM supports two connection pools for an Oracle DB:- Hikari Connection Pool (HikariCP), described in the [HikariCP GitHub Repository](https://github.com/brettwooldridge/HikariCP). - Oracle Universal Connection Pool (Oracle UCP), described in the [Universal Connection Pool for JDBC Developer's Guide](https://docs.oracle.com/cd/E11882_01/java.112/e12265/intro.htm).Many steps in this procedure will depend on your connection pool type.Oracle UCP does not support zero-downtime secret rotation. | ## Set up Oracle as an IDM repository 1. As the appropriate schema owner, import the IDM schema using the data definition language script (`/path/to/openidm/db/oracle/scripts/openidm.sql`). 2. Use the [Oracle SQL Developer Data Modeler](http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/datamodel1moddb/datamodel1moddb_otn.htm) to run the script that creates the tables required by the workflow engine: ``` /path/to/openidm/db/oracle/scripts/flowable.oracle.all.create.sql ``` 3. 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 ``` 4. Set the host and port of the Oracle DB instance, either in the `resolver/boot.properties` file or through the `OPENIDM_OPTS` environment variable. > **Collapse: 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` > **Collapse: 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="-Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=1521" > ``` 5. 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 ``` 6. 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/ ``` > **Collapse: For Oracle UCP Only** > > Edit the `repo.jdbc.json` file as follows: > > ```json > { > "dbType" : "ORACLE", > "useDataSource" : "ucp-oracle", > ... > } > ``` 7. []()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 your connection pool: * Hikari CP * For Oracle UCP 1. Copy the Oracle JDBC datasource file: ```none cp /path/to/openidm/db/oracle/conf/datasource.jdbc-default.json my-project/conf/ ``` 2. Edit the datasource file to reflect your deployment. The default configuration for a HikariCP connection pool is: ```json { "driverClass" : "oracle.jdbc.OracleDriver", "jdbcUrl" : "jdbc:oracle:thin:@//&{openidm.repo.host}:&{openidm.repo.port}/DEFAULTCATALOG", (1) (2) "databaseName" : "openidm", "username" : "openidm", (3) "password" : "openidm", (3) "connectionTimeout" : 30000, "connectionPool" : { "type" : "hikari", "minimumIdle" : 20, "maximumPoolSize" : 50 } } ``` | | | | ----- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **1** | 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. | | **2** | The `DEFAULTCATALOG` refers to the Oracle service name. For example, `orcl`. | | **3** | The `username` and `password` correspond to the credentials of the service user that connects from IDM.You can configure Hikari to [read credentials from a secret store using purposes](../security-guide/secret-stores.html#secret-rotation-hikari). | 1. Copy the Oracle UCP JDBC datasource file: ```none cp /path/to/openidm/db/oracle/conf/datasource.jdbc-ucp-oracle.json my-project/conf/ ``` 2. Edit the datasource file to reflect your deployment. The default connection configuration for an Oracle UCP connection pool is: ```json { "databaseName" : "openidm", "jsonDataSource" : { "class" : "oracle.ucp.jdbc.PoolDataSourceImpl", "settings" : { "connectionFactoryClassName" : "oracle.jdbc.pool.OracleDataSource", "url" : "jdbc:oracle:thin:@//&{openidm.repo.host}:&{openidm.repo.port}/DEFAULTCATALOG", (1) (2) "user" : "openidm", (3) "password" : "openidm", (3) "connectionWaitTimeout" : 30, (4) "minPoolSize" : 20, "maxPoolSize" : 50 } } } ``` | | | | ----- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **1** | The `url` corresponds to the URL of the Oracle DB listener, including the service name, 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`. | | **2** | The `DEFAULTCATALOG` refers to the Oracle service name. For example, `orcl`. | | **3** | The `user` and `password` correspond to the credentials of the service user that connects from IDM. | | **4** | The `connectionWaitTimeout` specifies the time (in seconds) that UCP waits for a connection to become available. This is a UCP-specific property. Don't use the HikariCP `connectionTimeout` property with UCP. | 8. 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 . The [bnd](http://bnd.bndtools.org/) utility lets you create OSGi bundles for JDBC libraries that do not yet support OSGi. > **Collapse: For Oracle UCP Only** > > 1. Download the following additional files: > > * `ucp.jar` > > * `ons.jar` 2. Copy the downloaded files to the `/path/to/openidm/db/oracle/scripts` directory. 3. 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 ``` | | | | - | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | * 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`. | 4. 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 ``` > **Collapse: 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. 5. Move all the OSGi bundle files to the `openidm/bundle` directory. 9. When you have set up Oracle DB for use as the internal repository, make sure that the server starts without errors.