Oracle E-Business Suite: Recommended SQL Scripts To Provide For Oracle Support and Environment Information 12.2.* - EBS 12.2.4 ile ilgili gerekli scriptler

Running the Diagnostic Patch:

The main thing that Oracle wants to analyze the system is the diagnostic pack run which comes with a patch. That is Patch 19045166. In it there are some sql's which needs to be run and that is applied by running a perl script which uses the contents of the Patch:

" perl adzddiagnostic.pl "

Determining Code Levels:

Ad and txk code levels are needed, because a lot of patch is associated with the code levels of ad and txk. There are lots of problems regarding these statuses.

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

Collecting Fusion Middleware Log Files:

One of the most importan log files are the fusion middleware log files, since the application is run on the Weblogic Server. /Reference 1)

Therefore we need to collect them in case of a failure to both review and supply them to Oracle Support. The following 4 script should be run individually and created zip files can be added to Oracle.

--------------

1-Opmn OHS Log Files:

. /u01/install/APPS/EBSApps RUN

zip -r /home/applmgr/`uname -n`_`date +%m%d%y.%H%M`_OPMN_OHS.zip $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*log* $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/*

2-Weblogic Server:

zip -r /home/applmgr/`uname -n`_`date +%m%d%y.%H%M`_FMW.zip $IAS_ORACLE_HOME/../wlserver_10.3/common/nodemanager $EBS_DOMAIN_HOME/servers/oa*/logs/*out* $EBS_DOMAIN_HOME/servers/oa*/logs/*log* $EBS_DOMAIN_HOME/servers/forms*/logs/*out* $EBS_DOMAIN_HOME/servers/forms*/logs/*log* $EBS_DOMAIN_HOME/servers/AdminServer/logs/*out* $EBS_DOMAIN_HOME/servers/AdminServer/logs/*log* $EBS_DOMAIN_HOME/sysman/log/* $EBS_DOMAIN_HOME/servers/oac*/adr/diag/ofm/EBS_domain_*/oac*/incident/* $EBS_DOMAIN_HOME/servers/forms_s*/adr/diag/ofm/EBS_domain_*/forms_s*/incident/*



3-Apache WLS Log Files:

. /u01/install/APPS/EBSApps.env RUN

#####################################################################################

#### Start of script

#####################################################################################

(

# pick up files which have been modified in the last 1 day only

HowManyDaysOld=3

echo "Picking up files which have been modified in the last ${HowManyDaysOld} days"

set -x

find $LOG_HOME -type f -mtime -${HowManyDaysOld} > m.tmp

find $FMW_HOME/webtier/instances/*/diagnostics/logs -type f -mtime -${HowManyDaysOld} >> m.tmp

find $FMW_HOME/wlserver_10.3/common/nodemanager/nmHome*/*.log -type f -mtime -${HowManyDaysOld} >> m.tmp

## Get log files for only the WLS servers needed. Valid server names are one or more of:

## AdminServer forms-c4ws_server forms_server oacore_server oaea_server oafm_server

for SERVERNAME in AdminServer oacore_server forms_server oafm_server

do

find $EBS_DOMAIN_HOME/servers/${SERVERNAME}*/logs -type f -mtime -${HowManyDaysOld} >> m.tmp

find $EBS_DOMAIN_HOME/servers/${SERVERNAME}*/adr/diag/ofm/EBS_domain_*/${SERVERNAME}*/incident -type f -mtime -${HowManyDaysOld} >> m.tmp

done

zip -r mzAppsLogFiles_`hostname`_`date '+%m%d%y'`.zip -@ < m.tmp

rm m.tmp

) 2>&1 | tee mzLogZip.out

#####################################################################################


#### End of script



4- Inventory Related Logs and Reports

. /u01/install/APPS/EBSApps.env RUN

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass= -outfile=/home/applmgr/Report_App_Inventory.html



zip -r /home/applmgr/node_info.rar /u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/applications/oafm/APP-INF/node_info.txt /u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/APP-INF/node_info.txt /u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/applications/forms-c4ws/APP-INF/node_info.txt /u01/install/APPS/fs1/FMW_Home/Oracle_EBS-app1/applications/forms/APP-INF/node_info.txt

zip -r /home/applmgr/EBS_DOMAIN_Config.rar

$EBS_DOMAIN_HOME/config/config.xml



zip -r /home/applmgr/Property.rar

$INST_TOP/appl/admin/*.properties

zip -r /home/applmgr/OHS_Logs.rar $IAS_ORACLE_HOME/instances/EBS_web_IKYSDEV_OHS1/config/OHS/EBS_web_IKYSDEV/*

--------------

Running SQL's :

What are the SQL's used below? They are providing us a lot of information regarding the configuration of the system, patching status, node information and lots of other stuff. These information can be used when patching is needed and to determine the status of the system or before applying a particular solution.


The code below can be put within a sql script and run.

---

set markup html on 
spool adop_sessions.html 
set timing on 
set linesize 120 
set pagesize 300 
show user 

select * from AD_NODES_CONFIG_STATUS; 

select NODENAME,CTX_FILE_CHANGED from AD_NODES_CONFIG_STATUS; 

select * from FND_OAM_CONTEXT_FILES; 

Select node_name from FND_OAM_CONTEXT_FILES 
where NAME not in ('TEMPLATE','METADATA','config.txt') and 
CTX_TYPE='A' and (status is null or upper(status) in ('S','F')) 
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'run' 
and EXTRACTVALUE(XMLType(TEXT),'//oa_service_group_status[@oa_var=''s_web_admin_status'']')='enabled' 
and EXTRACTVALUE(XMLType(TEXT),'//oa_service_list/oa_service[@type=''admin_server'']/oa_service_status')='enabled'; 

SELECT abbreviation, codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad'); 

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad'); 

select bug_number from ad_bugs where bug_number in ('18288881','18283295','18886213','19259764'); 

select object_name, object_type, status 
from user_objects 
where status = 'INVALID' 
order by object_type; 

SELECT release_name FROM fnd_product_groups; 

select * from FND_NODES; 

select node_name, platform_code, node_mode, node_id, 
server_id, server_address, host, domain, webhost, 
support_cp, support_forms, support_web, support_admin 
from fnd_nodes 
order by node_name ; 

select node_name "Node Name", node_mode "Mode", support_cp "C", 
support_web "W", support_admin "A", support_forms "F" 
from FND_NODES; 

select bug_number from ad_bugs where bug_number in ('19782999', '19494816', '19807163'); 

select * from AD_APPL_TOPS; 

select APPL_TOP_ID, NAME,APPL_TOP_TYPE from AD_APPL_TOPS; 

select * from AD_ADOP_SESSIONS; 

select adop_session_id, APPLTOP_ID, NODE_NAME, NODE_TYPE from AD_ADOP_SESSIONS ;

 select ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS, 
CLEANUP_STATUS,ABORT_STATUS,STATUS,ABANDON_FLAG,NODE_NAME 
from AD_ADOP_SESSIONS 
order by ADOP_SESSION_ID; 

select ADOP_SESSION_ID, EDITION_NAME, NODE_TYPE, NODE_NAME, APPLTOP_ID, PREPARE_STATUS, APPLY_STATUS, CUTOVER_STATUS, CLEANUP_STATUS, ABORT_STATUS, STATUS from AD_ADOP_SESSIONS order by ADOP_SESSION_ID; 

select * from AD_ADOP_SESSION_PATCHES; 
select ADOP_SESSION_ID,BUG_NUMBER,CLONE_STATUS,STATUS,NODE_NAME from AD_ADOP_SESSION_PATCHES order by ADOP_SESSION_ID; 

set linesize 132 
set pagesize 132 
col NAME format A40 
col LEVEL_SET format a15 
col CONTEXT format a20 
col VALUE format A20 wrap 
col Server format a10 
col resp format a8 wrap 
col application format a10 

break on NAME 

select 
'('||language||')-'||n.user_profile_option_name NAME, 
decode(v.level_id, 
10001, 'Site', 
10002, 'Application', 
10003, 'Responsibility', 
10004, 'User', 
10005, 'Server', 
10006, 'Organization', 
10007, 'ServResp', 
'Undefined') LEVEL_SET, 
decode(to_char(v.level_id), 
'10001', '', 
'10002', app.application_short_name, 
'10003', rsp.responsibility_key, 
'10004', usr.user_name, 
'10005', svr.node_name, 
'10006', org.name, 
'10007', (select n.node_name 
from fnd_nodes n 
where n.node_id=level_value2) 
||'/'|| 
(decode(v.level_value, 
-1,'Default', 
(select responsibility_key 
from fnd_responsibility 
where responsibility_id=level_value))), 
v.level_id) "CONTEXT", 
v.profile_option_value VALUE 
from 
fnd_profile_options p, 
fnd_profile_option_values v, 
fnd_profile_options_tl n, 
fnd_user usr, 
fnd_application app, 
fnd_responsibility rsp, 
fnd_nodes svr, 
hr_operating_units org 
where 
p.profile_option_id = v.profile_option_id (+) 
and p.profile_option_name = n.profile_option_name 
and usr.user_id (+) = v.level_value 
and rsp.application_id (+) = v.level_value_application_id 
and rsp.responsibility_id (+) = v.level_value 
and app.application_id (+) = v.level_value 
and svr.node_id (+) = v.level_value 
and org.organization_id (+) = v.level_value 
order by name, v.level_id; 

set pagesize 300 
set linesize 200 

col NAME format a20 
col HOST format a35 
col RELEASE format a15 
col VERSION format a15 

select i.instance_name NAME, i.host_name HOST, f.release_name RELEASE, i.version VERSION 
from v$instance i,fnd_product_groups f 
where upper(substr(i.instance_name,1,4)) = upper(substr(f.applications_system_name,1,4)); 

select ad_zd.get_edition('RUN') from dual; 
select ad_zd.get_edition('PATCH') from dual; 
select ad_zd.get_edition_type from dual; 

set timing off 
set markup html off 
spool off 

-----------------------

References:

1-12.2 Ebusiness Suite - Collecting Fusion Middleware Log Files (Doc ID 1362900.1)

2-Oracle Applications E-Business Suite 12.2 Fusion Middleware Log Files: Locate,View, and Control (Doc ID 1366187.1)