OIM to OIA sync : Import Users Job failed : OIA_STAGING_ACCOUNTS & OIM_PKG_OIA_INTEGRATION
Oracle Identity Manager (OIM – earlier Thor Xellerate) is an User/Role/Entitlement Provisioning and Identity Administration software and part of Oracle Identity Management Suite.
Oracle Identity Analytics (OIA – earlier Sun Role Manager) is Role Management and Certification/Attestation software and is part of Oracle Identity Management Suite.
- OIA 11gR1 is integrated to OIM using steps mentioned here and here .
- Global Users in OIA (There are two type of users in OIA, Global Users and OIA users , more on users in OIA here ) are synced from OIM by running scheduled job ‘Import Users, Accounts, User Role Memberships and Entitlements’ in OIA.
- To configure schedule job to import users from OIM to OIA : Administration -> Configuration -> Import/Export -> Schedule Job
- To view Import/Export logs from execution of Job in OIA : Administration -> Auditing & Events -> Import Export Logs -> Click on Description column -> click on Show Exception
You should also check errors in following locations
- Diagnostic and out Logs of OIA Managed Server (WebLogic/Application Server on which OIA is deployed)
- Diagnostic and out Logs of OIM Managed Server (WebLogic/Application Server on which OIM is deployed)
- OIA RBACX.log (Log Location defined in OIA war file $WAR_FILE/WEB-INF/log4j.properties by parameter log4j.appender.file.file)
In my case error message in rbacx.log was
_____
18:01:06,677 ERROR [DBIAMSolution] ERROR: Data Staging Failed: DATA COLLECTION FAILED. Aborting.
18:01:06,692 ERROR [DBIAMSolution] Data Staging Failed: DATA COLLECTION FAILED. Aborting.
______
Information in the OIA logs was not enough so I looked at OIM log files (oim_server1.out)
_____
<11-Mar-2013 18:00:06 o’clock GMT> <Error> <XELLERATE.SCHEDULER.TASK> <BEA-000000> <SQLException occured while performing data collection
java.sql.SQLException: ORA-12899: value too large for column “DEV_OIM”.”OIA_STAGING_ACCOUNTS”.”ACCESS_POLICY_NAME” (actual: 33, maximum: 30)
ORA-06512: at “DEV_OIM.OIM_PKG_OIA_INTEGRATION”, line 799
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
________
Issue: With error message , it was clear that column size in column ACCESS_POLICY_NAME of table OIA_STAGING_ACCOUNTS is of size 30 where as value it was trying to insert as 34.
Fix: Increase column size
SQL> alter table dev_oim.OIA_STAGING_ACCOUNTS modify (ACCESS_POLICY_NAME varchar2 (40 CHAR));
I scheduled the job and then again encountered error
______
<11-Mar-2013 20:46:05 o’clock GMT> <Error> <XELLERATE.SCHEDULER.TASK> <BEA-000000> <SQLException occured while performing data collection
java.sql.SQLException: ORA-12899: value too large for column “IAM_OIM”.”OIA_STAGING_ACCOUNT_ATTRIBUTES”.”ACCESS_POLICY_NAME” (actual: 33, maximum: 30)
ORA-06512: at “IAM_OIM.OIM_PKG_OIA_INTEGRATION”, line 1356
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
_______
This time error was for table OIA_STAGING_ACCOUNT_ATTRIBUTES
SQL> alter table dev_oim.OIA_STAGING_ACCOUNT_ATTRIBUTES modify (ACCESS_POLICY_NAME varchar2 (40 CHAR));
After incresing column width I then re-scheduled job and then it failed again but this time error message reported was in OIA log i.e.
___
20:56:12,163 ERROR [ConcurrentAccountImporterHelper] SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column “RBACXSERVICE”.”ACCOUNTS”.”ACCESS_POLICY_NAME” (actual: 33, maximum: 30)
; nested exception is java.sql.BatchUpdateException: ORA-12899: value too large for column “RBACXSERVICE”.”ACCOUNTS”.”ACCESS_POLICY_NAME” (actual: 33, maximum: 30)
______
SQL > alter table rbacxservice.ACCOUNTS modify (ACCESS_POLICY_NAME varchar2 (40 CHAR));
After changing column width for column ACCESS_POLICY_NAME in ACCOUNTS, I was able to sync users from OIM to OIA.
- More on Policies in OIA here