EBS Basic Sql Scripts - Application

Database Information

set pages 999 set linesize 160 col HOST_NAME format a40 col PLATFORM_NAME for a30 select INSTANCE_NAME,HOST_NAME,VERSION,STATUS,LOGINS,DATABASE_STATUS from gv$instance; select NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME from gv$database;

APPS Node Info

set lines 160 set pages 300 col PLATFORM_CODE for a15 col VIRTUAL_IP for a30 select NODE_NAME "Node Name", to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') "Creation Date", decode(STATUS,'Y','ACTIVE','INACTIVE') Status, decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr, decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms, decode(SUPPORT_WEB,'Y','Web', 'No') WebServer, decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin, decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database, PLATFORM_CODE, VIRTUAL_IP from apps.fnd_nodes where node_name != 'AUTHENTICATION';

Application version

select RELEASE_NAME from fnd_product_groups;

APPLICATION URL

select home_url from apps.icx_parameters;

languages installed

select NLS_LANGUAGE, INSTALLED_FLAG,LANGUAGE_CODE from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');

Shared appl_top or not

select SHARED, APPL_TOP_GUID,PATH from apps.fnd_appl_tops;

find MRC Enabled ??

select multi_currency_flag from fnd_product_groups;

find Multi-org enabled?

select multi_org_flag from fnd_product_groups;

Workflow Version

select TEXT from WF_RESOURCES where NAME='WF_VERSION';

workflow mailer status

select COMPONENT_NAME,COMPONENT_STATUS from FND_SVC_COMPONENTS where component_id = '10006';

workflow COMPONENT status

set lines 160 select COMPONENT_NAME,COMPONENT_STATUS from FND_SVC_COMPONENTS;

to findout the MODULE/PRODUCT is installed or not ?

col product for a10 col status for a15 col product_version for a10 col patchset for a30 col update_date for a15 select decode(nvl(a.APPLICATION_short_name,'Not Found'),'SQLAP','AP','SQLGL','GL','OFA','FA', 'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) Product, decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status, fpi.product_version, nvl(fpi.patch_level,'-- Not Available --') Patchset, to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update_Date" from apps.fnd_oracle_userid o, apps.fnd_application a, apps.fnd_product_installations fpi where fpi.application_id = a.application_id(+) and fpi.oracle_id = o.oracle_id(+) order by 2,1 /

Patching querys

select bug_number creation_date from ad_bugs where bug_number in ( '6272107', '6272107', '6272107', '6272107', '6272107', '6272107'); select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where BUG_NUMBER like '&patchno'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date between '11-FEB-11' AND '12-FEB-11'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date like '%11-FEB-11%'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where bug_number like '598704%'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date>'11-FEB-11'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date<'11-FEB-11'; select PATCH_NAME,PATCH_TYPE,CREATION_DATE from AD_APPLIED_PATCHES where CREATION_DATE LIKE '%12-FEB-11%';

what are all the nodes patch has been applied

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

What drivers are applied while applying patch??

select apd.driver_file_name, apd.DRIVER_TYPE_C_FLAG "C", apd.DRIVER_TYPE_D_FLAG "D", apd.DRIVER_TYPE_G_FLAG "G" , apdl.language from ad_patch_drivers apd, ad_patch_driver_langs apdl where apd.patch_driver_id=apdl.patch_driver_id and apd.driver_file_name like '%&a%';

distributed ad

on node 1 give the below command adpatch options=hotpatch workers=48 localworkers=24 on node 2 give the below command adctrl distributed=y

admerge

admrgpch -s /cfsdshr/ccld3/appccld3/temp/patches/oem/9444535_long -d /cfsdshr/ccld3/appccld3/temp/patches/oem/9444535_long/dest

Apache version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

perl version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built

Java version

$AFJVAPRG -version

Client JRE version

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

PL/SQL Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication Mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=