Oracle Apps R12.2 Online Patching(ADOP) Part 1

Introduction

Oracle E-Business Suite Release 12.2 installation comes with a new feature called “Online Patching” with two editions (versions) of the application code and seed data. The file system contains two complete copies of the Oracle E-Business Suite and technology files. In the database, we use the Edition-based Redefinition feature to create a new database edition for each online patching cycle.

The "Run Edition" is the code and data used by the running application. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.

The "Patch Edition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching.

The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:

     1)    How to see, the RUN, PATCH & NON-EDITIONED Filesystems?

• fs1 - file system 1 (either run or patch edition)
• fs2 - file system 2 (alternate of file system 1)
• fs_ne - non-editioned file system, for data files

By just setting the ebs environment, shows the filesystems.

[ajithpathiyil1::applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env

  E-Business Suite Environment Information

  ----------------------------------------

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]>

     2)    How to identify the PATCH & RUN edition of filesystem?

[ajithpathiyil1:ajithebs:applmgr]>pwd

/u02/app/applmgr/120/ajithebs

[ajithpathiyil1:ajithebs:applmgr]> grep FILE_EDITION= */EBSapps/appl/*.env

fs1/EBSapps/appl/ajithebs_ajithpathiyil1.env:FILE_EDITION="run"

fs2/EBSapps/appl/ajithebs_ajithpathiyil1.env:FILE_EDITION="patch"

[ajithpathiyil1:ajithebs:applmgr]>     

     3)    How to connect to the PATCH & RUN edition of filesystem?

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

  ----------------------------------------

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]> echo $FILE_EDITION

run

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

  ----------------------------------------

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> echo $FILE_EDITION

patch

[ajithpathiyil1:ajithebs:applmgr]>

     4)    How to display edition status ?

To help keep track of what environment and edition you are connected to, it can be helpful to set the TWO_TASK or FILE_EDITION environment variable as your shell prompt. 

[ajithpathiyil1:ajithebs:applmgr]>  . /u02/app/applmgr/120/ajithebs/EBSapps.env patch

  E-Business Suite Environment Information

  ----------------------------------------

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the PATCH File System ...

[ajithpathiyil1:ajithebs:applmgr]> PS1='$TWO_TASK> '

ajithebs_patch>

     5)    How to find out whether a system is in an Online Patching cycle using the "adop -status" command.

[ajithpathiyil1:ajithebs:applmgr]> adop -status

Enter the APPS username: apps

Enter the APPS password:

Current Patching Session ID: 7

Node Name       Node Type       Phase       Status          Started                        Finished                       Elapsed

--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------

ajithpathiyil1        master          PREPARE     COMPLETED       05-APR-14 03:57:39 +00:00      07-APR-14 04:55:57 +00:00     36:58:18

                                FINALIZE    COMPLETED       07-APR-14 06:09:01 +00:00      07-APR-14 06:54:41 +00:00      0:45:40

                                CUTOVER     COMPLETED       07-APR-14 06:59:47 +00:00      07-APR-14 07:26:32 +00:00      0:26:45

                                CLEANUP     COMPLETED       07-APR-14 11:26:07 +00:00      07-APR-14 11:28:53 +00:00      0:02:46

                                APPLY       COMPLETED

File System Synchronization Used in this Patching Cycle: Full

For more information, run ADOP Status Report by using -detail option

Generating ADOP Status Report at location: /u02/app/applmgr/120/ajithebs/fs_ne/EBSapps/log/status_20140529_163313/adzdshowstatus.out

Please wait...

Done...!

adop exiting with status = 0 (Success)

[ajithpathiyil1:ajithebs:applmgr]>

     6)    How to find names and status of past and present database editions using the ADZDSHOWED.sql script.

The below lists the existing database editions and identifies the OLD, RUN, and PATCH editions.
  

[ajithpathiyil1:ajithebs:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

=                             Editions

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

Edition Name    Type     Status   Current?

--------------- -------- -------- --------

ORA$BASE                 RETIRED

V_20140202_0749 OLD      RETIRED

V_20140405_2132 RUN      ACTIVE   CURRENT

V_20140508_1528 PATCH    ACTIVE

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ajithpathiyil1:ajithebs:applmgr]>

     7)    How to change to the patch edition of database using SQL*Plus?

[ajithpathiyil1:ajithebs:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql

SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec ad_zd.set_edition('PATCH');

     

     8)    What are the Tools and Scripts for Edition-based Development?

[ajithpathiyil1:ajithebs:applmgr]> . /u02/app/applmgr/120/ajithebs/EBSapps.env run

  E-Business Suite Environment Information

  ----------------------------------------

  RUN File System : /u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl

  PATCH File System : /u02/app/applmgr/120/ajithebs/fs2/EBSapps/appl

  Non-Editioned File System : /u02/app/applmgr/120/ajithebs/fs_ne

  DB Host: ajithpathiyil1.lab.com  Service/SID: ajithebs

  Sourcing the RUN File System ...

[ajithpathiyil1:ajithebs:applmgr]> which adop

/u02/app/applmgr/120/ajithebs/fs_ne/EBSapps/appl/ad/bin/adop

[ajithpathiyil1:ajithebs:applmgr]> which xdfgen.pl

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfgen.pl

[ajithpathiyil1:ajithebs:applmgr]> which xdfcmp.pl

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl

[ajithpathiyil1:ajithebs:applmgr]>

  

     9)    Useful SQL*Plus scripts that provides information about the state of your editioned development environment. All ADZD* scripts are found under $AD_TOP/sql. 

    

     Add this directory to the SQLPATH environment variable so that you can refer to the scripts by simple name.

[ajithpathiyil1:ajithebs:applmgr]> SQLPATH=$AD_TOP/sql; export SQLPATH

[ajithpathiyil1:ajithebs:applmgr]> echo $SQLPATH

/u02/app/applmgr/120/ajithebs/fs1/EBSapps/appl/ad/12.0.0/sql

[ajithpathiyil1:ajithebs:applmgr]>

1. ADZDDBCC - database compliance checker, shows violations of the database object development standards documented in the Oracle E-Business Suite Developer's Guide, Part No. E22961. Warning: this script takes a long time to run.
2. ADZDSHOWED - Show database editions and current edition.
3. ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure
4. ADZDSHOWLOGEVT - Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).
5. ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.
6. ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for table.
7. ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.
8. ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.
9. ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
10. ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
11. ADZDSHOWDDLS - Show stored DDL summary by phase.
12. ADZDALLDDLS - Show stored DDL statement text and status.
13. ADZDDDLERROR - Show stored DDL execution errors and messages.
14. adutlrcmp - Recompile all objects, with before/after status report. Warning: this script may take a long time to run.

The following scripts are for experts:

15. ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
16. ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
17. ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.
18. ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.
19. ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
20. ADZDSHOWSM - Show Seed Manager status.
21. ADZDSHOWTM - Show Table Manager status.
22. ADZDSHOWAD - AD (online patching) database object status
23. ADZDSHOWSES - Show sessions connected to the database (by edition).
24. ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
25. ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.

Click here to read previous Second part of this blogpost click here

Workflow Mailer Configuration / Status

The following Query to get the config of the workflow mailer:

SQL> conn apps/apps

Connected.

SQL> set linesize 130;

col value for a30;SQL> 

SQL> select p.parameter_id,p.parameter_name,v.parameter_value value

from fnd_svc_comp_param_vals_v v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in

('OUTBOUND_SERVER', 'INBOUND_SERVER','ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')

order by p.parameter_name;  

PARAMETER_ID PARAMETER_NAME                 VALUE

------------ ------------------------------ ------------------------------

       10018 ACCOUNT                        NoImapUser

       10026 DISCARD                        DISCARD

       10029 FROM                           Workflow Mailer

       10033 INBOUND_SERVER                 NoImapHost.NoImapDomain

       10034 INBOX                          INBOX

       10037 NODENAME                       WFMAIL

       10043 OUTBOUND_SERVER                localhost.localdomain

       10044 PROCESS                        PROCESS

       10053 REPLYTO                        NoReplyTo

9 rows selected.

Display the status of all the listeners and agents:

SQL> set pages 44;

set linesize 130;

SQL> select COMPONENT_NAME,COMPONENT_STATUS from fnd_svc_components;
COMPONENT_NAME                                                                   COMPONENT_STATUS
-------------------------------------------------------------------------------- ------------------------------
ECX Inbound Agent Listener                                                       STOPPED
ECX Transaction Agent Listener                                                   STOPPED
Workflow Deferred Agent Listener                                                 STOPPED
Workflow Deferred Notification Agent Listener                                    STOPPED
Workflow Error Agent Listener                                                    STOPPED
Workflow Inbound Notifications Agent Listener                                    STOPPED
Workflow Notification Mailer                                                     NOT_CONFIGURED
WF_JMS_IN Listener(M4U)                                                          STOPPED
Web Services OUT Agent                                                           STOPPED
Web Services IN Agent                                                            STOPPED
Workflow Java Deferred Agent Listener                                            STOPPED
COMPONENT_NAME                                                                   COMPONENT_STATUS
-------------------------------------------------------------------------------- ------------------------------
Workflow Java Error Agent Listener                                               STOPPED
Workflow Inbound JMS Agent Listener                                              STOPPED
13 rows selected.


To check WorkFlow mail status:

SQL> col mail_status for a12;

SQL> select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;  2    3    4

no rows selected

Check the status of Agent Listeners:

SQL> set pagesize 400

set linesize 120

set pagesize 50

column COMPONENT_NAME format a45

column STARTUP_MODE format a15

column COMPONENT_STATUS format a15

SQL> select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS

from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc

where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME; 

COMPONENT_NAME                                STARTUP_MODE    COMPONENT_STATU

--------------------------------------------- --------------- ---------------

Workflow Notification Mailer                  AUTOMATIC       NOT_CONFIGURED

WF_JMS_IN Listener(M4U)                       AUTOMATIC       STOPPED

Workflow Deferred Agent Listener              AUTOMATIC       STOPPED

Workflow Deferred Notification Agent Listener AUTOMATIC       STOPPED

Workflow Error Agent Listener                 AUTOMATIC       STOPPED

Workflow Inbound Notifications Agent Listener AUTOMATIC       STOPPED

Workflow Java Deferred Agent Listener         AUTOMATIC       STOPPED

Workflow Java Error Agent Listener            AUTOMATIC       STOPPED

ECX Inbound Agent Listener                    MANUAL          STOPPED

ECX Transaction Agent Listener                MANUAL          STOPPED

Web Services IN Agent                         MANUAL          STOPPED

Web Services OUT Agent                        MANUAL          STOPPED

Workflow Inbound JMS Agent Listener           MANUAL          STOPPED

13 rows selected.

Status of the JAVA Workflow Mailer:

SQL> SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

COMPONENT_STATU

--------------------------------

NOT_CONFIGURED

Show inbound,reply and account information:

SQL> set pagesize 100

set linesize 132

set feedback off

set verify off

SQL> col value format a35

col component_name format a30

SQL> select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')

order by c.component_id, c.component_name,p.parameter_name;

COMPONENT_ID COMPONENT_NAME                 PARAMETER_ID PARAMETER_NAME                 VALUE

------------ ------------------------------ ------------ ------------------------------ -----------------------------------

       10006 Workflow Notification Mailer          10018 ACCOUNT                        NoImapUser

       10006 Workflow Notification Mailer          10033 INBOUND_SERVER                 NoImapHost.NoImapDomain

       10006 Workflow Notification Mailer          10053 REPLYTO                        NoReplyTo

Workflow Mailer Config

SQL> set wrap on

set linesize 130;

col parameter_name format a30 head "Parameter Name"

col parameter_value format a50 head "Value"

SQL> SELECT

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 = 'Workflow Notification Mailer'

ORDER BY c.parameter_name;

Parameter Name                 Value

------------------------------ --------------------------------------------------

ACCOUNT                        NoImapUser

ALLOW_FORWARDED_RESPONSE       Y

ALTERNATE_EMAIL_PARSER         oracle.apps.fnd.wf.mailer.DirectEmailParser

ATTACHED_URLS                  WFMAIL:ATTACHED_URLS

ATTACH_IMAGES                  Y

ATTACH_STYLESHEET              Y

AUTOCLOSE_FYI                  Y

CANCELED                       WFMAIL:CANCELED

CLOSED                         WFMAIL:CLOSED

COMPONENT_LOG_LEVEL            5

DEBUG_MAIL_SESSION             N

DIRECT_RESPONSE                N

DISCARD                        DISCARD

EMAIL_PARSER                   oracle.apps.fnd.wf.mailer.TemplatedEmailParser

ENABLE_STYLESHEET              N

EXPUNGE_ON_CLOSE               Y

FRAMEWORK_APP                  1

FRAMEWORK_RESP                 20420

FRAMEWORK_URL_TIMEOUT          30

FRAMEWORK_USER                 0

FROM                           Workflow Mailer

HTMLAGENT                      http://localhost.localdomain:8000/pls/sun

HTML_DELIMITER                 DEFAULT

HTTP_USER_AGENT                Mozilla/4.76

INBOUND_CONNECTION_TIMEOUT     120

INBOUND_FETCH_SIZE             100

INBOUND_MAX_IGNORE_SIZE        1000

INBOUND_MAX_LOOKUP_CACHE_SIZE  100

INBOUND_MAX_RET_EMAIL_SIZE     100

INBOUND_PROTOCOL               imap

INBOUND_SERVER                 NoImapHost.NoImapDomain

INBOUND_SSL_ENABLED            N

INBOUND_UNSOLICITED_THRESHOLD  2

INBOX                          INBOX

INLINE_ATTACHMENT              N

MAILER_SSL_TRUSTSTORE          NONE

MAX_INVALID_ADDR_LIST_SIZE     100

MESSAGE_FORMATTER              oracle.apps.fnd.wf.mailer.NotificationFormatter

MORE_INFO_ANSWERED             WFMAIL:MORE_INFO_ANSWERED

NODENAME                       WFMAIL

OPEN_INVALID                   WFMAIL:OPEN_INVALID

OPEN_INVALID_MORE_INFO         WFMAIL:OPEN_INVALID_MORE_INFO

OPEN_MAIL                      WFMAIL:OPEN_MAIL

OPEN_MAIL_DIRECT               WFMAIL:OPEN_MAIL_DIRECT

OPEN_MAIL_FYI                  WFMAIL:OPEN_MAIL_FYI

OPEN_MORE_INFO                 WFMAIL:OPEN_MORE_INFO

OUTBOUND_CONNECTION_TIMEOUT    120

OUTBOUND_PROTOCOL              smtp

OUTBOUND_SERVER                localhost.localdomain

OUTBOUND_SSL_ENABLED           N

PROCESS                        PROCESS

PROCESSOR_DEFER_EVTDATA_READ   Y

PROCESSOR_ERROR_LOOP_SLEEP     60

PROCESSOR_IN_THREAD_COUNT      1

PROCESSOR_LOOP_SLEEP           5

PROCESSOR_MAX_ERROR_COUNT      10

PROCESSOR_MAX_LOOP_SLEEP       60

PROCESSOR_OUT_THREAD_COUNT     1

PROCESSOR_READ_TIMEOUT         10

PROCESSOR_READ_TIMEOUT_CLOSE   Y

REPLYTO                        NoReplyTo

RESET_NLS                      N

SEND_ACCESS_KEY                Y

SEND_CANCELED_EMAIL            Y

SEND_UNSOLICITED_WARNING       Y

SUMHTML                        WFMAIL:SUMHTML

SUMMARY                        WFMAIL:SUMMARY

TEST_ADDRESS                   NONE

WARNING         WFMAIL:WARNING

Workflow Notification Mailer Setup in Oracle Apps R12

                      Oracle Notification Mailer is the program that does e-mail send and response processing for the Oracle Workflow Notification System in the R12 EBS system. It checks for the messages in the database table to send to the respective recipients.

1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.

Steps to configure Workflow Notification Mailer:

1. Login to Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager

If this is first time you are configuring Workflow Notification Mailer in Oracle Apps R12/12i you will see Notification Mailers as unavailable as shown in screenshot

Click on Notification Mailers

In next screen (as shown below, click on Edit Button)

Here you have option to select Inbound notification setup or Just outgoing Notification Setup.

Provide SMTP Server Name (ensure that CM node should be able to connect to SMTP Server or SMTP Relay)

Uncheck Inbound Processing (from above screen), if you don’t wish to configure Inbound Notification Mailer.

If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)

Click on Apply button to finish configuration, at this stage Notification Mailer will test SMTP Server & IMAP Server connectivity.

SQL to monitor (check status) of Workflow Notification Mailer (Java):

 SQL> SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS
where COMPONENT_ID=10006;

Metalink Notes for Notification Mailer1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer