Wednesday, June 11, 2014

Oracle ACE Associate Award

Hi Friends,

I take immense pleasure in writing this particular post and letting you know that "I am an Oracle ACE Associate now!"

My Oracle ACE Associate Profile


Thanks to the "Oracle ACE Program" for accepting and giving an entry into this elite group of ACEs.

 
Special thanks to 
~~~~~~~~~~~~~~~~
1) Murali Vallath for nominating me & AIOUG, NZOUG, DOAG for giving me opportunity to speak in their conferences (SANGAM12, NZOUG13, DOAG13, SANGAM13, OTNYathra13)

2) My family & friends, For supporting me.
3) Kai Yu, for his inspiration.
4) All my colleagues & Oracle community friends.  
5) Thousands of all other bloggers who have helped at work in many occasions.

Regards,
Ajith Narayanan

Thursday, May 29, 2014

Everything About Oracle Apps R12.2 Online Patching – Part I

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:
  1. ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
  2. ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
  3. ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.
  4. ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.
  5. ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
  6. ADZDSHOWSM - Show Seed Manager status.
  7. ADZDSHOWTM - Show Table Manager status.
  8. ADZDSHOWAD - AD (online patching) database object status
  9. ADZDSHOWSES - Show sessions connected to the database (by edition).
  10. ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
  11. ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.

Part-II on the same topic, coming soon, watch this space.

Contents Of Part II – How to  Apply Online Patches
In next few posts I will try to show how to apply patch to an editioned system in an Online Patching Cycle. We will further see the patching cycle with several phases as shown below.

Ø  Prepare - creates the patch edition.
Ø  Apply - apply ARU or manual patches to the patch edition.
Ø  Finalize - perform any actions required to prepare for cutover.
Ø  Cutover - Promote Patch Edition to be the new Run Edition.
Ø  Cleanup - remove obsolete code and data from old editions.
 


HAPPY LEARNING!