Thursday, March 23, 2017

SLA Hot Patch for After Upgrade Data Migration

a. Run this SQL script:
select * from gl_period_statuses where migration_status_code='P'
-- this script should return zero rows, only then should you consider running the SLA Hot Patch.

b. Take a backup of the xla_upgrade_dates table before running the SLA Hot Patch

c. Set a realistic date for the SLA: Initial Date for Historical Upgrade profile.

    (i)  Change the SLA: Initial Date for Historical Upgrade profile to 01-OCT-2015
    (ii) Then run the Hot Patch

adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115          patches=driver:xla5584908.drv hotpatch=yes options=forceapply

    (iii) Repeat the above steps for 6-month increments until all the data from start has been upgraded. 

    The 6-month increment is not a rule because it depends of the amount of history to be upgraded.


Friday, March 17, 2017

Important Queries to check Oracle EBS users logged

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

select * from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

select last_connect, usr.user_name, resp.responsibility_key, function_type, icx.*
  from apps.icx_sessions icx
  join apps.fnd_user usr on usr.user_id=icx.user_id
  left join apps.fnd_responsibility resp on resp.responsibility_id=icx.responsibility_id
  where last_connect>sysdate-nvl(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),30)/60/24
and disabled_flag != 'Y' and pseudo_flag = 'N'
AND USER_NAME <>'GUEST'

select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

select * from v$license;