How to modify workflow administrator role in Oracle EBS?

By default, Workflow System administrator role is set to SYSADMIN User in Oracle EBS 11i/R12. This means only SYSADMIN user can have access to view Oracle workflow notifications of all the EBS users.
We can modify this to any other EBS user who has administrator rights or assign to a reponsibility. For Example, If we change Workflow System administrator to Responsinility name "Workflow Administrator Web Applications", All users who are assigned with this responsibility can have access to view workflow details owned by other Oracle EBS users.

Let us identify the scenarios which require Workflow System administrator privilege.
- Workflow notification is errored and not processed to next approver
- There is an urgent requirement to delegate the workflow notification to another approver.
- Check the status of workflow notification
- Retry / Rewind the workflow notification
- Check the pending notifications for any Oracle EBS user
- Check Workflow Status Diagram

Steps to modify Workflow system administrator role in Oracle EBS 11i/R12

Workflow System Administrator can be changed in below ways.

1.update wf_resources set text='&Enter_Admin_Name' where name='WF_ADMIN_ROLE';

e.g
update wf_resources set text='FND_RESP1:20420' where name='WF_ADMIN_ROLE';


2. Change the value of Context file parameter s_wf_admin_role and run Autoconfig

$ cat $CONTEXT_FILE|grep wf_admin
         <username oa_var="s_wf_admin_role" customized="yes">SYSADMIN</username>


3. Change it from Workflow Administrator Web Applications responsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration


[Note: Ensure that context file parameter "s_wf_admin_role" is updated with modified value to preserve changes during autoconfig Run]

Resolve ORA-28545 When trying to access database link between Oracle database and MSSQL

Heterogenous database link between Oracle database and MSSQL was created using the below command.

SQL> create public database link mssql connect to "integration" identified by "*****" using 'dg4msql'; 

Database link created. 

Error Message

SQL> select sysdate from dual@mssql 
2 ; 
select sysdate from dual@mssql 
* 
ERROR at line 1: 
ORA-28545: error diagnosed by Net8 when connecting to an agent 
Unable to retrieve text of NETWORK/NCR message 65535 
ORA-02063: preceding 2 lines from MSSQL 


LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 
) 
) 
(SID_NAME=dg4msql) 
(ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
(ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
(PROGRAM=dg4msql) 


Resolution 
Listener file on the Oracle Database gateway server should look like below 

LISTENER_GTWY = 
(DESCRIPTION_LIST = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST =oraclegtwy.dmn)(PORT = 1526)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526)) 
) 
) 

SID_LIST_LISTENER_GTWY =------------------------>Missing 
 (SID_LIST= 
 (SID_DESC= 
 (SID_NAME=dg4msql) 
 (ORACLE_HOME=/orabin/gateway_mssql/product/12.1.0/tghome_1) 
 (ENV="LD_LIBRARY_PATH=/orabin/gateway_mssql/product/12.1.0/tghome_1/dg4msql/driver/lib:/orabin/gateway_mssql/product/12.1.0/tghome_1/lib") 
 (PROGRAM=dg4msql) 
 ) 
 ) 

How to Compile Forms in Oracle Applications Release 11i and Release 12?

Steps to compile forms  in  Oracle Applications Release 12.1.x and 12.2.x

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

frmcmp_batch userid=apps/<password> module=formname.fmb
output_file=$PROD_TOP/forms/US/<name>.fmx module_type=form batch=no compile_all=special

After execution of the above command, verify there are no compilation errors in output displayed and the fmx is created succesfully.


Steps to compile forms  in  Oracle Applications Release 11i

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

f60gen module=$AU_TOP/forms/US/<custom_form>.fmb  userid=apps/<passwd> output_file=$PRODUCT_TOP/forms/<customformname>.fmx module_type=form compile_all=yes


Tips:

Use command "f60gen help=y" or "frmcmp_batch.sh help=y" to find the optional parameters.

Using Compile_all= yes changes cached version in the source file in addition to compiling pl/sql in the utput fmx file.

Oracle EBS DBA Utilities :FNDLOAD

What are the objects loaded using FNDLOAD in Oracle Applications?

FNDLOAD (Generic Loader) is a concurrent Program which is used to move metadata between database and text file representations.The loader reads a configuration file to determine what data to access.

FNDLOAD can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader. FNDLOAD downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). FNDLOAD can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Syntax to upload/download concurrent Programs using FNDLOAD:

To Download:

FNDLOAD apps/** 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct  PROGNAME.ldt PROGRAM APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME CONCURRENT_PROGRAM_NAME="Program_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PROGNAME.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Below are the application entities which can be uploaded/downloaded using FNDLOAD.

FND Messages

Lookups

EBS Front End Users

Responsibilities

Alerts

Concurrent Programs

Profile Options

Request Groups 

Menus

Adpatch Worker Fails with "ORA-01031: insufficient privileges"

While Applying Patch on Customer environment, adpatch failed with error

Worker log has the below error.
=====================
GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

AD Worker error:
The following ORACLE error:

ORA-01031: insufficient privileges


occurred while executing the SQL statement:

GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

Error occurred in file

/test12/applmgr/1200/ad/12.0.0/patch/115/sql/ademusr.sql

with arguments '&systempwd &un_fnd &pw_fnd &un_apps &pw_apps'.

==============


Solution
=========

1.Login to oracle database server as oracle user

2. Connect to sqlplus as sysdba and run the failed sql statement
sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role
  2  ;

Grant succeeded.

SQL>


3. Use adctrl to skip the failed worker (Option 8) and continue with Patching

How to Recover lost APPS Password in Oracle Applications R12?

Have you ever forgot or lost apps password in R12. In Oracle Applications 11i, It is easy to recover apps password as it is stored in application files appsweb.cfg and wdbsvr.app.
Apps password is not available in the application files in R12. We can still recover the lost password by creating a decrypt function.

Steps to retreive apps password in R12:

1.Create function Using below syntax

SQL> create or replace FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2
AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
4
5 /

Function created.


2. Find the encrypted password using below query

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A


3.Run the below query to decrypt the password


SQL> SELECT apps.decrypt_pin_func(‘GUEST/ORACLE’,’JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A') from dual;

APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZG761B2FB6A2C49BC802F57E55AC26637388B4EA99
——————————————————————————–
PASSWORD


4. Verify Database Connection using the password retreived


SQL> conn apps/PASSWORD
Connected.

How to enable Trace for Application User in Oracle Applications 11i/R12?

Enabling Tracing for a Application User makes diagnostics easier to analyze.Since only the sql operations performed by a specific User are Traced.


Steps to Enable Trace for Application User:

1.Login to Oracle Applications and select the System Administrator responsibility. 
Navigate to  Profile -->  System 

2.Search for Profile Option 'Initialization SQL Statement - Custom' .Also Specify the username whom you wish to enable tracing during Search.

3.
Under username column Update the below line

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USER_TRACE_IDENTIFER' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note : Level cane be 4,8,12 depends on your requirement 


Save the Changes done in Profile Option

4. Be careful while updating the Profile Option, Any incorrect Syntax may Prevent user from Logging in

5. Inform User to Re-login and Perform the Activity which needs to be traced.

6. Trace Files can be found in the directory specified by "user_dump_directory" parameter on the database server.
Use the identifier given in Profile Option to search the trace file in udump directory.

7. After the issue is reproduced and required trace files are collected for analysis, disable Tracing from Front End.
Its recommended to disable the trace immediately after the diagnostics are collected, as it will generate huge logfiles.

8. Navigate to system=> Profile form under System Administrator Responsibility.

Query for the username  and Profile Option name "Initialization SQL Statement - Custom"

Remove the values and make it blank.

Save the changes.

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

If you are restarting a failed patch session in oracle applications, sometimes you may encounter the error

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

Telling workers to quit...

All workers have quit.

Connecting to APPS......Connected successfully.

AutoPatch error:

Error running SQL and EXEC commands in parallel

Cause:

1. adpatch or adop process was killed from OS level while patch is being applied

2. Database shutdown or terminated



Solution:

Using adctrl utility, use option 4 to change the worker status to Failed


Review the messages above, then press [Return] to continue.

                    AD Controller Menu
     ---------------------------------------------------

     1.    Show worker status

     2.    Tell worker to restart a failed job

     3.    Tell worker to quit

     4.    Tell manager that a worker failed its job

     5.    Tell manager that a worker acknowledges quit

     6.    Restart a worker on the current machine

     7.    Exit


Enter your choice [1] : 4

Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : all

Status changed to 'Failed' for worker 1.
Status changed to 'Failed' for worker 2.
Status changed to 'Failed' for worker 3.
Status changed to 'Failed' for worker 4.
Review the messages above, then press [Return] to continue.



select option "1. Show Worker Status" , The worker status will be "Failed" 

Now select option 2. "2. Tell worker to restart a failed job" 


Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : all

Status changed to 'Fixed, restart' for worker 1.
Status changed to 'Fixed, restart' for worker 2.
Status changed to 'Fixed, restart' for worker 3.
Status changed to 'Fixed, restart' for worker 4.


Restart adpatch 

In 11i, R12.1.X version of oracle applications

when applying patch using adpatch , Select option Yes When it prompts for

'Do you wish to Continue with Previous adpatch Session'

In R12.2.x, Use restart and abandon parameters


adop phase=apply patches=123456 restart=yes abandon=no 

To restart the patch session from where it failed.

Applying Patch R12.AD.B.7 fails with ORA-1031 while running ademusr.sql

While Applying Patch 21314548:R12.AD.B.DELTA.7 using adpatch, Patch worker failed at running the script ademusr.sql.
To begin troubleshooting the issue,First check the worker id which was running the failed script.
We can get this information from Patch logfile[adpatch.log or u21314548.log] or console output while applying the patch.

Check the worker logfile[adworkxxx.log] for the failed worker

Errors in workerlog
===================
ORA-01031 occurs while Granting SELECT on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role For Patch 20395533 (Doc ID 2093641.1)


GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

AD Worker error:
The following ORACLE error:
ORA-01031: insufficient privileges
occurred while executing the SQL statement:

GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

Error occurred in file
/test1/applmgr/1213/ad/12.0.0/patch/115/sql/ademusr.sql
with arguments '&systempwd &un_fnd &pw_fnd &un_apps &pw_apps'.

Time when worker failed: Fri Mar 04 2016 09:48:00


The error occurred because worker is running the scripts as apps user, and APPS user does not have enough privileges to grant DBA_USERS_WITH_DEFPWD

Solution
========

1.Run the below sql statements to grant the role as system user.

SQL> show user
USER is "SYS"
SQL> GRANT select on DBA_USERS_WITH_DEFPWD to SYSTEM with grant option;

Grant succeeded.

SQL> show user
USER is "SYSTEM"
SQL> GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role;

Grant succeeded.

2. Restart the failed worker using adctrl.

3. Monitor Patching until completion.

How to Unlock Objects Statistics?

While running Concurrent Program, Gather Schema Statitics the request failed with error
20005: object statistics are locked (stattype = ALL)***

Logfile:

start of log messages from FND_FILE

+----------------------------------------------------------------------

-----+

In GATHER_SCHEMA_STATS , schema_name= APPS percent= 10 degree = 2 

internal_flag= NOBACKUP

Error #1: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.CZ_MESSAGE_QENTRIES***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #2: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.ECX_INQUEUE***ORA-20005: 

object statistics are locked (stattype = ALL)***

Error #3: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.ECX_IN_OAG_Q_TABLE***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #4: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.ECX_OUTQUEUE***ORA-20005: 

object statistics are locked (stattype = ALL)***

Error #5: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_CRMXRWD_REQ_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #6: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_CRMXRWD_RES_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #7: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_CRMXRWL_REQ_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #8: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_CRMXRWL_RES_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #9: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_GTREQ_QTBL***ORA-20005: 

object statistics are locked (stattype = ALL)***

Error #10: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_GTRES_QTBL***ORA-20005: 

object statistics are locked (stattype = ALL)***

Error #11: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_HM000A_REQ_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #12: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_HM000A_RES_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #13: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_JTF_REQ_Q_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #14: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_JTF_RES_Q_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #15: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_RAPID_BP_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #16: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_RAPID_B_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #17: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_RAPID_ER_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #18: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_RAPID_MP_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #19: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_FM_RAPID_M_QTBL***ORA-

20005: object statistics are locked (stattype = ALL)***

Error #20: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_IH_BULK_QTBL***ORA-20005: 

object statistics are locked (stattype = ALL)***

Error #21: ERROR: While GATHER_TABLE_STATS:

                        object_name=APPS.JTF_PF_LOGGING_TABLE***ORA-

20005: object statistics are locked (stattype = ALL)***

+----------------------------------------------------------------------

-----+

End of log messages from FND_FILE

+----------------------------------------------------------------------

-----+

Solution:

This error comes when analyzing tables in oracle. Basic issue in this error is  when you
import table without data i.e structure/schema only, oracle will lock table statistics.
You can view all the locked tables in schema by executing following query:

select table_name, stattype_locked 
from dba_tab_statistics 
where owner = ‘APPS’ and stattype_locked is not null;

Then how to unlock them, following query will help us to sort this issue:

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name 
||”’);’ from dba_tab_statistics where owner = ‘MBS’ and stattype_locked is not null;

or you can do that for every single table:

exec DBMS_STATS.UNLOCK_TABLE_STATS(‘owner’,'table name’);

How to Purge Workflow Notifications from Mailer Queue, so that Email is not sent to Users?

What to do when Workflow Notification  Mailer was not running properly for a certain period of time and accumulated all the emails in Workflow Queue.
We can Purge the workflow Mails which are not required by updating mail_status='SENT' for unwanted notifications in wf_notifications table.

Here are a couple of reasons to Purge Workflow Notifications

a)The end user may not want to receive outdated email.
b)The Workflow Queue has accumulated a lot of emails during the issue period, and processing has become very slow.

Below are the steps to Purge Workflow Notifications which are not required

1.First Take a Backup of wf_notifications table  and stop the Workflow Notification Mailer

2. Verify notifications from WF_NOTIFICATIONS table that has potential for being sent

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     where status in ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     order by notification_id;
   
3. Narrow down the emails which are not to be sent by the Mailer from a specific date range.

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
     from wf_notifications
     Where Status In ('OPEN', 'CANCELED')
     And Mail_Status In ('MAIL', 'INVALID')
     and begin_date < sysdate-30              -- List only emails older than 30 days ago
     order by notification_id;

4. Set Mail_status='SENT' in WF_NOTIFICATIONS

SQL>update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-30;      

SQL>Commit;


This will update  notifications ,which are older than 30 days and waiting to be sent by the mailer to SENT.
Therefore the notifications will not get emailed when the Mailer is restarted.

5.Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :
$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr


Example Syntax:
$ sqlplus apps/***@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps ***** applsys


6.Now start the Workflow Notification Mailer.

PROCESS and DISCARD Folders IMAP

While Troubleshooting workflow relates issues in EBS, we may need to clear PROCESS and DISCARD folders under workflow user.
The below commands are useful to move folders, connecting to imap


a)Shutdown Workflow Components
b) login to imap host(e.g server1.ebs.com) as applmgr user

  $ telnet server1.ebs.com 143
  
    You will get below messages:

    ----------------------------
Trying **.**.***.**

Connected to server1.ebs.com (**.**.***.**).
Escape character is '^]'.

* OK Dovecot ready.
    ----------------------------
  i login wfmgr *****
  

  i rename PROCESS process_<sys_date>
  
 i rename DISCARD discard_<sys_date>
  

 i create PROCESS
  
i create DISCARD

i subscribe PROCESS

 i subscribe DISCARD


 i unsubscribe process_<sys_date>

 i unsubscribe discard_<sys_date>
  

c) Startup Workflow Components

R12-Password Change Utility

Oracle has introduced a new utility to change the Oracle E-Business Suite passwords in R12.1.2.AFPASSWD is an enhanced version of FNDCPASS utility.
Differances between AFPASSWD and FNDCPASS

1) AFPASSWD only prompts for passwords required for the current operation. allowing separation of duties between applications administrators and database administrators.
Changing sysadmin Password using AFPASSWD prompts only for APPS Password. Changing APPLSYS password prompts for APPS and SYSTEM password.
Whereas, FNDCPASS requires SYSTEM and APPS password to change password for application and database users.

2) AFPASSWD can be run from the database tier as well as the application tier. Whereas, FNDCPASS can be run only on Concurrent Manager Node

3) When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

Changing SYSADMIN Password  

Syntax:$AFPASSWD -f SYSADMIN
Output:Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.  Working…
Enter new password for user [SYSADMIN]:
Verify new password for user [SYSADMIN]:
Password is changed successfully for user SYSADMIN.
Password is changed successfully for user SYSADMIN.
AFPASSWD completed successfully.

Changing APPLSYS Password

Syntax: $AFPASSWD -c apps@PROD -s APPLSYS
Output: Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.  Working…
Enter new password for user:
Verify new password for user:
Working…
AFPASSWD completed successfully.

Usage

AFPASSWD
Usage:
AFPASSWD [-c [@]] -f
AFPASSWD [-c [@]] -o
AFPASSWD [-c [@]] -a
AFPASSWD [-c [@]] -l {TRUE|FALSE}
AFPASSWD [-c [@]] -L {TRUE|FALSE}
AFPASSWD [-c [@]] -s

Options:
-c [@]
Specify the connection string to be use, parameters

Application-manager user and/or TWO_TASK value,
this option can be use in combination with the others.
(If not provided, AFPASSWD will try to default the values
from the environment)
is the APPS schema owner.

-f
Change the password for an Application user.

-o
Change the password for an Oracle Applications Database user.

-a Modify ALLORACLE users.

-l {TRUE|FALSE}
Lock|Unlock account for a single Oracle Applications database user.

-L {TRUE|FALSE}
Lock|Unlock accounts for non-essential Oracle Applications database users

-s
Modify APPLSYS user. This requires the execution
of autoconfig (in ALL-TIERS) to distribute the
changes on your instance ie DB-TIER and ADMIN-TIER.

-h
Display this help.

Notes:
- AFPASSWD will prompt for the required passwords.
- AFPASSWD will prompt for new passwords twice for confirmation.
- Data entered for which contains spaces must be enclosed
in double quotes. For example, AFPASSWD -f "SCOTT TIGER"
- The only option permitted in combination with other options is the -c option.
Otherwise, there should only be one option per command (see usage above).
If additional options are provided on the command line, only the first valid
option after -c (if -c is present) will be executed.
$

Script to Check History of a concurrent Program

Sometimes, While troubleshooting Performance issues related to Concurrent Requests, we need to know the duration for request completion in the past. The script below gives the start time,end time and arguments for a given Concurrent Program

===

set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99

select request_id,
       user_concurrent_program_name,
       to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,
       to_char(ACTUAL_COMPLETION_DATE,'DD/MON HH24:MI:SS') END_TIME,
       (actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name like '%&%'
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id
and actual_start_date >(sysdate-1)
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;

===

How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.


1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes

 commit;