Using Oracle DB as OpenIDM's repository

This article is dedicated to setting up OpenIDM with Oracle DB as repository. As of the OpenIDM 2.1 Xpress release, that has been used for the purpose of this blog, OpenIDM does not yet supports Oracle DB as an internal repository, but below in the article I’m providing procedure that could help reader to setup OpenIDM with Oracle DB for successful operation.

Prerequisites:

This article assumes that reader has knowledge of installing Oracle DB.

Used software

• OpenIDM 2.1 Xpress
• Oracle XE 11.2
• ojdbc6
• SQL Developer

Step one: Install Oracle DB and import OpenIDM database

Install whatever Oracle database suites you. For the needs of this article the lightest distribution is sufficient. I have used Oracle XE 11.2. Once you have installed the database there comes the time to import OpenIDM repository database. The database script is located under

openidm/db/scripts/oracle

. Use any database client tool (e.g.: Oracle SQL Developer) to connect to your Oracle database’s instance and execute the script. After the execution is recommended to check whether the OpenIDM databse has been created. For testing purposes an administrator account can be used but when coming to production I strongly recommend to create a user with privileges to access and modify OpenIDM database.

You can check the OpenIDM database by following command. It should return 2 records.

select * from internaluser;

When you will create a user to login to OpenIDM’s database, you have to grant privileges to tables:

AUDITACCESS, AUDITACTIVITY, AUDITRECON,
CONFIGOBJECTPROPERTIES, CONFIGOBJECTS,
GENERICOBJECTPROPERTIES, GENERICOBJECTS,
MANAGEDOBJECTPROPERTIES, MANAGEDOBJECTS,
INTERNALUSER, LINKS

Step two: Create Oracle JDBC driver as an OSGi bundle

The database is ready now. This step will describe how to turn Oracle’s JDBC driver into an OSGi bundle. First, take the jdbc delivered with your Oracle database. If there was not delivered any, you can download one from

here

. The next thing you will need is

biz.aQute.bnd.jar

from http://dl.dropbox.com/u/2590603/bnd/biz.aQute.bnd.jar. and create a bind file - you can alter the one located in

openidm/db/scripts/mssql/sqljdbc4.bnd

.

The bind file should be like following:

version=4.0
Export-Package: *;version=${version}
Bundle-Name: Oracle JDBC Driver 4.0 for SQL Server
Bundle-SymbolicName: oracle.jdbc.OracleDriver

The version of your jdbc driver can be found as

specification-version property

in MANIFEST file of the jdbc.

Put all three files - jdbc driver, biz.aQute.bnd.jar and the bind file to the same directory and run command:

java -jar biz.aQute.bnd.jar wrap -properties sqljdbc4.bnd ojdbc6_g.jar

As result of the command above you will get an .bar file. Rename it to whatever you want, but it must have .jar extension (E.g. ojdbc6_g-osgi.jar) and copy it to

openidm/bundle

Step three: Configure OpenIDM to use Oracle DB as repo

Copy a

openidm/samples/misc/repo.jdbc-oracle.json

to

openidm/conf,

rename it to

repo.jdbc.json

and delete the

repo.orientdb.json

Open the

repo.jdbc.json

and fill in proper

jdbcUrl, username and password

of an account which have rights to read and modify the OpenIDM database. Example of repo.jdbc.json:

{
"connection" : {
"dbType" : "ORACLE",
"jndiName" : "",
"driverClass" : "oracle.jdbc.OracleDriver",
"jdbcUrl" : "jdbc:oracle:thin:@//10.0.0.2:1521/XE",
"username" : "openidmAdmin",
"password" : "OpenIDMP@ssw0rd",
"maxBatchSize" : 100,
"maxTxRetry" : 5
}, ...

Step four: Use OpenIDM with Oracle DB as a repository

After performing all the steps above you can start OpenIDM and verify that

org.forgerock.openidm.repo.jdbc

has been activated by using felix web console running on http://localhost:8080/system/console/components

NOTE: If you’re seeing the error below, delete tables related to activity ACT_* and during next OpenIDM startup, the tables will be re-created. Be aware that you will lose all activity data stored in that databse!

Sep 2, 2013 3:55:52 PM org.activiti.engine.impl.db.DbSqlSession dbSchemaUpgrade
INFO: upgrading activiti engine schema from 5.11 to 5.12
Sep 2, 2013 3:55:52 PM org.activiti.engine.impl.db.DbSqlSession dbSchemaUpgrade
INFO: upgrading activiti history schema from 5.11 to 5.12
Sep 2, 2013 3:55:52 PM org.activiti.engine.impl.db.DbSqlSession executeSchemaResource
INFO: performing upgrade on history with resource org/activiti/db/upgrade/activiti.oracle.upgradestep.511.to.512.history.sql
Sep 2, 2013 3:55:52 PM org.forgerock.openidm.repo.jdbc.impl.pool.DatabaseShutdownHook onConnectionException
WARNING: Database down at 2013-09-02T15:55:52.789+02:00
Sep 2, 2013 3:55:52 PM com.jolbox.bonecp.MemorizeTransactionProxy invoke
SEVERE: Connection failed. Attempting to recover transaction on Thread #13
Sep 2, 2013 3:55:52 PM org.forgerock.openidm.repo.jdbc.impl.pool.DatabaseShutdownHook onAcquireFail
WARNING: Failure to acquire connection at 2013-09-02T15:55:52.858+02:00. Retry attempts remaining : 10
Sep 2, 2013 3:55:52 PM com.jolbox.bonecp.hooks.AbstractConnectionHook onAcquireFail
SEVERE: Failed to replay transaction Sleeping for 7000ms and trying again. Attempts left: 10. Exception: java.sql.SQLException: ORA-01430: column being added already exists in table

Sep 2, 2013 3:56:00 PM org.forgerock.openidm.repo.jdbc.impl.pool.DatabaseShutdownHook onAcquireFail
WARNING: Failure to acquire connection at 2013-09-02T15:56:00.027+02:00. Retry attempts remaining : 9

;