OIM: Troubleshooting Reconcilliation

1. Each instance of the recon schedule job execution process is associated with an unique identifier – the Job ID. This job ID is created when createReconciliationEvent() method is called from the recon code.

2. All the monitoring happens on the basis of job ID. Useful tables are:

• recon_jobs
• recon_batches
• recon_events
• ra_* staging tables
• orchevents
• orchfailedevents

3. Job ID can be found in two ways:

• By searching for the recon event created by the scheduled job 
• By querying the recon_jobs table

4. Tables involved in recon process:

• RECON_JOBS
• RECON_BATCHES
• RECON_EVENTS
• RA_* TABLES

5. Helpful SQLs:

--RECON_JOBS

select count(*) from recon_jobs

select * from recon_jobs order by rj_start_time DESC

select * from recon_jobs where rj_name = 'HRMS Trusted User Recon' order by rj_start_time DESC

select distinct(rj_job_status) from recon_jobs

--RECON_BATCHES

select * from recon_batches where rj_key = '386261'

select count(*) from recon_batches where rj_key = '386261'

--RECON_EVENTS

select * from recon_events where rj_key = '386261'

select re_key, rj_key, usr_key, RE_CHANGE_TYPE, RE_STATUS, RE_LINK_SOURCE from recon_events where rj_key = '386261'

select count(*) from recon_events where rj_key = '386261'

select DISTINCT(RE_CHANGE_TYPE) from recon_events  --DELETE, CHANGELOG, REGULAR

select DISTINCT(RE_STATUS) from recon_events

select DISTINCT(RE_LINK_SOURCE) from recon_events

6. Recon Batch Size System Property: OIM.ReconBatchSize

This property is used to specify the batch size for reconciliation. You can specify 0 as the value for this to indicate that the reconciliation will not be performed in batches.

7. Drilling into a single event

Tables used: recon_events, recon_batches, orchevents, orchfailedevents

• Get event id of the event from the UI
• Run the following SQLs:

select rb_key from recon_events where re_key = '2989631'

select rb_note from recon_batches where rb_key = '12962'

select * from orchevents where processid='572404' ORDER by orchorder  -- processid=obtained from rb_note column ALSO This would give the details of the all the Event handlers invoked including their execution order and their status

select * from orchfailedevents where processid='572404'