Oracle APPS Releated Queries

Oracle APPS Releated Queries

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