[oracle@lufRAMfexanpq1-ekd5g3 ~]$ more $HOME/scripts/YE_Monitor_Running_ram.sh
LOG_FILE=$HOME/scripts/log/YE_Monitor_Running_jobs.log
. $HOME/.bash_profile > /dev/null
EMAIL_LIST="seetharam.nanubalu@RAM.gov"
PATH=/u02/app/oracle/product/19.0.0.0/dbhome_3/bin:/u02/app/oracle/product/19.0.0.0/dbhome_3/OPatch:$PATH; export PATH
ORACLE_UNQNAME=EBSST1; export ORACLE_UNQNAME
ORACLE_SID=EBSST13; export ORACLE_SID
ORACLE_HOSTNAME=lufRAMfexanpq1-ekd5g3.nonprodexadb.RAMfwest.oraclevcn.com; export ORACLE_HOSTNAME
LD_LIBRARY_PATH=/u02/app/oracle/product/19.0.0.0/dbhome_3/lib; export LD_LIBRARY_PATH
ORACLE_BASE=/u02/app/oracle; export ORACLE_BASE
OH=/u02/app/oracle/product/19.0.0.0/dbhome_3; export OH
ORACLE_HOME=/u02/app/oracle/product/19.0.0.0/dbhome_3; export ORACLE_HOME
TNS_ADMIN=/u02/app/oracle/product/19.0.0.0/dbhome_3/network/admin/EBSST1; export TNS_ADMIN
sqlplus -s / as sysdba >/dev/null <<EOF
### Connecting to a Container Database (CDB)
alter session set container=SRAM2I;
SET LINESIZE 160 PAGESIZE 200 FEEDBACK ON
SET ECHO OFF MARKUP HTML ON ENTMAP OFF SPOOL ON HEAD '' TABLE 'align=centre border=1 color:Black background:#f7f7e7'
SPOOL $LOG_FILE
PROMPT <h1>YE and Standard Concurrent Manager Status</h1>
select q.concurrent_queue_name || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_queues q
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes > 0
and ((r.phase_code in ('R','P','I') and upper('N') = 'Y') or
upper('N') != 'Y')
and q.concurrent_queue_name in( 'STANDARD','STANDARD2', 'YETSMANAGER')
group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;
PROMPT <h1> Stale Stats of FV and GL </h1>
select distinct OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED,stale_stats from dba_tab_statistics where stale_stats='YES'
and owner in ('FV','GL') order by owner;
select distinct owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where stale_stats='YES' and owner in ('FV','GL') order by owner;
SPOOL OFF
EOF
echo "<html>" > $LOG_FILE.html
echo "<title>$MSUB</title>" >> $LOG_FILE.html
echo "<style>" >> $LOG_FILE.html
echo " h1 {font:11pt Arial,Helvetica,sans-serif; color:black; background:White;line-height:0px;margin-top:16px;margin-bottom:0px;}" >> $LOG_FILE.html
echo " body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;line-height:0px;margin-top:0px;margin-bottom:0px;}" >> $LOG_FILE.html
echo "</style>" >> $LOG_FILE.html
echo "<head>" >> $LOG_FILE.html
echo "<h3><b>$MSUB</b></h1>" >> $LOG_FILE.html
echo "<b>Enviroment:</b> $ORACLE_SID" >> $LOG_FILE.html
echo "<br><b>Context</b>: $CONTEXT_NAME<br>`date '+Date: %m/%d/%y Time:%H:%M:%S'`<br>" >> $LOG_FILE.html
echo "</head><hr />" >> $LOG_FILE.html
echo "<body>" >> $LOG_FILE.html
cat $LOG_FILE >> $LOG_FILE.html
echo "</body>" >> $LOG_FILE.html
echo "</html>" >> $LOG_FILE.html
if [ `grep -ci "rows selected." $LOG_FILE` -ne 0 -o `grep -ci "row selected." $LOG_FILE` -ne 0 ];then
mailx -s "$(echo "$ORACLE_SID: 19c YE Specific Monitoring Details\n "Content-Type: text/html; charset=\"us-ascii\""")" -r ociNONPROD_donotreply@RAM.gov $EMAIL_LIST <YE_Monit
or_Running_jobs.log.html
fi
No comments:
Post a Comment