Oracle:Script to track Database Changes

This script monitors your database environments for any newly changed objects, this will help DBAs to keep track of Database changes and also if you follow change control procedures.

#!/bin/sh 
################################################### 
#PROGRAM NAME: Weekly Reports for monitoring Database Environments 
#WRITTEN BY : Milind 
#Version: 1.0 
#DATE : 07/12/2007 
################PLEASE APPEND REVISION HISTORY BELOW################## 
set -x 
#Check Input And Print Usage. 
NUMARGS=$# 
if [ $NUMARGS != 1 ] 
then 
echo “Please review the script before running” 
echo “Usage : $0 <Oracle Instance>” 
echo “Example: $0 DEVDB” 
exit 1 
fi 
#Initialize the Environment variable setup 
#Start of Configuration Parameters 
#Generic Parameters 
export RUN_DATE=`date ‘+%y_%m_%d_%H_%M’` 
export DATE=`date ‘+%m%d%y’` 
export TIME=`date ‘+%H:%M:%S’` 
export FAIL_MODE=0 
export ORACLE_SID=$1 
export ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d”:” -f2` 
export PATH=$PATH:/usr/bin:/etc:/usr/sbin:$ORACLE_HOME/bin 
export LD_LIBRARY_PATH=$PATH:$ORACLE_HOME/lib:/usr/local/lib 
export TNS_ADMIN=$ORACLE_HOME/network/admin 
export HOST_NAME=`uname -n` 
export LOG_RET=30 
#Parameters Specific to the Server 
export SCRIPT_HOME=/idisprod3/export/infracare/reports #<—CONFIGURE ME PLEASE 
#Email Configuration 
export MAIL_USERS=’Your Email Here, Your Email Here’ #<—CONFIGURE ME PLEASE 
#Define Functions 
################################## 
# INITIATE THE MONTHLY REPORT 
################################## 
start_weekly() 
{ 
touch $SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log 
echo “#BEGIN WEEKLY REPORTING FOR ${ORACLE_SID}” >>$SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log 
echo “WEEKLY REPORT starting on $DATE $TIME” >>$SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log 
echo “DATABSE : $ORACLE_SID” >>$SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log 
} 
################################## 
# END THE WEEKLY REPORT 
################################## 
end_weekly() 
{ 
echo “#END WEEKLY REPORTING FOR ${ORACLE_SID}” >> $SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log 
mv $SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.log $SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.${RUN_DATE} 
find ${SCRIPT_HOME}/log -name ‘*.log.*’ -mtime +${LOG_RET} -exec rm -f {} \; 2> /dev/null & 
} 
################################## 
# Notification Email 
################################## 
notify() 
{ 
/bin/mail -s “WEEKLYy Report Failed with errors for ${ORACLE_SID} on $DATE at $TIME” $MAIL_USERS< $SCRIPT_HOME/log/weekly_report.${ORACLE_SID}.${RUN_DATE} 
} 
################################## 
# START OF FUNCTIONS 
################################## 
track_object_change() 
{ 
$ORACLE_HOME/bin/sqlplus <User>/<Password> <<EOF 
WHENEVER SQLERROR EXIT FAILURE 
@${SCRIPT_HOME}/bin/track_object_change.sql 
EOF 
usrcnt=0 
set `wc -l ${SCRIPT_HOME}/log/${ORACLE_SID}_track_object_change_report.txt` 
usrcnt=$1 
echo “${usrcnt}” 
if [ $usrcnt -eq 0 ] 
then 
echo “INFRACARE:000:No Objects changed during past 7 days in ${ORACLE_SID}.” >> $SCRIPT_HOME/log/weekly_reports.runtime.${ORACLE_SID}.log 
elif [ $usrcnt -ge 1 ] 
then 
echo ” ” 
echo “INFRACARE:001:Changed Objects found in past 7 days in ${ORACLE_SID}.” >> $SCRIPT_HOME/log/weekly_reports.runtime.${ORACLE_SID}.log 
FAIL_MODE=1 
mutt -s “Weekly Report for ${ORACLE_SID}: Changed Objects from past 7 days in ${ORACLE_SID}.” -a $SCRIPT_HOME/log/${ORACLE_SID}_track_object_change_report.txt ${MAIL_USERS} <$SCRIPT_HOME/cfg/report_email.txt 
else 
mailx -s “Weekly Report for ${ORACLE_SID}: Changed Objects from past 7 days in ${ORACLE_SID}.” ${MAIL_USERS} < “No objects changed in past 7 days in ${ORACLE_SID}” 
fi 
} 
########################################### 
#END OF FUNCTIONS 
########################################### 
#Call Functions 
start_weekly 
track_object_change 
end_weekly

Create a sql file at ${SCRIPT_HOME}/bin/track_object_change.sql in following format :

TTITLE CENTER ‘Weekly Report for Monitoring changed objects in DATABASE environment’ 
TTITLE CENTER ‘WEEKLY REPORT FOR MONITORING CHANGED OBJECTS’ SKIP 1 - 
CENTER ============================================= SKIP 1 LEFT ‘By: Infracare Monitoring’ - 
RIGHT ‘WEEKLY REPORT’ SKIP 2 
spool ${SCRIPT_HOME}/log/${ORACLE_SID}_track_object_change_report.txt 
COLUMN OWNER HEADING ‘Object Owned By’ FORMAT a15 
COLUMN OBJECT_NAME HEADING ‘Object Name’ FORMAT a30 
COLUMN OBJECT_Type HEADING ‘Object Type’ FORMAT a15 
COLUMN last_ddl_time HEADING ‘Changed:’ 
SET UNDERLINE = 
SET PAGES 20 
SET PAGESIZE 30 
SET LINESIZE 100 
SET FEEDBACK OFF 
BREAK ON OWNER; 
select owner, object_name, object_type, last_ddl_time “Last_ddl_time” 
from dba_objects 
where 
owner not in (’SYS’,'SYSTEM’,'SYSMAN’,'PUBLIC’) and 
object_name not like ‘BIN%’ and 
last_ddl_time > sysdate -7 
group by owner, last_ddl_time,object_name, object_type 
order by owner,last_ddl_time,object_name, object_type 
;