Hi Tom,
REQUIREMENT:
Need to send database report before and after database bounce.
Test Case:
1) Created a scheduler job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'RUN_CHECK_REPORT',
schedule_name => 'HEALTHCHECK',
job_type => 'EXECUTABLE',
job_action => '/export/home/oracle/hc.sh'
);
END;
/
2) Executed a Job.
SQL> exec dbms_scheduler.run_job('RUN_CHECK_REPORT')
PL/SQL procedure successfully completed.
========================================================================
My plan is to call shell script before database down and after startup and it needs to send some details as email. But when i executed the Job, it is just reading the mail related contents and sending this as ('DATABASE SESSION REPORT')content nothing more than that. But whereas when i execute the shell script manually it is sending the details as expected. From my observation it is not looking at the contents starting from sqlplus till the start of mail content in shell. Please advise.
SHELL SCRIPT:
#!/bin/sh
export ORACLE_SID=actd1
export ORACLE_HOME=/actdbdrtp1/oracle/product/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export date=`date`
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF
SELECT NAME from v\$database;
set markup html on
SPOOL /export/home/oracle/session.log APPEND
select name,open_mode from v\$database;
select sid,username,status,sql_id,event from v\$session;
set markup html off
spool off
EOF
mv /export/home/oracle/session.log /export/home/oracle/session.html
export MAILTO="divakar.sugumar@fmr.com"
export CONTENT="/export/home/oracle/session.html"
export SUBJECT="SESSION_REPORT"
(
echo "Subject: $SUBJECT"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
echo "<html><title>DATABASE USAGE REPORT</title><table border=3 width="750" height="150" align='center' cellpadding="5" cellspacing="5"><TR><TD bgcolor="azure"><CAPTION>DATABASE SESSION REPORT</CAPTION><p><font face="Courier New">"
cat /export/home/oracle/session.html
echo "</TD></TR></font></p></table></html>"
) | /usr/sbin/sendmail $MAILTO
================================================================================
Hi Tom,
I need to send health check report before database shutdown and after database startup automatically. For that I will use before shutdown and after startup trigger. But I am worrying how to send a report via email.
Could you please help me on this concept. How can we achieve this, any sample codes , pls ...
Thanks in advance!!!
You cannot make any assumptions about your environment...because from the scheduler, it starts from zero. So, for example, here's a version of the header of your script that works for me on my machine. Notice all the variables are fully set - no assumption of an existing environment
ORACLE_SID=db122
export ORACLE_SID=db122
ORACLE_HOME=/u01/app/oracle/product/12.2/db_2
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin
export PATH