#--
#-- Program : Apps Health Checks
#--
#-- Description : oracle_apps_full_report.sh
#--
#-- Purpose : Run a variety of Oracle Apps Health Checks.
#-- Warning section at beginning should usually return no rows.
#--
#-- Created By : oracle-latest-technology.blogspot.com
#--
#-- Creation Date : Dec 19, 2011
#--
#-- Change History :
#-- ............................................................
#--
#-- ...........................................................................
dt=`date +"%d.%m.%y"`
./_.env
sqlplus -s <<!
apps/
SET linesize 80
set pagesize 500
SET space 1
#COLUMN db_name new_value dbname noprint
#COLUMN today new_value runtime noprint
spool/apps_health_checkup_$dt.txt
SELECT name db_name FROM v\$database;
SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI') today FROM dual;
SET pagesize 1000
COLUMN owner FORMAT a8
COLUMN object_type FORMAT a12
COLUMN object_name FORMAT a30
COLUMN created FORMAT a9
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
--
-- Health Check Logic starts Here
--
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ Warning Section: Should see no rows in this section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
COLUMN obj_name FORMAT a18
COLUMN own FORMAT a5
-- General Warnings:
PROMPT Confirm not in Maintenance Mode - should see no rows
SELECT
pot.user_profile_option_name "Profile Name"
,v.PROFILE_OPTION_VALUE "Profile Value"
,v.LEVEL_ID "Level ID"
,v.LAST_UPDATE_DATE "Last Update Date"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 10001) = 10001
AND pot.user_profile_option_name = 'Applications Maintenance Mode'
AND v.profile_option_value != 'NORMAL';
/*PROMPT Warn login accounts that should not become locked out like SYSADMIN
PROMPT
COLUMN user_name FORMAT a25
COLUMN creation_date FORMAT a30
COLUMN last_logon_date FORMAT a30
COLUMN password_date FORMAT a3
SELECT user_name, creation_date
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
--, ENCRYPTED_USER_PASSWORD
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
FROM apps.fnd_user
WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'
AND END_date IS NULL
AND USER_NAME IN ('SYSADMIN')
ORDER BY user_name;*/
/*
PROMPT User Security AND Access Issues section:
PROMPT Show any logins whose password is NOT SET to expire in 60 days
COLUMN user_name FORMAT a20
COLUMN encrypted_password FORMAT a20
SELECT user_id "User ID"
, user_name "User Name"
, password_lifespan_days "Password Lifespan Days"
FROM apps.fnd_user
WHERE end_date IS NULL
AND nvl(password_lifespan_days, 0) != 60
AND user_id > 1051;
*/
PROMPT Non-DBAs who have System Administrator or other powerful Responsibilities
COLUMN user_name FORMAT a10
COLUMN description FORMAT a20
COLUMN responsibility_name FORMAT a25
COLUMN end_date FORMAT a20
SELECT fu.user_name "User Name"
,fu.description "Description"
,substr(fr.responsibility_name,1,35) "Responsibility"
,fur.end_date "End Date"
FROM
apps.fnd_user fu,
apps.fnd_user_resp_groups_direct fur,
apps.fnd_responsibility_vl fr,
apps.fnd_application_vl fa
WHERE
fur.user_id = fu.user_id
AND fu.user_name NOT IN ('YOUR_DBA_NAME','SYSADMIN')
AND fur.responsibility_application_id = fa.application_id
AND fur.responsibility_application_id = fr.application_id
AND fur.responsibility_id = fr.responsibility_id
AND fu.end_date is null -- only show active users
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment
AND fr.responsibility_name in ('System Administrator'
,'Application Developer'
,'Application Developer Common Modules'
,'CRM HTML Administration'
,'FICO Sys Admin Conversion'
,'FICO System Administration GUI'
,'Functional Administrator' -- can chg Profiles
,'Functional Developer'
,'Knowledge Administrator' --use FICO instead
,'Oracle Sales Administrator' --use FICO instead
,'Sales Administrator' --use FICO instead
,'SFM System Administrator'
,'TeleSales Administration' --use FICO instead
)
ORDER BY 1;
COL emp_num format a7
COL per_serv_id format 999999
-- Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number "Emp No."
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') "Termination Date"
, period_of_service_id "Period Serv ID"
FROM apps.fnd_user fu
, apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND papf.person_id = ppos.person_id
AND fu.end_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
FROM per_periods_of_service
WHERE person_id = papf.person_id)
AND ppos.actual_termination_date < TRUNC(SYSDATE);
PROMPT Show users whose Responsibility roles have early end date in future
-- To remove, Use Define User Form toggle fnd_user end_date by
-- adding end_date, commit, remove user end_date, commit. Some time lag
-- Remove this logic when Oracle no longer end dates resp roles early
COLUMN user_name FORMAT a25
COLUMN user_end_date FORMAT a20
select distinct user_name "User Name"
, user_end_date "End Date"
from apps.wf_local_user_roles
where user_end_date >= sysdate -- in future
and user_end_date <= sysdate + 700 -- within next week
and user_orig_system != 'WF_LOCAL_USERS'
and to_char(user_end_date,'DD-MON-YYYY') != '31-DEC-4712'
order by user_end_date, user_name;
-- Duplicate email addresses have security issues with workflow notifications
-- Two people should not have same email address since one user can get
-- another user's email for instance when doing password reset
COL email_address FORMAT a50
PROMPT Show fnd_users who have same email address
SELECT user_name "User Name"
, email_address "Email Address"
FROM fnd_user WHERE email_address IN (
SELECT
email_address
FROM apps.fnd_user
WHERE email_address is not NULL
AND email_address != 'employee@.com'
AND end_date is null
GROUP BY
email_address
HAVING count(*) > 1);
PROMPT Show active fnd_users tied to the same HR employee (shows person_id)
SELECT
fu.employee_id "Employee ID"
, count(*) "Count"
FROM apps.fnd_user fu
WHERE fu.employee_id is not null -- this is person_id in per_all_people_f
AND fu.end_date is null
GROUP BY fu.employee_id
HAVING count(*) > 1;
PROMPT Workflow Section:
PROMPT
--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Users whose mail preference is 'do not send' since causes WF notif issues
SELECT fu.user_name
FROM apps.fnd_user_preferences fup
, apps.fnd_user fu
WHERE fup.preference_name = 'MAILTYPE'
AND fup.preference_value = 'QUERY'
AND fup.user_name = fu.user_name
AND fu.end_date IS NULL
ORDER BY fu.user_name;
PROMPT Show any wf adhoc roles that have wrong notification_preference
SELECT notification_preference "Notification Preference"
, COUNT(*) "Count"
FROM apps.wf_local_roles
WHERE orig_system /*= 'WF_LOCAL_ROLES'*/in ('FND_USR','PER')
AND notification_preference NOT IN ('MAILHTML', 'MAILHTM2')
GROUP BY notification_preference;
PROMPT Show if workflow mailer setting PROCESSOR_READ_TIMEOUT_CLOSE has changed
PROMPT to something other than Y. Should not see any rows
SELECT p.parameter_name
, v.last_update_date
, u.user_name
FROM apps.fnd_svc_comp_param_vals v
, apps.fnd_svc_comp_params_b p
, apps.fnd_svc_components c
, apps.fnd_user u
WHERE c.component_type = 'WF_MAILER'
AND c.component_id = v.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
AND v.parameter_value <> 'Y'
AND v.last_updated_by = u.user_id;
PROMPT Profile Check Section:
PROMPT
-- Confirm Examine function is password protected in Production
-- If profile is Y can use Examine without password PROMPT so unprotected
PROMPT Profile 'Utilities:Diagnostics' for Examine set to unprotected
SELECT
v.profile_option_id "Profile Option ID"
, pot.user_profile_option_name "Prof Name"
, substr(v.profile_option_value, 1,5) "Prof Value"
, v.level_id "Level ID"
, v.level_value "Level Value"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N' -- N means password protected
ORDER BY pot.user_profile_option_name;
PROMPT User Level 'Utilities:Diagnostics' Profile Settings for Examine
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level Profile
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N'
order by pot.user_profile_option_name;
-- List Profile options for personalization related settings
--
PROMPT User level profile for Personalization related Profiles
SELECT
user_name
, pot.user_profile_option_name prof_name
, v.profile_option_value prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND pot.user_profile_option_name in
(
'AMS : Oracle Personalization Enabled'
,'Create Seeded Personalizations'
,'FND: Personalization Region Link Enabled'
,'FND: Personalization Seeding Mode'
,'HR: Enable User Personalization'
,'OSO : Enable Admin Personalization Feature'
,'Personalize Self-Service Defn'
)
ORDER BY
pot.user_profile_option_name
, user_name;
PROMPT Find Personalization related profiles at other levels
PROMPT
PROMPT *** Site level ***
select t.user_profile_option_name, profile_option_value
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and level_id=10001
and (upper(t.user_profile_option_name) like '%PERSONALIZ%'
and upper(t.user_profile_option_name) not like '%PERSONALIZ%WYSIWYG%'
)
order by user_profile_option_name;
PROMPT
PROMPT *** Application level ***
select t.user_profile_option_name, profile_option_value, application_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_application_tl a
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and a.application_id = level_value
and level_id=10002
and t.language = a.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, application_name;
PROMPT
PROMPT *** Responsibility level ***
select t.user_profile_option_name, profile_option_value,
responsibility_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_responsibility_tl r
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and responsibility_id = level_value
and level_id=10003
and t.language = r.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, responsibility_name;
COLUMN prof_value on heading 'Prof|value' FORMAT a5
PROMPT Confirm self service diagnostics are not turned on
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'FND%Diag%'
AND nvl(v.level_id, 1001) != 10004 -- skip user level options
ANd v.profile_option_value != 'N' -- when Y then can use ss diagnostics
order by pot.user_profile_option_name;
PROMPT Confirm only DBA and sysadmin users have access to SS Diagnostics
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level options
AND fu.user_name NOT IN ('YOUR_DBA_NAME', 'SYSADMIN')
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'FND%Diag%'
order by pot.user_profile_option_name;
PROMPT Look for Password Profiles set at user level
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, fu.user_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like '%Password%'
order by pot.user_profile_option_name;
-- End User Security Warnings
PROMPT Warnings about Concurrent Programs and Concurrent Manager
column user_concurrent_program_name on heading Program format a32 trunc
column concurrent_program_name on heading Program format a10 trunc
column description on heading Description format a35 trunc
--column user_name format a10
PROMPT Show any requests by SYSADMIN that ended in error in last 5 days
PROMPT
select request_id "Request ID"
/*,fu.user_name User_Name*/
,to_char(cr.actual_start_date, 'DD HH24:MI') "When Started"
,to_char(cr.actual_completion_date, 'DD HH24:MI') "When Ended"
,cp.concurrent_program_name "Program Name"
,phase_code "Phase Code"
,status_code "Status Code"
from
apps.fnd_concurrent_programs cp,
apps.fnd_user fu,
apps.fnd_concurrent_requests cr
where
cr.concurrent_program_id = cp.concurrent_program_id
and cr.program_application_id = cp.application_id
and cr.actual_completion_date is not null
and cr.requested_by = fu.user_id
and fu.user_name = 'SYSADMIN'
and cr.status_code = 'E' -- requests that ended in error
and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently
order by cr.actual_start_date DESC;
PROMPT Show conc programs that have trace enabled. Normally should be none
PROMPT
-- To Turn off: Go to Define Con Pgm form and uncheck the Enable Trace field
col prog_name format a35
col enable_trace format a15
SELECT concurrent_program_name "Prog Name"
, enable_trace "Trace Enabled"
, last_update_date "Last Update Date"
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y';
-- Confirm Alert Periodic Scheduler is pending so that alerts will run
PROMPT Confirm Alert Periodic Scheduler is okay, should see no rows
select 'PROBLEM: No pending Alert Periodic Scheduler request' "Message" from dual
where not exists (
select
cp.concurrent_program_name Program,
cr.request_id Request,
phase_code S,
status_code C,
cr.argument1||', '||
cr.argument2||', '||
cr.argument3||', '||
cr.argument4||', '||
cr.argument5||', '||
cr.argument6||', '||
cr.argument7||', '||
cr.argument8 Arguments
from
apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_programs cp
where
cr.concurrent_program_id = cp.concurrent_program_id
and cp.application_id >= 0 -- force on index leading edge
and cr.program_application_id = cp.application_id
and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm
and cr.phase_code = 'P'
and cr.status_code != 'H' -- not on hold
);
-- End Con Program section
/*-- Recent changes to objects with Custom Direct Changes - Research
-- Add standard Apps objects we Customized and will be lost when patched
PROMPT Recent changes to objects with Custom Direct Changes - usually None
PROMPT
PROMPT Any ADP views that have been changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name like Upper('%ADP%')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any changes to iExpense pkg where we have custom direct chg
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name = 'AP_WEB_DB_HR_INT_PKG'
AND owner = 'APPS'
AND object_type in ('PACKAGE SPEC', 'PACKAGE')
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any OTL/iExpense views changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name in ('AP_WEB_PA_PROJECTS_V')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT PA Gen Draft Rev parm we want disabled
col short_name format a10
col executable_name format a10
col parameter format a10
SELECT SUBSTR(fcp.user_concurrent_program_name,1,60) concurrent_program_name
, fcp.concurrent_program_name short_name
, SUBSTR(fe.executable_name,1,25) executable_name
, SUBSTR(fl_em.meaning,1,18) execution_method
, LPAD(TO_CHAR(fdfcu.column_seq_num,'fm990'),4) " SEQ"
, fdfcu.end_user_column_name parameter
, RPAD(fdfcu.enabled_flag,8) "ENABLED?"
, SUBSTR(ffvs.flex_value_set_name,1,45) validation_value_set
, SUBSTR(ffvs.description,1,75) value_set_description
, SUBSTR(fl_dt.meaning,1,13) default_type
, RPAD(fdfcu.required_flag,5) "REQD?"
-- If the default value is populated and more than 80 chars
-- then trim it to 80 for purposes of this report.
, RPAD(DECODE(SIGN(NVL(LENGTH(fdfcu.default_value),0) - 80)
, +1, SUBSTR(fdfcu.default_value,1,80) || '...'
, fdfcu.default_value),83) default_value
, RPAD(fdfcu.display_flag,10) "DISPLAYED?"
FROM apps.fnd_application fa
, apps.fnd_application_tl fat
, apps.fnd_concurrent_programs_vl fcp
, apps.fnd_executables fe
, apps.fnd_descr_flex_column_usages fdfcu
, apps.fnd_descr_flex_col_usage_tl fdfcut
, apps.fnd_flex_value_sets ffvs
, apps.fnd_lookups fl_dt
, apps.fnd_lookups fl_em
WHERE USERENV('LANG') = fat.language
AND fat.application_id = fa.application_id
AND fa.application_id = fcp.application_id
AND fcp.concurrent_program_name = 'PARGDR' -- PA program
AND fdfcu.end_user_column_name = 'Release Draft Revenue' -- this parameter
AND fdfcu.enabled_flag = 'Y' -- has become enabled when it should not
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcp.application_id = fdfcu.application_id
AND '$SRS$.'
|| fcp.concurrent_program_name = fdfcu.descriptive_flexfield_name
AND fdfcu.application_id = fdfcut.application_id
AND fdfcu.descriptive_flexfield_name = fdfcut.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = fdfcut.descriptive_flex_context_code
AND fdfcu.application_column_name = fdfcut.application_column_name
AND USERENV('LANG') = fdfcut.language
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fdfcu.default_type = fl_dt.lookup_code (+)
AND 'FLEX_DEFAULT_TYPE' = fl_dt.lookup_type (+)
AND fcp.execution_method_code = fl_em.lookup_code (+)
AND 'CP_EXECUTION_METHOD_CODE' = fl_em.lookup_type (+)
ORDER BY fcp.user_concurrent_program_name
, fdfcu.column_seq_num;
-- End Custom Direct Changes Section*/
/*PROMPT Confirm selected Applimation DB triggers that cause problems are off
select count(*)
from dba_triggers
where trigger_name like 'AM_WF_LOCAL_USER_ROLES%'
and status != 'DISABLED' -- these triggers cause severe perf problems when on
/*/
-- Std RDBMS Checks Section
PROMPT Watch for HighWaterMark -- higher than 1000 since may need to incr processes
PROMPT
SELECT V.SESSIONS_CURRENT "Current Sessions",
V.SESSIONS_HIGHWATER "HighWater Sessions",
V.CPU_COUNT_CURRENT "CPU Current Count",
V.CPU_COUNT_HIGHWATER "CPU Count HighWater"
FROM V\$LICENSE V;
--where SESSIONS_HIGHWATER > 100
-- Check for datafiles that have autoextended to their max so stuck
column bytes format 999999999
column file_name format a35
column TSPACE format a12
prompt
prompt Datafiles with autoextend ON that have reached their max so have got STUCK
select tablespace_name "Tablespace"
, file_name "Filename"
, bytes/1048576 "Size"
, maxbytes / 1048576 "Maxsize(MB)" -- convert to MB
from dba_data_files
where autoextensible = 'YES'
and bytes >= maxbytes -- datafile already extended to max
order by tablespace_name;
-- DB Security Warnings:
-- Check for any locked db user accounts
COLUMN username FORMAT a14
COLUMN default_tablespace heading 'DEFAULT TS' FORMAT a10
COLUMN temporary_tablespace heading 'TEMP' FORMAT a4
COLUMN profile FORMAT a13
COLUMN account_status heading 'ACCT|STAT' FORMAT a6 trunc
PROMPT List Locked Oracle schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE lock_date IS NOT NULL
AND account_status NOT LIKE 'EXPIRED%';
PROMPT Check whether UNDO datafiles have autoextended
PROMPT May want to resize datafiles down lower after big AutoExtend
COLUMN sname FORMAT a12 heading 'Tablespace' justify c
COLUMN ssize FORMAT 999,999.99 heading 'Mb|Total' justify c
COLUMN extents FORMAT 9,999 heading 'Free|Exts' justify c
COLUMN mbytes FORMAT 9,999.99 heading 'Largest|Free Ext' justify c
COLUMN tbytes FORMAT 999,999.99 heading 'Mb|Avail' justify c
COLUMN mused FORMAT 999,999.99 heading 'Mb|Used' justify c
COLUMN pct FORMAT 990.99 heading 'Percent|Free' justify c
SELECT
total.tablespace_name sname,
COUNT(free.bytes) extents,
max(free.bytes)/1048576 mbytes,
total.ts_size/1048576 ssize,
(total.ts_size/1048576) - (sum(free.bytes/1048576)) mused,
sum(free.bytes)/1048576 tbytes,
sum(free.bytes)/total.ts_size * 100 pct
FROM
dba_free_space free
,(SELECT tablespace_name,
sum(bytes) ts_size
FROM dba_data_files
GROUP BY tablespace_name)
total
WHERE
total.tablespace_name = free.tablespace_name (+)
AND total.tablespace_name = 'APPS_UNDOTS1'
AND total.ts_size/1048576 > 8600 -- greater than 8600 MB then autoextended
GROUP BY
total.tablespace_name,
total.ts_size;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT END Warning Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT Begin General Health Checks Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
-- Revise prompt as persistent invalid objects increase or reduce
-- See SharePoint Invalid Object Log for details about these objects
PROMPT Invalid APPS objects, at least 6: FND_OID_DIAG FND_TS_SIZE and 4 MRP_
COLUMN object_name FORMAT a30
SELECT
owner, object_name, object_type
, to_char(created, 'DD-MON-YY') created
, status
FROM dba_objects
WHERE Owner = 'APPS'
AND status = 'INVALID'
ORDER BY object_name;
-- Add any custom schemas here
PROMPT Invalid objects owned by custom schema XXCUSTOM Account
SELECT
owner, object_name, object_type, created, status
FROM dba_objects
WHERE Owner = 'XXCUSTOM'
AND status = 'INVALID'
ORDER BY object_name;
-- RDBMS Checks
PROMPT Check SYS audit table to determine number of audit rows
SELECT
COUNT(*)
FROM sys.aud$;
PROMPT List Expired schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE /*lock_date is not null
AND */ account_status != 'OPEN';
PROMPT Usage Checks Section:
-- Check highest DB connections COUNT since last DB start
-- Count variety of current Oracle connections
PROMPT Current Oracle Connection Characteristics including highwater mark
COLUMN formsusers HEADING "Forms connects" FORMAT 99999
COLUMN selfservusers HEADING "Self Service" FORMAT 99999
COLUMN rundatetime HEADING "TIME OF DAY" FORMAT A16
COLUMN currsession HEADING "Current Sess" FORMAT 99999
COLUMN sesshighwater HEADING "High Water" FORMAT 99999
COLUMN jdbcthinclient HEADING "JDBC Thin" FORMAT 99999
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') "Current Time"
--show currently logged in Forms connections, multiple connections per user
--approximately half as many Forms users as DB forms connections
,(SELECT COUNT(*)
FROM apps.fnd_logins fl -- only has Forms login rows
, apps.fnd_user fu
, v\$session s
, v\$process p
WHERE fl.end_time IS NULL
AND fl.user_id = fu.user_id(+)
AND fl.process_spid = p.spid
AND fl.pid = p.pid
AND fl.serial# = p.serial#
AND p.addr = s.paddr
AND s.program IS NULL) "Forms Users"
--
--
-- SelfServ Activity last 60 min based on icx_sessions
,(SELECT COUNT(*)
FROM apps.icx_sessions icx
, apps.fnd_user fu
, apps.fnd_responsibility_vl frv
WHERE icx.user_id = fu.user_id(+)
AND icx.disabled_flag = 'N'
AND icx.last_connect > SYSDATE - 1/24
--Preferences
AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)
AND icx.responsibility_application_id = frv.application_id(+)
-- only web self service responsibilities
AND frv.version(+) = 'W') "Self Serv Users"
--
--
,(SELECT SESSIONS_CURRENT
FROM v\$license ) "Curr Session"
--
--
,(SELECT SESSIONS_HIGHWATER
FROM v\$license ) "Sess HighWater"
--
--
,( SELECT COUNT(*)
FROM sys.v_\$session
WHERE program = 'JDBC Thin Client') "JDBC ThinClient"
FROM DUAL;
PROMPT User Checks Section:
PROMPT
PROMPT Users whose FND Login Account password has become INVALID (11.5.10.2)
PROMPT and whose account is not end dated
COLUMN encrypted_password FORMAT a20
SELECT user_name
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
, ENCRYPTED_USER_PASSWORD encrypted_password
, TO_CHAR(end_date, 'DD-Mon-YY') end_date
FROM apps.fnd_user
WHERE encrypted_user_password = 'INVALID' -- value when user is blocked
AND user_name NOT IN ('ANONYMOUS', 'APPSMGR','XML_USER') --inactive seeded
AND end_date is null -- only look at active logins in this query
ORDER BY user_name;
PROMPT Non-Terminated people with end dated fnd_user plus INVALID Password
PROMPT We assume that locked users should not be both end dated and INVALID
SELECT DISTINCT(fu.user_name)
, ENCRYPTED_USER_PASSWORD encrypted_password
, fu.end_date
FROM apps.fnd_user fu
, apps.per_all_people_f papf
WHERE fu.encrypted_user_password = 'INVALID' -- value when user is blocked
AND fu.end_date IS NOT NULL
AND fu.employee_id = papf.person_id
AND papf.current_employee_flag = 'Y'
AND trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date;
-- Profiles Section:
PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT
PROMPT List Site level Profiles that have changed in last Month
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading 'opt|id' FORMAT 9999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
PROMPT Site Level Profile Settings
SELECT
o.user_profile_option_name user_prof_name
, substr(v.profile_option_value, 1,45) prof_value
, fu.user_name
, to_char(v.last_update_date,'DD-Mon-YYYY') update_date
FROM apps.fnd_profile_option_values v
, apps.fnd_profile_options_vl o
, apps.fnd_user fu
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND level_id = 10001
AND v.last_updated_by = fu.user_id
AND (v.last_update_date >= sysdate - 30 -- updated in last 4 weeks
OR v.creation_date > sysdate -30) -- created in the last 4 weeks
ORDER BY fu.user_name, o.user_profile_option_name;
PROMPT User Level Profile Settings relating to Trace or Debug
--
COLUMN application_id on heading appl_id FORMAT 999999
COLUMN profile_option_id on heading prof_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN level_id on heading 'level|id' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a5
COLUMN prof_name FORMAT a25 trunc
COLUMN user_name FORMAT a15
PROMPT List Profile options WHERE debug/trace is SET on
SELECT
v.profile_option_id
, v.level_id
, user_name
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND fu.end_date is null -- only look at active logins
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
ORDER BY pot.user_profile_option_name;
PROMPT Non-User Level Profile Settings relating to Trace or Debug
SELECT
v.profile_option_id
, v.level_id
, v.level_value
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id != 10004 -- not user level option
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
order by pot.user_profile_option_name;
PROMPT Module Specific Checks Section:
PROMPT
PROMPT Module: Order Mgmt
PROMPT Check OE Processing Msgs for Number AND types of rows
PROMPT If too many rows check whether 'Message Purge' Con Pgm is scheduled
SELECT
request_id
, substr(opmt.message_text,1,50) msg
, COUNT(*)
FROM ont.oe_processing_msgs opm
, ont.oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
group by request_id,opmt.message_text;
PROMPT Module: Advanced Supply Chain Planning
PROMPT We run Purge ATP Temp Tables but it leaves rows with null creation_date
PROMPT Need to truncate mrp.mrp_atp_schedule_temp periodically when too many
PROMPT rows due to the null problem.
SELECT
COUNT(*)
FROM mrp.mrp_atp_schedule_temp;
PROMPT Module: AOL
PROMPT We run Purge FND_STATS History Records but it leaves some rows.
PROMPT Need to truncate apps.fnd_stats_hist periodically when too many rows
SELECT
COUNT(*)
FROM apps.fnd_stats_hist;
PROMPT End Module specific Checks Section
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ End of oracle_apps_full_report.sh Logic
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SPOOL OFF
CLEAR COLUMNS
EXIT
!
#-- Program : Apps Health Checks
#--
#-- Description : oracle_apps_full_report.sh
#--
#-- Purpose : Run a variety of Oracle Apps Health Checks.
#-- Warning section at beginning should usually return no rows.
#--
#-- Created By : oracle-latest-technology.blogspot.com
#--
#-- Creation Date : Dec 19, 2011
#--
#-- Change History :
#-- ............................................................
#--
#-- ...........................................................................
dt=`date +"%d.%m.%y"`
.
sqlplus -s <<!
apps/
SET linesize 80
set pagesize 500
SET space 1
#COLUMN db_name new_value dbname noprint
#COLUMN today new_value runtime noprint
spool
SELECT name db_name FROM v\$database;
SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI') today FROM dual;
SET pagesize 1000
COLUMN owner FORMAT a8
COLUMN object_type FORMAT a12
COLUMN object_name FORMAT a30
COLUMN created FORMAT a9
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
--
-- Health Check Logic starts Here
--
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ Warning Section: Should see no rows in this section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
COLUMN obj_name FORMAT a18
COLUMN own FORMAT a5
-- General Warnings:
PROMPT Confirm not in Maintenance Mode - should see no rows
SELECT
pot.user_profile_option_name "Profile Name"
,v.PROFILE_OPTION_VALUE "Profile Value"
,v.LEVEL_ID "Level ID"
,v.LAST_UPDATE_DATE "Last Update Date"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 10001) = 10001
AND pot.user_profile_option_name = 'Applications Maintenance Mode'
AND v.profile_option_value != 'NORMAL';
/*PROMPT Warn login accounts that should not become locked out like SYSADMIN
PROMPT
COLUMN user_name FORMAT a25
COLUMN creation_date FORMAT a30
COLUMN last_logon_date FORMAT a30
COLUMN password_date FORMAT a3
SELECT user_name, creation_date
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
--, ENCRYPTED_USER_PASSWORD
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
FROM apps.fnd_user
WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'
AND END_date IS NULL
AND USER_NAME IN ('SYSADMIN')
ORDER BY user_name;*/
/*
PROMPT User Security AND Access Issues section:
PROMPT Show any logins whose password is NOT SET to expire in 60 days
COLUMN user_name FORMAT a20
COLUMN encrypted_password FORMAT a20
SELECT user_id "User ID"
, user_name "User Name"
, password_lifespan_days "Password Lifespan Days"
FROM apps.fnd_user
WHERE end_date IS NULL
AND nvl(password_lifespan_days, 0) != 60
AND user_id > 1051;
*/
PROMPT Non-DBAs who have System Administrator or other powerful Responsibilities
COLUMN user_name FORMAT a10
COLUMN description FORMAT a20
COLUMN responsibility_name FORMAT a25
COLUMN end_date FORMAT a20
SELECT fu.user_name "User Name"
,fu.description "Description"
,substr(fr.responsibility_name,1,35) "Responsibility"
,fur.end_date "End Date"
FROM
apps.fnd_user fu,
apps.fnd_user_resp_groups_direct fur,
apps.fnd_responsibility_vl fr,
apps.fnd_application_vl fa
WHERE
fur.user_id = fu.user_id
AND fu.user_name NOT IN ('YOUR_DBA_NAME','SYSADMIN')
AND fur.responsibility_application_id = fa.application_id
AND fur.responsibility_application_id = fr.application_id
AND fur.responsibility_id = fr.responsibility_id
AND fu.end_date is null -- only show active users
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment
AND fr.responsibility_name in ('System Administrator'
,'Application Developer'
,'Application Developer Common Modules'
,'CRM HTML Administration'
,'FICO Sys Admin Conversion'
,'FICO System Administration GUI'
,'Functional Administrator' -- can chg Profiles
,'Functional Developer'
,'Knowledge Administrator' --use FICO instead
,'Oracle Sales Administrator' --use FICO instead
,'Sales Administrator' --use FICO instead
,'SFM System Administrator'
,'TeleSales Administration' --use FICO instead
)
ORDER BY 1;
COL emp_num format a7
COL per_serv_id format 999999
-- Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number "Emp No."
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') "Termination Date"
, period_of_service_id "Period Serv ID"
FROM apps.fnd_user fu
, apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND papf.person_id = ppos.person_id
AND fu.end_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
FROM per_periods_of_service
WHERE person_id = papf.person_id)
AND ppos.actual_termination_date < TRUNC(SYSDATE);
PROMPT Show users whose Responsibility roles have early end date in future
-- To remove, Use Define User Form toggle fnd_user end_date by
-- adding end_date, commit, remove user end_date, commit. Some time lag
-- Remove this logic when Oracle no longer end dates resp roles early
COLUMN user_name FORMAT a25
COLUMN user_end_date FORMAT a20
select distinct user_name "User Name"
, user_end_date "End Date"
from apps.wf_local_user_roles
where user_end_date >= sysdate -- in future
and user_end_date <= sysdate + 700 -- within next week
and user_orig_system != 'WF_LOCAL_USERS'
and to_char(user_end_date,'DD-MON-YYYY') != '31-DEC-4712'
order by user_end_date, user_name;
-- Duplicate email addresses have security issues with workflow notifications
-- Two people should not have same email address since one user can get
-- another user's email for instance when doing password reset
COL email_address FORMAT a50
PROMPT Show fnd_users who have same email address
SELECT user_name "User Name"
, email_address "Email Address"
FROM fnd_user WHERE email_address IN (
SELECT
email_address
FROM apps.fnd_user
WHERE email_address is not NULL
AND email_address != 'employee@
AND end_date is null
GROUP BY
email_address
HAVING count(*) > 1);
PROMPT Show active fnd_users tied to the same HR employee (shows person_id)
SELECT
fu.employee_id "Employee ID"
, count(*) "Count"
FROM apps.fnd_user fu
WHERE fu.employee_id is not null -- this is person_id in per_all_people_f
AND fu.end_date is null
GROUP BY fu.employee_id
HAVING count(*) > 1;
PROMPT Workflow Section:
PROMPT
--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Users whose mail preference is 'do not send' since causes WF notif issues
SELECT fu.user_name
FROM apps.fnd_user_preferences fup
, apps.fnd_user fu
WHERE fup.preference_name = 'MAILTYPE'
AND fup.preference_value = 'QUERY'
AND fup.user_name = fu.user_name
AND fu.end_date IS NULL
ORDER BY fu.user_name;
PROMPT Show any wf adhoc roles that have wrong notification_preference
SELECT notification_preference "Notification Preference"
, COUNT(*) "Count"
FROM apps.wf_local_roles
WHERE orig_system /*= 'WF_LOCAL_ROLES'*/in ('FND_USR','PER')
AND notification_preference NOT IN ('MAILHTML', 'MAILHTM2')
GROUP BY notification_preference;
PROMPT Show if workflow mailer setting PROCESSOR_READ_TIMEOUT_CLOSE has changed
PROMPT to something other than Y. Should not see any rows
SELECT p.parameter_name
, v.last_update_date
, u.user_name
FROM apps.fnd_svc_comp_param_vals v
, apps.fnd_svc_comp_params_b p
, apps.fnd_svc_components c
, apps.fnd_user u
WHERE c.component_type = 'WF_MAILER'
AND c.component_id = v.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
AND v.parameter_value <> 'Y'
AND v.last_updated_by = u.user_id;
PROMPT Profile Check Section:
PROMPT
-- Confirm Examine function is password protected in Production
-- If profile is Y can use Examine without password PROMPT so unprotected
PROMPT Profile 'Utilities:Diagnostics' for Examine set to unprotected
SELECT
v.profile_option_id "Profile Option ID"
, pot.user_profile_option_name "Prof Name"
, substr(v.profile_option_value, 1,5) "Prof Value"
, v.level_id "Level ID"
, v.level_value "Level Value"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N' -- N means password protected
ORDER BY pot.user_profile_option_name;
PROMPT User Level 'Utilities:Diagnostics' Profile Settings for Examine
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level Profile
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N'
order by pot.user_profile_option_name;
-- List Profile options for personalization related settings
--
PROMPT User level profile for Personalization related Profiles
SELECT
user_name
, pot.user_profile_option_name prof_name
, v.profile_option_value prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND pot.user_profile_option_name in
(
'AMS : Oracle Personalization Enabled'
,'Create Seeded Personalizations'
,'FND: Personalization Region Link Enabled'
,'FND: Personalization Seeding Mode'
,'HR: Enable User Personalization'
,'OSO : Enable Admin Personalization Feature'
,'Personalize Self-Service Defn'
)
ORDER BY
pot.user_profile_option_name
, user_name;
PROMPT Find Personalization related profiles at other levels
PROMPT
PROMPT *** Site level ***
select t.user_profile_option_name, profile_option_value
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and level_id=10001
and (upper(t.user_profile_option_name) like '%PERSONALIZ%'
and upper(t.user_profile_option_name) not like '%PERSONALIZ%WYSIWYG%'
)
order by user_profile_option_name;
PROMPT
PROMPT *** Application level ***
select t.user_profile_option_name, profile_option_value, application_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_application_tl a
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and a.application_id = level_value
and level_id=10002
and t.language = a.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, application_name;
PROMPT
PROMPT *** Responsibility level ***
select t.user_profile_option_name, profile_option_value,
responsibility_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_responsibility_tl r
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and responsibility_id = level_value
and level_id=10003
and t.language = r.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, responsibility_name;
COLUMN prof_value on heading 'Prof|value' FORMAT a5
PROMPT Confirm self service diagnostics are not turned on
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'FND%Diag%'
AND nvl(v.level_id, 1001) != 10004 -- skip user level options
ANd v.profile_option_value != 'N' -- when Y then can use ss diagnostics
order by pot.user_profile_option_name;
PROMPT Confirm only DBA and sysadmin users have access to SS Diagnostics
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level options
AND fu.user_name NOT IN ('YOUR_DBA_NAME', 'SYSADMIN')
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'FND%Diag%'
order by pot.user_profile_option_name;
PROMPT Look for Password Profiles set at user level
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, fu.user_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like '%Password%'
order by pot.user_profile_option_name;
-- End User Security Warnings
PROMPT Warnings about Concurrent Programs and Concurrent Manager
column user_concurrent_program_name on heading Program format a32 trunc
column concurrent_program_name on heading Program format a10 trunc
column description on heading Description format a35 trunc
--column user_name format a10
PROMPT Show any requests by SYSADMIN that ended in error in last 5 days
PROMPT
select request_id "Request ID"
/*,fu.user_name User_Name*/
,to_char(cr.actual_start_date, 'DD HH24:MI') "When Started"
,to_char(cr.actual_completion_date, 'DD HH24:MI') "When Ended"
,cp.concurrent_program_name "Program Name"
,phase_code "Phase Code"
,status_code "Status Code"
from
apps.fnd_concurrent_programs cp,
apps.fnd_user fu,
apps.fnd_concurrent_requests cr
where
cr.concurrent_program_id = cp.concurrent_program_id
and cr.program_application_id = cp.application_id
and cr.actual_completion_date is not null
and cr.requested_by = fu.user_id
and fu.user_name = 'SYSADMIN'
and cr.status_code = 'E' -- requests that ended in error
and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently
order by cr.actual_start_date DESC;
PROMPT Show conc programs that have trace enabled. Normally should be none
PROMPT
-- To Turn off: Go to Define Con Pgm form and uncheck the Enable Trace field
col prog_name format a35
col enable_trace format a15
SELECT concurrent_program_name "Prog Name"
, enable_trace "Trace Enabled"
, last_update_date "Last Update Date"
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y';
-- Confirm Alert Periodic Scheduler is pending so that alerts will run
PROMPT Confirm Alert Periodic Scheduler is okay, should see no rows
select 'PROBLEM: No pending Alert Periodic Scheduler request' "Message" from dual
where not exists (
select
cp.concurrent_program_name Program,
cr.request_id Request,
phase_code S,
status_code C,
cr.argument1||', '||
cr.argument2||', '||
cr.argument3||', '||
cr.argument4||', '||
cr.argument5||', '||
cr.argument6||', '||
cr.argument7||', '||
cr.argument8 Arguments
from
apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_programs cp
where
cr.concurrent_program_id = cp.concurrent_program_id
and cp.application_id >= 0 -- force on index leading edge
and cr.program_application_id = cp.application_id
and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm
and cr.phase_code = 'P'
and cr.status_code != 'H' -- not on hold
);
-- End Con Program section
/*-- Recent changes to objects with Custom Direct Changes - Research
-- Add standard Apps objects we Customized and will be lost when patched
PROMPT Recent changes to objects with Custom Direct Changes - usually None
PROMPT
PROMPT Any ADP views that have been changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name like Upper('%ADP%')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any changes to iExpense pkg where we have custom direct chg
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name = 'AP_WEB_DB_HR_INT_PKG'
AND owner = 'APPS'
AND object_type in ('PACKAGE SPEC', 'PACKAGE')
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT Any OTL/iExpense views changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name in ('AP_WEB_PA_PROJECTS_V')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;
PROMPT PA Gen Draft Rev parm we want disabled
col short_name format a10
col executable_name format a10
col parameter format a10
SELECT SUBSTR(fcp.user_concurrent_program_name,1,60) concurrent_program_name
, fcp.concurrent_program_name short_name
, SUBSTR(fe.executable_name,1,25) executable_name
, SUBSTR(fl_em.meaning,1,18) execution_method
, LPAD(TO_CHAR(fdfcu.column_seq_num,'fm990'),4) " SEQ"
, fdfcu.end_user_column_name parameter
, RPAD(fdfcu.enabled_flag,8) "ENABLED?"
, SUBSTR(ffvs.flex_value_set_name,1,45) validation_value_set
, SUBSTR(ffvs.description,1,75) value_set_description
, SUBSTR(fl_dt.meaning,1,13) default_type
, RPAD(fdfcu.required_flag,5) "REQD?"
-- If the default value is populated and more than 80 chars
-- then trim it to 80 for purposes of this report.
, RPAD(DECODE(SIGN(NVL(LENGTH(fdfcu.default_value),0) - 80)
, +1, SUBSTR(fdfcu.default_value,1,80) || '...'
, fdfcu.default_value),83) default_value
, RPAD(fdfcu.display_flag,10) "DISPLAYED?"
FROM apps.fnd_application fa
, apps.fnd_application_tl fat
, apps.fnd_concurrent_programs_vl fcp
, apps.fnd_executables fe
, apps.fnd_descr_flex_column_usages fdfcu
, apps.fnd_descr_flex_col_usage_tl fdfcut
, apps.fnd_flex_value_sets ffvs
, apps.fnd_lookups fl_dt
, apps.fnd_lookups fl_em
WHERE USERENV('LANG') = fat.language
AND fat.application_id = fa.application_id
AND fa.application_id = fcp.application_id
AND fcp.concurrent_program_name = 'PARGDR' -- PA program
AND fdfcu.end_user_column_name = 'Release Draft Revenue' -- this parameter
AND fdfcu.enabled_flag = 'Y' -- has become enabled when it should not
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcp.application_id = fdfcu.application_id
AND '$SRS$.'
|| fcp.concurrent_program_name = fdfcu.descriptive_flexfield_name
AND fdfcu.application_id = fdfcut.application_id
AND fdfcu.descriptive_flexfield_name = fdfcut.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = fdfcut.descriptive_flex_context_code
AND fdfcu.application_column_name = fdfcut.application_column_name
AND USERENV('LANG') = fdfcut.language
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fdfcu.default_type = fl_dt.lookup_code (+)
AND 'FLEX_DEFAULT_TYPE' = fl_dt.lookup_type (+)
AND fcp.execution_method_code = fl_em.lookup_code (+)
AND 'CP_EXECUTION_METHOD_CODE' = fl_em.lookup_type (+)
ORDER BY fcp.user_concurrent_program_name
, fdfcu.column_seq_num;
-- End Custom Direct Changes Section*/
/*PROMPT Confirm selected Applimation DB triggers that cause problems are off
select count(*)
from dba_triggers
where trigger_name like 'AM_WF_LOCAL_USER_ROLES%'
and status != 'DISABLED' -- these triggers cause severe perf problems when on
/*/
-- Std RDBMS Checks Section
PROMPT Watch for HighWaterMark -- higher than 1000 since may need to incr processes
PROMPT
SELECT V.SESSIONS_CURRENT "Current Sessions",
V.SESSIONS_HIGHWATER "HighWater Sessions",
V.CPU_COUNT_CURRENT "CPU Current Count",
V.CPU_COUNT_HIGHWATER "CPU Count HighWater"
FROM V\$LICENSE V;
--where SESSIONS_HIGHWATER > 100
-- Check for datafiles that have autoextended to their max so stuck
column bytes format 999999999
column file_name format a35
column TSPACE format a12
prompt
prompt Datafiles with autoextend ON that have reached their max so have got STUCK
select tablespace_name "Tablespace"
, file_name "Filename"
, bytes/1048576 "Size"
, maxbytes / 1048576 "Maxsize(MB)" -- convert to MB
from dba_data_files
where autoextensible = 'YES'
and bytes >= maxbytes -- datafile already extended to max
order by tablespace_name;
-- DB Security Warnings:
-- Check for any locked db user accounts
COLUMN username FORMAT a14
COLUMN default_tablespace heading 'DEFAULT TS' FORMAT a10
COLUMN temporary_tablespace heading 'TEMP' FORMAT a4
COLUMN profile FORMAT a13
COLUMN account_status heading 'ACCT|STAT' FORMAT a6 trunc
PROMPT List Locked Oracle schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE lock_date IS NOT NULL
AND account_status NOT LIKE 'EXPIRED%';
PROMPT Check whether UNDO datafiles have autoextended
PROMPT May want to resize datafiles down lower after big AutoExtend
COLUMN sname FORMAT a12 heading 'Tablespace' justify c
COLUMN ssize FORMAT 999,999.99 heading 'Mb|Total' justify c
COLUMN extents FORMAT 9,999 heading 'Free|Exts' justify c
COLUMN mbytes FORMAT 9,999.99 heading 'Largest|Free Ext' justify c
COLUMN tbytes FORMAT 999,999.99 heading 'Mb|Avail' justify c
COLUMN mused FORMAT 999,999.99 heading 'Mb|Used' justify c
COLUMN pct FORMAT 990.99 heading 'Percent|Free' justify c
SELECT
total.tablespace_name sname,
COUNT(free.bytes) extents,
max(free.bytes)/1048576 mbytes,
total.ts_size/1048576 ssize,
(total.ts_size/1048576) - (sum(free.bytes/1048576)) mused,
sum(free.bytes)/1048576 tbytes,
sum(free.bytes)/total.ts_size * 100 pct
FROM
dba_free_space free
,(SELECT tablespace_name,
sum(bytes) ts_size
FROM dba_data_files
GROUP BY tablespace_name)
total
WHERE
total.tablespace_name = free.tablespace_name (+)
AND total.tablespace_name = 'APPS_UNDOTS1'
AND total.ts_size/1048576 > 8600 -- greater than 8600 MB then autoextended
GROUP BY
total.tablespace_name,
total.ts_size;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT END Warning Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT Begin General Health Checks Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT
-- Revise prompt as persistent invalid objects increase or reduce
-- See SharePoint Invalid Object Log for details about these objects
PROMPT Invalid APPS objects, at least 6: FND_OID_DIAG FND_TS_SIZE and 4 MRP_
COLUMN object_name FORMAT a30
SELECT
owner, object_name, object_type
, to_char(created, 'DD-MON-YY') created
, status
FROM dba_objects
WHERE Owner = 'APPS'
AND status = 'INVALID'
ORDER BY object_name;
-- Add any custom schemas here
PROMPT Invalid objects owned by custom schema XXCUSTOM Account
SELECT
owner, object_name, object_type, created, status
FROM dba_objects
WHERE Owner = 'XXCUSTOM'
AND status = 'INVALID'
ORDER BY object_name;
-- RDBMS Checks
PROMPT Check SYS audit table to determine number of audit rows
SELECT
COUNT(*)
FROM sys.aud$;
PROMPT List Expired schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE /*lock_date is not null
AND */ account_status != 'OPEN';
PROMPT Usage Checks Section:
-- Check highest DB connections COUNT since last DB start
-- Count variety of current Oracle connections
PROMPT Current Oracle Connection Characteristics including highwater mark
COLUMN formsusers HEADING "Forms connects" FORMAT 99999
COLUMN selfservusers HEADING "Self Service" FORMAT 99999
COLUMN rundatetime HEADING "TIME OF DAY" FORMAT A16
COLUMN currsession HEADING "Current Sess" FORMAT 99999
COLUMN sesshighwater HEADING "High Water" FORMAT 99999
COLUMN jdbcthinclient HEADING "JDBC Thin" FORMAT 99999
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') "Current Time"
--show currently logged in Forms connections, multiple connections per user
--approximately half as many Forms users as DB forms connections
,(SELECT COUNT(*)
FROM apps.fnd_logins fl -- only has Forms login rows
, apps.fnd_user fu
, v\$session s
, v\$process p
WHERE fl.end_time IS NULL
AND fl.user_id = fu.user_id(+)
AND fl.process_spid = p.spid
AND fl.pid = p.pid
AND fl.serial# = p.serial#
AND p.addr = s.paddr
AND s.program IS NULL) "Forms Users"
--
--
-- SelfServ Activity last 60 min based on icx_sessions
,(SELECT COUNT(*)
FROM apps.icx_sessions icx
, apps.fnd_user fu
, apps.fnd_responsibility_vl frv
WHERE icx.user_id = fu.user_id(+)
AND icx.disabled_flag = 'N'
AND icx.last_connect > SYSDATE - 1/24
--Preferences
AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)
AND icx.responsibility_application_id = frv.application_id(+)
-- only web self service responsibilities
AND frv.version(+) = 'W') "Self Serv Users"
--
--
,(SELECT SESSIONS_CURRENT
FROM v\$license ) "Curr Session"
--
--
,(SELECT SESSIONS_HIGHWATER
FROM v\$license ) "Sess HighWater"
--
--
,( SELECT COUNT(*)
FROM sys.v_\$session
WHERE program = 'JDBC Thin Client') "JDBC ThinClient"
FROM DUAL;
PROMPT User Checks Section:
PROMPT
PROMPT Users whose FND Login Account password has become INVALID (11.5.10.2)
PROMPT and whose account is not end dated
COLUMN encrypted_password FORMAT a20
SELECT user_name
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
, ENCRYPTED_USER_PASSWORD encrypted_password
, TO_CHAR(end_date, 'DD-Mon-YY') end_date
FROM apps.fnd_user
WHERE encrypted_user_password = 'INVALID' -- value when user is blocked
AND user_name NOT IN ('ANONYMOUS', 'APPSMGR','XML_USER') --inactive seeded
AND end_date is null -- only look at active logins in this query
ORDER BY user_name;
PROMPT Non-Terminated people with end dated fnd_user plus INVALID Password
PROMPT We assume that locked users should not be both end dated and INVALID
SELECT DISTINCT(fu.user_name)
, ENCRYPTED_USER_PASSWORD encrypted_password
, fu.end_date
FROM apps.fnd_user fu
, apps.per_all_people_f papf
WHERE fu.encrypted_user_password = 'INVALID' -- value when user is blocked
AND fu.end_date IS NOT NULL
AND fu.employee_id = papf.person_id
AND papf.current_employee_flag = 'Y'
AND trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date;
-- Profiles Section:
PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT
PROMPT List Site level Profiles that have changed in last Month
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading 'opt|id' FORMAT 9999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc
PROMPT Site Level Profile Settings
SELECT
o.user_profile_option_name user_prof_name
, substr(v.profile_option_value, 1,45) prof_value
, fu.user_name
, to_char(v.last_update_date,'DD-Mon-YYYY') update_date
FROM apps.fnd_profile_option_values v
, apps.fnd_profile_options_vl o
, apps.fnd_user fu
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND level_id = 10001
AND v.last_updated_by = fu.user_id
AND (v.last_update_date >= sysdate - 30 -- updated in last 4 weeks
OR v.creation_date > sysdate -30) -- created in the last 4 weeks
ORDER BY fu.user_name, o.user_profile_option_name;
PROMPT User Level Profile Settings relating to Trace or Debug
--
COLUMN application_id on heading appl_id FORMAT 999999
COLUMN profile_option_id on heading prof_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN level_id on heading 'level|id' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a5
COLUMN prof_name FORMAT a25 trunc
COLUMN user_name FORMAT a15
PROMPT List Profile options WHERE debug/trace is SET on
SELECT
v.profile_option_id
, v.level_id
, user_name
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND fu.end_date is null -- only look at active logins
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
ORDER BY pot.user_profile_option_name;
PROMPT Non-User Level Profile Settings relating to Trace or Debug
SELECT
v.profile_option_id
, v.level_id
, v.level_value
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id != 10004 -- not user level option
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
order by pot.user_profile_option_name;
PROMPT Module Specific Checks Section:
PROMPT
PROMPT Module: Order Mgmt
PROMPT Check OE Processing Msgs for Number AND types of rows
PROMPT If too many rows check whether 'Message Purge' Con Pgm is scheduled
SELECT
request_id
, substr(opmt.message_text,1,50) msg
, COUNT(*)
FROM ont.oe_processing_msgs opm
, ont.oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
group by request_id,opmt.message_text;
PROMPT Module: Advanced Supply Chain Planning
PROMPT We run Purge ATP Temp Tables but it leaves rows with null creation_date
PROMPT Need to truncate mrp.mrp_atp_schedule_temp periodically when too many
PROMPT rows due to the null problem.
SELECT
COUNT(*)
FROM mrp.mrp_atp_schedule_temp;
PROMPT Module: AOL
PROMPT We run Purge FND_STATS History Records but it leaves some rows.
PROMPT Need to truncate apps.fnd_stats_hist periodically when too many rows
SELECT
COUNT(*)
FROM apps.fnd_stats_hist;
PROMPT End Module specific Checks Section
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ End of oracle_apps_full_report.sh Logic
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SPOOL OFF
CLEAR COLUMNS
EXIT
!
No comments:
Post a Comment