Patching

patchsets.sh usage:

$ ./patchsets.sh connect=apps/apps (Most Common Usage)
$ ./patchsets.sh connect=apps/apps patch_list=ad_applied_patches
$ ./patchsets.sh connect=apps/apps@TEST ad_bug_date=01-FEB-02
$ ./patchsets.sh
$ ./patchsets.sh applptch=/tmp/applptch.txt
$ ./patchsets.sh connect=apps/apps debug=y
$ ./patchsets.sh connect=apps/apps debug=y (if you have a problem send this)
$ ./patchsets.sh connect=apps/apps available=metalink


Apps Patch Commands:
-------------------------------
1.Maintainance Mode:
  ==================

$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaultsfile logfile=adadmin.log menu_option=ENABLE_MAINT_MODE

$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaultsfile logfile=adadmin.log menu_option=DISABLE_MAINT_MODE


sqlplus apps/****** @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

Check:
=======
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;



2.Patch Merge:
  ============

How to Merge Patches in AD.I.5:

Another great way to save time and effort on patching is by merging patches together.

Most patches can be merged, starting with Oracle E-Business Suite 11i.

--Patch_top directory is $STAGE/patches/CPUAPR2005
--Source Directory is $STAGE/patches/CPUAPR2005/source
--Destination directory is $STAGE/patches/CPUAPR2005/destination
--Manifest file is $STAGE/patches//CPUAPR2005/manifest.lst
--Create manifest.lst file as follows
$ cd $STAGE/patches//CPUAPR2005
$ find `pwd` –name "*.zip" >manifest.lst
--Content of manifest.lst is
$STAGE/patches/CPUAPR2005/p3748678_11i_GENERIC.zip
$STAGE/patches/CPUAPR2005/p4092104_11i_GENERIC.zip
$STAGE/patches/CPUAPR2005/p4252614_11i_GENERIC.zip
$STAGE/patches/CPUAPR2005/p3803148_11i_GENERIC.zip
--merge patch
$ cd $STAGE/patches//CPUAPR2005
$ admrgpch -s `pwd`/source \
  -d `pwd`/cpuapr2005merged \
  -merge_name cpuapr2005merged \
  -manifest `pwd`/manifest.lst

Examples:
=========

admrgpch -s /home/applmgr/patch10g/source -d /home/applmgr/patch10g/dest -merge_name test123

$admrgpch -s /u01/patches/source -d /u01/patches/destination -merge_name NLS20.

$ cd /d01/patch_merge
$ admrgpch -s /d01/patch_merge/source -d /d01/patch_merge/destination -merge_name merge99


3.Patch Commands:
  ===============
adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt logfile=654321.log patchtop=/patches/654321 driver=u654321.drv interactive=no

adpatch help=y

usage: adpatch [help=y]

       adpatch Pre-Install Mode
               [preinstall=y|n]

       adpatch Test Mode
               [apply=y|n]

       adpatch Non-Interactive mode
               [defaultsfile=<$APPL_TOP/admin/SID/defaultsfile>]
               [logfile=<logfile>][interactive=y|n][workers=<workers>]
               [patchtop=<patchtop>][driver=<driver_file>][restart=y|n]
               [abandon=y|n][wait_on_failed_job=y|n][stdin=y|n]

               [maxworkers=<maxworkers>]

        adpatch
               [localworkers=<localworkers>][workers=<workers>]
               [printdebug=y|n][parallel_index_threshold=<threshold_value>]
               [order=<order>] [flags=<flags>][options=<options>]

       adpatch Upload Patch History From Filesystem
               [uploadph=y|n]
where

* help=y      - Prints help information about adpatch options.

* preinstall  - To run adpatch in Pre-Install Mode.
                Default - No.

* apply       - To run adpatch in Test Mode.
                Default - Yes.

* uploadph    - To upload the patch history from the filesystem to the
                database and exit. This feature can be used when the system
                is not in maintenance mode.
                Default - No.

* interactive - Invokes adpatch in Non-Interactive mode when
               "interactive=no" is specified.
                Default - Yes.

  Following are usually specified in Non-Interactive Mode, however not all
  are compulsory. Some are also valid for interactive mode.

    * defaultsfile         - Defaults file name.
                             Default - none. No default file read or written.
    * stdin                - If "stdin=y" then password will be prompted.
                             Default - No.
    * logfile              - Adpatch log file name.
                             Default - none. Adpatch prompts for this value.
    * workers              - Specifies the number of workers to run.
                             Default - none. Adpatch prompts for this value.
    * maxworkers           - Specifies the maximum number of workers allowed to run.
                             Default - computed dynamically based on database parameters.
    * patchtop             - Top-level directory for the current patch.
                             Default - none. Adpatch prompts for this value.
    * driver               - Name of the patch driver file.
                             Default - none. Adpatch prompts for this value.
    * backup               - Absolute Path for backup.
                             Default - patchtop.
    * restart              - To restart an existing session.
                             Default - none. Adpatch prompts for this value.
    * abandon              - To abandon the previous failed session.
                             Default - No.
    * wait_on_failed_job   - To prevent adpatch from exiting, when all
                             workers have failed in Non-Interactive Mode.
                             Default - No.

* localworkers             - Used in Distributed AD to specify the number of
                             workers to be run on the current machine.
                             Default - Value specified for workers.
* printdebug               - To display extra debugging information.
                             Default - No.
* parallel_index_threshold - Threshold block count in each table, which when
                             exceeded for a table causes its indexes to be
                             created using parallel slaves.
                             Default - none. Adpatch does not create indexes
                             in parallel.

* order = To organize list of jobs to run in a phase. Default - forward.
    * forward              - The order in which jobs are listed in the
                             patch driver file.
    * backward             - Opposite to how the jobs are listed in the
                             patch driver file.

* flags = Generic flags passed to adpatch
    * hidepw                - To omit the "HIDEPW:" comments in the log file.
                              Default - hidepw.
    * trace                 - To log all database operations to a trace file.
                              Default - notrace.
    * logging               - To create indexes in LOGGING or NOLOGGING mode.
                              Default - nologging.

* options = Generic options passed to adpatch.
    * checkfile             - To skip running exec, SQL, and exectier
                              commands if they are recorded as already run.
                              Default - checkfile.
    * compiledb             - To compile invalid objects in the database
                              after running actions in the database driver.
                              Default - compiledb.
    * compilejsp            - To compile out-of-date JSP files, if the patch
                              has copy actions for at least one JSP file.
                              Default - compilejsp.
    * copyportion           - To run commands found in a copy driver.
                              Default - copyportion.
    * databaseportion       - To run commands found in a database driver.
                              Default - databaseportion.
    * generateportion       - To run commands found in a generate driver.
                              Default - generateportion.
    * integrity             - To perform patch integrity checking.
                              Default - nointegrity.
    * autoconfig            - To run AutoConfig.
                              Default - autoconfig.
    * actiondetails         - To get the details of actions turned off.
                              Default - actiondetails.
    * parallel              - To run actions that update the database or
                              actions that generate files in parallel.
                              Default - parallel.
    * prereq                - To perform prerequisite patch checking prior
                              to running patch driver files.
                              Default - noprereq.
    * validate              - To connect to all registered Oracle
                              Applications schemas at the start of the patch.
                              Default - novalidate.
    * hotpatch              - A patch which can be applied in Normal mode.
                              Default - nohotpatch.
    * maintenancemode       - Enable and disable the maintenance mode before
                              and after applying the patch respectively.
                              Default - nomaintenancemode.
    * phtofile              - The patch history will be saved to files.
                              Default - nophtofile.


$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=1234567.log patchtop=$APPL_TOP/patches/1234567 driver=u1234567.drv workers=3 interactive=no restart=yes

$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=7654321.log patchtop=$APPL_TOP/patches/7654321 driver=c7654321.drv workers=3 interactive=no abandon=yes


Distributed Appl_tops:
======================
$ adpatch workers=<total number of workers> localworkers=<number of workers on primary node>
Primary:
--------
$ adpatch workers=8 localworkers=3
Secondary:
----------
$ adctrl distributed=y

Complete Production Patch History
=================================
$ perl $AD_TOP/bin/adphmigr.pl userid=apps/apps startdate='2003/10/10 00:00:00' enddate='2003/14/10 00:00:00'appsystemname=stage appltopname=tafnwl nodatabaseportion=Y

The Best practices to reduce the patching downtime (Execution point view):

1)Use Hotpatch option:
---------------------
where ever Possible apply the patches in the hotpatch mode (ihelp patches, One off patches (small patches) can be applied in hotpatch mode), In hotpatch mode there is no downtime required, Patch can be applied when the services are running.

Note : hotpatch mode is not suggested/supported for patching, It must be used in extreame situations.


2)Use adpatch options:
---------------------
adpatch provides multiple options which can be used reduce the downtime, Eg: No compile DB, No Compile jsp...etc

These activies can be done later when the services are running.

3)Merge Patches:
----------------
Merging of the patches is very good option and it reduces the downtime significantly in case where we have Multiple patches to apply.

4)Increase the batch size during the patching:
----------------------------------------------
Increasing the batch size to higher value (10000) will reduce the patch time by 10%.

5)Distributed AD can be used in Multi Node systems:
---------------------------------------------------
In case of multinode systems we can Distributed AD feature, In this method patch workers will spread and run in all the Applications nodes effectively utilizing the OS resources of others nodes as well.

6)Increase the OS resources (as possible) which help in increasing the the no.of parallel workers of the patch:
---------------------------------------------------------------------------------------------------------------
Having well sized OS resources such as CPU,Memory..etc will increase the patch runtime there by reduces the downtime.


7)Staged APPL_TOP:
-----------------
A staged Applications system represents an exact copy of your Production system, including all APPL_TOPs as well as a copy of the Production database. Patches are applied to this staged system, while your Production system remains up. When all patches have been successfully applied to the test system, the reduced downtime for the Production system can begin.

Please Note:
------------

i) Staged APPL_TOP only reduces the the time required to Apply the patch on Application Node only.(The database part of the patch requires downtime anyway)

ii)Usually Database portion of the patch takes 60-70% of the patching time, So by this method we can only reduce the 30-40% of the patch time,

iii) But staged APPL_TOP is only usefull in case of bulk patching or Upgrades,This approach is not a soultion to the small and day to day patching as this involves lot of preparation (cloning of the Production system to target) and synching back the APPL_TOP to Prod and it is a complex process.

Scenario-Patch fails because of missing prerequisite
=====================================================
Adpatch is running and fails on one of the workers.To fix this worker and continue with
the patch installation, a new patch (Prerequisite) needs to be applied.
Considering that only 1 adpatch session can run on an instance at any given time,how can
a patch be applied when adpatch is already running?

Solution:
=========

Step 1: Using the adctrl utility, shutdown the workers.
a. adctrl
b. Select option 3 " Tell worker to shutdown/quit"

Step 2: Backup the FND_INSTALL_PROCESSES table which is owned by the APPS schema.
a.  sqlplus apps/<password>
b.  create table fnd_install_processes_back as select * from fnd_install_processes;
c.  The 2 tables should have the same number of records.

    Select count(*) from fnd_install_processes_back;
    Select count(*) from fnd_install_processes;

Step 3: Backup the AD_DEFERRED_JOBS table.
a.  sqlplus apps/<password>
b.  create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
c.  The 2 tables should have the same number of records.
    
    Select count(*) from AD_DEFERRED_JOBS_back;
    Select count(*) from AD_DEFERRED_JOBS;

Step 4: Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.
        At this point, the adpatch session should have ended and the cursor should be back at
        the unix prompt.
a.  cd $APPL_TOP/admin/<SID>
b.  mv restart restart_back
c.  mkdir restart

Step 5: Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a.  sqlplus apps/<password>
b.  drop table FND_INSTALL_PROCESSES;
c.  drop table AD_DEFERRED_JOBS;

Step 6: Apply the new patch (The Prerequisite)

Step 7: Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back directory.
a.  cd $APPL_TOP/admin/<SID>
b.  mv restart restart_<patch number>
c.  mv restart_back restart

Step 8: Retore the FND_INSTALL_PROCESSES table, which is owned by the APPS schema.
a.  sqlplus apps/<password>
b.  create table fnd_install_processes as select * from fnd_install_processes_back;
c.  The 2 tables should have the same number of records.

    Select count(*) from fnd_install_processes;
    Select count(*) from fnd_install_processes_back;

Step 9: Restore the AD_DEFERRED_JOBS table.
a.  sqlplus apps/<password>
b.   create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
c.  The 2 tables should have the same number of records.
    
    Select count(*) from AD_DEFERRED_JOBS;
    Select count(*) from AD_DEFERRED_JOBS_back;

Step 10: Re-create synonyms
a.  sqlplus apps/<password>
b.  create synonym AD_DEFERRED_JOBS for APPS.AD_DEFERRED_JOBS.
c.  create synonym FND_INSTALL_PROCESSES for APPS.FND_INSTALL_PROCESSES;

Step 11: Start adpatch, it will resume where it stopped previously.


How to troubleshoot a Patch Failure - adpatch 
=============================================

Check the patch log file : Log file for adpatch or apps patch will exist as specified while running adpatch The default location is $APPL_TOP/admin/SID/log with name adpatch.log.
See on which worker the Patch has failed.
Check the Worker log for the failed Worker at the same location ($APPL_TOP/admin/SID/log) by the name adwork<worker_number>.log. 
To Note that Workers are controlled by adctrl. Based on the error in the log , fix the problem ; Use adctrl ; select option restart failed worked & then give worker number you want to restart to confirm if problem is fixed.


Please check the following file types / errors causing job failures : 

a) odf files : (Object Definition Files) -odf files are there to create tables ,indexes and view etc.
b) pls scripts : -If the failed pls scripts are for creating stored procedure or packages
c) sql Scripts: -Failure on the SQL Script
d) WFLOAD and FNDLOAD failure- Look for the error message in the log file of the worker that fails. 


In order to resolve the error, you may consider the following action plan

1) Try to execute the failing sql / pls manually and see the results
2) Check the Alert Log  and If any of the workers failed due to insufficient tablespace, then add the space , restart the failed worker.
3) Check the database session waits - There could be enquires.
Check the locks and locked objects to ensure that the session applying the patch is now waiting on a lock. If so need to diagnose, kill the session holding the lock.
(Scripts for database Diagnosis provoded in the database Scripts Section)
4) -For WFLOAD and FNDLOAD, you need to look at the log file generated by the above command. The worker’s logfile should point you to the location of the log file.
5) If adpatch fails to generate reports or forms. it is ok to skip these failure as you can always regenerate them after patching.
6) If it fails for a Pre-req patch and a lot of downtime has already lapsed : 

If Adpatch Fails due to pre-requisite Patch or for some other reason and a lot of time has already been spent since the patch started running then , instead of applying this patch from begining you need to do the following steps in order to save the time you spend while applying the patch.
1. Using the adctrl utility, shutdown the workers.
a. adctrl
b. Select option 3 “Tell worker to shutdown/quit”

2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
a. sqlplus applsys/<password>
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;

3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

4. Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.
At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_back
c. mkdir restart

5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;

6. Apply the new patch ( say pre-req patch).
Once this Pre-req Patch is applied successfully.

7. Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back directory.
a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_<patchnumber>
c. mv restart_back restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema.
a. sqlplus applsys/<password>
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/<password>
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms
a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

11. Start adpatch, it will resume where it stopped previously.

If you want to skip a worker , this option is not visible & do it using adctrl option 8 . It is not a good idea to skip the failed worker without consulting from oracle.