List of resource provisioned to user in OIM

Query to list the resource that are in different status for given user.

Table Name	Table Description
OIU	Object Instance Request Target User Information. Associate user information to the resource object instance when provisioning take places.
OST	Object Status Information.
OBI	Object Instance Information. Once resource provisioned to user, OIM created resource instance for each resource provisioning.
OBJ	Resource Object definition information This contains detail about resource such as resource name, auto-save enable or not and auto-prepopulate is enable or not, and whether or not the resource object allows multiple instances.
USR	It contains user information like login id, password, etc.,

SQL Query:

select oiu.oiu_key, oiu.obi_key, oiu.orc_key, ost.ost_status, obj.obj_name, obj.obj_key,oiu.req_key 
from oiu 
inner join ost on oiu.ost_key = ost.ost_key 
inner join obi on oiu.obi_key = obi.obi_key
inner join obj on obi.obj_key = obj.obj_key 
where oiu.usr_key =(select usr_key from usr where usr_login='chellappan.sampath');

Set Environment Variables OIM 11G R2 PS2

Before we do deployments such as, register plugin, unregister plugin, upload jar, delete jar, etc., we need to set environment variable. I hope below list of environment variable will be easier for reference.

Note: These variables configured as per my local environment and you need make changes according to your environment.

export APP_SERVER=weblogic
export ANT_HOME=/opt/middleware/modules/org.apache.ant_1.7.1
export JAVA_HOME=/opt/java/jdk1.7.0_02
export PATH=/opt/java/jdk1.7.0_02/bin:/opt/middleware/modules/org.apache.ant_1.7.1/bin:$PATH
export MW_HOME=/opt/middleware
export XEL_HOME=/opt/middleware/Oracle_IDM1
export WL_HOME=/opt/middleware/wlserver_10.3
export OIM_ORACLE_HOME=/opt/middleware/Oracle_IDM1
export OIM_HOME=/opt/middleware/Oracle_IDM1/server
export DOMAIN_HOME=/opt/middleware/user_projects/domains/base_domain

OIM 11G Orchestration tables

Orchestration is vital component in OIM and we can say it is heart of OIM because, all operations, such as create user, modify user, delete, ldap sync, etc., were closely integrate with OIM Orchestration.

Known Issue: OIM Orchestration will retry failed event handlers ONLY 2 times and will ignore after that. Because, the retry limit was hard coded in OIM.

Table Name	Table Description
ORCHPROCESS	Stores the process instances that are being executed.
ORCHEVENTS	Stores event handler names, status and result for all orchestration processes. Event status like COMPLETED, FAILED, PENDING, etc.
ORCHFAILEDEVENTS	Stores event handler information that are executed because of failures in main flow.

SQL Query:

Below sql query is to get list of event handlers, which are executed for a particular users during enable process:

This query used to get user key from usr table.

select usr_key from USR 
where usr_login = ‘chellappan.sampath’;
-- 1024

This query get process instance of enabled user ‘chellappan.sampath’

select id from orchprocess 
where entityid=’1024’ and entitytype='User' and operation='ENABLE';
-- 561092

This query gets all the event handler for enabled user ‘chellappan.sampath’

select * from orchevents 
where processid=’561092’ order by orchorder;

Similarly you can do for user enable, create user, etc.,

Tuning database & application servers for OIM

Tuning Database

Since OIM has heavily depends on database, obviously we need to tune the database for better performance. Every time a user is created or provisioned resource to user, there are dozens of operations taking places behind the scenes especially tables of the functional databases.

Below table show recommended initial setting for 4 CPU (64 bit) and 8/20 GB RAM:

Parameters	Initial setting for DB
db_block_size	8192
memory_target	Minimum value is 6 GB. Maximum is MEMORY_TARGET/MEMORY_MAX_TARGET=Total Memory X 80% or 20GB, whichever is greate
sga_target	Minimum value is 4 GB. Maximum is SGA_TARGET=Total Memory X 80% X 60% or 16 GB assuming an overall memory cap of 20 GB for the Oracle Identity Manager database to run.
pga_aggregate_target	Minimum value is 2 GB. Maximum is PGA_TARGET=Total Memory X 80% X 40% or 4 GB whichever is greater
sga_max_size	10 GB
db_keep_cache_size	800M
log_buffer	15 MB
cursor_sharing	FORCE
open_cursors	2000
session_cached_cursors	800
query_rewrite_integrity	TRUSTED
db_file_multiblock_read_count	16
db_writer_processes	2
processes	Based on connection pool settings

While it is always recommended to check the Oracle Database Tuning reports within the database, Oracle provides recommendations of which can be found at this location: http://docs.oracle.com/cd/E14571_01/doc.1111/e14308/tuningfordb.htm#OMADM1811

Tuning Application Server

1. OIM uses oimOperationsDB and oimJMSStoreDS datasource deployed on WebLogic server. By default, maximum connection size is set to 50. You can increase size as per your environment. Note: Make sure the same connection size should be set to in DB side too.
2. Number of Message Driven Bean (MDB):OIM uses OIMMDBWorkManager MDB for processing all offline operations such as recon, audit, orchestration, etc. By default, it set to 80. You can set to -1 for unlimited.
3. Disable Reloading of Adapters and Plug-in Configuration
4. Change default memory arguments.

Periodically purging the Orchestration, Requests, Audit, Reconciliation tables in OIM.

OIM 11G R2 PS2 Installation Steps – High Level

Before you begin on OIM 11GR2 installation, I strongly recommend you to have look at the certification matrix. Because, developers may spend more time working with wrong components such as, OS version, JDK/JRE version etc. As a result, they could not complete installation.

OIM 11GR2 Certification Matrix is available in http://www.oracle.com/technetwork/middleware/id-mgmt/documentation/identity-access-111220certmatrix-2105036.xlsx

Note: Make sure you should have given full privileges to installation folder.

This post covers high-level steps based on the above certification matrix.

1. Install WebLogic 10.3.6 or later
2. Run the Repository Creation Utility.
3. Install SOA into the middleware home
4. Install the IAM components into the middleware home
5. Extend the WebLogic domain (If already exists) OR create a new domain for all the required IAM components by running the domain configuration.
6. Create the Policy Store
7. Configure SOA
8. Start WebLogic server
9. Configure OIM
10. Stop WebLogic Admin server
11. Start WebLogic Admin server, SOA, and followed by OIM

OIM SQL query to find who modified user attributes

We can identify when and who made change for user profile attributes for example, email address.

Below are tables stores users and audit information:

Table Name	Table Description
USR	It contains user information like login id, password, etc.,
UPA	User profile audit information

SQL Query:

Below query fetch the email address value for user ‘chellappan.sampath’ from audit table: 

select field_name, field_old_value, field_new_value 
from upa_fields fields 
where upa_usr_key in ( select upa_key from upa 
                       where upa_key in (select usr_key 
                                         from usr 
                                         where lower(usr_login) like 'chellappan.sampath')))
and field_name = 'Users.Email' 
order by upa_usr_key, field_name;

OIM SQL query to find who assigned role to users

OIM provides strong auditing features that will capture all user profile modification. It will be stored on UPA table.

Below are tables stores users and audit information:

Table Name	Table Description
USR	It contains user information like login id, password, etc.,
UPA	User profile audit information

SQL Query:

Below query gets list of roles when was assigned to user ‘chellappan.sampath’:

select * from upa 
where usr_key = (select usr_key from usr 
                 where lower(usr_login)= 'chellappan.sampath')
and src like '%RoleManager%CREATE%';

Similarly, we can check for user role revoked by using src with ‘%RoleManager%DELETE%'