Oracle数据库管理常用的监控脚本------极大的简化运维工作
Oracle数据库管理常用的监控脚本------极大的简化运维工作
2017-11-15 1218
简介:
最近几天都研究SHELL脚本,为了方便对公司的Oracle运维,简化管理,学习一些SHELL脚本是非常有必要的,通过书本和网上的一些资料,整理出了一些比较精典的脚本,都是经过清自测试可行的,放上来共大家分享。
- # 监控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 dba@163.com -s "The Listener Shutdown,and Restarted Success"
- else
- cat $tempfile | mail dba@163.com -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" dba@163.com < $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"\
- dba@163.com
- 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" dba@163.com < 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" dba@163.com
- 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