Last Login User’s And Number Of Current EBS Connections
December 8th, 2024, posted in Oracle QueriesYou can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into EBS. This diagnostic test (on “Application Object Library”) will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.
– To check running EBS forms sessions, go to System Administration > Oracle Application Manager > Dashboard > Site Map > Monitoring > Forms Sessions
It links the OS session ID with user ID who runs the Forms session.
- FND_USER table stores the details of all end users. Below query can give a good idea who is logged on:
SQL> select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');- Use below code to get active users' last login:
set heading on
set feedback off
set echo off
ttitle on
Column the_today noprint new_value the_date format a20
Select distinct to_char(sysdate,'MM/DD/YY HH:MIPM') the_today from dual;
ttitle skip 2 -
left 'Run on: ' the_date center 'EBS Active Users and their Active Roles' skip 1 -
left 'Page: ' format 99999 sql.pno skip 1
set lines 145
set pages 30000
col user for a55 trunc
col RESPONSIBILITY_NAME for a40 trunc
col Last_Logon_date for a20
select substr(c.user_name||' - '||c.description, 1, 60) "USER", RESPONSIBILITY_NAME,
c.start_date "ActiveDate", to_char(last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date
from FND_Responsibility_tl a,
FND_USER_RESP_GROUPS b,
fnd_user c
where a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and b.user_id = c.user_id
and (c.end_date is null or c.end_date > sysdate)
and (b.end_date is null or b.end_date > sysdate)
-- and c.last_logon_date > sysdate - 180
order by 1, 2
spool EBS_Active_users_08_2017.txt
/
Spool off- For terminated user, run below to find the last login. Seems the user never logged onto EBS if its last_logon_date is NULL.
SQL> select substr(c.user_name||' - '||c.description, 1, 60) "USER",
to_char(c.last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date, user_id, creation_date, password_date, start_date, end_date
from fnd_user c
where c.user_name like '%ABC%';- POSSIBLE queries to get number of EBS users connections. (not sure how accurate they are)
1) SQL> select count(*), to_char(sysdate, 'DY MON DD HH24:MI:SS YYYY') format
from v$session
where module like '%FNDSCSGN';
2) The number of users on the system in the past 1 hour:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1/24 and user_id != '-1';
The number of users on the system in the past 1 day:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1 and user_id != '-1';Oracle R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error ‘Unable to find an Output Post Processor service to post-process request ’
November 17th, 2024, posted in Concurrent Request, Oracle QueriesE-Business Suite R12 Concurrent Processing, Output Post Processing related issues
Requests fail during Post Processing with the following errors :
‘Unable to find an Output Post Processor service to post-process request xxxxx.
Check that the Output Post Processor service is running’
Cause :
Incorrect data is present in the OPP queue table AQ$FND_CP_GSM_OPP_AQTBL_S, which occurs when the concurrent managers are not shutdown correctly.
A quick sql query shows orphaned OPP subscribers in APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S, causing the select_random_subscriber routine to pick a non-running process.
Solution :
To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Find the active processes for OPP :
SELECT fcp.concurrent_process_id
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE concurrent_queue_name = ‘FNDCPOPP’
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND fcp.process_status_code = ‘A’;
Output example:
CONCURRENT_PROCESS_ID
———————
93557
93558
2. Find the OPP subscribers :
select name from APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S ;
The name in the table is the concurrent_process_id prefixed with ‘OPP’.
The output should normally correspond to the output of first query: have the same number of records and have as name the concurrent_process_id of the running OPP processes prefixed by OPP
Output example:
NAME
——————————
OPP35562
OPP35563
OPP35564
OPP35565
3. If there are any old process which are left from previous runs of OPP processes, unsubscribe them via the following command:
exec fnd_cp_opp_ipc.unsubscribe(”);
Example:
exec fnd_cp_opp_ipc.unsubscribe(‘88933’);
exec fnd_cp_opp_ipc.unsubscribe(‘92625’);
4. Bounce the concurrent managers.
5. Retest some concurrent requests and confirm the post processing actions now complete successfully.
Output Post Processor (OPP) – An Overview By Author – A.Kishore/Sach
October 12th, 2024, posted in Concurrent Request, Oracle EBS Application, Oracle QueriesOutput Post Processor (OPP) – An Overview
- Concurrent processing uses the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests.
- Post-processing actions are actions taken on concurrent request output. An example of a post-processing action is that used in publishing concurrent requests with XML
- Publisher.
- The Output Post Processor (OPP) is an enhancement to Concurrent Processing and is designed to support XML Publisher as post-processing action for concurrent requests. If a request is submitted with an XML Publisher template specified as a layout for the concurrent request output, then after the concurrent manager finishes running the concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output.
- A concurrent manager contacts an available OPP process when a running concurrent request needs an OPP processing action. Concurrent manager uses a local OPP process(that, is, on the same node) by default, but will choose a remote OPP if no local OPP process is available. There should always be at least one OPP process active in the system. If no OPP service is available, completed requests that require OPP processing will complete with a status of Warning.
- The Output Post Processor makes use of the Oracle Streams Advanced Queuing (AQ) database feature. Every OPP service instance monitors the FND_CP_GSM_OPP_AQ queue for new messages and this queue has been created with no value specified for primary_instance (link). This implies that the queue monitor scheduling and propagation is done in any available instance. In other words, ANY OPP service instance may pick up an incoming message independent of the node on which the concurrent request ran.
Maximum Memory Usage Per Process :
The maximum amount of memory or maximum Java heap size a single OPP process can use is by default set to 512MB. This value is seeded by the Loader Data File:
$FND_TOP/patch/115/import/US/afoppsrv.ldt which specifies that the DEVELOPER_PARAMETERS is “J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m”.
Determine the current maximum Java heap size :
SELECT service_id, service_handle, developer_parameters
FROM fnd_cp_services
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
SERVICE_ID SERVICE_HANDLE DEVELOPER_PARAMETERS1091 FNDOPP J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m
Increase the maximum Java heap size for the OPP to 1024MB (1GB) :
UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');The OPP queue can be Recreated the using $FND_TOP/patch/115/sql/afopp002.sql file as
‘APPLSYS’ user. On running the script you will be prompted for username and password.
There are two new profiles options that can be used to control the timeouts :
Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its
request for post processing.Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually
process the request.