Oracle E-business suite logs clean up
#!/bin/bash
cd $EBS_DOMAIN_HOME
find $EBS_DOMAIN_HOME -type f -path "*/logs/*.log?*" -mtime +10 -exec rm {} \;
find $EBS_DOMAIN_HOME -type f -path "*/logs/*.out?*" -mtime +10 -exec rm {} \;
#add validation code to check if env is set,
cd $IAS_ORACLE_HOME/instances
find $IAS_ORACLE_HOME/instances -type f -path "*diagnostics/logs*log.*" -mtime +10 -exec rm {} \;
Concurrent Request Analytics.
Find number of concurrent requests submitted month wise.
select to_char(a.requested_start_date,'yymon'), count(*) from
fnd_concurrent_requests a
--where concurrent_program_id in ('20428','20821') use to find for a particular program
GROUP BY to_char(a.requested_start_date,'yymon') order by to_char(a.requested_start_date,'yymon') desc;
Find size of files per extension
Note: change grep to include month year file or file extension
cd $APPLCSF
Size of files per month and per type
ls -ltr | grep Jul |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'
ls -ltr | grep Jun |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'
ls -ltr | grep Jun |grep xml | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'
ls -ltr | grep Jun |grep txt | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'
ls -ltr | grep Jun |grep xls | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'
size of file per day
ls -ltr | grep Aug |grep xls |awk '{a[$6 $7]+=$5;}END{for(i in a)print i", "a[i];}'
Find concurrent requests older than 60 days with completion duration
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
and f.actual_start_date > sysdate - 60
and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60
order by
f.actual_start_date desc;
Find average, min, max for concurrent program in last 60 days
SELECT
pt.user_concurrent_program_name user_conc_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
count(*),
TRUNC (AVG (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
|| ' Days'
|| ' + '
|| TO_CHAR (
TRUNC (SYSDATE)
+ NUMTODSINTERVAL (
AVG ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
* 86400,
'second'),
'HH24:MI:SS')
"AVERAGE",
TRUNC (MIN (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
|| ' Days'
|| ' + '
|| TO_CHAR (
TRUNC (SYSDATE)
+ NUMTODSINTERVAL (
MIN ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
* 86400,
'second'),
'HH24:MI:SS')
"Min",
TRUNC (MAX (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
|| ' Days'
|| ' + '
|| TO_CHAR (
TRUNC (SYSDATE)
+ NUMTODSINTERVAL (
MAX ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))
* 86400,
'second'),
'HH24:MI:SS')
"Max"
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
and f.actual_start_date > sysdate - 60
and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60
group by pt.user_concurrent_program_name, f.phase_code ;
Dealing with Oracle jobs
Views:
dba_jobs_running - lists all jobs that are currently running in the instance.
dba_jobs - describes all jobs in the database.
To manage jobs as sys users for the jobs created by other users
use package dbms_ijobs to remove or mark other user jobs broken
To manage jobs logged in as user who submitted jobs
use package dbms_jobs
FIND LOW LEVEL DEBUG ON E-BUSINESSS SUITE
PRODUCTION DATABASES SHOULD NOT HAVE DEBUG ENABLED
1. Query to find if debug is enabled in EBS by setting trace event at application user level
SELECT po.profile_option_name "NAME", po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'***'
) "LEVEL",
DECODE (TO_CHAR (pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'***'
) "CONTEXT",
pov.profile_option_value "VALUE"
FROM apps.fnd_profile_options_vl po,
apps.fnd_profile_option_values pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
and po.profile_option_name like '%FND_INIT_SQL%'
and lower(pov.profile_option_value) like '%event%'
ORDER BY "NAME";
2. FIND CONCURRENT PROGRAMS WITH TRACE ENABLED
select * from apps.fnd_concurrent_programs_vl WHERE ENABLE_TRACE='Y' ;
3. Check for debug profile options which are set to Y
SELECT po.profile_option_name "NAME", po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'***'
) "LEVEL",
DECODE (TO_CHAR (pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'***'
) "CONTEXT",
pov.profile_option_value "VALUE"
FROM apps.fnd_profile_options_vl po,
apps.fnd_profile_option_values pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
and po.profile_option_name like '%DEBUG%'
and lower(pov.profile_option_value)= 'y'
ORDER BY "NAME";
