Using Oracle DB as OpenIDM’s repository
lfolta
12 years ago
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
;
I think we also need to remove the original repo json file: “repo.orientdb.json”, correct? Otherwise, orientdb is still working.
Good point! You’re absolutely right. I’ve updated the article. Thank you for spotting this.
No problem. This is a great post. Excellent job!
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.
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
},
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?
Openidm died without leaving any logging. I am not able to run any command.
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.
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.
I used the double quote for the user name.