Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications 

Cause:

The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.

When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging.
Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.

Please be aware:
This solution has been identifed for a Forms Configuration using the SOCKET mode, which is not the default mode in EBS R12. You can use the solution also for the SERVLET mode - in this case you have to add the FORMS_RECORD_GROUP_MAX to the default.env file.

Solution:

To implement the solution, please execute the following steps:

1. Stop all services on the middle tier.

2. Set following forms environment variables:

FORMS_RECORD_GROUP_MAX to 10000 or if that proves too restrictive, increase it to 20000 or 30000.
FORMS_CATCHTERM=0

In order to set the above forms variables so next time autoconfig run does not override those values, do the following steps.

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/)

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:

a) Go to the autoconfig Template folder:
$cd $AD_TOP/admin/template

b) Create new directory named (custom)
$ mkdir custom

c) Make sure that new directory has same file permissions as ($AD_TOP/admin/template)

d) Copy the following autoconfig template to the new custom directory:
$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env

e) Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

f) Save and exit from the file.

g) Next time autoconfig run, it will read the custom directory and check for any customizations there.

3. Run Autoconfig on the middle tier and make sure it is completed successfully.

4. Startup all services.

5. Monitor the forms process to see its CPU usage, and you will see that form process usage is reduced and not causing any more CPU consumption up to 100% as before.

6. Migrate the solution as appropriate to other environments.

Reference metalink Doc ID 1382442.1

Scripts to check Long Running Sessions

Scripts to check Long Running Sessions

Summary:

This one shows SQL that is currently "ACTIVE"

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
and s.username <> 'SYSTEM' order by s.sid,t.piece;

RMAN – crosscheck archivelog – validation failed for archived log

RMAN – crosscheck archivelog – validation failed for archived log 

Error:

name=/u01/oracle/archives/LOG0000007225_0705278833_0001.ARC RECID=7216 STAMP=896284282
validation failed for archived log

Cause:

Cause of this message is because archive log file destination was changed or someone deleted/moved the archive log files manually using OS commands.
Use “DELETE EXPIRED ARCHIVELOG ALL” will alleviate the VALIDATION FAILED messages.

Solution:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

(or)

run {
allocate channel d1 device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel d1;
}

EXIT;

Start / Stop Mobile Application Services in Oracle Apps R12

Start / Stop Mobile Application Services in Oracle Apps R12

Login as applmgr user :

Go to $ADMIN_SCRIPTS_HOME

Stop Mobile Application Services :

./mwactl.sh -login apps/apps_password stop_force
./mwactlwrpr.sh stop apps/apps_password

Start Mobile Application Services :

./mwactl.sh -login apps/apps_password start
./mwactlwrpr.sh start apps/apps_password

Check Mobile Services are up and Runing:

ps -ef | grep mwa

Find port Number of Mobile Services :

grep mwa $CONTEXT_FILE

Connect to Mobile Services :

telnet hostname.domainname portnumber(mobile application service port_number)

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues

Find Workflow Notification Mailer is up and Running?

SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';

Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory

Find the Failed One’s?

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;

Sql> SELECT * FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'SENT'
ORDER BY begin_date DESC


Check the Workflow notification has been sent or not?

select mail_status, status from wf_notifications where notification_id=<notification_id>

--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference

1. Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<nid>'
<nid> - notification id

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '<nid'>
To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Users complain that notifications are stuck ?

Use the following query to check to see whatever the users are saying is correct

SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;

E.g o/p of query -

MESSAGE_Type      COUNT(1)
--------        ----------
POAPPRV                     11           --- 11 mails of Po Approval not sent ---
INVTROAP                    12
REQAPPRV                  9
WFERROR                     45             --- 45 mails have error


If Mail not received by User ?

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;

Status – Active
Notification_preference-> Mailtext
Email Address should not be null

Notification not sent waiting to be mailed ?

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?

$FND_TOP/sql
run wfmlrdbg.sql

Concurrent Programs with trace enabled

Concurrent Programs with trace enabled

Summary:

Shows which programs are defined with Trace enabled.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

How to enable trace for Concurrent Requests

How to enable trace for Concurrent Requests

It is very common to enable trace for concurrent requests, the main reason for this is to troubleshooting performance problems. Here is a simple guide how to enable oracle session tracing for the request.

1. Login as sysadmin
2. Go to -> System Administrator -> Concurrent : Program -> Define and Press F11
3. Enter the Concurrent Program Name (Eg. Journal Import) -> Press Ctrl + F11
4. Enable the check box "Enable Trace" above "Copy To" button. Click on Save and close the window.
5. Select Requests -> Run->Submit the concurrent request!
6. The trace file will be created in the Database Server under the location of "udump"
sql>select value from v$parameter where name like '%user_dump_dest%'; 

HP-UX Server & Unix Shutdown Command.

HP-UX Server & Unix Shutdown Command.

To shut down HP-UX for power-off, you can do any of the following:

# init 0 -- (Unix or Red hat linux )
# shutdown -h -y now

To shut down and reboot HP-UX:
# init 6 -- (Unix or Red hat linux )
# reboot
# shutdown -r -y now

To shut down HP-UX to single-user mode:
# init S
# shutdown -y now
# shutdown 0

The -h option to the shutdown command halts the system completely but
will prompt you for a message to issue users. The -y option completes
the shutdown without asking you any of the questions it would normally ask.