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
