ADPATCH Part-2

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/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema
sqlplus applsys/apps
create table fnd_Install_processes_back as select * from
fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the
adpatch session should have ended and the cursor should be back at the UNIX prompt.
cd $APPL_TOP/admin/DEVL
mv restart restart_back
mkdir restart
4. Drop the FND_INSTALL_PROCESSES table and the AD_DEFFERED_JOBS table.
sqlplus applsys/apps
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;
5. Apply the new patch.
6. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
cd $APPL_TOP/admin/DEVL
mv restart restart_3263588
mv restart_back restart
7. Restore the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema.
sqlplus applsys/apps
create table fnd_Install_processes as select * from
fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
8. Re-create synonyms
sqlplus apps/apps
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
9. Start adpatch, it will resume where it stopped previously.



How do you determine what patch set you are currently utilizing?
select pa_server_pkg.get_patch_level from dual;

• Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/ 
  
  select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = '' 
  
  To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id 
  
  select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3; 
  
  To find the base application version  
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y' 
  
  
   To find all available application version 
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES 
  
  
  To get file version of any application file which is changed through patch application
  
  select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME 
  
  To get information related to how many time driver file is applied for bugs
• select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '' 
  
  To find latest patchset level for module installed
  
  select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME 
  
  To find what is being done by the patch
  
  select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE 
  
  
  To find Merged patch Information from database in Oracle Applications
  
  select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) ); 
  
  Second Query to know, what all has been done during application of PATCH
  
  Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
  AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and 
  B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID 
  and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and 
  I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and 
  G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
  in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
  GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR, 
  D.FILENAME, E.ACTION_CODE 
  
  
  Script to find out Patch level of mini Pack
  
  Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%'; 
  
  Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex. 
  AD,PA,…
• Script to find the patch timeings
  
  SELECT to_char(drivers.LAST_UPDATE_DATE, 'yyyy-mm-dd') install_date,
           bug.BUG_NUMBER,
           patches.patch_type,
           drivers.PLATFORM,
           langs.language,
           drivers.DRIVER_FILE_NAME,
           to_char(runs.start_date, 'HH24":"MI":"SS') start_time,
           to_char(runs.end_date, 'HH24":"MI":"SS') end_time,
           to_char(to_date('00:00:00','HH24:MI:SS') + (runs.end_date - runs.start_date), 'HH24:MI:SS') time_difference,
           (to_number(to_char(to_date('00','HH24')  + (runs.end_date - runs.start_date), 'HH24'))*60) +
           (to_number(to_char(to_date('00','MI')    + (runs.end_date - runs.start_date), 'MI'))) +
           (to_number(to_char(to_date('00','SS')    + (runs.end_date - runs.start_date), 'SS')) / 60) time_dif_min
    FROM apps.ad_bugs bug,
         apps.ad_patch_drivers drivers,
         apps.ad_patch_driver_langs langs,
         apps.ad_applied_patches patches,
         apps.ad_patch_runs runs
    WHERE langs.patch_driver_id = drivers.patch_driver_id AND
          runs.patch_driver_id=drivers.patch_driver_id AND
          patches.APPLIED_PATCH_ID=drivers.APPLIED_PATCH_ID AND
          patches.patch_name=bug.bug_number AND
          drivers.orig_patch_name = 'bug_' || bug.bug_number
          and bug_number like '%patch_number%'
    ORDER BY install_date ASC, start_time ASC;
  
  

to merge the patch

admrgpch -s /patches/madhu/M2/source -d /patches/madhu/M2/dest -merge_name M2P