How to check if a patch is applied in Oracle E Business Suite 11i, R12.1.x and R12.2.x

In Oracle EBS R12.2.x :

In Oracle E Business Suite (ebs erp) R12.2.x you cannot query the AD_BUGS table to check if patches have been applied..
The AD_BUGS table may have entries for patches that were applied but later the patching cycle was aborted (not really applied).


The way to check whether a patch is really applied is to use the AD_PATCH.IS_PATCH_APPLIED PL/SQL function.

Usage:

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') 

from dual;

Example sql:

SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (20034256);

or for single app tier installations:

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

Expected results:

EXPLICIT = applied
NOT APPLIED = not applied / aborted

Note: If you are sure patch is applied, but showing as not applied then do the following workaround.

1. Start adadmin after source the RUN FS env.
2. Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3. In "Maintain Applications Files", select "4. Maintain snapshot information".
4. Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5. Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".

In EBS ERP 11i and R12.1.x:

Below Queries can be used to check if patch is applied:

select * from ad_bugs 
where bug_number = '&bug_number'; 

select * from ad_applied_patches 
where patch_name = '&bug_number'; 

SELECT DISTINCT a.bug_number, e.patch_name, c.end_date, b.applied_flag 
FROM ad_bugs a, 
  ad_patch_run_bugs b, 
  ad_patch_runs c, 
  ad_patch_drivers d, 
  ad_applied_patches e 
WHERE a.bug_id = b.bug_id 
AND b.patch_run_id = c.patch_run_id 
AND c.patch_driver_id = d.patch_driver_id 
AND d.applied_patch_id = e.applied_patch_id 
AND a.bug_number LIKE '&bug_number' 
ORDER BY 1 DESC ;

Reference:

Useful Scripts for E-Business Suite Applications Analysts (Doc ID 887438.1)
Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)
How To Check if a Patch is Applied in 12.2.x? (Doc ID 1963046.1)

Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank

Description of Issue

Oracle APEX 5.1 new installation(Embedded plsql gateway configuration) completed successfully, However, Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank. Tried to access below URL still same issue http://<Machine-Name>:<port-number>/i/apex_version.txt

Cause

Database parameters [shared_servers,DISPATCHERS] are not properly set which are required for XDB

Solution

Verify Listener and below Database Parameters:     $ lsnrctl status     SQL> show parameter local listener     SQL> show parameter dispatchers     SQL> show parameters shared_servers 1) We should have shared_servers > 0, please set the following      alter system set shared_servers = 5;    2) You will also need to set dispatchers      Reference:     How to Setup XDB Protocol Server: FTP, HTTP, WebDAV ( Doc ID 362540.1 )      Specifically:      *************************************************************************      Set the DISPATCHERS parameter.      The DISPATCHERS system parameter is required for XDB protocol registration with the                 Listener. The DISPATCHERS parameter is set in the init.ora.      Run the following in SQLPlus to determine if the dispatchers parameter is already set:      show parameter dispatchers      If XDB dispatchers is not already set, add the following line to the init.ora:      Non-RAC:      dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"      Or from the SQL*Plus prompt:      SQL> alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both                 sid='<sid>';      RAC:      instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid1>XDB)"      instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid2>XDB)"      Or from the SQL*Plus prompt:       Execute this for each node:       alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both sid='<sid>';       NOTE: Replace <sid> ,<instanceid1>, etc. with the actual values.       For example:       dispatchers="(PROTOCOL=TCP) (SERVICE=OrclXDB)"       If the DISPATCHERS parameter is set then the default value for SHARED_SERVERS is 1.            The value of SHARED_SERVERS parameter should always be >= 1 for this setup to work.

Script To Find Versions of Oracle E Business Suite R12.2 Forms & Reports, FMW WebTier & oracle_common, Weblogic Server Products and other technology stack components

Execute below commands to find the version of Oracle Forms and Reports, Oracle Fusion Middleware (FMW) - Web Tier & oracle_common and Oracle WebLogic Server (WLS) product in EBS R12.2

Source the Oracle EBS Applications environment file as the owner of the application tier file system.


========================================================================
Oracle Forms and Reports - Product version
========================================================================

export ORACLE_HOME=`grep s_tools_oh $CONTEXT_FILE | sed 's/^.*s_tools_oh[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
${ORACLE_HOME}/bin/frmcmp_batch help=y |grep 'Forms 10.1 (Form Compiler) Version' |awk '{ print "Oracle Forms and Reports Product version : " $6 }'

========================================================================
Oracle Fusion Middleware (FMW) - Web Tier & oracle_common - Product version
========================================================================

export ORACLE_HOME=`grep s_weboh_oh $CONTEXT_FILE | sed 's/^.*s_weboh_oh[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
$ORACLE_HOME/OPatch/opatch lsinventory -detail | grep 'Oracle WebTier and Utilities CD' | awk NR==1{'print "FMW - WebTier & oracle_common Product version : " $6'}

========================================================================
Oracle WebLogic Server (WLS) - Product version
========================================================================

export MYJAVA=`grep s_adjvaprg $CONTEXT_FILE | sed 's/^.*s_adjvaprg[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
${MYJAVA} -cp $FMW_HOME/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:$FMW_HOME/wlserver_10.3/server/lib/weblogic.jar weblogic.version |grep PSU |awk {'print "Oracle WebLogic Server (WLS) Product Version : " $3'}

Execute below for Detailed versions of technology stack components (Forms, iAS, Framework, JDK, OJSP, Database, etc.):

On Application Tier

Source the Applications environment file as the owner of the application tier file system and run:

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl \
 -script=$FND_TOP/patch/115/bin/txkInventory.pl \
 -txktop=$APPLTMP \
 -contextfile=$CONTEXT_FILE \
 -appspass=apps \
 -outfile=$APPLTMP/Report_App_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script.

On Database Tier 

Source the Oracle Database environment file as the owner of the Database tier file system and run:

$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl \
 -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp \
 -contextfile=$CONTEXT_FILE \
 -appspass=apps \
 -outfile=$ORACLE_HOME/appsutil/temp/Report_DB_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script.

AutoPatch error: The worker should not have status 'Running' or 'Restarted' at this point.

Description of Issue

R12.2 ADOP: AutoPatch error: The worker should not have status 'Running' or 'Restarted' at this point. AutoPatch error: Error running SQL and EXEC commands in parallel

Cause

ADOP Patch Session got terminated abnormally due to network issue or patch hung or Lost connectivity. Error encountered when tried to re-start adop patch.

Solution

1. Start Adctrl and look at the worker status.  Are workers running or started?   Select option: 1. Show Worker Status then 4. Tell manager that a worker failed its job   2. Restart the adop patch from the begining. Use below options: - If you want to restart a failed patch from the very beginning, you need to specify below options with adop restart=no abandon=yes - If you want to restart a failed patch from where it left off, you only need to specify below options with adop restart=yes abandon=no

datapatch : Database 12c Post Patch(Opatch) SQL Automation

Datapatch:

Database release 12c extends patch install automation for patches that contain post-patch SQL instructions.
Prior to Oracle 12c such patches required manual intervention to complete the post-patch SQL instructions after restarting the database.

Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches.

Datapatch usage:

All arguments are optional, if there are no arguments then datapatch will automatically determine which SQL scripts need to be run in order to complete the installation of any patches that contain post-patch SQL instructions.

Optional arguments:

-db <sid>
Use the specified database's SID rather than $ORACLE_SID

-apply <patch1,patch2,...,patchn>
Only consider the specified patch list for apply operations

-rollback <patch1,patch2,...,patchn>
Only consider the specified patch list for rollback operations

-force
Run the apply and/or rollback scripts even if not necessary per the SQL registry

-prereq
Run prerequisite checks only, do not actually run any scripts

-oh <oracle_home value>
Use the specified directory to determine what patches are installed

-verbose
Output additional information used for debugging

-help
Output usage information and exit

-version
Output build information and exit

Invoke datapatch:

$ cd $ORACLE_HOME/OPatch
$ datapatch

EBSapps.env environment does not exists in Oracle E Business Suite R12.2.0

EBSapps.env does not exists in Oracle E Business Suite R12.2.0 and it will be created after upgrading to R12.2.2 or later.

If you would like to create the environment file similar to EBSapps.env then refer below metalink

How To Automatically Set the Current Run or Patch Edition / File System for EBS 12.2 (Doc ID 1545584.1)

Please not R12.2.0 is not certified and you must consider of upgrading the Oracle E Business Suite R12.2.0 to R12.2.2 or later version ASAP.


EBSapps.env in R12.2.2 or later :

Change directory to the Base directory and run script EBSapps.env giving "run" or "patch" as argument, eg:

Ex:
cd /u01/oracle/EBS122
. ./EBSapps.env run

How to verify the Oracle E Business Suite R12.2 startCD Version

To verify the Rapid Install version, use the RapidWizVersion executable
located in the rapidwiz directory on "Start Here" CD.

$ cd /Stage/12.2/startCD/Disk1/rapidwiz
$ ./RapidWizVersion

[root@dbahost rapidwiz]# ./RapidWizVersion


Oracle E-Business Suite Rapid Install Wizard
Version 12.2.0.51
(c) Copyright 2000-2011 Oracle Corporation.  All rights reserved.

[root@dbahost rapidwiz]#

ORA-00600: internal error code, arguments: [kcbgcur_6] 4096 4194 4193 4197 on database crash

Description of Issue

ORCL Instance Crashed due to ORA-00600: internal error code, arguments: [600], [ORA-00600: internal error code, arguments: [kcbgcur_6], [583], [4294967295], [4096], [0], [], [], [] , [], [], [], [], [], []

Tried to restart the ORCL database, we were able to mount the ORCL DB however unable to open
the database.

Below errors observed in alert log:

Errors in Alert Log

SMON: enabling cache recovery Errors in file /u01/ORCL/app/oraORCL/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_32464.trc  (incident=322482): ORA-00600: internal error code, arguments: [kcbgcur_6], [583], [4294967295], [4096], [0], [], [], [] Incident details in: /u01/ORCL/app/oraORCL/diag/rdbms/ORCL/ORCL/incident/incdir_322482/ ORCL_ora_32464_i322482.trc Errors in file /u01/ORCL/app/oraORCL/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_32464.trc: ORA-00600: internal error code, arguments: [kcbgcur_6], [583], [4294967295], [4096], [0], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 32464): terminating the instance due to error 600 Instance terminated by USER, pid = 32464 ORA-1092 signalled during: ALTER DATABASE OPEN... ORA-1092 : opiodr aborting process unknown ospid (32464_1) Mon Aug 29 02:26:31 2016 ORA-1092 : opitsk aborting process

Cause

This issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.

Solution

1. Shutdown the instance 2. Startup in mount mode and set the following parameters SQL> startup mount; SQL> alter system set undo_management=MANUAL scope=SPFILE; 3. After setting the above parameter we are able to open the database SQL> shutdown immediate; SQL> startup mount; SQL> alter database open; 4. Check the status of all undo segments SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE'; This is critical - we are looking for all undo segments to be offline - System will always be online. If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR.  There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments. If all offline then continue to the next step 5. Create new undo tablespace - example SQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M; 6. Drop old undo tablespace (You can drop this later on as well) SQL> drop tablespace <old undo tablespace> including contents and datafiles; 7. Modify the spfile with the new undo tablespace name and change the undo management to AUTO SQL> alter system set undo_tablespace = '<new tablespace created in step 5>' scope=spfile; SQL> alter system set undo_management=AUTO scope=SPFILE; 8. Restart Database SQL> shutdown immediate; SQL> startup;

How to Limit Active Concurrent Requests by a FND User

As System Administrator we can limit the number of requests that may be active (status of Running) for an individual user.

Use the Profile Options window to set the Concurrent: Active Request Limit profile

The profile Concurrent: Active Request Limit profile can only be set at Site and User levels and is null by default. Null means unlimited.

Concurrent Requests stay in Pending/Standby and Inactive/No Manager status forever

In Oracle E Business Suite, It has been observed that when attempting to submit any request, request fails with Inactive No Manager Phase/Status Combination and sometimes concurrent manager does not pick up any request it stays in Pending/Standby status

Review the following points when the concurrent request is in Inactive phase with No Manager status and pending standby status


Verify that Internal Concurrent Manager(ICM) is up and running.

Use any one navigation mentioned below to check the status details of Internal Manager.

i. Oracle Applications Manager(OAM) > Site Map > Monitoring > Availability 

   > Internal Concurrent Manager > View Status.
                          OR
ii. System Administrator Responsibility > Concurrent > Manager > Administer

If Concurrent Managers are up and running fine however not processing any requests then there can be few reasons:

1. Specialization rules prohibits processing any requests

 i.Verify if Include Specialization Rule Exists for the Standard Manager.

if exists then remove the same as below:

1. Log into Applications.
2. Navigate: System Administrator / Concurrent / Manager / Define.
3. Query for 'Standard Manager'.
4. Click Specialization Rules.
5. Remove any Include Rules from the Specialization Rules for Standard Manager.
6. Restart the Managers to test.

***Any "Include" entry is highly unadvisable for Standard Manager***

ii.Verify that there is at least one active concurrent manager with/without specialization rules that allow the concurrent program to run.

Run the following query to check whether any specialization rule defined for any concurrent manager that includes/excludes the concurrent program

SELECT    'Concurrent program '
       || fcp.concurrent_program_name
       || ' is '
       || DECODE (fcqc.include_flag, 'I', 'included in ', 'E', 'excluded from ')
       || fcqv.user_concurrent_queue_name specialization_rule_details
  FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_queue_content fcqc, fnd_concurrent_programs fcp
 WHERE fcqv.concurrent_queue_id = fcqc.concurrent_queue_id 
  AND fcqc.type_id = fcp.concurrent_program_id 
  AND fcp.concurrent_program_name = '<PROGRAM_SHORT_NAME>';

Make sure that Concurrent Manager whose specialization rule includes the concurrent program is up and running.


2. Program with Run alone flag is submitted with pending or running status

Run alone means exactly what it says. It means that it runs alone, no other requests can be running. It also means that it cannot start until no other requests are running.

Run the Below query to identify the programs that are Run alone flag enabled.

select request_id from fnd_concurrent_requests
where CONCURRENT_PROGRAM_ID in (
SELECT CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE RUN_ALONE_FLAG='Y');  

Remove Run alone: Y for the Concurrent Program returned in above query


3. All the Managers are all busy

Verify if manager is busy, increase the processes for CM if needed

4. If nothing worked above, then perform below action plan if requests are in inactive no manager status

1. Deactivate manager
2. Remove all specialization rules
 a. Login to Oracle Applications as System Administrator responsibility
 b. Navigate to: Concurrent > Manager > Define > Query for desired 
manager
 c. Click Specialization Rules tab 
 d. Delete entries
3. Activate manager 
4. Add specialization rules back
 a. Login to Oracle Applications as System Administrator responsibility
 b. Navigate to: Concurrent > Manager > Define > Query for desired 
manager
 c. Click Specialization Rules tab 
 d. Add entries

FYI:

The concurrent request queue view is used internally to map requests to managers. This view would be regenerated when concurrent managers are created, or specialization rules are altered.

"Build Concurrent Request Queue View" concurrent request internally gets submitted when concurrent managers are created, or specialization rules are altered.

However, If required we can manually regenerate the concurrent request queue view for concurrent managers by entering the following command as an applmgr user at operating system prompt.

FNDLIBR FND FNDCPBWV apps/<pwd> SYSADMIN 'System Administrator' SYSADMIN

Troubleshooting Oracle E Business Suite Workflow Mailer and Notification issues

1. Check status of workflow mailer

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

2. Check the log file of workflow mailer

Please run the following query to locate all current Workflow Mailer Service log:

set linesize 155; 
set pagesize 200; 
set verify off; 
column MANAGER format a15; 
column MEANING format a15; 
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name 
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup 
WHERE concurrent_queue_name in ('WFMLRSVC') 
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id 
AND fcq.application_id = fcp.queue_application_id 
AND flkup.lookup_code=fcp.process_status_code 
AND lookup_type ='CP_PROCESS_STATUS_CODE' 
AND meaning='Active';

After setting app env file run the following command to collect the logs

grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log

3. Check If the notification for the user is going to discard folder

- First check, If the notifications approved through email are going to Discard folder for that user.
- Try to approve the notification from workflow worklist from Oracle EBS ERP and see if its going to Discard folder or processing successfully.

Please run below command to see notifications moved to Discard folder

grep ":DISCARD:"  $APPLCSF/$APPLLOG/FNDCPGSC*.txt  > mlrdiscard.log 
grep "Approver:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt  > mlrapprover.log 

4. Check below queries if the messages are in ready state

select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,
 0,' 0 = Ready',
 1,'1 = Delayed',
 2,'2 = Retained',
 3,'3 = Exception',
 to_char(substr(wfe.state,1,12))) State,
 count(*) COUNT
from applsys.wf_notification_out wfe
group by wfe.corrid, wfe.state;

select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;

select count(*) from APPLSYS.AQ$WF_NOTIFICATION_OUT
where msg_state in ('READY','WAIT');

Restart Oracle Physical Standby Database and MRP Process

Shutdown Physical Standby Database and MRP:

Please execute below on physical standby database to stop the Managed Recovery Process(MRP) and shutdown physical standby database:

  # connect / as sysdba
 
   STANDBY> alter database recover managed standby database cancel;
   
   STANDBY> recover standby database until cancel;
   auto
   
   STANDBY> shutdown immediate
   
   STANDBY> exit

Startup Physical Standby Database and MRP:

Please execute below commands on physical standby database to start physical standby database and the Managed Recovery Process(MRP) :

   # connect / as sysdba
   
   STANDBY> startup nomount
   
   STANDBY> alter database mount standby database;
   
   STANDBY> alter database recover managed standby database disconnect from session;
   
   STANDBY> exit

If you want to Enable/Disable Archive Log shipping from Primary to Standby Server then execute below command on Primary Node

Defer or Disable Log shipping on the Primary server: 

# connect / as sysdba

PRIMARY> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;

Enable Log shipping on the Primary server:

# connect / as sysdba

PRIMARY> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;

Oracle Endeca Information Discovery (EID) Installation and Overview

Below document describes the high level steps of Oracle Endeca Information Discovery(12.2) implementation/installation and its integration with Oracle EBS R12.2.x

Embedded PL/SQL Gateway: HTTP-404 Bad Request : The HTTP client sent a request that this server could not understand

Issue:

Getting - Bad Request : The HTTP client sent a request that this server could not understand error
WHEN PL/SQL DYNAMIC CONTENT GREATER than default value.



Embedded PL/SQL Gateway: HTTP-404 ORA-6502: PL/SQL: numeric or  value error: character string buffer too small.

After enabling trace we could see below error in trace file.
[Refer --> Enable Logging and Debugging for the Embedded PL/SQL HTTP Gateway (EPG) ( Doc ID 563704.1 )]

"Embedded PL/SQL Gateway: /apex/wwv_flow.accept HTTP-400 Too many arguments passed in. Got 2120 parameters. Upper limit is 2000"

Cause:

These kind of issues will occur due to a modplsql error that indicates that the number of parameters exceeds the number of parameters defined for the modplsql Database Access Descriptor (DAD).
The default value is 2000. Since the APEX page request exceeds the maximum defined, the request fails.

Solution:

To modify the global parameter "max-parameters" to be set for the XDB:

1. Connect to the database through SQLPLUS as SYSDBA user and run the following command to set the parameter at the global level:
SQL> exec dbms_epg.set_global_attribute('max-parameters','6000');
SQL> commit;

2. Verify the value set by above

SQL> SELECT dbms_epg.get_global_attribute('max-parameters') FROM dual;

DBMS_EPG.GET_GLOBAL_ATTRIBUTE('MAX-PARAMETERS')
--------------------------------------------------------------------------------
6000

SQL>

3. Restart the database and listener.

4. Test the issue

Unlock specific USER in the APEX Workspace

If an APEX User is unlocked then follow below procedure to unlock the same

Check the status of an User:

SQL> select WORKSPACE_NAME,USER_NAME,ACCOUNT_LOCKED,DESCRIPTION 
from APEX_040200.apex_workspace_apex_users;

SQL> select DEFAULT_SCHEMA,LAST_FAILED_LOGIN,FAILED_ACCESS_ATTEMPTS,USER_NAME 
from APEX_040200.WWV_FLOW_FND_USER;

Steps to Unlock ADMIN user in the TEST Workspace

Login to SQL*Plus as the owner of the parsing schema if you are making modifications to a workspace.

As an alternative, you can login as SYS or SYSTEM and then do an alter session:

SQL> alter session set current_schema = TEST;

Run the following, which sets the workspace contect using a combination of apex_util.find_security_group_id ./ apex_util.set_security_group_idand issues the appropriate requests. 

SQL> begin
apex_util.set_security_group_id(p_security_group_id => apex_util.find_security_group_id('TEST'));
apex_util.unlock_account(p_user_name => 'ADMIN');
commit;
end;
/

SQL Script to Start and Stop Oracle EBS Workflow Service Containers

Download(wf_container_restart.sql)

REM   +=============================================================================+ 
REM  
REM   Script Name : wf_container_restart.sql
REM     
REM    This is an admin script to start and stop the Workflow Service Containers.
REM    - WFMLRSVC : Workflow Mailer Service
REM - WFALSNRSVC : Workflow Agent Listener Service
REM     - WFWSSVC : Workflow Document Web Services Service
REM
REM   How to run it?
REM   
REM    sqlplus apps/<password>
REM
REM    @wf_container_restart.sql
REM
REM
REM  Type Number to Perform Operation:
REM
REM    1. Activate
REM
REM    2. Deactivate
REM
REM    3. Abort
REM   
REM  +=============================================================================+ 


WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE;
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 200000


select to_char(rownum)|| '. ' || decode(meaning,
              'Activated', 'Activate',
              'Deactivated', 'Deactivate',
              'Terminated', 'Abort'
              ) meaning
from fnd_lookups 
where lookup_type='CP_CONTROL_CODE' and meaning in ('Activated', 'Terminated', 'Deactivated') order by meaning;


-- Option value from user
accept l_option default '1'  prompt 'Enter Workflow Service Container Operation[1]: '



Declare
 l_qaid               number;
 l_queue              number;
 spid                 number;
 errbuf               varchar2(300);
 l_cqname             varchar2(300);
 l_operation varchar2(30) :=  &l_option;

cursor wfsrv_ctl is
   select application_id, CONCURRENT_QUEUE_ID, CONCURRENT_QUEUE_NAME 
   from fnd_concurrent_queues    where CONCURRENT_QUEUE_NAME in ('WFMLRSVC', 'WFALSNRSVC', 'WFWSSVC');
   
Begin

 fnd_global.apps_initialize(0,20420,1);


 for l_rec in wfsrv_ctl loop
 
 
 l_queue := l_rec.CONCURRENT_QUEUE_ID;
 
 l_cqname := l_rec.CONCURRENT_QUEUE_NAME;
 
 if l_queue > 0 and l_operation = '1' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'ACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
elsif l_queue > 0 and l_operation = '2' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'DEACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
else 
   spid:=fnd_request.submit_svc_ctl_request( command => 'ABORT',
                             service => l_cqname,
                             service_app => 'FND');                             
 end if;

if spid = 0 then
    errbuf := fnd_message.get;       DBMS_OUTPUT.put_line(errbuf);
 end if;
 
end loop;

end;
/

commit;
exit;