In the mire: cleaning open provisioning tasks in Oracle Identity Manager
By user12587121 on Nov 13, 2009
Looking at Oracle Identity Manager (OIM) I found I was accumulating open provisioning tasks that had either failed or that I simply no longer wanted. The problem is the product does not offer a way to delete them. Furthermore some resources (like the Sun DS Connector) will not allow you to start a new provisioning task for that resource until the old one has completed. The obvious scheduled task 'Remove Open Tasks', as described here appears to be more a cosmetic cleanup of some views into open tasks. In fact looking into the task code, it does a time based 'DELETE from' on the OTI table--this turns out not to resolve the open provisioning task problem. So, short of reverting to a clean snapshot of the database, what to do ? I should say that the following is more in the way of an exploration that a recommendation.
The folks on the OTN forum offered some help but that was inconclusive. Time to roll up the sleeves.
There are 225 tables in the OIM schema. Which ones are involved when a provisioning task is created ? What we need is a way to diff the database before and after an operation. This will give an idea of the tables involved, the data model OIM is using and may allow us to remove those tasks--however recommended that may be.
For the purpose of reverse engineering table usage for individual application actions (such as in my case initiating a provisioning task) what we want is something that will indicate table data that has changed and do it's best to present those changes in a text form. The delta will typically be small so we do not require a fancy diff capability. It would be nice if the tool could work against Oracle, MS SQL Server and MySQL. 'Ah feel a wee tool comin' oooon.
This JDBC based tool, DbDump, does it's best to dump a specified set of tables to a text file. You will need to configure the jdbc properties and copy in the database driver jar files. We can then run a diff tool to compare before and after outputs. The command line diff tool is adequate though windiff.exe or FileMerge.app for example are easier to read.
1451c1451 < 'ADMINSERVER_WLSTORE'::'ID:-1','TYPE:-1','HANDLE:2''RECORD:000000707B7365727665723D41646D696E53657276657221686F73743D3132372E302E302E3121646F6D61696E3D626173655F646F6D61696E2173746F72653D41646D696E5365727665725F4F494D5F4A44424353544F5245217461626C653D41646D696E5365727665725F574C53746F72657D980976C5055875C800000124D3BC92EF' --- > 'ADMINSERVER_WLSTORE'::'ID:-1','TYPE:-1','HANDLE:2''RECORD:000000707B7365727665723D41646D696E53657276657221686F73743D3132372E302E302E3121646F6D61696E3D626173655F646F6D61696E2173746F72653D41646D696E5365727665725F4F494D5F4A44424353544F5245217461626C653D41646D696E5365727665725F574C53746F72657D980976C5055875C800000124D3C43CE8' 20792a20793 > 'AUD_JMS'::'AUD_JMS_KEY:187','AUD_CLASS:UserProfileAuditor','IDENTIFIER:86','JMS_VALUE:BLOB','DELAY:1','FAILED:0','CREATE_DATE:2009-11-8 184.108.40.2069000000','UPDATE_DATE:2009-11-8 220.127.116.119000000''PARENT_AUD_JMS_KEY:null' 33785a33787 > 'OBI'::'OBI_KEY:145','OBJ_KEY:11','REQ_KEY:null','ORC_KEY:null','OBI_STATUS:Approved','OBI_DEP_REQUIRED:null','OBI_STAGE_FLAG:2','QUE_KEY:null','USR_KEY:null','OBI_DATA_LEVEL:null','OBI_CREATE:2009-11-8 18.104.22.168','OBI_CREATEBY:1','OBI_UPDATE:2009-11-8 22.214.171.124','OBI_UPDATEBY:1','OBI_NOTE:null''OBI_ROWVER:0000000000000000' 33863a33866 > 'OIU'::'OIU_KEY:84','OBI_KEY:145','ORC_KEY:170','USR_KEY:86','OST_KEY:85','POL_KEY:null','REQ_KEY:null','OIU_PWD_MUST_CHANGE:null','OIU_PWD_FLAGGED:null','OIU_POLICY_BASED:null','OIU_POLICY_REVOKE:null','OIU_SERVICEACCOUNT:0','OIU_DATA_LEVEL:null','OIU_CREATE:2009-11-8 126.96.36.199','OIU_CREATEBY:1','OIU_UPDATE:2009-11-8 188.8.131.52','OIU_UPDATEBY:1','OIU_NOTE:null','OIU_ROWVER:0000000000000002','OIU_LAST_ATTESTED_BY:null','OIU_LAST_ATTESTED_ON:null''OIU_OFFLINED_DATE:null' 33968a33972 > 'ORC'::'ORC_KEY:170','TOS_KEY:32','ORD_KEY:1','PKG_KEY:34','ORC_SUPPCODE:00 ','ACT_KEY:1','REQ_KEY:null','PKH_KEY:null','USR_KEY:86','ORC_ASSIGNED_TO:1','ORC_STATUS:P','ORC_TOS_INSTANCE_KEY:170','ORC_PACKAGE_INSTANCE_KEY:170','ORC_SUBTOSKEY:null','ORC_REFERENCEKEY:null','ORC_DEPENDS:null','ORC_LAST_UPDATE:2009-11-8 184.108.40.206','ORC_LAST_UPDATEBY:1','ORC_SUBORDER:null','ORC_SERVICEORDER:null','ORC_PARENT_KEY:158','ORC_REQUIRED_COMPLETE:0','ORC_ORDERBY_POLICY:null','ORC_TARGET:0','ORC_TASKS_ARCHIVED:null','ORC_DATA_LEVEL:null','ORC_CREATE:2009-11-8 220.127.116.11','ORC_CREATEBY:1','ORC_UPDATEBY:1','ORC_UPDATE:2009-11-8 18.104.22.168','ORC_NOTE:null''ORC_ROWVER:0000000000000002' 34291a34296 > 'OSH'::'OSH_KEY:257','SCH_KEY:241','STA_KEY:4','OSH_ACTION:Engine','OSH_ASSIGN_TYPE:Default task assignment','OSH_ASSIGNED_TO_USR_KEY:1','OSH_ASSIGNED_TO_UGP_KEY:null','OSH_ASSIGNED_BY_USR_KEY:null','OSH_ASSIGN_DATE:2009-11-8 22.214.171.124','OSH_DATA_LEVEL:null','OSH_CREATE:2009-11-8 126.96.36.199','OSH_CREATEBY:1','OSH_UPDATE:2009-11-8 188.8.131.52','OSH_UPDATEBY:1','OSH_NOTE:null''OSH_ROWVER:0000000000000000' 34522a34528 > 'OSI'::'SCH_KEY:241','ORC_KEY:170','MIL_KEY:146','REQ_KEY:null','TLG_KEY:null','RSC_KEY:null','OSI_RECOVERY_FOR:null','OSI_RETRY_FOR:null','OSI_ASSIGNED_TO:null','TOS_KEY:32','PKG_KEY:34','ACT_KEY:1','ORD_KEY:1','ORC_SUPPCODE:00 ','OSI_ASSIGN_TYPE:Default task assignment','OSI_ESCALATE_ON:null','OSI_ASSIGNED_TO_USR_KEY:1','OSI_ASSIGNED_TO_UGP_KEY:null','OSI_RETRY_ON:null','OSI_RETRY_COUNTER:null','OSI_CHILD_TABLE_KEY:null','OSI_CHILD_OLD_VALUE:ecFFyIei7ntqs5tETSu38w==','OSI_ASSIGNED_DATE:2009-11-8 184.108.40.206','SCH_INT_KEY:null','OSI_LOG_KEY:null','OSI_DATA_LEVEL:null','OSI_CREATE:2009-11-8 220.127.116.11','OSI_CREATEBY:1','OSI_UPDATE:2009-11-8 18.104.22.168','OSI_UPDATEBY:1','OSI_NOTE:null''OSI_ROWVER:0000000000000000' 34648a34655 > 'OTI'::'OTI_KEY:190','SCH_KEY:241','SCH_TYPE:null','SCH_STATUS:P','SCH_DATA:null','SCH_PROJ_START:2009-11-8 22.214.171.124','SCH_PROJ_END:2009-11-8 126.96.36.199','SCH_ACTUAL_START:2009-11-8 188.8.131.52','SCH_ACTUAL_END:null','SCH_ACTION:null','SCH_OFFLINED:0','ORC_KEY:170','MIL_KEY:146','OSI_RETRY_FOR:null','OSI_ASSIGNED_TO:null','PKG_KEY:34','REQ_KEY:null','OSI_ASSIGNED_TO_USR_KEY:1','OSI_ASSIGNED_TO_UGP_KEY:null','OSI_ASSIGNED_DATE:2009-11-8 184.108.40.206','ACT_KEY:1','OSI_ASSIGN_TYPE:Default task assignment','PKG_TYPE:Provisioning','STA_BUCKET:Pending','OBJ_KEY:11','OTI_CREATE:2009-11-8 220.127.116.11','OTI_UPDATE:2009-11-8 18.104.22.168','OTI_CREATEBY:1','OTI_UPDATEBY:1','OTI_ROWVER:0000000000000000','OTI_DATA_LEVEL:0''OTI_NOTE:null' 38599a38607 > 'SCH'::'SCH_KEY:241','SCH_TYPE:null','SCH_STATUS:P','SCH_PROJ_START:2009-11-8 22.214.171.124','SCH_PROJ_END:2009-11-8 126.96.36.199','SCH_ACTUAL_START:2009-11-8 188.8.131.52','SCH_ACTUAL_END:null','SCH_DATA:null','SCH_REASON:null','SCH_ACTION:null','SCH_DATA_LEVEL:null','SCH_CREATE:2009-11-8 184.108.40.206','SCH_CREATEBY:1','SCH_UPDATEBY:1','SCH_UPDATE:2009-11-8 220.127.116.11','SCH_NOTE:null','SCH_ROWVER:0000000000000000''SCH_OFFLINED:null'
So the tables involved are: OSI, OSH, SCH, OIU, OTI, ORC and OBI.
Using the ORC_KEY and SCH_KEY the relevant rows can be deleted from the above tables in the order listed. An order is implied as there are contraints on the tables.
select sch_key from OIMUSER.OTI where orc_key=XXX;
So that is be one way to remove the tasks brutally--whether supported or not.
Using the Client API to complete the tasks
Rather than deleting rows one can try to at least complete the tasks manually, making a call to the tcProvisioningOperationsIntf interface (see here for code examples):
tcProvisioningOperationsIntf provIntf = (tcProvisioningOperationsIntf) getUtilityFactory().getUtility("Thor.API.Operations.tcProvisioningOperationsIntf"); provIntf.setTasksCompletedManually(tasksArray);
The tasksArray contains a list of task instance ids. What are these? The Description field of the open provisioning task in the admin interface contains a number. It turns out that this number is the ORC key. However, in order to call into the Oracle API, as suggested on the OTN forum, we need the task instance id. This turns out to be the SCH_KEY can be recovered using the query shown above.
To use the API to complete the tasks appropriately actually requires two steps. The first is to make sure that when the task is completed that the appropriate status mapping takes place. Do this in the Design Console by going to the Process Definition of the task, for example 'Create User' for your resource. Then to the 'TaskToObjectStatusMapping' tab. When a task is completed from the API, the completion code goes to 'MC', for 'Manually Complete'. So we need to map 'MC' to 'Provisioned' in order to get the task to complete appropriately. If the task was stuck in the 'System Validation' phase then mapping 'MC' to for example 'Revoked' will cause the provisioning event to be completed, allowing us to launch further provisioning tasks without any issues.
Of course these manually completed tasks are still visible in the admin interface. Perhaps the best way to clean them out would be to run the Task Archival maintenance scripts that come with the product as described here.
OIM does not appear to offer any easy way to clean up unwanted open provisioning tasks. In fact it does not appear to offer any documented way at all to clean them up. The best one can do in terms of what the product supports is to complete them and possibly then hide these tasks from view or archive them off. In dev environments and possibly POCs going straight to the tables may be best, but without an official spec of the data model this is always risky.