Oracle : Scheduling crontab to send EMAIL in Unix
$ vi test.bash
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
#!/bin/bash #Script to perform testing ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` export ORACLE_HOME='/u01/app/oracle/product/12102' export ORACLE_SID='ORCL' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexperts@gmail.com' export LOG='/tmp/test_mig.log' #Function to trigger email function mailog { cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST} } #Function step1 function step1 { echo " " >> $LOG echo "Started Step 1" >> $LOG echo " " >> $LOG echo "Test email " >> $LOG sqlplus -s /nolog <<START >>$LOG Connect / as sysdba set serveroutput on set timing on prompt Todays date select sysdate from dual; START echo " " >> $LOG echo "----- END OF Step1 ------" >> $LOG } #Script execution is here > $LOG if [ "$#" -eq 0 ] then step1 mailog else $1 mailog fi |
Crontab entry
|
1 |
00 02 * * * /local/home/oracle/test.bash 1>/local/home/oracle/test.cronlog 2>/local/home/oracle/test.cronerr |
Updated Script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
#!/bin/bash #Script to perform testing ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2' export ORACLE_SID='prod' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexperts@gmail.com' export LOG='/tmp/test_mig.log' #Function to trigger email function mailog { cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST} } #Function step1 function step1 { echo " " >> $LOG echo "Started Step 1" >> $LOG echo " " >> $LOG echo "Test email " >> $LOG sqlplus vinod/vinod <<START >>$LOG prompt updating records in EMP table select * from emp where deptno=20; prompt update emp set sal=sal+500 WHERE deptno=20; update emp set sal=sal+500 WHERE deptno=20; prompt select * from emp where deptno=20; select * from emp; START echo " " >> $LOG echo "----- END OF Step1 ------" >> $LOG } #Script execution is here > $LOG if [ "$#" -eq 0 ] then step1 mailog else $1 mailog fi |
Crontab entry
|
1 |
02 * * * * /home/oracle/test.bash 1>/home/oracle/test.cronlog 2>/home/oracle/test.cronerr |
Note: Please test scripts in Non Prod before trying in Production.
Loading...
