How to check who logged in Oracle Apps(EBS) Front End ?

 It is very common for DBA to check who logged into EBS application in order to take application down for some maintenance ... etc and make sure the system is idle.

Here is the query to see who is there on the system from backend, connect to database as APPS user and run the query.

I ran this query to see who logged in last one day, based on your requirement you can change it for hours as well.

 col user_name for a40

 select USER_NAME, to_char(LAST_LOGON_DATE, 'DD-MON-YYYY HH24:MI:SS') from fnd_user where last_logon_date > sysdate -1 order by USER_NAME;

USER_NAME        TO_CHAR(LAST_LOGON_DATE,'D

-----------      --------------------------

ABC.DEF          21-JUN-2021 09:48:28

GHI.JKL          21-JUN-2021 08:51:31

MNO.PQR          21-JUN-2021 10:18:14

Workflow Override Email Address

It is very common requirement to stop workflow notifications going out of the system, particularly from Development instances and Test instances. Oracle have given a functionality “workflow override email address” to achieve this requirement.

In many companies this task is done by Database Administrators (DBA) but incase you need to do it yourself you follow the below steps.  There are two ways to setup this: 

1. Login to Oracle Apps preferably with a user who is "Workflow System Administrator".

Navigate to “Workflow Administrator Web Applications” Responsibility
Expand "Oracle Applications Manager"

Click "Workflow Manager"

Click "Service Components"

Click “Workflow Notification Mailer”

Click "Set Override Address"

Once you provide the email address you will receive a verification code for the first time which you need to enter it back in the above page to complete the process. Verification field will be enabled after you provide the email address.
2.  From backend, connect to the database using apps user and run below update statement.

update fnd_svc_comp_param_valsset    parameter_value = '<email address>'where  parameter_value = 'NONE'and    parameter_id =( select parameter_idfrom   fnd_svc_comp_params_tlwhere  display_name = 'Test Address');
commit;

OTA – not up and Running in 12.1.3

 

We were in the process of creating Purchase Orders from iProc Requisitions and to send the Auto-approved Purchase Orders through OSN. We created OSN accounts and then realized that the PO’s didn’t hit the OSN. We did check the Work-flow agents and listeners were Enabled and running fine. As part of the trouble-shooting the issue, we found that the OTA (OXTA) was not up and running.

SQL –

select machine,action, decode(count(*),0,’Error: OTA is Not Running’,’OTA is Running’)
from gv$session
where action like ‘%OXTA%’
group by machine, action;

The above query returned no rows.

Reason –

1. ASADMIN password didn’t either match in the system-jazn-data.xml ($INST_TOP/ora/10.1.3/j2ee/oafm/config/system-jazn-data.xml) as of ASADMIN set through the User Management
2. If there was an Upgrade the passwords got changed

Fix –

1. Check and confirm that dbc file specified in $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/data-sources.xml file exists and is correct.
2.  Confirm that the ASADMIN user setup is correct as below

a. Set profile Applications SSO Login Types  (APPS_SSO_LOCAL_LOGIN) for ASADMIN user to ‘Local’.

b. Change the ASADMIN password from User Management as follows (note: in this example password is set to welcome)

– Log onto Oracle E-Business Suite using sysadmin/<sysadmin_pw>.
– Select the User Management responsibility in the Navigator.
– Click the Users link from the navigation menu to open the User Maintenance window.
– Locate ‘ASADMIN’ user by entering information in the search area to retrieve the ‘ASADMIN’ user.
– Click the Update icon next to the ASADMIN user to open the Update User window.
– Remove the Active to date field and click Apply in case is not already removed
– Click the Reset Password icon next to the ASADMIN user to open the Reset Password window.
– Enter new password twice and click Submit

c. Confirm the definition for ASADMIN user in $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/data-sources.xml is defined correctly:

user=”ASADMIN”
password=”->ASADMIN”

Note: The password above must be set with exact value as above because ->ASADMIN is an indirection, it indicates AS 10.1.0.3 that the password of ASADMIN must be taken from system-jazn-data.xml

d. Change password for ASADMIN user in $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml to be !(! in front is mandatory)

Mention: ! in front of password is mandatory and this sign means that password will be encrypted

e. Shutdown and restart the OAFM container and re-test:

sh $ADMIN_SCRIPTS_HOME/adoafmctl.sh stop

sh $ADMIN_SCRIPTS_HOME/adoafmctl.sh start

References –

1. NOTE: 1O87499.1 – Cannot Start OXTA After Upgrade To R12.1.x
2. NOTE:418926.1 – How to Configure OTA For XML Gateway in Release 12
3. NOTE:419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12

Checking Status of all Concurrent Managers from backend.

 There are two ways you can check the concurrent managers running in Oracle apps server.

1. From Oracle Apps Front End :-

Below is the navigation to check currently running concurrent managers in the server:

System Administrator >> Concurrent : Manager >> Administer.

2. Query to get the info of all the concurrent managers running in the Oracle apps DB:-

SELECT DECODE (
          CONCURRENT_QUEUE_NAME,
          'FNDICM', 'Internal Manager',
          'FNDCRM', 'Conflict Resolution Manager',
          'AMSDMIN', 'Marketing Data Mining Manager',
          'C_AQCT_SVC', 'C AQCART Service',
          'FFTM', 'FastFormula Transaction Manager',
          'FNDCPOPP', 'Output Post Processor',
          'FNDSCH', 'Scheduler/Prereleaser Manager',
          'FNDSM_AQHERP', 'Service Manager: AQHERP',
          'FTE_TXN_MANAGER', 'Transportation Manager',
          'IEU_SH_CS', 'Session History Cleanup',
          'IEU_WL_CS', 'UWQ Worklist Items Release for Crashed session',
          'INVMGR', 'Inventory Manager',
          'INVTMRPM', 'INV Remote Procedure Manager',
          'OAMCOLMGR', 'OAM Metrics Collection Manager',
          'PASMGR', 'PA Streamline Manager',
          'PODAMGR', 'PO Document Approval Manager',
          'RCVOLTM', 'Receiving Transaction Manager',
          'STANDARD', 'Standard Manager',
          'WFALSNRSVC', 'Workflow Agent Listener Service',
          'WFMLRSVC', 'Workflow Mailer Service',
          'WFWSSVC', 'Workflow Document Web Services Service',
          'WMSTAMGR', 'WMS Task Archiving Manager',
          'XDP_APPL_SVC', 'SFM Application Monitoring Service',
          'XDP_CTRL_SVC', 'SFM Controller Service',
          'XDP_Q_EVENT_SVC', 'SFM Event Manager Queue Service',
          'XDP_Q_FA_SVC', 'SFM Fulfillment Actions Queue Service',
          'XDP_Q_FE_READY_SVC', 'SFM Fulfillment Element Ready Queue Service',
          'XDP_Q_IN_MSG_SVC', 'SFM Inbound Messages Queue Service',
          'XDP_Q_ORDER_SVC', 'SFM Order Queue Service',
          'XDP_Q_TIMER_SVC', 'SFM Timer Queue Service',
          'XDP_Q_WI_SVC', 'SFM Work Item Queue Service',
          'XDP_SMIT_SVC', 'SFM SM Interface Test Service')
          AS "Concurrent Manager's Name",
       max_processes AS "TARGET Processes",
       running_processes AS "ACTUAL Processes"
  FROM apps.fnd_concurrent_queues
 WHERE CONCURRENT_QUEUE_NAME IN
          ('FNDICM',
           'FNDCRM',
           'AMSDMIN',
           'C_AQCT_SVC',
           'FFTM',
           'FNDCPOPP',
           'FNDSCH',
           'FNDSM_AQHERP',
           'FTE_TXN_MANAGER',
           'IEU_SH_CS',
           'IEU_WL_CS',
           'INVMGR',
           'INVTMRPM',
           'OAMCOLMGR',
           'PASMGR',
           'PODAMGR',
           'RCVOLTM',
           'STANDARD',
           'WFALSNRSVC',
           'WFMLRSVC',
           'WFWSSVC',
           'WMSTAMGR',
           'XDP_APPL_SVC',
           'XDP_CTRL_SVC',
           'XDP_Q_EVENT_SVC',
           'XDP_Q_FA_SVC',
           'XDP_Q_FE_READY_SVC',
           'XDP_Q_IN_MSG_SVC',
           'XDP_Q_ORDER_SVC',
           'XDP_Q_TIMER_SVC',
           'XDP_Q_WI_SVC',
           'XDP_SMIT_SVC');