Monitoring User Activity with “Sign-On: Audit Level” Profile Option in Oracle E-Business Suite R12

1.      Overview:

Users Activity in Oracle E-Business Suite can be monitored online (using a given form) or via reports available in the system administration responsibility.

The online monitoring of user activity within Oracle Applications is achieved via the Monitor Users form (Form Name: FNDSCMON.fmx). In order to use this form and also to use the reports the profile option Sign-On: Audit Levelmust be set to an appropriate value. The available options are: - 

• NONE: No monitoring performed on users’ activity.
• USER: Only show a list of logged in users.
• RESPONSIBILITY: Will show the users logged in and the responsibility they are using.
• FORM: show the most detailed level, it will show the User, Responsibility and Form being accessed.

The general overview of the process of monitoring user activity in Oracle Applications R12 is:

1. Enabling users tracking by setting the profile option “Sign-On: Audit level” to take the value for example “Form”.
2. Viewing Users online using “Monitor Users” form.
3. Viewing Monitoring Reports about users and their activity.
4. If not used disable tracking users by changing the “Sign-On: Audit level” to take the value for example “None”.

2.      Enable User Tracking – Setting the Sign-On: Audit Level profile option:

To enable user tracking by changing the value of “Sign-On: Audit Level” profile option use the following steps (we will use the value Form):

1. Log in to Oracle APPS with system administrator responsibility
2. Navigate to Profile > System
3. Make sure the Site option is checked.
4. Navigate to Profile and search for sign > Click Find
5. Select the “Sign-On: Audit Level” > Click Find
6. Change its value to be Form
7. From the Menu bar click File > Save

 

3.      Viewing Users Online Using the “Monitor Users” form:

To monitor users online we use the “Monitor Users” screen or form. To display current users and their information using Monitor Users form:

1. Log in with System Administrator Responsibility
2. Navigate to Security: Users > Monitor
3. Click (CTL + F11) keys to display the result.

The screen shot below indicates what you would see if you had chosen the FORM option for the profile option in question.

 

 

This is a very useful screen since it tells you exactly which users are logged in and what are they doing in the system at any point in time. One may check this screen before bouncing or restarting the system to make sure all users are logged out.

It is a good practice to set the Sign-On: Audit Level profile option to “Form” since it gives the most detailed information above other choices but it will impact the system performance since it collects a lot of information, so you have keep that in mind.

 

4.      Viewing Monitoring Reports about Users and their activity:

Depending on what audit level you have selected for the profile option under discussion you may also generate various reports as indicated below: –

• Sign-On Audit Concurrent Requests: View information about who is requesting what concurrent requests and from which responsibilities and forms.
• Sign-On Audit Forms:View who is navigating to what form and when they do it.
• Sign-On Audit Responsibilities: Used to view who is selecting what responsibility and when they are doing it.
• Sign-On Audit Users:Used to view who signs on and for how long.
• Sign-On Audit Unsuccessful: Show audit information about unsuccessful logins to Oracle Applications.

To view any of the given reports monitoring user activity use following steps:

1. Navigate to System Administrator Responsibility > Concurrent > Requests Or from the Menu Bar go to View > Requests
2. Choose Submit New Request > Single Request
3. Select report you want from the 4 requests given above.
4. Click Submit > Find
5. Select report you choose and click View output button to view the report.

5.      Notifying Users of Unsuccessful Logins to their accounts:

 

Sign-On Audit can track user logins and provide users with a warning message if anyone has made an unsuccessful attempt to sign on with their application username since their last sign-on. This warning message appears after a user signs on. You do not have to audit the user with Sign-On Audit to use this notification feature.

To inform users about unsuccessful logins to their account, you can set the “Sign-On: Notification” profile option to Yes. To do that from System Administrator Responsibility > Profile > System > Find the profile option “Sign-On: Notification” and change its value to Yes.

 

Queries to get the SESSION INFORMATION

Queries to get the SESSION INFORMATION

Checking  Timing details, Client PID of associated oracle SID

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


To Find Session Information Details based on SID or SPID or CLIENTPID

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));



Checking Timing details, Client PID of associated oracle SID

undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);

Checking Timing Details of SID and event waiting for

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,
v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);


Checking what is the Last SQL (input multiple sids)

undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);

All Active and Inactive connections

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and s.type != 'BACKGROUND';

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
s.status='ACTIVE' and
--(s.last_call_et/3600)<1 and="" br="">nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


Active sessions

select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s
where p.addr=s.paddr
order by substr(s.osuser,1,15);


Session details from Session long ops

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from
v$session_longops where sid=&SID and serial#=&SERIAL


To list the nodes

set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /*+ CHOOSE*/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB  ..............: '||SUPPORT_DB
from  apps.fnd_nodes;

Session details thru SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '11533')






Checking  Timing details, Client PID of associated oracle SID

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));


To list count of connections from other machines

select count(1),machine from gv$session where inst_id=2 group by machine;

To get total count of sessions and processes

select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress

select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue

select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs

select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

To find Undo Generated For a given session

select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sessionid';


***APPS 11i*****

To Find Forms User Session Details Given ClientProcess id

SELECT /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.process, 1, 8 ) f60webmx,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
and ft.language='US'
), 1, 40 ) form
FROM apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms rf,
gv$session vs
--fnd_form_tl ft
WHERE fl.end_time IS NULL
AND fl.start_time > sysdate - 31 /* login within last 7 days */
AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
AND vs.process='&1'
ORDER BY
user_name,
login_start_time,
spid,
pid,
f60webmx,
sid,
serial#;

Checking Timing Details of SID and event waiting for

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi')
LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from
v$session a,
v$session_wait b where a.sid=&sid and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and
s.sid='&sessionid';
SQL> SQL> Enter value for sessionid: 219
old   1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='&sessionid'
new   1: select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where
t.addr=s.taddr and s.sid='219';


Checking rollback/Undo segment info used by SID

column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr, nvl(s.username,'no transaction') us,s.sid, s.osuser os, s.terminal te, rs.rssize,
rs.xacts, rs.rssize/1048576 Rssize
FROM v$lock  l, v$session  s,v$rollname  r , v$rollstat rs
WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND
l.lmode = 6   AND r.usn=rs.usn  and s.sid in (&sid_list_comma_sep);

Checking what is the Last SQL

undefine sid
col "Last SQL" for a70
select s.username, s.sid, t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';

Killing inactive sessions for more than 48hrs

set heading off
set feedback off
spool /PENVI/applcsf/prevent/scripts/kill_session.sql
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
where last_call_et > 43200 and username is not null AND STATUS='INACTIVE';
spool off
exit

Session details complete (Input sid)

set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
SID number := 0 ;
inst_id number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id,a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and a.sid in (75)
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' || m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' || m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );

end loop;
end;

Session details complete (Input SPID)

set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
inst_id number := 0 ;
SID number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id, a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
event event,
state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and b.spid in ( '&spid')
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||' '||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' ||m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' ||m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from v$session s,v$transaction t where
s.taddr=t.addr(+) and
s.sid=m.sid )
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text sql_texts from v$session s,v$sqltext v where
s.sql_hash_value=v.hash_value and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );
end loop;end;

Count of JDBC thin client sessions grouped by status

col program for a15F
col machine for a15
col terminal for a15
set lines 152
select count(s.sid),s.status from gv$session s, gv$process p where p.addr=s.paddr and s.program || s.module like ('%JDBC Thin Client%') group by status;

JDBC Session count

select count(s.sid) from gv$session s where s.program || s.module like ('%JDBC Thin Client%');

Inactive sessions count

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 1800
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 43200
and username is not null
and module like '%JDBC Thin Client%'
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;


JDBC Sessions count

SELECT username,count(*) sess
FROM v$session
where username is not null
and module like '%JDBC Thin Client%'
group by username
order by sess DESC;

Machine wise count

select MACHINE, PROCESS,COUNT(*)
from V$SESSION
where program like '%JDBC%'
and username = 'APPS'
and process is not null
group by MACHINE, PROCESS
order by MACHINE ;

Inactive sessions count

SELECT count(*),module FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE' group by module;

SELECT username,status,count(*)sesion FROM v$session where username is not null and module
like '%JDBC Thin Client%' group by username ,status;

select count(status) Count, status, machine, program from v$session where program like '%JDBC%'
group by status, machine,program having status = 'INACTIVE' order by 1;

ACTIVE / INACTIVE Sessions

set linesize 132
set pagesize 100
col machine format a15
col OSuser format a12
col program format a30
SQL> select count(*) from v$session;

SQL> select count(*) from v$session where status='INACTIVE';

SQL> select count(*) from v$session where status='ACTIVE';

SQL> select machine, osuser, program, count(*) from v$session
group by machine, osuser, program order by 4 desc;

SQL> select count(status) Count, status, machine, program from v$session
where program like '%JDBC%' group by status, machine, program;

SQL> select count(status) Count, status, machine, module from v$session
where program = 'JDBC Thin Client' group by status, machine, module;

Logon time of JDBC

SQL> SELECT serial#, substr(program,1,20) program, status,
to_char(logon_time,'DD-MON-YY HH24:SS') Login_Time,
to_char(sysdate-last_call_et/86400,'DD-MON-YY HH24:SS') Last_Activity FROM
v$session
WHERE program like 'JDBC%' order by 4;

Session distribution

select to_char(sysdate,'DD/MM HH24:MI') "DATE",inst_id,count(inst_id) total_ses,sum(decode(status,'INACTIVE',1,0) ) inactive_ses from gv$session group by inst_id;

Program grouped by count of user connection

select unique s.program,s.osuser ,count(1) from v$session s, v$process p where s.username
is not null and s.paddr = p.addr and s.status='INACTIVE' group by s.program,s.osuser;

TOTAL Sessions/Inactive Sessions

select to_char(sysdate,'DD/MM HH24:MI') "DATE", inst_id, count(inst_id) total_ses,
sum(decode(status,'INACTIVE',1,0) )inactive_ses from gv$session group by inst_id

Thru Os user

col program for a15F
col machine for a15
col terminal for a15
set lines 152

select s.sid,
s.serial#,
'*'||s.process||'*'  Client,
p.spid Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program || s.module,
s.terminal,
s.machine,
s.status,
--s.last_call_et
s.last_call_et/3600
from gv$session s, gv$process p
where p.addr=s.paddr
and s.osuser='&osuser';

Session accessing an object

select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from v$sqlarea b,v$session a where b.sql_text like '&object_name' and a.sql_address=b.address;


select b.sql_text,a.sid,a.serial#,b.users_executing, b.rows_processed,a.last_call_et/3600 Hrs from gv$sqlarea b,gv$session a where sql_text like '%DTEA_PA_REPORTING_AGT_HISTO%' and a.sql_address=b.address;

Select
a.session_id, b.sql_text,
count(*)
from
v$active_session_history a,v$sql b
where
a.session_state= 'ON CPU' and
a.SAMPLE_TIME > sysdate - (120/(24*60)) and a.sql_id=b.sql_id and b.sql_text like '%WF_ITEM_ATTRIBUTE_VALUES%'
group by a.session_id,b.sql_text
order by
count(*) desc;

Listing out details of program thru SQLID

Select
a.session_id, b.sql_text,a.program,a.module,a.action
from
v$active_session_history a,v$sql b
where
a.sql_id=b.sql_id and b.sql_id like '%fk9qzystpcazs%';

 How to find apps user when you know the o/s  pid in 11i for Forms users (f60webmx 100% CPU)


 You have to pass the UNIX process id to this script

 column "User Name" format a20
 column "ClPID" format a8
 select
 d.user_name "User Name",
 b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
 to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
 from
 fnd_logins a, v$session b, v$process c, 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)
 and a.SPID = &PID;

mod_oc4j: Failed to find a failover oc4j process for session request for destination

Error Noticed in the log file 

$LOG_HOME/ora/10.1.3/Apache

mod_oc4j: Failed to find a failover oc4j process for session request for destination

Solution -

1. Noticed the When we have started the Application Services the load average was 1.

2. When executed ps -ef  |grep applmgr  noticed many processes.

3. Killed the process via 

ps -ef |grep applmgr|grep -v grep |awk '{print $2}'|xargs kill -9

Issue got resolved.

a. Started the services.

b. Via forms fixed restart and all the concurrent managers are up.

Regards
Mohammed Abdul Muqeet

What should be the Correct Setting for Parameter AQ_TM_PROCESSES in E-Business Suite Instance?

What should be the Correct Setting for Parameter AQ_TM_PROCESSES in E-Business Suite Instance?


Oracle Workflow Cartridge - Version 11.5.10.0 to 11.5.10.2 [Release 11.5.10]
Information in this document applies to any platform.
Checked for relevance on 03-MAR-2012


GOAL

That note explains the importance of parameter AQ_TM_PROCESSES, in an E-Business Suite instance, and then gives some tips to set it to the correct value, depending on the database version.

FIX

The AQ_TM_PROCESSES parameter determines the number of AQ background processes that will be started to perform Queue Monitoring. Queue Monitoring is responsible for removing the processed messages from the queues (after the retention time of the queue has been reached), and for changing the status of the delayed messages from "Waiting" to "Ready" (when the delay has been reached). As E-Business Suite operates many queues, of course, in an E-Business Suite instance, Queue Monitoring must be active. So it is important to properly set AQ_TM_PROCESSES, by following the instructions below:

1- The parameter AQ_TM_PROCESSES must never be set to 0, whatever the version of the database is, otherwise Queue Monitoring will not occur.

2- For pre-10g databases:

AQ_TM_PROCESSES has to be set in the database parameter file, and it must originally be set to 1.  That value allows to start 1 AQ background process for Queue Monitoring, and this is usually sufficient for most E-Business Suite instances.  However, it can be increased, if you experience some delay in the queue maintenance.

3- For 10g+ databases:

Starting from 10g, Queue Monitoring can "auto-tune".  That means Queue Monitoring does not need AQ_TM_PROCESSES to be defined, it is instead able to adapt to the number of AQ background processes to the system load.

As a consequence, the parameter AQ_TM_PROCESSES no longer needs to be set in the database parameter file, but you can still define it:

     -> If one sets the parameter, then the behavior is similar to a pre-10g database, i.e. a number of AQ background processes will be started, according to the value of the parameter.

     -> If one  does not set the parameter, then the Queue Monitoring will "auto-tune", i.e. it will start the AQ background processes that are needed to handle the load, and will adapt the number of processes, as the load changes.

So, ideally, in 10g+ database, you should no longer set AQ_TM_PROCESSES in the database parameter file, thus enabling the "auto-tune" feature ...

However, when AQ_TM_PROCESSES is not set in the database parameter file, and you query the parameter value from table V$PARAMETER, then the value appears as being 0.  Some commonly used data collection scripts (wfver.sql for instance), select the parameter value from V$PARAMETER. The output could be confusing, leading to think that AQ_TM_PROCESSES is actually set to 0.

Note : in 11.2.0.3 database, when "auto-tune" feature is enabled (i.e. aq_tm_processes is not defined in database parameter file), the value of aq_tm_processes will be shown as 1, so confusion is no longer possible, and one can run with "auto-tune" feature enabled.

So, the recommendation, for 10g+ databases, is still to set AQ_TM_PROCESSES in the database parameter file. And again, the original value should be 1, but it can be increased when some delay is noted in the queue maintenance.

Note

4- In case it needs to increase the value of AQ_TM_PROCESSES, it must be noted that, in 9.2 and 10g+ databases, the parameter AQ_TM_PROCESSES must not be set to 10, as setting it to 10 disables some other features of the Queue Monitoring.

Important Links of Hyperion

I have collected some important links of hyperion.



http://www.adistrategies.com/index.php?loc=knowledge1&item=605
http://effectiveessbase.blogspot.in/2013/09/using-new-planning-flat-file-load.html
http://www.aioug.org/sangam/index.php/2013-08-22-17-33-03/friday-agenda.html
http://epmcult.blogspot.in/2013/07/automate-life-cycle-management-in.html
http://innovuspartners.com/making-the-push-to-a-reporting-application-from-planning-2/
http://john-goodwin.blogspot.in/2010/06/1112-planning-mapping-reporting.html
http://john-goodwin.blogspot.in/2010/06/1112-planning-mapping-reporting.html
http://john-goodwin.blogspot.co.uk/2011/05/planning-11121-automate-pushing-of.html
http://www.infosysblogs.com/oracle/2012/01/thinking_beyond_hyperion_essba.html
http://blog.innov8it.in/wp/hyperion-ie10-ie9/
http://www.interrel.com/educations/webcasts
http://epm-errors.blogspot.in/
http://hyperionplanningandmore.blogspot.in/
http://hyperionplanningandmore.blogspot.in/2013/05/msad-admin-conflicting-with-native-admin.html
http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin_11122/frameset.htm?ch14s11.html
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?dotattrs.html
https://blogs.oracle.com/pa/
https://blogs.oracle.com/proactivesupportEPM/entry/java_update_alert_java_1
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?ddlfield.html#ddlfield1015706
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?dotattrs.html
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?dotattrs.html
http://john-assoc.com/index.php/hyperion-essbase-knowledgebase/7-hyperion-essbase-knowledgebase
http://www.oracle.com/technetwork/middleware/bi-foundation/hyperion-supported-platforms-085957.html
http://apex.oracle.com/pls/apex/f?p=44785:24:108498758888462::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:4304,29
http://innovuspartners.com/essbase-consolidation-operators-tilde-vs-caret-2/
http://hyperionexpert.blogspot.in/2009/11/essbase-calculation-performance-tunning.html
http://epm-errors.blogspot.in/search/label/EPMA
http://apex.oracle.com/pls/apex/f?p=44785:2:1722209389670701:FORCE_QUERY::2,CIR,RIR:P2_TAGS:Hyperion
http://www.oracle.com/webfolder/technetwork/tutorials/obe/hyp/PLN11.1.2_LCM/index.html
http://apex.oracle.com/pls/apex/f?p=44785:24:1936339231374901::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:4307,2
http://apex.oracle.com/pls/apex/f?p=44785:24:1936339231374901::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5408,2
http://docs.oracle.com/cd/E17236_01/index.htm
http://apex.oracle.com/pls/apex/f?p=44785:2:0:::2:P2_GROUP_ID:1001
http://docs.oracle.com/cd/E17236_01/epm.1112/readme/erpi_1112200_readme.html
http://apex.oracle.com/pls/apex/f?p=44785:24:0::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5420,29
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_master_work_repos/odi_master_work_repos.htm
http://www.oracle.com/technetwork/middleware/financial-management/tutorials/configerpi-093532.html
http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_dbag/frameset.htm?dotdimb.htm
http://john-goodwin.blogspot.in/2012/04/applying-epm-11122-maintenance-release.html
http://docs.oracle.com/cd/E17236_01/epm.1112/epm_install_1112200/frameset.htm?ch11.html
http://docs.oracle.com/cd/E17236_01/epm.1112/epm_install_1112200/frameset.htm?ch11.html
http://docs.oracle.com/cd/E15523_01/doc.1111/e14007/toc.htm
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/frameset.htm?failover.html
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_db/frameset.htm?ainaggr.html
https://forums.oracle.com/message/11104885
http://www.network54.com/Forum/58296/thread/1348868171/Hyperion+DRM+-+Need+advice
https://blogs.oracle.com/proactivesupportEPM/entry/advisor_webcast_integrating_drm_with
http://www.checkpointllc.com/resources
http://docs.oracle.com/cd/E15523_01/core.1111/e10105/logs.htm
http://vimeo.com/liniumconsulting/videos
http://vimeo.com/40618203
http://blog.topdownconsulting.com/2011/10/rolling-forecasts-in-four-parts/
http://oraclebisolutions.blogspot.in/2012/11/hyperion-essbase-interview-questions.html
http://blog.mtgny.com/
https://mtg.webex.com/mw0307l/mywebex/default.do?siteurl=mtg
http://www.mtgny.com/odtug/kscope12/MTGEWR12.pdf
http://docs.oracle.com/cd/E17236_01/epm.1112/epma_batch_user/frameset.htm?launch.html
http://www.oracle.com/technetwork/middleware/epm/documentation/index.html
http://www.slideshare.net/Ranzal/fdm-erpi-with-essbase-streamlining-data-integration-and-challenging-dogma
http://hyperionoracle.blogspot.in/2012/05/in-essbase-calculation.html#!/2012/05/in-essbase-calculation.html
http://essbaselabs.blogspot.in/2010/02/smart-view-timeout-settings.html#!/2010/02/smart-view-timeout-settings.html
http://www.oracle.com/technetwork/middleware/bi-foundation/hyperion-supported-platforms-085957.html
http://www.marketsphere.com/marketsphere2.aspx?pgID=957&id=153
http://www.youtube.com/watch?v=2ryG3Jy6eIY
http://www.youtube.com/watch?v=Zd4VK3gHYs0
http://www.youtube.com/watch?feature=fvwp&NR=1&v=LFnewuBsYiY
http://www.youtube.com/watch?v=mgEugd5kZgk
http://www.youtube.com/watch?v=Z9l87sB96cc
http://www.youtube.com/watch?v=EIS-CcdmLe0&list=PL7154A202EAF003FE
http://www.youtube.com/watch?v=d2xeNpfzsYI
http://www.youtube.com/watch?v=XAuwAHWpzPc
https://blogs.oracle.com/HyperionPlanning/entry/calculation_scripts_session_6
https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=174809419109481&id=1456233.1&_afrWindowMode=0&_adf.ctrl-state=iokwf42g1_69
https://blogs.oracle.com/proactivesupportEPM/
http://hyperionfinn.blogspot.in/2013/04/top-gun-2013-presentations.html
http://docs.oracle.com/cd/E37502_01/general.300/eid_getting_started/toc.htm#About%20this%20guide
http://epm-errors.blogspot.in/
http://www.infratects.com/
http://docs.oracle.com/cd/E14571_01/core.1111/e10105/monitor.htm
http://www.advancedepm.com/experience/education.html
http://www.advancedepm.com/education/blog/item/31-purge-epma-transaction-history.html
http://docs.oracle.com/cd/E40248_01/index.htm
https://blogs.oracle.com/proactivesupportEPM/entry/epm_11_1_2_32
https://forums.oracle.com/forums/thread.jspa?threadID=2531198&tstart=0
https://blogs.oracle.com/proactivesupportEPM/entry/epm_11_1_2_31?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref/frameset.htm?set_runtimesubvars.html
https://forums.oracle.com/forums/thread.jspa?threadID=2532395&tstart=0
http://www.youtube.com/watch?v=2-TqcRd_0qM
https://blogs.oracle.com/proactivesupportEPM/entry/oracle_epm_webcasts_youtube_channel1
http://www.accelatis.com/
http://www.packtpub.com/article/essbase-aso-enterprise-analytics-essbase-bso-essbase-analytics
https://forums.oracle.com/forums/thread.jspa?messageID=9972960&#9972960
http://download.oracle.com/docs/cd/E17236_01/epm.1112/hp_admin/pref_sys.html
http://download.oracle.com/docs/cd/E17236_01/epm.1112/hp_admin/ch09s01s06.html
http://download.oracle.com/docs/cd/E17236_01/epm.1112/hp_admin/tsk_add.html
http://docs.oracle.com/cd/E17904_01/integrate.1111/e12644/hyperion_plan.htm
https://forums.oracle.com/forums/thread.jspa?threadID=2206753
http://hyperionfinn.blogspot.in/2013/05/mobile-support-in-epm-11123.html
http://looksmarter.blogspot.in/2013/04/all-cool-new-features-in-oracle-epm.html
http://hyperionfinn.blogspot.in/2013/05/mobile-support-in-epm-11123.html

FDM Application Weblink Error

FDM Application Weblink Error

Hi,

If you get error with FDM Url http://localhost/HyperionFDM is not working and giving error message like

Application Error:

Description: An exception occured during the execution of the current web request. Please contact administrator to review the stact trace in the event log for more information about the error.

Solution for this error is:

1. Stop and start the FDM service. if it works with this option is fine Note: In my scenario Issue got resolved here.
2. otherwise follow the below steps.

3.  Run Web logic server-> May be it was stopped when we click on it cmd prompt will get displayed and weblogic server will start in 5-10 minutes.

4. Go to run and type "inetmngr".

5. Start the "HyperionFDMAppPool"

Try these steps The FDM Link will work. if not try to reboot your system this will also solve you problem.

HFM 11.1.2.1 PSU 104

HFM 11.1.2.1 PSU 104

Here's a quick heads-up note for HFM customers: HFM 11.1.2.1 has received another update in the form of Patch Set Update 104. The update includes one new feature - logging capability for the Task Automation module which helps in troubleshooting taskflow issues. The PSU is cumulative so it includes all previous HFM Patch Set Updates and also a lenghty list of new fixes.

Review the readme file and download the package for HFM 11.1.2.1 PSU 104 on Oracle Support.

Available Patch Sets and Patch Set Updates for Oracle Hyperion Enterprise Performance Management Products

Available Patch Sets and Patch Set Updates for Oracle Hyperion Enterprise Performance Management Products (Doc ID 1400559.1)

Applies to:
Hyperion BI+ - Version 9.3.0.0.00 to 11.1.2.2.000 [Release 9.3 to 11.1]
Hyperion Financial Management - Version 9.3.0.0.00 to 11.1.2.3.000 [Release 9.3 to 11.1]
Hyperion Essbase - Version 9.3.0.0.00 to 11.1.2.2.000 [Release 9.3 to 11.1]
Hyperion Planning
Information in this document applies to any platform.
Purpose

This article contains information about the latest Patch Sets and Patch Set Updates for all current releases of Oracle Hyperion Products:

    Calculation Manager
    Enterprise Performance Management Architect (EPMA)
    Essbase
    Financial Data Quality Management (FDM)
    Financial Management (HFM)
    Financial Reporting
    Hyperion Data Relationship Management (DRM)
    Hyperion Disclosure Management
    Hyperion Financial Close Management (FCM)
    Hyperion Profitability and Cost Management (HPCM)
    Hyperion Strategic Finance (HSF)
    Hyperion Crystal Ball
    Interactive Reporting
    Planning
    Reporting and Analysis
    Shared Services

Details

See the following documents for detailed information about recommended patches for EPM products:

    Document 1481942.1 Available Patch Sets and Patch Set Updates for Oracle Hyperion Shared Services
    Document 1321453.1 Available Patch Sets and Patch Set Updates for Oracle Hyperion Financial Management
    Document 1360962.1 Available Patch Sets and Patch Set Updates for Oracle Hyperion Reporting and Analysis, Financial Reporting and Interactive Reporting
    Document 1395593.1 Available Patch Sets and Patch Set Updates for Oracle Hyperion Planning
    Document 1396084.1 Available Patch Sets and Patch Set Updates for Oracle Hyperion Essbase
    Document 1400076.1 Available Patch Sets and Patch Set Updates for Enterprise Performance Management Architect and Calculation Manager
    Document 1400561.1 Available Patch Sets and Patch Set Updates for Financial Data Quality Management (FDM)
    Document 1525518.1 Available Patch Sets and Patch Set Updates for Oracle Crystal Ball, DRM, FCM, HPCM, HSF and Disclosure Management

Every Patch Set and Patch Set Update contains a readme file with detailed installation instructions and list of fixed defects or added features. For product releases on Premier and Extended Support, previous maintenance updates will be supported for six months after the release date of a new maintenance update. Service Patch Updates will be supported for three months after the release of a new service patch update. More information about available patch types can be found in Document 1535618.1 Explanation of Patch types For Hyperion EPM Products.

For further details regarding support for Oracle Hyperion products see Lifetime Support Policy: Oracle Applications brochure available on Lifetime Support web page.
References
NOTE:1360962.1 - Available Patch Sets and Patch Set Updates for Oracle Hyperion Reporting and Analysis, Financial Reporting and Interactive Reporting.
NOTE:1395593.1 - Available Patch Sets and Patch Set Updates for Oracle Hyperion Planning
NOTE:1400561.1 - Available Patch Sets and Patch Set Updates for Hyperion Financial Data Quality Management and FDM ERPI Integrator
NOTE:1396084.1 - Available Patch Sets and Patch Set Updates for Oracle Hyperion Essbase
NOTE:1400076.1 - Available Patch Sets and Patch Set Updates for Enterprise Performance Management Architect and Calculation Manager
NOTE:1481942.1 - Available Patch Sets and Patch Set Updates for Oracle Hyperion Shared Services
NOTE:1321453.1 - Available Patch Sets and Patch Set Updates for Oracle Hyperion Financial Management (HFM)

Applying This Patch Release 11.1.2.1.000 Patch Set Update (PSU): 11.1.2.1.600

Applying This Patch

The section includes important information about applying this patch for EPM System Installer.

Note: This patch should be applied to all machines that have the Financial Management Client/ADM driver installed.  This patch should be applied to ALL HFM application servers and ALL HFM Web servers.

To apply this patch:

1.    Stop Financial Management. Ensure that all HFM processes are not running, including HsxServer.exe, HsvDataSource.exe, odl_rotatelogs.exe and CASecurity.exe. If you are running IIS, stop and re-start it.

2.    Download and unzip the downloaded patch file, .zip, to the \OPatch directory (by default, Oracle/Middleware\EPMSystem11R1\OPatch).

Note: .zip is the name that My Oracle Support assigns to this patch. When you download the file, a message indicates what the filename is.

3.    From a command line, change the directory to \OPatch.

4.    Enter the following command to apply the patch:

opatch.bat apply \OPatch\14354419 -oh -jre \jdk160_21

where  is Oracle\Middleware by default.

5.    Restart Foundation Services.

6.    All users should now clear cached files from their browsers.

PSU 600 Brings IE9 Support To EPM 11.1.2.1

PSU 600 Brings IE9 Support To EPM 11.1.2.1

The following Patch Set Updates have been made available on Oracle Support with the primary purpose of adding support for Internet Explorer 9 into Oracle EPM System Release 11.1.2.1.

• Patch Set Update: 11.1.2.1.600 for EPM Architect (patch ID 14224664)
• Patch Set Update: 11.1.2.1.600 for Hyperion Financial Management (HFM) (patch ID 14354419)
• Patch Set Update: 11.1.2.1.600 for Hyperion Profitability and Cost Management (patch ID 14548006)
• Patch Set Update: 11.1.2.1.600 Hyperion Planning (patch ID 14464109)
• Patch Set Update: 11.1.2.1.600 Shared Services 11.1.2.1 (patch ID 14142678)
• Patch Set Update: 11.1.2.1.600 Workspace 11.1.2.1 (patch ID 14119724)

You should apply the PSUs for Shared Services and Workspace in all environments and include the other specific updates depending on what products are installed. Take time to study the readme files carefully as some of the updates (especially the one for Planning) includes quite a few prerequisites and also instructions to work around known issues.

I'm not sure if these will be the only products to receive (or to require) the IE9 treatment but this information will hopefully be added to the EPM certification matrix document soon. It's also worth noting that the readme file for the Planning PSU mentions that even with these updates applied, Internet Explorer 9 is still supported in compatibility view mode only

Upgrade to ODI 10.1.3.6

First: Check the Master Rep by Repository Consistency Checker (an optional step for customers who are not using Microsoft SQL Server) by doing the following steps:
1. Edit config/sample.properties to refer at your Mrep

2. Run bin/start_diag.bat, then run bin/rcc.bat

3. After that go to diag_tools >> logs >> DiagsYYYYMMDD >> you will find 3 XML files

4. Copy these XML files into the following ODI 10.1.3.6 directory prior to run the upgrade of your repositories: /lib/scripts//patches


Instructions for Oracle Data Integrator

1. Perform a backup of the ODI installation directory.

2. Perform a backup of the Master and Work Repositories.

3. Uncompress the content of the patch to a temporary directory.

4. Remove the content of the oracledi/lib/scripts/ sub-directory your ODI installation directory.

5. Copy the content of the oracledi sub-directory of the temporary directory to your Oracle Data Integrator installation directory. The temporary directory content should overwrite the Oracle Data Integrator installation directory content.

6. If using Jython, it is recommended to run jython.bat | jython.sh in the /bin/ sub-directory.

7. Repeat the two previous steps on each machine where an Oracle Data Integrator component is installed.

8. Perform an upgrade of your master and work repositories.

###################################################################

To upgrade the master repository:

1. On Windows: From the Start Menu, select Programs > Oracle Data Integrator > Repository Management > Master Repository Upgrade, or run /oracledi/bin/mupgrade.bat
On UNIX: Run /oracledi/bin/mupgrade.sh.
The Master Repository Upgrade tool appears.

2. Select the Login Name corresponding to your master repository connection, or fill in the fields:

Driver: the driver used to access the technology which hosts the repository.

URL: The complete path for the data server hosting the repository.

User: The id or login of the user who owns the tables

Password: This user's password.

Technologies: Select the technology your repository will be based on from the list.

3. Click OK.
The repository upgrade process will begin. You can follow its progress on the console. A message appears when the master repository has been upgraded.


To upgrade the work repositories:

1. Connect to your master repository through Topology Manager.

2. In the Repositories tree view select Work repositories, right click on the repository you want to upgrade and choose Upgrade.

3. Oracle Data Integrator will now upgrade this work repository. A window appears when the work repository has been upgraded.

4. Repeat these steps for each work repository in the list.


9. Open Designer and connect a repository. Select the Help > About Oracle Data Integrator menu option.
The patch version should now appear in the About... window.

10. Import the new and updated technologies.

Successive version of Oracle Data Integrator include new and updated technologies.
Refer to the change log starting from your version to review the technologies that have been updated in each version. If you are using one of the modified technologies, or if you want to use one of the new technologies, you can import it using Synonym Insert-Update model.

Warning! Importing a technology erases all the changes performed in the technology. Customized datatype conversions are also erased by the import process. It is recommended to perform copies of the technologies you have altered, and reapply the changes to the imported technologies.

11. Import new and updated Knowledge Modules.

New versions of existing knowledge modules and new knowledge modules are included in this version. These are listed in the new features section.
Although updating knowledge modules for existing projects is not required, it is recommended that you use the new knowledge modules for new projects. Existing interfaces, packages and scenarios compiled with old knowledge module should still work normally after the upgrade without having to re-generate them.

Before importing or updating knowledge modules, you should be aware of the following:

New knowledge modules should be imported into projects in Duplication mode.

Knowledge modules for new technologies should be imported after the associated technologies have been imported.

Updating an existing knowledge module used in a project should be made using the Import Replace option in the context menu of the knowledge module to replace. This option automatically updates any interfaces using the selected knowledge modules. After replacing a knowledge module, the affected interfaces should be thoroughly checked and tested.

If you have customized the default knowledge modules included in Oracle Data Integrator to suit your needs, you should manually implement the latest changes to these original knowledge modules, otherwise the current KM options get lost and the KM options are set to the default values.