Home > Integration > Using Oracle DB as OpenIDM’s repository

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

;

About these ads
  1. Nan Wang
    31/07/2013 at 8:55 pm

    I think we also need to remove the original repo json file: “repo.orientdb.json”, correct? Otherwise, orientdb is still working.

    • lfolta
      01/08/2013 at 9:17 am

      Good point! You’re absolutely right. I’ve updated the article. Thank you for spotting this.

      • Nan Wang
        01/08/2013 at 5:47 pm

        No problem. This is a great post. Excellent job!

  2. Maria Purchell
    27/08/2013 at 12:41 am

    I tried your steps. When I tried to bring up openIdm in step4, the java console for openidm was displayed few seconds, then disappeared. No log file is generated. I want to know what went wrong, but there is no clue.

    Do you have to do anything else beside the steps you provided? I read cli.sh configureconnector oracle in this reference: http://blogs.forgerock.com/OpenIDM/2012/05/16/integrate-openidm-with-oracle-db/. I just need to integrate Oracle with OpenIdm, and run REST interface with OpenIdm from a java application. Do I have to run configureconnector step?

    I did run step 2 and step3.

    My sqljdbc4.bnd has the following content:

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

    repo.jdbc.json contains:

    “connection” : {
    “dbType” : “ORACLE”,
    “jndiName” : “”,
    “driverClass” : “oracle.jdbc.OracleDriver”,
    “jdbcUrl” : “jdbc:oracle:thin:@//ip-address:1521/vstart”,
    “username” : username-in-oracle,
    “password” : password-in-oracle,
    “maxBatchSize” : 100,
    “maxTxRetry” : 5
    },

    Any help will be greatly appreciated.

    • Maria Purchell
      27/08/2013 at 4:44 pm

      In the “userName” field I have the name for a user who can access oracle, but who is not a openidm user. Do I have to use openidmadmin as username for the connection in repo.jdbc.json .

      repo.jdbc.json contains:

      “connection” : {
      “dbType” : “ORACLE”,
      “jndiName” : “”,
      “driverClass” : “oracle.jdbc.OracleDriver”,
      “jdbcUrl” : “jdbc:oracle:thin:@//ip-address:1521/vstart”,
      “username” : username-in-oracle, /* oracle user, but not openIdm user Is this right ???*/
      “password” : password-in-oracle,
      “maxBatchSize” : 100,
      “maxTxRetry” : 5
      },

      • Nan Wang
        27/08/2013 at 5:11 pm

        the username in repo.jbcs.json is just the user for oracle DB, not for OpenIDM.

        Did you verify the connection by command “scr list” in OpenIDM terminal?

      • Maria Purchell
        27/08/2013 at 9:44 pm

        Openidm died without leaving any logging. I am not able to run any command.

      • lfolta
        28/08/2013 at 8:29 am

        Maria this is just a wild guess, but I think the osgi bundle you’ve generated is somehow wrong. Please increase the logging level to console in conf/logging.properties to java.util.logging.ConsoleHandler.level = FINE .This might have show you some additional information what has gone wrong.

        Regarding the user, it’s json file, so everything except numbers MUST be in quotes. And yes, Nan Wang is right, that user is used by openidm to connect to Oracle DB and has nothing to do with openidm users.

    • Nan Wang
      27/08/2013 at 5:20 pm

      I was using ojdbc6.jar at step two.

      For the username part, my format is “username” : “username-in-oracle”. I am not sure the double quote matters.

  3. Maria Purchell
    27/08/2013 at 9:42 pm

    I used the double quote for the user name.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: