AD Patches Tables
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_PATCH_RUNS
AD_APPL_TOPS
AD_RELEASES
AD_FILES
AD_FILE_VERSIONS
AD_PATCH_RUN_BUGS
AD_BUGS
AD_PATCH_COMMON_ACTIONS
AD_PATCH_RUN_BUG_ACTIONS
ad_comprising_patches
FND Tables
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES
To know which driver patch has been applied:
select DRIVER_FILE_NAME from AD_PATCH_DRIVERS;
select DRIVER_FILE_NAME from AD_PATCH_DRIVERS where DRIVER_FILE_NAME like '%2408149%';
To check if multicurrency is present:
select MULTI_CURRENCY_FLAG from fnd_product_groups;
M
-
Y
Purging timing information for prior sessions.
sqlplus -s APPS/***** @$AD_TOP/admin/sql/adtpurge.sql 10 1000
Snapshot sql
sqlplus -s &un_apps/***** @$AD_TOP/patch/115/sql/adbkflsn.sql 111
Maintenance mode
$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE')
--------------------------------------------------------------------------------
MAINT
to check the product is registered
select * from applsys.fnd_application where application_short_name='BNE';
adutconf.sql --- script used to generate Oracle Applications Database Configuration Report
Location : $AD_TOP/sql/adutconf.sql
Output : $AD_TOP/sql/adutconf.lst
PRODUCT VERSIONS (AD,PO,....)
select patch_level from fnd_product_installations where patch_level like '%&PRODUCT_NAME%';
select PATCH_LEVEL from fnd_product_installations where PATCH_LEVEL like '%AD%';
select PATCH_LEVEL,status from fnd_product_installations where PATCH_LEVEL like '%ICX%';
applying opatch without inventory
opatch apply -no_inventory
$ORACLE_HOME/cfgtoollogs/opatch/opatch-2009_Oct_29_22-33-37-CST_Thu.log --> opatch log location
To find opatch version:
/OPatch/
./opatch version
opatch options:
$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164
opatch apply -local: apply patch only on local node in clusterdatabase environment
opatch apply -jdktop : if opatch could not find the path of jdk
opatch apply -no_inventory : Apply patch without updating inventory
to find the opatch that are installed
cd /OPatch/.patch_storage
OR cd /unioac/oracle/product/920/OPatch/
./opatch lsinventory
If it fails please check the path is correct in /etc/oraInst.loc or /var/opt/oracle/oraInst.loc
To know which services are running on what nodes:
select SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN from fnd_nodes;
To Know All the Drivers (NLS) language applied to Instance?col PATCH_NAME format a10
col PATCH_TYPE format a10
col DRIVER_FILE_NAME format a15
col PLATFORM format a10
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE, AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
and AP.PATCH_NAME = '&No';"
select aap.patch_name,count(*) from AD_patch_driver_langs apdl, ad_applied_patches aap, AD_PATCH_DRIVERS apd
where apdl.patch_driver_id=apd.patch_driver_id and
aap.applied_patch_id=apd.applied_patch_id and
apdl.language <>'US'
group by aap.patch_name
having count(*) > 0 and count(*)<10 -- Changed from 9 to 10
order by patch_name"
To see NLS patches applied:
select language,driver_file_name from AD_PATCH_DRIVERS adp,AD_PATCH_DRIVER_LANGS adpl where adp.patch_driver_id = adpl.patch_driver_id
and driver_file_name like '%&a%' order by 1;
Script for Patch Log Analysis :
@$AD_TOP/patch/115/sql/adphrept.sql 1 ALL ALL 03/01/2004 07/05/2004 ALL ALL ALL ALL ALL N N N N N sample.txt
To knowd all the patdhes applied from 01-Sep-2005 to 28-Jan-2006. i.e b/w 2 dates use
$AD_TOP/patch/115/sql/adpchlst.sql
To check to make sure the correct data was installed run the following script this script can also be used to check if data installer was run successfully:
select application_short_name, Legislation_code, status, action, last_update_date
from hr_legislation_installations
where application_short_name in ('PER','PAY');
To check if DB version is 32 or 64 bit:
a.) conn to sqlplus if it is 64 ,then will show
b.)select address from v$sql where rownum<2;
c.)go to ORACLE_HOME/bin
do a file oracle.
Commands useful during Patch analysis
select bug_number from ad_bugs where bug_number='&t';
select to_char(CREATION_DATE,'dd-mon-yyyy hh24:mi:ss') from ad_bugs where bug_number=’&t’;
select to_char(LAST_UPDATE_DATE,'dd-mon-yyyy hh24:mi:ss')from ad_bugs where bug_number='&t’;
select patch_level from fnd_product_installations where patch_level like '&p';
select release_name from fnd_product_groups;
select DRIVER_FILE_NAME from ad_patch_drivers where DRIVER_FILE_NAME like '%3117672%';
For querying the MERGED PATCHES you can use the following script which will show which merged patches are applied for which language
select a.PATCH_DRIVER_ID,DRIVER_FILE_NAME,c.bug_id,d.language
from ad_patch_drivers a,AD_COMPRISING_PATCHES b, ad_bugs
c,AD_PATCH_DRIVER_LANGS d
where c.bug_number = '&no'
and c.bug_id = b.bug_id
and a.PATCH_DRIVER_ID = b.patch_driver_id
and a.patch_driver_id = d.patch_driver_id;
we can querry the ad_bugs for the US language version patches
To check if specific bug fix is applied, you need to query the AD_BUGS table only. This table contains all patches and all superseded patches ever applied:select ab.bug_number, ab.creation_date
from ad_bugs ab
where ab.bug_number = '&BugNumber';
Retrieve basic information regarding patch applied, useful when you need to know when and where (node) you applied specific patch:select aap.patch_name, aat.name, apr.end_date
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '&PatchName';
Run the following query, it will show you all modules affected by specific patch in one click :select distinct aprb.application_short_name as "Affected Modules"
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_patch_run_bugs aprb
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and apr.patch_run_id = aprb.patch_run_id
and aprb.applied_flag = 'Y'
and aap.patch_name = '&PatchName';
One of the ways to find out the exact patchset that was applied to your database successfully, is you can query from props$ table. This table is owner by sys. Logon as system or sys and select from props$ table. This table has fields like name, values and comments. The name columne NLS_RDBMS_VERSION has the value equilent to the patchset applied to that database.
SQL> select name, value$ from props$;
NAME VALUE$
NLS_RDBMS_VERSION 7.3.4.3.1
Query to find languages installed or not:Select distinct NLS_LANGUAGE, LANGUAGE_CODE,NLS_TERRITORY,INSTALLED_FLAG
from fnd_languages
where INSTALLED_FLAG = 'I' or INSTALLED_FLAG = 'B'
ORDER BY NLS_LANGUAGE
Select distinct NLS_LANGUAGE, LANGUAGE_CODE,NLS_TERRITORY,INSTALLED_FLAG
from fnd_languages
ORDER BY NLS_LANGUAGE
To find localization patches are applied.select * from jai_applied_patches where patch_number = 123456;
How to check whether the product is install,shared and Not installed in Apps.
select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I',’Fully Installed’,
‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;
/* To find the latest application version */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 AD_RELEASES where END_DATE_ACTIVE IS NULL
/* 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
