Oct
15

Tip: Getting Unix command output and status in your email

Leave a comment »
Share

I have a scheduled script that refreshes some of my materialized views but sometimes these operations could fail if the database backup takes too long or an unexpected condition presents. This led me to think of sending the output of my refresh procedures to my email or my team’s listserv, here are sample versions of my scripts:

daily_mview_refresh.sql:

--connect to the database
conn / as sysdba
--Refresh Instructor FTE view
begin
PKG_SA_ETL_MAINTENANCE.refresh_instructor_fte;
end;
/
--Refresh Current Term Enrollment View
begin
PKG_SA_ETL_MAINTENANCE.refresh_term_enrl_current;
end;
/
exit
daily_mview_refresh.sh:

#!/bin/sh

#re-schedule script to run again tomorrow
at 5:50am tomorrow -f /path/db_maintenance/daily_job.sh

#Set ORACLE_SID
ORACLE_SID=MYDB
export ORACLE_SID

#Execute daily_mview_refresh.sql using SQL*Plus
date > daily_mview_refresh.log

echo "Materialized view refresh starting" >> daily_mview_refresh.log

sqlplus /nolog < daily_mview_refresh.sql >> daily_mview_refresh.log

echo "Materialized view refresh completed" >> daily_mview_refresh.log

date >> daily_mview_refresh.log

#email results
mail -s "Materialized View Refresh Output" ETL-List@mycompany.com < daily_mview_refresh.log

Something to have in mind would be to incorporate appropriate error handling in the stored procedures being called and also providing some output that would indicate progress through the materialized view refresh process. In the scripts I actually use I also collect statistics and rebuild some indices, each view is refreshed in a different procedure as to contain any errors and allow the remaining views to be built anyway.

Tags: , , , , ,

Posted in Misc Technical Tips
No Comments Yet Posted by Ignacio de la Torre

Leave a Comment