1. you must disable trigger ebs_login prior to running autoconfig.
2. Log into sqlplus as SYSTEM
3. SQL> alter trigger ebs_logon_disable; trigger altered. exit;
4.$ehco $FILE_EDITIONS it should show patch file system PATCH
5. adautocfg.sh once completed successfully enable the trigger.
6. log into sqlplus as SYSTEM
7 . SQL> alter trigger ebs_logon_enable; trigger altered. exit;
####<Jan 11, 2021 9:58:56 PM EST> <Info> <JDBC> <suthakar.prince> <oaea_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1610420336490> <BEA-001177> <Creating data source connection pool named OAEADatasource, URL = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=suthakar.prince)(PORT=1608)))(CONNECT_DATA=(SERVICE_NAME=DEV))), Properties = user=APPS;.>
####<Jan 11, 2021 9:58:56 PM EST> <Info> <Common> <suthakar.prince> <oaea_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1610420336505> <BEA-000626> <Free resources in pool "OAEADatasource" will be tested every "120" seconds.>
####<Jan 11, 2021 9:58:59 PM EST> <Info> <JDBC> <suthakar.prince> <oaea_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1610420339875> <BEA-001508> <Destroying data source connection pool OAEADatasource.>
####<Jan 11, 2021 9:58:59 PM EST> <Info> <JDBC> <suthakar.prince> <oaea_server1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1610420339876> <BEA-001155> <The following exception has occurred:
weblogic.common.ResourceException: Unknown Data Source OAEADatasource
at weblogic.jdbc.common.internal.ConnectionPoolManager.shutdownConnectionPool(ConnectionPoolManager.java:622)
at weblogic.jdbc.common.internal.ConnectionPoolManager.shutdownAndDestroyPool(ConnectionPoolManager.java:594)
at weblogic.jdbc.common.internal.ConnectionPoolManager.shutdownAndDestroyPool(ConnectionPoolManager.java:520)
at weblogic.jdbc.module.JDBCModule.prepare(JDBCModule.java:320)
Solution:
login into EBS weblogic Console Navigate to EBSDATASource and click OAEADataSource - > connectionpool - and update apps password.
admanagedsrvctl.sh stop oaea_server1
admanagedsrvctl.sh start oaea_server1
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
1. Download sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
2. mv the zip to $ORACLE_HOME
3. unzip -o sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
4. /apps2/oracle/product/12.1.0/sqlt
oracle@suthakar$ls -tlr
total 120
drwxr-x---. 3 oracle oinstall 4096 Jun 5 10:37 input
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:37 doc
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:38 run
drwxr-x---. 2 oracle oinstall 4096 Jun 5 10:38 install
drwxr-x---. 7 oracle oinstall 4096 Jun 5 10:38 utl
-rw-r-----. 1 oracle oinstall 58466 Jun 5 10:41 sqlt_instructions.html
-rw-r-----. 1 oracle oinstall 39368 Jun 5 10:43 sqlt_instructions.txt
oracle@suthakar$cd install
oracle@suthakar$pwd
/apps2/oracle/product/12.1.0/sqlt/install
oracle@suthakar$
oracle@suthakar$sqlplus '/as sysdba'
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 11 21:43:55 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> !ls -ltr sqdrop.sql
-rw-r-----. 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
SQL> sta sqdrop.sql
SQL> SET ECHO OFF;
SQDUSR completed.
SQDROP completed.
SQL>
Execute installation script sqlt/install/sqcreate.sql connected as SYS
SQL> !ls -ltr sqdrop.sql
-rw-r-----. 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
SQL> sta sqdrop.sql
... uninstalling SQLT, please wait
TADOBJ completed.
SQDOLD completed. Ignore errors from this script
SQDOBJ completed. Ignore errors from this script
SQL>
SQL> DECLARE
2 my_count INTEGER;
29 END LOOP;
30 END IF;
31 END;
32 /
Cannot drop procedure sys.sqlt$_trca$_dir_set. ORA-04043: object
SQLT$_TRCA$_DIR_SET does not exist
Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old 1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new 1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE
Session altered.
Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.
Optional Connect Identifier (ie: @PROD): @DEV
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN: APPS
Re-enter password:
PL/SQL procedure successfully completed.
The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: YES
... please wait
TABLESPACE FREE_SPACE_MB
------------------------------ -------------
APPS_TS_ARCHIVE 96
APPS_TS_TX_DATA 456
APPS_TS_NOLOGGING 655
APPS_TS_INTERFACE 728
APPS_TS_MEDIA 1250
APPS_TS_QUEUES 2084
OWB 2323
APPS_TS_SEED 4616
APPS_TS_TX_IDX 11173
APPS_TS_SUMMARY 12029
10 rows selected.
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: APPS_TS_SUMMARY
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP
PL/SQL procedure successfully completed.
The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.
Main application user of SQLT: XXXX
PL/SQL procedure successfully completed.
SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: N
PL/SQL procedure successfully completed.
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SQL>SQL> grant SQLT_USER_ROLE to APPS;
Grant succeeded.
SQL>
Monitor the progress on what is happening with the WF Background Processes by running these:
1. Show what is exactly on the Background Process Deferred Queue
ready for processing
select
w.user_data.itemtype "Item Type", w.user_data.itemkey "Item
Key",
decode(w.state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(w.state)) State,
w.priority, w.ENQ_TIME, w.DEQ_TIME, w.msgid
from wf_deferred_table_m w
where w.user_data.itemtype = '&item_type';
2. Monitor the Queue by Item Type ie APPS<itemtype>
select corrid,
user_data user_data
from wf_deferred_table_m
where state = 0
and corrid = '&Corrid'
order by priority, enq_time;
3. Monitor the Deferred Queue to see exactly what's coming off
next
SELECT
wfdtm.corrid, wfdtm.user_data.ITEMTYPE ITEM_TYPE,
wfdtm.user_data.ITEMKEY ITEM_KEY, wfdtm.enq_time,
DECODE(wfdtm.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
TO_CHAR(SUBSTR(wfdtm.state,1,12))) State
FROM wf_deferred_table_m wfdtm
WHERE wfdtm.state = 0
ORDER BY wfdtm.priority, wfdtm.enq_time;
4. Show what frequency the FNDWFBG request is going to be
resubmitted and its parameters:
select
r.REQUEST_ID, r.REQUESTED_BY, r.PHASE_CODE, p.USER_CONCURRENT_PROGRAM_NAME,
r.ARGUMENT_TEXT "Arguments",
Nvl(Substr(R.Argument_Text,0,Instr(R.Argument_Text,',')-1),'All Items')
Item_Type,
Substr(R.Argument_Text,Instr(R.Argument_Text,',')+1,Instr(R.Argument_Text,',',1,2)-Instr(R.Argument_Text,',')-1)
Min_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,2)+1,(Instr(R.Argument_Text,',',1,3)-1)-(Instr(R.Argument_Text,',',1,2)))
Max_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,3)+1,(Instr(R.Argument_Text,',',1,4)-1)-(Instr(R.Argument_Text,',',1,3)))
Deferred,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,4)+1,(instr(r.ARGUMENT_TEXT,',',1,5)-1)-(instr(r.ARGUMENT_TEXT,',',1,4)))
TIMEOUT,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,5)+1) STUCK,
r.RESUBMIT_INTERVAL EVERY, r.RESUBMIT_INTERVAL_UNIT_CODE SO_OFTEN,
r.RESUBMIT_END_DATE
FROM fnd_concurrent_requests r, FND_CONCURRENT_PROGRAMS_TL p
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
and p.USER_CONCURRENT_PROGRAM_NAME LIKE 'Workflow%Background%'
AND p.LANGUAGE = 'US'
and r.ACTUAL_COMPLETION_DATE is null
and r.PHASE_CODE in ('P','R');
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 nnnnn' (Doc ID 2215371.1)
SELECT fcp.concurrent_process_id FROM apps.fnd_concurrent_queues fcq, apps.fnd_concurrent_processes fcp WHERE concurrent_queue_name = 'FNDCPOPP'AND fcq.concurrent_queue_id = fcp.concurrent_queue_idAND fcq.application_id = fcp.queue_application_id AND fcp.process_status_code = 'A'
select w.user_data.itemtype "Item Type", w.enq_time,w.user_data.itemkey "Item Key" from apps.wf_deferred_table_m w order by w.enq_time desc
SELECT component_name, component_status FROM apps.fnd_svc_componentsWHERE component_type = 'WF_MAILER';
select message_type, count(1) from apps.wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;
select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';
select component_status from apps.fnd_svc_components where component_id = (select component_id from apps.fnd_svc_components where component_name = 'Workflow Notification Mailer');
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.phase_code = 'R'
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET, fcq.RUNNING_PROCESSES ACTUAL, fcq.ENABLED_FLAG ENABLED, fsc.COMPONENT_NAME, fsc.STARTUP_MODE, fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp, apps.fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC' and fsc.COMPONENT_STATUS = 'RUNNING' and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+) and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE
