Adop phases and parameters

Adop phases

1) prepare  - Starts a new patching cycle.
          Usage:  adop phase=prepare

2) Apply - Used to apply a patch to the patch file system (online mode)
         Usage:  adop phase= apply  patches = <>
     
    Optional parameters during apply phase
             
          --> input file : adop accepts parameters in a input file
              adop phase=apply input_file=
       
             Input file can contain the following parameter:
             workers=
              patches=:.drv, :.drv ...
             adop phase=apply input_file=input_file
             patches
             phase
             patchtop
             merge
             defaultsfile
             abandon
             restart
             workers

Note : Always specify the full path to the input file


        --> restart  --  used to resume a failed patch
           adop phase=apply patches=<> restart=yes

       --> abandon  -- starts the failed patch from scratch
           adop phase=apply patches=<>  abandon=yes

       --> apply_mode
             adop phase=apply patches=<>  apply_mode=downtime

         Use apply_mode=downtime to apply the patch in downtime mode ( in this case,patch is applied on run file system)
 
    --> apply=(yes/no)
        To run the patch test mode, specify apply = no
 
    --> analytics
     adop phase=apply analytics=yes
 
           Specifying this option will cause adop to run the following scripts and generate the associated output files (reports):

   ADZDCMPED.sql - This script is used to display the differences between the run and patch editions, including new and changed objects.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop////adzdcmped.out.
 
   ADZDSHOWED.sql - This script is used to display the editions in the system.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowed.out.
 
   ADZDSHOWOBJS.sql - This script is used to display the summary of editioned objects per edition.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowobjs.out
 
   ADZDSHOWSM.sql - This script is used to display the status report for the seed data manager.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowsm.out
 
 
 
3) Finalize :  Performs any final steps required to make the system ready for cutover..     invalid objects are compiled in this phase
 
   Usage: adop phase=finalize
   finalize_mode=(full|quick)  
 
 
4) Cutover  : A new run file system is prepared from the existing patch file system.
   adop phase=cutover
 
   Optional parameters during cutover phase:

         -->mtrestart - With this parameter, cutover will complete without restarting the application tier services
       adop phase=cutover mtrestart=no
 
  -->cm_wait -  Can be used  to specify how long to wait for existing concurrent processes to finish running before shutting down the Internal Concurrent Manager.
           By default, adop will wait indefinitely for in-progress concurrent requests to finish.
 
5) CLEANUP  
 cleanup_mode=(full|standard|quick)  [default: standard]


6) FS_CLONE  : This phase syncs the patch file system with the run file system.
    Note : Prepare phase internally runs fs_clone if it is not run in the previous patching cycle

    Optional parameters during fs_clone phase:

 i ) force - To start a failed fs_clone from scratch
 adop phase=fs_clone force=yes  [default: no]

    ii ) Patch File System Backup Count ==> s_fs_backup_count  [default: 0 : No backup taken]
 Denotes the number of backups of the patch file system that are to be preserved by adop. The variable is used during the fs_clone phase,
 where the existing patch file system is backed up before it is recreated from the run file system.


7) Abort - used to abort the current patching cylce.
   abort can be run only before the cutover phase
    adop phase=abort

Oracle Applications table suffix naming conventions

The simple rule of tables you would want to query:

No suffix (similar to a base table)
_B

Base table. This is the primary table containing the base data - in theory language agnostic. An associated _TL table will have the translated display value (what will be displayed on the applications front end) for a specific code in the base table. If multi language has not been enabled there will be a one to one relationship between base and translation table. (see additional info below)
_ALL
If Oracle apps has been configured for multi org then these tables will have the combined data of all operating units.
_V

Views, in a lot of cases you may prefer to use the views which are a standardised of viewing data from more than one table
_VL

View with language information
_FVL

View with resolved lookups instead of only showing the codes

In some cases you might want to use these tables:
_A

Audit tables
_AVN, _AVC
Audit view of what was changed, when it was changed and by who
_F
Tracked tables, each record associated with the same entity has a start and end date that won't overlap. Used in HR and Payroll.

In most cases you will ignore unless there is a specfic requirements:
_TL
Table with language, is associated with a base or unsuffixed tabled. Contains translations of terms (multi language) relating to the base table. One record in a base table could have many translations.

_GT
Global temporary; data only visible by session owner. Will likely be populated when a process on the apps front end has been run, so most of the time you won't see any data in these tables.
_T

Is an interface or processing table, it will be populate with records that are to be processed into base and other tables.
_ACS

Analytical criteria sources; assuming if you have analytics enabled this ties the data to the associated analytic tables, otherwise they are not populated.
_H

History table; updated only when data from the related tables has been exported (via concurrent job I am assuming), you can trace back exactly what version / state the data was in at the time of export. Useful for analytics perhaps, when the data is being exported to populate a dimension for reporting.
_INT
Standard interface or import table. Records are to be processed into base and other tables.
_DTL/_DTLS
Detail table, contains additional detail for a base table. E.g. address detail table will contain the extended geographic location details of the address table.

Multi-language tables and views:
_TL and _VL extend their associated tables with language information (Multi Language Support) if it is being used by Oracle Apps. For example the TL table contain one or more language indicators for a code so that when it is viewed on the front end it uses the associated values according to the correct language that Oracle Applications has been configured for.
A simple example would be names of months, January could be code '1' and if language code = 'US' display value = 'January'; 'FR' display value = 'Janvier'; 'SP' display value = 'Enero etc.

Some Scheduled Requests Are Duplicated Or Stopped

With Release 12.1.1, find that the submitted scheduled requests are getting duplicated or stop working.

It is expected that the scheduled requests work fine with same schedule parameters.

SOLUTION

==========

1. Navigate to Concurrent > Program > Define, query for "Workflow Background Process" program or any other program for which schedule is not working correctly.

2. De-select the option "Restart on System Failure"

3. Save the change and reschedule the request.

Change APPS password in R12.2

1. Shut down the application tier services using the below script:

$INST_TOP/admin/scripts/adstpall.sh

2. Change the APPLSYS password using

FNDCPASS apps/ 0 Y system/manager SYSTEM APPLSYS WELCOME

3. Run autoconfig with the newly changed password.

4. Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.

5. Change the "apps" password in WLS Datasource as follows:

a. Log in to WLS Administration Console.
b. Click Lock & Edit in Change Center.
c. In the Domain Structure tree, expand Services, then select Data Sources.
d. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
e. On the "Settings for EBSDataSource" page, select the Connection Pool tab.
f. Enter the new password in the "Password" field.
g. Enter the new password in the "Confirm Password" field.
h. Click Save.
i. Click Activate Changes in Change Center.

6. Start all the application tier services using the below script

$INST_TOP/admin/scripts/adstrtal.sh

7. Verify the WLS Datastore changes as follows:

a. Log in to WLS Administration Console.
b. In the Domain Structure tree, expand Services, then select Data Sources.
c. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
d. On the "Settings for EBSDataSource" page, select Monitoring > Testing.
e. Select "oacore_server1".
f. Click Test DataSource
g. Look for the message "Test of EBSDataSource on server oacore_server1 was successful".

Weblogic password change in EBS 12.2

Shutdown the application tier leaving admin server up and running.

$ perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

Program: txkUpdateEBSDomain.pl started at Mon Jul 11 23:35:03 2016

AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: Yes

Enter the full path of Applications Context File [DEFAULT - $CONTEXT_FILE]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:

Finding Archivelog Names using the SCN

How to find the Archivelog names using the SCN

During database recovery,we may have a SCN number and need to know the archivelog names.

set pages 300 lines 300
col first_change# for 9,999,999,999
col next_change# for 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from v$archived_log
where between first_change# and next_change#;

SEQUENCE# number usually shows up on the archivelog name.

If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

rman target /

list backup of archivelog from logseq= until logseq=; 

restore archivelog from logseq= until logseq=;

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=

–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()= ”
– 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 = ‘
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
******************************

Note: 1054215.1 – How to Check if the Workflow Mailer is Running
Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not
Note: 831982.1 – 11i/R12 – A guide for troubleshoting Workflow Notification Emails – Inbound and Outbound
Note: 1012344.7 – Notifications Not Being Sent In Workflow
Note: 560472.1 – Workflow Mailers Not Sending Notifications
Please see (Note: 753845.1 – How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.