# 监控Oracle监听状态(chk_lsnr_stat.sh)

# ======================================================================================

# 监控Oracle监听器状态，发现状态异常启动监听，并发送邮件通知管理员，如果启动监听失败，发送邮件

# 通知管理员。

# ======================================================================================

 

#! /bin/bash

 

. /home/oracle/.bash_profile

 

tempfile=$ORACLE_BASE/admin/$ORACLE_SID/tempfile.lis

 

su - oracle -c "lsnrctl status" > /dev/null

 

if [ $? != '0' ]; then

echo "" >> $tempfile

 

echo "======================================================" >> $tempfile

 

echo "`date +%D-%T`" >> $tempfile

 

su - oracle -c "lsnrctl start" >> $tempfile

 

if [ $? = '0' ]; then

 

cat $tempfile | mail [email protected] -s "The Listener Shutdown,and Restarted Success"

 

else

 

cat $tempfile | mail [email protected] -s "The Listener Shutdown,and Restarted Failed"

 

fi

 

fi

 

 

 

----------------------------------------------------------------------------------------

 

 

# 监控Oracle实例状态(chk_inst_stat.sh)

# =====================================================================================

# 监控Oracle实例是否打开，实例打开时，数据库是否可用，当实例关闭，数据库不可用时发送告警邮件

# 通知管理员(判断时除开+ASM这个特殊实例)

# =====================================================================================

 

#! /bin/bash

 

ORATAB=/etc/oratab

 

tempfile=/home/oracle/tempfile.lis

 

db=`cat $ORATAB |egrep -i ":Y|:N"|cut -d ":" -f 1|grep -v "^+"`

 

pslist="`ps -ef | grep pmon|grep -v grep`"

 

mark=n

 

dbstat=`su - oracle << EOF

 

sqlplus -s /nolog

 

conn / as sysdba

 

set feedback off heading off pagesize 0

 

select status from v\\$instance;

 

exit

 

EOF`

 

for db_name in $db; do

 

echo "$pslist" | grep "ora_pmon_$db_name" > /dev/null 2>&1

 

if ［　$? = "0" ］; then

 

if [ $dbstat != "OPEN" ];then

 

mark=y

 

break

 

fi

 

else

 

mark=y

 

break

 

fi

 

done

 

if [ $mark != 'n' ];then

 

echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>' >> $tempfile

echo "SERVER: $HOSTNAME" >> $tempfile

echo "`date +%D-%T`"  >> $tempfile

echo 'WARN!!! Oracle Database Unavailable' >> $tempfile

echo "Maybe The Following Reasons: The Instance or Database is not OPEN" >>$tempfile

 

echo | mail -s

"Oracle Database Abnormal"　[email protected] < $tempfile

rm -f $tempfile

 

fi

 

------------------------------------------------------------------------------------------

 

 

# 监控归档目录空间（chk_arc_space.sh）

#=====================================================================================

# 将日志目录空间控制在200M，当容量大于200M时，将最旧日志打包复制到其它目录，并删除之

# 直到日志目录空间容量小于200M为止。

#=====================================================================================

 

#! /bin/bash

 

ARC_DIR=/disk01/tbs03

 

BAK_DIR=/opt/arcbackup

 

limit=200

 

capacity()

 

{

 

du -sh $ARC_DIR|awk -F " " '{print $1}'|tr -d M

 

}

 

 

oldlog()

 

{

 

ls -l|sort -k6|sed '1d'|head -1|awk -F " " '{print $9}'

 

}

 

cd $ARC_DIR

 

if [ `capacity` -gt $limit ]; then

 

echo ""|mail -s "The Archivelog Directory is Over Than $limit"\

[email protected]

 

fi

 

while [ `capacity` -gt $limit ]

 

do

 

file=`oldlog`

 

tar -czf $file.`date +%Y%m%d%H%M`.tar.gz $file

 

cp $file.`date +%Y%m%d%H%M`.tar.gz $BAK_DIR/

 

rm -rf $ARC_DIR/$file $ARC_DIR/$file.`date +%Y%m%d%H%M`.tar.gz

 

done

 

 

 

# 监控警告文件错误信息(chk_alert_info.sh)

#================================================================================

# 固定时间间隔内检查alert_$ORACLE_SID.log文件中是否包含ORA-开头的错误信息，如果存在

# 则将其以邮件的形式通知管理员。

#================================================================================

 

#! /bin/bash

 

. /home/oracle/.bash_profile

 

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump/

 

mv alert_$ORACLE_SID.log alert_temp.log

 

touch alert_$ORACLE_SID.log

 

cat alert_temp.log >> alert.$ORACLE_SID.hist

 

grep ORA- alert_temp.log > alert.err

 

if [ `cat alert.err|wc -l` -gt 0 ];then

 

mail -s "ORACLE ALERT ERROR" [email protected] < alert.err

 

fi

 

rm -rf alert.err

 

rm -rf alert_temp.log

 

 

 

# 监控磁盘空间利用率(chk_disk_space)

# ====================================================================

# 判断/dev开头的磁盘或分区空间利用率，当磁盘空间利用率超过90%则发送邮件通知

# 管理员

# ====================================================================

 

#! /bin/bash

 

limit=90%

 

tempfile=chk_disk_space.tmp

 

mark=n

 

diskusage()

 

{

 

df -h|grep -v Filesystem|sed '/\/dev\/mapper/N;s/\n//'|grep "^/dev"|awk -F " " '{print $5}'

 

}

 

for percent in `diskusage`

 

do

 

if [[ $percent > $limit]];then

 

mark=y

 

break

 

fi

 

done

 

if [ $mark != 'n' ];then

 

df -h > $tempfile

 

mail -s "Disk Usage Over than $limit on `hostname`" < $tempfile

 

rm -rf $tempfile

fi

 

 

 

 

# 监控表空间空闲表空间(chk_tbs_free.sh)

#==================================================================================

# 监控空闲表空间，当空闲表空间低于20%时，发送邮件通知管理员

#==================================================================================

 

#! /bin/bash

 

su - oracle > /dev/null << EOF

sqlplus -s /nolog

conn / as sysdba

set feedback off

set heading off

set verify off

set pagesize 0

set linesize 200

 

spool tbsfree.alert

 

select t.tablespace_name,f.free_space/t.total_space from

(select tablespace_name,sum(bytes) total_space from

dba_data_files group by tablespace_name) t,

(select tablespace_name,sum(bytes) free_space from

dba_free_space group by tablespace_name) f

where t.tablespace_name=f.tablespace_name and f.free_space/t.total_space < 0.20

/

 

spool off

 

exit

 

EOF

 

if [ `cat tbsfree.alert|wc -l` -gt 0 ];then

 

cat tbsfree.alert|mail -s "No Free Space in Oracle db" [email protected]

 

rm -rf tbsfree.alert

 

fi

 

 

# 全库冷备份(full_cold_backup.sh)

# =================================================================================

# 数据库打开时，自动生成备份脚本。然后关闭数据库，对控制文件，数据文件，重做日志文件，

# 初始化参数文件及口令文件做冷备，完成后打开数据库。

# =================================================================================

 

#! /bin/bash

 

. /home/oracle/.bash_profile

 

backup_dir=/disk01/backup/coldbak

 

log_file=/disk01/backup/coldbak/cold_backup_$ORACLE_SID.log

 

echo 'Begin Cold Backup>>>>>>>>>>>>>>>>'  >> $log_file

 

date >> $log_file

 

su - oracle > /dev/null << EOF

 

sqlplus -s /nolog

 

conn / as sysdba

 

set feedback off heading off pagesize 0 line 1000

 

spool file_copy_$ORACLE_SID.sh

 

select 'cp ' || name || ' $backup_dir/' from v$controlfile;

 

select 'cp' || file_name || ' $backup_dir/' from dba_data_files;

 

select 'cp' || member || ' $backup_dir/' from v$logfile;

 

spool off

 

shutdown immediate

 

! bash file_copy_$ORACLE_SID.sh

 

startup

 

exit

 

EOF

 

if [ -e $ORACLE_HOME/dbs/init$ORACLE_SID.ora ];then

 

cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora $backup_dir/

 

fi

 

if [ -e $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora ];then

 

cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $backup_dir/

 

fi

 

if [ -e $ORACLE_HOME/dbs/orapw$ORACLE_SID ];then

 

cp $ORACLE_HOME/dbs/orapw$ORACLE_SID $backup_dir/

 

fi

 

 

echo 'Cold Backup Finished>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>' >>$log_file

 

date >> $log_file

 

 

# RMAN备份SHELL脚本(rman_backup.sh)

#===========================================================================

# 实现指定级别的增量备份，由用户传入备份级别参数，如果不指参数则进行0级备份

#==========

=================================================================

 

#! /bin/bash

 

. /home/oracle/.bash_profile

 

if [ $1 ];then

 

backup_level=$1

 

else

 

backup_level=0

 

fi

 

backup_user=sys

 

backup_user_pw=oracle

 

#catalog_user=rman

 

#catalog_user_pw=rman

 

log_file=/home/oracle/rman_backup.log

 

echo 'Begining rman backup   >>>>>>>>>>>>>>>>>>>>>>>>>>'  >> $log_file

 

date >> $log_file

 

su - oracle >> $log_file << EOF

 

rman target $backup_user/$backup_user_pw

# catalog $catalog_user/$catalog_user_pw

 

backup incremental level = $backup_level database;

 

quit;

 

EOF

 

echo 'rman backup finished   >>>>>>>>>>>>>>>>>>>>>>>>>'  >> $log_file

 

date >> $log_file

 

 

 

# 逻辑备份SHELL脚本(schema_exp.sh)

#=========================================================================

# EXP对数据库schema对象进行备份，用户可以将需要备份的用户名做为参数传入SHELL脚本

#=========================================================================

 

#! /bin/bash

 

BAK_DIR=/disk01/backup/logical_bak/

 

log_file=/disk01/backup/logical_bak/user_full_bak.log

 

exp_par="userid=system/oracle buffer=10485760 owner=$1"

 

if [ $2 ];then

 

exp_par="$exp_par file=$2"

 

else

 

exp_par="$exp_par file="$BAK_DIR/$1_`date +%Y%m%d%H%M`.dmp""

 

fi

 

echo "Begining User $1 Export ---------------------" >> $log_file

 

echo "Export with following parameters: $exp_par" >> $log_file

 

date >> $log_file

 

su - oracle -c "exp $exp_par" >> $log_file 2>&1

 

echo "Backup Finished ---------------------" >> $log_file

 

date >> $log_file