High CPU utilization Scheduled ORA$AT_SA_SPC_SY Jobs 12.1.0.2

Oracle has raised an alert in the alert.log and created a trace file as well, for a failed DBMS_SCHEDULER job with a strange name which doesn’t appear in DBA_SCHEDULER_JOBS or DBA_SCHEDULER_PROGRAMS – what’s going on?

An extract from the alert log and/or the trace file mentioned in the alert log shows something like

*** SERVICE NAME:(SYS.USERS) ...
*** MODULE NAME:(DBMS_SCHEDULER) ...
*** ACTION NAME:(ORA$AT_SA_SPC_SY_nnn) ...

Where ‘nnn’ in the action name is a number.

No matter how hard you scan the DBA_SCHEDULER_% views, you will not find anything with this name. What is actually failing?

Oracle 11.1.0.6 onwards stopped listing these internal jobs inDBA_SCHEDULER_JOBS, as they did in 10g, and instead lists them inDBA_AUTOTASK_% views. However, not by actual name, so don’t go looking for a TASK_NAME that matches the above action name. You will fail.

There are three different autotask types:

• Space advisor
• Optimiser stats collection
• SQl tuning advisor

The tasks that run for these autotask ‘clients’ are named as follows:

• ORA$AT_SA_SPC_SY_nnn for Space advisor tasks
• ORA$AT_OS_OPT_SY_nnn for Optimiser stats collection tasks
• ORA$AT_SQ_SQL_SW_nnn for Space advisor tasks

See MOS notes 756734.1, 755838.1, 466920.1 and Bug 12343947 for details. The first of these has the most relevant and useful information.

UPDATE: My original failing autotask has been diagnosed by Oracle Support as bug 13840704 for which a patch exists herefor 11.2.0.2 and 11.2.0.3.

Oracle document id 13840704.8 has details, but it involves LOBs based on a user defined type. In this case, Spatial data in an MDSYS.SDO_GEOMETRY column.

The view DBA_AUTOTASK_CLIENT won’t show you anything about a specific task, with the above names, but will show you details of what the overall ‘client’ is, There are three:

select client_name, status
from dba_autotask_client;

CLIENT_NAME                     STATUS
------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor              ENABLED
sql tuning advisor              DISABLED

I can see from the task name in the alert log and trace file, that my failing task is a space advisor one, so, by looking into theDBA_AUTOTASK_JOB_HISTORY view, I can see what’s been happening:

select distinct client_name, window_name, job_status, job_info
from dba_autotask_job_history
where job_status <> 'SUCCEEDED'
order by 1,2;

CLIENT_NAME        WINDOW_NAME     JOB_STATUS JOB_INFO
------------------ --------------- ---------- -------------------------------------------
auto space advisor SATURDAY WINDOW FAILED     ORA-6502: PL/SQL: numeric or value error...
auto space advisor SUNDAY WINDOW   FAILED     ORA-6502: PL/SQL: numeric or value error...

So, in my own example, the auto space advisor appears to have failed on Saturday and Sunday. Given that this is an internal task, and nothing I can do will let me know about the invalid number problem, I need to log an SR with Oracle on the matter. However, as I don’t want my fellow DBAs to be paged in the wee small hours for a known problem, I have disabled the space advisor task as follows:

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

PL/SQL procedure successfully completed

Checking DBA_AUTOTASK_CLIENT again, shows that it is indeed disabled:

select client_name, status
from dba_autotask_client
where client_name = 'auto space advisor';

CLIENT_NAME                     STATUS
------------------------------- --------
auto space advisor              DISABLED

Enabling it again after Oracle Support have helped resolve the problem is as simple as calling dbms_auto_task_admin.enable with exactly the same parameters as for the disable call:

BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

PL/SQL procedure successfully completed

When enabling and/or disabling auto tasks, you must use theCLIENT_NAME as found in DBA_AUTOTASK_CLIENT view.

The full list of DBA_AUTOTASK_% views is:

• DBA_AUTOTASK_CLIENT
• DBA_AUTOTASK_CLIENT_HISTORY
• DBA_AUTOTASK_CLIENT_JOB
• DBA_AUTOTASK_JOB_HISTORY
• DBA_AUTOTASK_OPERATION
• DBA_AUTOTASK_SCHEDULE
• DBA_AUTOTASK_TASK
• DBA_AUTOTASK_WINDOW_CLIENTS
• DBA_AUTOTASK_WINDOW_HISTORY

Reducing time on txkWfClone.sh while running 12.2.4 Autoconfig in appsTier

Preface

Running Autoconfig on multiple tiers can take a long time sometimes and many a times, the culprit is txkWfClone.sh 

The real deal

During cloning, execution of txkWfClone  is required only for the first autoconfig run. For subsequent Autoconfig  runs,  it is not required. 

How can you be smart?

There are two smart things you can do to help yourself and make this happen.

1). Move the phase of the script txkWfClone.sh to INSTALL_SETUP phase in $FND_TOP/admin/driver/fndtmpl.drv:

Open the file $FND_TOP/admin/driver/fndtmpl.drv and make the following changes:

Before:
fnd admin/template txkWfClone.sh    INSTE8_SETUP     
<s_com>/admin/install/<s_contextname> txkWfClone.sh 700

After:
fnd admin/template txkWfClone.sh    INSTALL_SETUP     
<s_com>/admin/install/<s_contextname> txkWfClone.sh 700

2). Replace %s_webhost% with %s_webentryhost% and %s_domainname% with %s_webentrydomain%, in the template $FND_TOP/admin/template/txkWfClone.sql 

How does the savings add up..

Changing the autoconfig phase of this script from  INST8_SETUP to INSTALL_SETUP will prevent it from unwanted execution, resulting in better performance for future autoconfig runs. 

The role of txkWfCLone.sh is to create workflow related references in  db tier of target system in the cloning process. 

During txkWfClone.sh's execution, the comparison between source system and target system URLs is done. Essentially, in a scenario where we are creating an additional middle tier from an existing  middle tier/(s) having one common database tier, it will result in  redundant operation of changing/creating workflow references for target  middle tier. 

For comparison, txkWfClone constructs an url for target system using  the context variables as follows: 

'%s_webentryurlprotocol%'||'://'||'%s_webhost%'||'.'||'%s_domainname%'||':'||' 
%s_active_webport%'||'/pls/'||'%s_dbSid%'
Because the url for target system is constructed using s_webhost  context variable, the script creates distincts urls for each middle tier  which is not necessary because they use the same database instance and hence the workflow references are the same. 

This results into a good amount  of time consumption for every  autoconfig run while cloning. 

This can be avoided by changing the use of %s_webhost% with the use of %s_webentryhost%  in the template txkWfClone.sql. 

This way the urls created for each middle  tier would be the same as it should be in the case of a system with multi apps tiers and one db instance. 

Conclusion

It is good to save time during cloning and txkWfClone.sh can be easily fixed to avoid duplicate effort. Simple fixes like this should be absorbed into regular cloning practices for saving time and effort

Steps to reorganize Workflow tables and their corresponding indexes

1. The concurrent request “Purge Obsolete Workflow Runtime Data” has been completed successfully of request id # 3416008

2. Stopped the applications services in both the nodes

3. The following tables were reorganized in their tablespaces APPS_TS_TX_DATA

Command to move the tables:

select ‘alter table ‘ owner ‘.’ table_name ‘ move tablespace ‘ tablespace_name ‘;’ from dba_tables where table_name in (‘WF_ITEM_ACTIVITY_STATUSES’,
‘WF_ITEM_ATTRIBUTE_VALUES’,’WF_NOTIFICATION_ATTRIBUTES’)

a. WF_ITEM_ATTRIBUTE_VALUES
b. WF_ITEM_ACTIVITY_STATUSES
c. WF_NOTIFICATION_ATTRIBUTES

4. The following corresponding indexes were rebuilded in their tablespace APPS_TS_TX_IDX,

a .WF_ITEM_ACTIVITY_STATUSES_N1
b. WF_ITEM_ACTIVITY_STATUSES_N3
c. WF_ITEM_ACTIVITY_STATUSES_PK
d. WF_ITEM_ACTIVITY_STATUSES_N2
e. WF_ITEM_ACTIVITY_STATUSES_N4
f. WF_ITEM_ATTRIBUTE_VALUES_PK
g. WF_NOTIFICATIONS_ATTR_PK

Command to rebuild the indexes in the reorganized tables:

select ‘alter index ‘ owner ‘.’ index_name ‘ rebuild tablespace ‘ tablespace_name ‘;’ from dba_indexes where index_type=’NORMAL’ and table_name in (‘WF_ITEM_ACTIVITY_STATUSES’,
‘WF_ITEM_ATTRIBUTE_VALUES’,’WF_NOTIFICATION_ATTRIBUTES’)

5. Checked the status of the indexes using the following command,

select status from dba_indexes where index_type=’NORMAL’ and table_name in (‘WF_ITEM_ACTIVITY_STATUSES’,
‘WF_ITEM_ATTRIBUTE_VALUES’,’WF_NOTIFICATION_ATTRIBUTES’)

6. Startup the application services in both nodes

7. The concurrent program “Gather Table Statistics” to analyze the tables has been ran

8. Monitoring the performance.

SMTP Telnet Test To Troubleshoot Java Mailer For Sending Email Notifications

How to verify SMTP server from E-business suite application server.



SOLUTION

It is very important to perform all the steps mentioned below; they will allow to perform the telnet SMTP test from the right node and to pass the correct values during the test.

1) Identify the concurrent tiers node where mailer runs

by running script below:

select target_node
from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';
It will return for example:

TARGET_NODE                    
------------------------------
ebiz1                    

In this example ebiz1 is the node where java mailer runs.

2) Gather other parameters values necessary for the SMTP telnet test:

To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer "outbound server" parameter), and what is the reply to address that is set up for the java mailer (this is mailer "reply to" parameter).

To get these values run the following:

SELECT b.component_name,
       c.parameter_name,
       a.parameter_value
FROM fnd_svc_comp_param_vals a,
     fnd_svc_components b,
     fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
     AND b.component_type = c.component_type
     AND c.parameter_id = a.parameter_id
     AND c.encrypted_flag = 'N'
     AND b.component_name like '%Mailer%'
     AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

It will return for example:

COMPONENT_NAME                  PARAMETER_NAME                 PARAMETER_VALUE        
------------------------------- ------------------------------ -----------------------
Workflow Notification Mailer    OUTBOUND_SERVER                mitini1                          
Workflow Notification Mailer    REPLYTO                        jmailer1@dummy_domain.com

In this example the outbound server is on mitini1 node and the reply to address is set to jmailer1@dummy_domain.com.



3) Perform the SMTP telnet test as follows:

3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)

This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.

In our example you should log to ebiz1 node.

3.2) From mailer node, issue the following commands one by one:

telnet [outbound server] 25
EHLO [mailer node]
MAIL FROM: [reply_to address]
RCPT TO: [my_test_email_address]
DATA
Subject: Test message

Test message body
.
quit 

Uncaught Throwable in processSockets

EBS Version=12.2.4
DB Version=12.1.0.2

Intermittent Failure of OACore Services

Found oacore manager server errors after upgrading from 12.1.3 to 12.2.4

Error:

<BEA-000421> <Uncaught Throwable in processSockets
java.io.IOException: DP_POLL failed for /dev/poll - [Operation not applicable (89)].
java.io.IOException: DP_POLL failed for /dev/poll - [Operation not applicable (89)]
        at weblogic.socket.DevPollSocketMuxer.doPoll(Native Method)
        at weblogic.socket.DevPollSocketMuxer.processSockets(DevPollSocketMuxer.java:95)
        at weblogic.socket.SocketReaderRequest.run(SocketReaderRequest.java:29)
        at weblogic.socket.SocketReaderRequest.execute(SocketReaderRequest.java:42)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:145)

Solution:
To avoid this issue, /dev/poll monitoring of polled file descriptors needs to be disabled. Please review sub-section "Customizing Managed Server Configuration via WebLogic Server Administration Console" in My Oracle Support Knowledge Document Document 1905593.1, "Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2" to disable /dev/poll monitoring and make sure the following actions are performed:

• Choose 'oacore' as the managed server which needs the configuration change


• Add the new server start-up argument:

  -Dweblogic.DevPollDisabled=true

• Save and Activate the configuration change