Oracle E-Business Suite: Patch Tabloları ve Scriptler ve Raporlar

Belirli Patch tabloları aşağıdakiler gibidir.

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS


Aşağıda yazdığımız sorguyla uyguladığımız patch'lerin uygulanıp uygulanmadığını görebiliriz.

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_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 APPS.AD_PATCH_RUNS A, APPS.AD_APPL_TOPS B, APPS.AD_PATCH_DRIVERS C,
 APPS.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 APPS.AD_PATCH_DRIVERS where APPLIED_PATCH_ID
 in (select APPLIED_PATCH_ID from APPS.AD_APPLIED_PATCHES
 --where PATCH_NAME = ''
 )) ORDER BY 9 desc;


Bu sorguyla herhangi bir patch'in uygulandığında hangi driverları içerdiğini görebiliriz.

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE,
 B.PATCH_DRIVER_ID,
B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE,
 B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE
  from APPS.AD_APPLIED_PATCHES A, APPS.AD_PATCH_DRIVERS B
  where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID ;
--and A.PATCH_NAME = '';

Uygulama versiyonunu öğrenmek için de aşağıdaki sorguyu kullanabiliriz.

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version,
START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",
 BASE_RELEASE_FLAG "Base version"
  FROM apps.AD_RELEASES where END_DATE_ACTIVE IS NULL;

Mümkün olan bütün uygulama versiyonlarını görmek için:

 select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version,
 START_DATE_ACTIVE  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 APPS.AD_RELEASES;

Herhangi bir patch ile  dosyası değişmiş application var mı yok mu onu aşağıdaki sorguyla bulabiliriz.

select A.FILE_ID, A.APP_SHORT_NAME,
 A.SUBDIR, A.FILENAME, max(B.VERSION)
 from APPS.AD_FILES A, APPS.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;

Patch sırasında yapılan işlemler:

   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 = ''
      )
      ) 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;

ve

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;



Ürün versiyonları ve Patch seviyeleri:

  Select product_version,patch_level from FND_PRODUCT_INSTALLATIONS where patch_level like '%&shortname%';


Burada AD - GL gibi kısaltmaları kullanırsak ürün özelliklerini görebiliriz.

Bazı Bugları düzeltmek için hangi driverların kaç kere yüklenildiğini görmek için:

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS );


Uygulamalar için yüklenmiş en son seviye Patch'i görmemiz için aşağıdaki sorgu önemlidir.

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME;

Bir patch için application'in kapanması gerekip gerekmediğinin bulunması:

1. Patch indirilir.

2. İçindeki /etc/config/inventory klasörü kontrol edilir.
3. Eğer içinde aşağıdaki ifade varsa kapatılması gerekir.

(instance_shutdown) true (/instance_shutdown)

Aynısı database patch'i içinde gerekir.

Patch Kontrol Raporu:

SELECT report_headings.report_date,  
     report_headings.sid_name,  
     :DAYS report_days,  
     to_char(sysdate - :DAYS, 'DD-Mon-YY (Dy)')||' and '||  
       to_char(sysdate, 'DD-Mon-YY (Dy)')report_period,  
     substr(app.patch_name,1, 50) patch_name,  
     app.patch_type,  
     apr.patch_run_id,  
     decode(apr.success_flag, 'Y', 'SUCCESS', 'FAILED') patch_result,  
     apr.failure_comments,  
     '('||decode( apr.server_type_admin_flag, 'Y', 'A', '-')||':'||  
         decode(apr.server_type_forms_flag, 'Y', 'F', '-')||':'||  
         decode(apr.server_type_node_flag,  'Y', 'N', '-')||':'||  
         decode(apr.server_type_web_flag,  'Y', 'W', '-')||')' patch_server_type,  
     to_char(apr.start_date, 'DD-Mon-YY HH24:MI') patch_start_date,  
     to_char(apr.end_date, 'DD-Mon-YY HH24:MI') patch_end_date,  
     round(((apr.end_date - apr.start_date) *24 * 60),0) load_time_minutes  
  FROM ad_patch_runs apr,  
    ad_applied_patches app,  
    ad_patch_drivers apd,  
    ad_appl_tops aat,  
    ( SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI') report_date,  
         vd.name sid_name  
      FROM gv$database vd  
     WHERE vd.inst_id = 1 ) report_headings   
  WHERE apr.patch_driver_id = apd.patch_driver_id  
   and apd.applied_patch_id = app.applied_patch_id  
   and apr.appl_top_id = aat.appl_top_id  
   and apr.end_date is not null  
   and apr.end_date > trunc(sysdate - :DAYS )  
    
  ORDER by apr.patch_run_id desc  

Referans:

1-http://www.piper-rx.com/pages/reports_free/fdba001_10.trd