Friday, September 16, 2011

Moinitor Invalid Packages in SYS to debug an issue

file name: sys_monitor.sh

#!/bin/bash


#Begin environment setup
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB
export PATH=$PATH:/usr/sbin:$ORACLE_HOME/bin:$HOME/bin
CURRENT_DATE=`date +%Y%m%d`
logfile="/home/oracle/bin/dba_scripts/sys_monitor$CURRENT_DATE.log"

# Begin sql script
sqlplus  /nolog @ '/home/oracle/bin/dba_scripts/sys_monitor.sql' > $logfile

# Validation and e-mail status
status=$?
if [ $status -gt 0 ]; then
mailx  -s "sys monitor info  FAILED" dba@testdb.com<< !
`cat $logfile`
!
else
 mailx  -s "sys monitor info SUCCESSFUL"dba@testdb.com<< !
 `cat $logfile`
!
fi

rm $logfile


--------------------
filename: sys_monitor.sql

connect / as sysdba
select 'ALTER PACKAGE SYS.' || OBJECT_NAME || ' COMPILE;' from dba_objects
where owner='SYS' and object_type='PACKAGE' AND STATUS='INVALID';


select 'ALTER PACKAGE SYS.' || OBJECT_NAME || ' COMPILE BODY;' from dba_objects
where owner='SYS' and object_type='PACKAGE BODY' AND STATUS='INVALID';

exit;







No comments:

Post a Comment