Using Oracle DB as OpenIDM’s repository

Posted 11 years ago by lfolta

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

;

lfolta

10 Responses to “Using Oracle DB as OpenIDM’s repository”

  1. Nan Wang says:

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

    • lfolta says:

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

  2. Maria Purchell says:

    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 says:

      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 says:

        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 says:

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

        • lfolta says:

          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 says:

      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 says:

    I used the double quote for the user name.

Leave a Reply

Related articles