--- 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: 7.2 page_id: pingidm:install-guide:repository-oracledb canonical_url: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-oracledb.html llms_txt: https://docs.pingidentity.com/pingidm/llms.txt docs_for_agents: https://developer.pingidentity.com/build-with-ai/docs-for-agents.md superseded_by: https://docs.pingidentity.com/pingidm/8.1/install-guide/repository-oracledb.html 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. | ## 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="-Xmx1024m -Xms1024m -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 the connection pool that you use: > **Collapse: 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: > > ```json > { > "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. > **Collapse: 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: > > ```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}: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. 8. Create an OSGi bundle for the Oracle DB driver, as follows: * 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` Copy the downloaded files to the `/path/to/openidm/db/oracle/scripts` directory. * 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`. | * 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. * 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.