IAM IDM: November 2014

Sunday, November 30, 2014

OIM Reports:OIM Users Without Managers

Query for Users Without Managers

select distinct
usr1.usr_login,
usr1.usr_manager AS "Manager Name",
usr1.USR_CREATED
from usr usr1,usr usr2
where
usr1.usr_manager_key is null
union
select distinct
usr1.usr_login,
usr2.usr_first_name || ' '|| usr2.usr_middle_name|| ' '|| usr2.usr_last_name AS "Manager Name",
usr1.USR_CREATED
from usr usr1,usr usr2
where
usr1.usr_manager_key = usr2.usr_key
and usr1.usr_status not in ('Disabled', 'Deleted')
AND ((usr2.usr_status in ('Disabled', 'Deleted')))

Thanks!!!

OIM Report:Proxy Users

OIM Query to find Proxy Users:

SELECT DISTINCT usr.usr_login AS "User_Login",
usr.usr_first_name AS "First_Name",
usr.usr_last_name AS "Last_Name" ,
pu.usr_first_name AS "Proxy_First_Name",
pu.usr_last_name AS "Proxy_Last_Name",
pu.usr_status AS "Proxy_Status",
p.pxd_start_date AS "Proxy_Start_Date",
p.pxd_end_date AS "Proxy_End_Date",
pa.usr_first_name AS "Proxy_Admin_First_Name",
pa.usr_last_name AS "Proxy_Admin_Last_Name"
FROM pxd p,
usr,
usr pu,
usr pa
WHERE p.pxd_orig_usr_key = usr.usr_key
AND p.pxd_proxy_key = pu.usr_key
and p.pxd_createby = pa.usr_key
and (nvl(:usr_login,null) is null or upper(usr.usr_login) like upper('%' || :usr_login || '%'))
and (nvl(:usr_first_name,null) is null or upper(usr.usr_first_name) like upper('%' || :usr_first_name || '%'))
and (NVL(:usr_last_name,null) is null or UPPER(usr.usr_last_name) like UPPER('%' || :usr_last_name || '%'))

Thanks !!!

OIM Reports:Open Provisioning Tasks

Query for Open Provisioning Tasks:

Query to see the Open Tasks under Administration Tab on the Left pane in the OIM.

select distinct
oti.sch_key as TaskKey,
mil.mil_name as TaskName,
oti.sta_bucket as TaskStatus,
oti.request_key,
obj.obj_name,
app_instance.app_instance_display_name as AppInstance,usr.usr_login as Beneficiary,
assigned_to_ugp.ugp_name AS assigned_to,
oti.osi_assign_type,
oti.osi_assigned_date
from OTI,obj,mil,usr,orc,app_instance,oiu,oug,ugp assigned_to_ugp
\where oti.obj_key=obj.obj_key and mil.mil_key=oti.mil_key and orc.orc_key=oti.orc_key and orc.usr_key=usr.usr_key and app_instance.app_instance_key=oiu.app_instance_key and app_instance.object_key = oug.obj_key
and obj.obj_key=app_instance.object_key and app_instance_is_soft_delete='0' AND assigned_to_ugp.ugp_key = oti.osi_assigned_to_ugp_key
and oti.osi_assign_type = 'Group'
AND oti.sta_bucket IN ('Pending', 'Rejected')
and oti.osi_assign_type =:AssignType
and oti.sta_bucket =:TaskStatus
union
select distinct oti.sch_key as TaskKey,mil.mil_name as TaskName,oti.sta_bucket as TaskStatus,oti.request_key,obj.obj_name,app_instance.app_instance_display_name as AppInstance,usr.usr_login as Beneficiary,assigned_to_usr.usr_login AS assigned_to,
oti.osi_assign_type,oti.osi_assigned_date from OTI,obj,mil,usr ,orc,app_instance,oiu,oug,usr assigned_to_usr
where oti.obj_key=obj.obj_key and mil.mil_key=oti.mil_key and orc.orc_key=oti.orc_key and orc.usr_key=usr.usr_key and app_instance.app_instance_key=oiu.app_instance_key and app_instance.object_key = oug.obj_key
and obj.obj_key=app_instance.object_key and app_instance_is_soft_delete='0' AND assigned_to_usr.usr_key = oti.osi_assigned_to_usr_key
and oti.osi_assign_type = 'User'
AND oti.sta_bucket IN ('Pending', 'Rejected')
--and oti.osi_assign_type =:AssignType
--and oti.sta_bucket =:TaskStatus
and (NVL(:AssignType,null) is null or UPPER(oti.osi_assign_type) like UPPER(:AssignType))
and (NVL(:TaskStatus,null) is null or UPPER(oti.sta_bucket) like UPPER(:TaskStatus))

Thanks !!!

OIM Reports:Closed Provisioning Tasks

Query for Closed Provisioning Tasks:

select distinct(SELECT usr_login FROM usr WHERE usr_key = oiu.usr_key) AS "target_user",
obj.obj_name AS "resource_name",
mil.mil_name AS "task_name",
sta.sta_bucket AS "task_status",
usr.usr_login AS "assigned_to_user",
sch.sch_actual_start AS "task_created_date",
sch.sch_actual_end AS "task_completed_date"
From OSI,MIL,ORC,SCH,STA,USR,OSH,OIU,OBI,OBJ,PKG
Where osh.sta_key = sta.sta_key
AND sch.sch_key = osh.sch_key
AND sch.sch_key = osi.sch_key
AND osi.mil_key = mil.mil_key
AND sch.sch_key = osh.sch_key
AND sch.sch_key = osi.sch_key
AND osi.mil_key = mil.mil_key
AND osi.orc_key = orc.orc_key
AND orc.orc_key = oiu.orc_key
AND obi.obi_key = oiu.obi_key
AND obj.obj_key = obi.obj_key
AND sta.sta_bucket = 'Completed'
AND mil.mil_name != 'System Validation'
AND osh.osh_assigned_to_usr_key = usr.usr_key
AND osh.osh_assigned_to_usr_key IS NOT NULL
AND osh.osh_assigned_to_usr_key != '1'
AND pkg.pkg_type = 'Provisioning'
AND usr.usr_login not in ('OIMINTERNAL','XELSYSADM')
AND sch.sch_actual_end between :from_date and :todate

Thanks !!!

OIM Reports : Closed Provisioning Tasks Counts

Query for Closed Provisioning Tasks Counts:

select assigned_to_usr.usr_login as "Completed By",
assigned_to_usr.usr_first_name || ' ' || assigned_to_usr.usr_last_name as "Full Name",
count(assigned_to_usr.usr_login)
FROM osi,mil,orc,sch,sta,usr assigned_to_usr, usr target_usr,pkg,oiu,obi,obj
WHERE oiu.usr_key = target_usr.usr_key
AND osi.mil_key = mil.mil_key
AND osi.orc_key = orc.orc_key
AND sch.sch_key = osi.sch_key
and sta.sta_status = sch.sch_status
AND assigned_to_usr.usr_key = sch.sch_updateBY
AND pkg.pkg_key = osi.pkg_key
AND osi.orc_key = orc.orc_key
AND orc.orc_key = oiu.orc_key
AND obi.obi_key = oiu.obi_key
and obj.obj_key = obi.obj_key
and sta.sta_bucket ='Completed'
and pkg.pkg_type = 'Provisioning'
and assigned_to_usr.usr_login not in ('XELSYSADM','OIMINTERNAL')
and sch.sch_actual_start between :task_completed_date_from and :task_completed_date_to
group by assigned_to_usr.usr_login, assigned_to_usr.usr_first_name || ' ' || assigned_to_usr.usr_last_name

Thanks !!!

OIM Reports :Pending Approvals Tasks for Groups

Query for Pending Approvals Tasks for Groups:

SELECT "WFASSIGNEE"."ASSIGNEETYPE" AS "ASSIGNEETYPE",
"WFASSIGNEE"."ASSIGNEE" AS "ASSIGNEE",
"WFPRODUCTIVITY_VIEW"."TASKNAME" AS "TASKNAME",
"WFPRODUCTIVITY_VIEW"."TASKNUMBER" AS "TASKNUMBER",
"WFPRODUCTIVITY_VIEW"."STATE" AS "STATE",
"WFTASK"."ASSIGNEDDATE" AS "ASSIGNEDDATE"
FROM "PROD_SOAINFRA"."WFPRODUCTIVITY_VIEW" "WFPRODUCTIVITY_VIEW",
"PROD_SOAINFRA"."WFTASK" "WFTASK",
"PROD_SOAINFRA"."WFASSIGNEE" "WFASSIGNEE"
WHERE "WFTASK"."TASKNUMBER" ="WFPRODUCTIVITY_VIEW"."TASKNUMBER"
AND "WFASSIGNEE"."TASKID" ="WFTASK"."TASKID"
AND "WFTASK"."STATE" ="WFPRODUCTIVITY_VIEW"."STATE"

Thanks!!!

OIM Reports:Pending Approvals Tasks for Users

Query for Pending Approvals Tasks for Users :

SELECT "WFASSIGNEE"."ASSIGNEETYPE" AS "ASSIGNEETYPE",

"WFASSIGNEE"."ASSIGNEE" AS "ASSIGNEE",

"WFPRODUCTIVITY_VIEW"."TASKNAME" AS "TASKNAME",

"WFPRODUCTIVITY_VIEW"."TASKNUMBER" AS "TASKNUMBER",

"WFPRODUCTIVITY_VIEW"."STATE" AS "STATE",

"WFTASK"."ASSIGNEDDATE" AS "ASSIGNEDDATE"

FROM "PROD_SOAINFRA"."WFPRODUCTIVITY_VIEW" "WFPRODUCTIVITY_VIEW",

"PROD_SOAINFRA"."WFTASK" "WFTASK",

"PROD_SOAINFRA"."WFASSIGNEE" "WFASSIGNEE"

WHERE "WFTASK"."TASKNUMBER" ="WFPRODUCTIVITY_VIEW"."TASKNUMBER"

AND "WFASSIGNEE"."TASKID" ="WFTASK"."TASKID"

AND "WFTASK"."STATE" ="WFPRODUCTIVITY_VIEW"."STATE"

Thanks!!!

Tuesday, November 25, 2014

apr_proc_mutex_lock failed. Attempting to shutdown process gracefully

Error:
#####

[OHS] [INCIDENT_ERROR:10] [OHS-9999] [core.c] VirtualHost: main] (37)No locks available: apr_proc_mutex_lock failed. Attempting to shutdown process gracefully.

This is what yo will see in the OHS log and http://ohshost:ohsport/ will give a 404.

This happens mostly when you install OHS instance on a shared drive (i.e., NFS). the lock file is the culprit.

OHS version: 11.1.1.7
OS: RHEL 6.6

Solution:
#######
Take a backup of httpd.conf
Locate module mpm_prefork_module and mpm_worker_module in httpd.conf. Observe that the LockFile is pointing to the path on shared drive.
This needs to be changed to a folder local to the server where you have your OHS instance running.

Modify it as below

<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0
AcceptMutex fcntl
LockFile " <LOCAL_DISK_PATH>"
</IfModule>

<IfModule mpm_worker_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
AcceptMutex fcntl
LockFile " <LOCAL DISK_PATH>"

Save the httpd.conf file and start OHS.

Thursday, November 13, 2014

Oracle HTTP Server 12.1.3 Installation & Configuration

OHS 12.1.3 have some minor changes in the installation process. It now has the flexibility to create any number of instances in one shot while configuring it.

Although this installation is straight forward, the installer will halt during configuration.

Download fmw_12.1.3.0.0_ohs_linux64.bin from Oracle website

OHS installation:
=============

[oracle@demo ~]$ cd /oracle/softwares/OHS_12.1.3

[oracle@demo OHS_12.1.3]$ chmod +x fmw_12.1.3.0.0_ohs_linux64.bin

[oracle@demo OHS_12.1.3]$ ./fmw_12.1.3.0.0_ohs_linux64.bin

0%....................................................................................................100%

Launcher log file is /oracle/tmp/OraInstall2014-11-13_07-28-32PM/launcher2014-11-13_07-28-32PM.log.

Starting Oracle Universal Installer

Checking if CPU speed is above 300 MHz. Actual 2691.389 MHz Passed

Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

Checking swap space: must be greater than 512 MB. Actual 10698748 MB Passed

Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)

Checking temp space: must be greater than 300 MB. Actual 15850 MB Passed

Preparing to launch the Oracle Universal Installer from /oracle/tmp/OraInstall2014-11-13_07-28-32PM

Log: /oracle/tmp/OraInstall2014-11-13_07-28-32PM/install2014-11-13_07-28-32PM.log

OHS Configuration:

===============

Navigate to /oracle/middleware_ohs_1213/oracle_common/common/bin

./config.sh

Installation will hang at 33% for sometime. approximately 2 to 4 minutes.

If you check the logs under /oracle/middleware_ohs_1213/logs you will notice the below

2014-11-13 19:39:59,373 INFO [1] com.oracle.cie.wizard.internal.wcf.WCFWrapper - Overriding task definition summary_gui

2014-11-13 19:40:02,810 INFO [12] com.oracle.cie.domain.DomainRegistryWrapper - need to initialize domainRegistrydocument object

2014-11-13 19:40:47,888 INFO [12] com.oracle.cie.domain.WLSTemplateBuilder - No config groups xml found in template

2014-11-13 19:40:51,759 INFO [12] com.oracle.cie.domain.AbstractTemplate - Adding component reference

2014-11-13 19:40:51,953 WARNING [12] com.oracle.cie.domain.AbstractTemplate - Could not find component name 'wlserver' version '0.0.0.0'

2014-11-13 19:40:52,081 INFO [12] com.oracle.cie.domain.WLSTemplateBuilder - No config groups xml found in template

2014-11-13 19:40:52,154 INFO [12] com.oracle.cie.domain.AbstractTemplate - Adding component reference

2014-11-13 19:40:52,163 WARNING [12] com.oracle.cie.domain.AbstractTemplate - Could not find component name 'ohs' version '0.0.0.0'

2014-11-13 19:40:52,211 WARNING [12] com.oracle.cie.domain.AbstractTemplate - Could not find component name 'ohs' version '0.0.0.0'

DON'T PANIC !!!!!

The installation will go forward

Logs after successful installation:

=========================