Oracle巡检脚本
来源：互联网 发布：网络改造方案 编辑：程序博客网 时间：2024/05/18 00:48
巡检项：
cpu使用率，内存使用率，文件系统使用率，日志文件大小，oracle后台日志(有无异常)，oracle连接数使用情况，oracle表空间使用率，asm存储使用率，失效索引，rman备份日志(有无异常)
主巡检脚本：Oracheck.sh
[oracle@orcl scripts]$ cat oracheck.sh #!/bin/sh# Get HostnameDB_CONN_STR=system/oraclesource /home/oracle/.bash_profile logfile=oracheck_`date +%y%m%d`.loghostinfodir=/home/oracle/oracheckDB_UNIQUE_NAME=orclif [[ ! -d $hostinfodir ]]then mkdir -p $hostinfodirfiecho -e "==== Oracheck started at $(date +"%y-%m-%d %H:%M:%S") ===\n" >> $hostinfodir/$logfileecho -e "*** step1.CPU使用率检查 \n" >> $hostinfodir/$logfilesar 1 10 >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step2.内存使用率检查 \n" >> $hostinfodir/$logfilefree -g >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step3.文件系统使用率检查 \n" >> $hostinfodir/$logfiledf -h >>$hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step4.日志文件大小检查 \n" >> $hostinfodir/$logfileecho -e "trace目录(trc,trm,alert_sid)：" >> $hostinfodir/$logfiledu -sh $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace >>$hostinfodir/$logfileecho -e "alert目录(log_*.xml)：" >> $hostinfodir/$logfiledu -sh $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/alert >>$hostinfodir/$logfileecho -e "监听日志(listener.log)：" >> $hostinfodir/$logfiledu -sh $ORACLE_HOME/network/admin/listener.ora >>$hostinfodir/$logfileecho -e "审计目录(adump)：" >> $hostinfodir/$logfiledu -sh $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump >>$hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step5.oracle后台日志检查 \n" >> $hostinfodir/$logfiletail -3000 $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log |grep ORA- >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step6.oracle连接数检查 \n" >> $hostinfodir/$logfilesh /home/oracle/oracheck/scripts/process_check.sh >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step7.oracle表空间使用率检查 \n" >> $hostinfodir/$logfilesh /home/oracle/oracheck/scripts/tbs_check.sh >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step8.asm存储使用率检查 \n" >> $hostinfodir/$logfilesh /home/oracle/oracheck/scripts/asm_check.sh >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step9.失效索引检查 \n" >> $hostinfodir/$logfilesh /home/oracle/oracheck/scripts/invalid_index_check.sh >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "*** step10.rman备份检查 \n" >> $hostinfodir/$logfileecho -e "rman备份日志：" >> $hostinfodir/$logfile# cat /backup/logs/rman`date +%Y%m%d`.log |grep ORA- >> $hostinfodir/$logfileecho -e "rman备份集校验日志：" >> $hostinfodir/$logfile# cat /backup/logs/rman_validate`$date+%Y%m%d`.log |grep ORA- >> $hostinfodir/$logfileecho -e "\n" >> $hostinfodir/$logfileecho -e "==== Oracheck ended at $(date +"%y-%m-%d %H:%M:%S") ===\n" >> $hostinfodir/$logfileexit 0
process_check.sh
[oracle@orcl scripts]$ cat process_check.sh sqlplus -s $DB_CONN_STR <<EOF set linesize 300col resource_name format a15select resource_name,current_utilization,limit_value, trunc(current_utilization * 100 / limit_value) Result_Number from v\$resource_limit where resource_name in ('processes');EOFexittbs_check.sh
[oracle@orcl scripts]$ cat tbs_check.sh sqlplus -s $DB_CONN_STR <<EOFset linesize 300col tablespace_name format a20SELECT DF.TABLESPACE_NAME, COUNT(*) DATAFILE_COUNT, ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB, ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB, ROUND(SUM(DF.BYTES) / 1048576 / 1024 - SUM(FREE.BYTES) / 1048576 / 1024, 2) USED_GB, ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE, 100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED, ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE FROM DBA_DATA_FILES DF, (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) BYTES, MAX(BYTES) MAXBYTES FROM DBA_FREE_SPACE WHERE BYTES > 1024 * 1024 GROUP BY TABLESPACE_NAME, FILE_ID) FREE WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+) AND DF.FILE_ID = FREE.FILE_ID(+) GROUP BY DF.TABLESPACE_NAME ORDER BY 8;EOFexit
asm_check.sh
[oracle@orcl scripts]$ cat asm_check.sh sqlplus -s $DB_CONN_STR <<EOF select name,state,total_mb,free_mb,usable_file_mb from v\$asm_diskgroup;EOFexit
invalid_index_check.sh
[oracle@orcl scripts]$ cat invalid_index_check.sh sqlplus -s $DB_CONN_STR <<EOF select owner, index_name, status from dba_indexes where status not in ('VALID','N/A') order by 1, 2; select i.owner, i.index_name, p.partition_name, p.status from dba_ind_partitions p, dba_indexes i where p.index_name = i.index_name and p.index_owner=i.owner and p.status != 'USABLE' order by 1, 2, 3;select i.owner, i.index_name, s.subpartition_name, s.status from dba_ind_subpartitions s, dba_indexes i where s.index_name = i.index_name and s.index_name = i.index_name and s.status != 'USABLE' order by 1, 2, 3;EOFexitrman_validate.sh(校验前一天的备份集)
#!/bin/bash# +-----------------------------------------------------------------------+# | EXPORT ENVIRONMENT VARIABLE OF ORACLE USER |# +-----------------------------------------------------------------------+source ~/.bash_profile;# +-----------------------------------------------------------------------+# | GLOBAL VARIABLES ABOUT THE ABSOLUTE PATH OF THE SHELL COMMAND |# +-----------------------------------------------------------------------+export AWK=`which awk`export DATE=`which date`export ECHO=`which echo`# +-----------------------------------------------------------------------+# | GLOBAL VARIABLES ABOUT STRINGS AND BACKTICK EXECUTION RESULT OF SHELL |# +-----------------------------------------------------------------------+export BACK_LOG=~/backup/logsexport RMAN=$ORACLE_HOME/bin/rmanexport SQLPLUS=$ORACLE_HOME/bin/sqlplusexport YESTERDAY=`$DATE +%Y-%m-%d -d yesterday`export DAY_OF_WEEK=`$DATE +%u`export BSKEY_LIST=export BSKEY_LIST_WITH_COMMA=# +-----------------------------------------------------------------------+# | QUERY ALL OF BS_KEY VALUE OF RMAN BACKUPSET YESTERDAY INTO BSKEY_LIST |# +-----------------------------------------------------------------------+BSKEY_LIST=`$SQLPLUS -S /nolog << EOFconnect / as sysdbaset echo off feedback off heading off underline offselect bs_key from v\\$backup_set_details where device_type='DISK' and completion_time > to_date('$YESTERDAY','yyyy-mm-dd') order by 1;exit;EOF`# +-----------------------------------------------------------------------+# | WITH AWK COMMAND TO PROCESS BSKEY_LIST SAVE TO BSKEY_LIST_WITH_COMMA |# +-----------------------------------------------------------------------+BSKEY_LIST_WITH_COMMA=`$ECHO $BSKEY_LIST | $AWK -F' ' '{ for ( i=1; i<NF; i++ ) print $i","; print $NF }'`# +-----------------------------------------------------------------------+# | VALIDATE RMAN BACKUPSET THAT IS GENERATED LAST NIGHT |# +-----------------------------------------------------------------------+case $DAY_OF_WEEK in6)$RMAN nocatalog log $BACK_LOG/rman_validate`$DATE +%Y%m%d`.log <<EOFconnect target /run {allocate channel d1 type disk maxpiecesize 16g;allocate channel d2 type disk maxpiecesize 16g;allocate channel d3 type disk maxpiecesize 16g;allocate channel d4 type disk maxpiecesize 16g;validate backupset $BSKEY_LIST_WITH_COMMA check logical;release channel d4;release channel d3;release channel d2;release channel d1;}exit;EOF;;1|2|3|4|5|7)$RMAN nocatalog log $BACK_LOG/rman_validate`$DATE +%Y%m%d`.log <<EOFconnect target /validate backupset $BSKEY_LIST_WITH_COMMA check logical;exit;EOF;;esac巡检结果：
==== Oracheck started at 17-11-18 14:15:20 ===*** step1.CPU使用率检查 Linux 2.6.32-431.el6.x86_64 (orcl) 11/18/2017 _x86_64_ (1 CPU)02:15:20 PM CPU %user %nice %system %iowait %steal %idle02:15:21 PM all 0.00 0.00 1.01 1.01 0.00 97.9802:15:22 PM all 0.00 0.00 1.02 0.00 0.00 98.9802:15:23 PM all 0.00 0.00 1.03 0.00 0.00 98.9702:15:24 PM all 1.03 0.00 1.03 0.00 0.00 97.9402:15:25 PM all 0.00 0.00 3.03 0.00 0.00 96.9702:15:26 PM all 0.00 0.00 2.08 0.00 0.00 97.9202:15:27 PM all 0.00 0.00 3.09 1.03 0.00 95.8802:15:28 PM all 1.02 0.00 3.06 0.00 0.00 95.9202:15:29 PM all 0.00 0.00 2.08 0.00 0.00 97.9202:15:30 PM all 1.02 0.00 3.06 0.00 0.00 95.92Average: all 0.31 0.00 2.05 0.21 0.00 97.44*** step2.内存使用率检查 total used free shared buffers cachedMem: 1 1 0 0 0 1-/+ buffers/cache: 0 1Swap: 3 0 3*** step3.文件系统使用率检查 Filesystem Size Used Avail Use% Mounted on/dev/sda3 36G 22G 12G 65% /tmpfs 932M 266M 666M 29% /dev/shm/dev/sda1 194M 34M 151M 19% /boot*** step4.日志文件大小检查 trace目录(trc,trm,alert_sid)：1.6M /u01/app/oracle/diag/rdbms/orcl/orcl/tracealert目录(log_*.xml)：592K /u01/app/oracle/diag/rdbms/orcl/orcl/alert监听日志(listener.log)：4.0K /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora审计目录(adump)：820K /u01/app/oracle/admin/orcl/adump*** step5.oracle后台日志检查 ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'ORA-27037: unable to obtain file status*** step6.oracle连接数检查 RESOURCE_NAME CURRENT_UTILIZATION LIMIT_VALUE RESULT_NUMBER--------------- ------------------- -------------------- -------------processes 34 150 22*** step7.oracle表空间使用率检查 TABLESPACE_NAME DATAFILE_COUNT SIZE_GB FREE_GB USED_GB MAXFREE PCT_USED PCT_FREE-------------------- -------------- ---------- ---------- ---------- ---------- ---------- ----------SYSTEM 1 .74 .01 .74 .01 99.08 .92SYSAUX 1 .57 .03 .54 .03 94.83 5.17USERS 1 0 0 0 0 26.25 73.75UNDOTBS1 2 .17 .14 .03 .08 17.06 82.94TBS_DATA 1 .2 .19 .01 .19 3 97*** step8.asm存储使用率检查 no rows selected*** step9.失效索引检查 no rows selectedno rows selectedno rows selected*** step10.rman备份检查 rman备份日志：rman备份集校验日志：==== Oracheck ended at 17-11-18 14:15:30 ===
阅读全文
0 0
- ORACLE 巡检脚本
- ORACLE 巡检脚本
- ORACLE 巡检脚本
- oracle 巡检脚本
- oracle巡检脚本
- oracle自动化巡检脚本
- Oracle巡检脚本
- oracle 自动化巡检脚本(V1)
- 实战:oracle巡检脚本v1
- oracle 巡检脚本（自动化）
- oracle 10g RAC 巡检脚本
- oracle on linux 巡检脚本-部分
- [Python] Oracle rac dg 巡检脚本
- oracle巡检
- AIX巡检脚本
- linux日常巡检脚本
- 数据库巡检脚本
- Aix巡检脚本
- MT6572的所有版本对语音解锁和语音控制功能的支持情况
- ZigBee---z-stack--低功耗实验--学习笔记
- Soap 协议 详解
- 解决应用服务器集群后的Session问题
- js判断搜索引擎代码,然后进行调整代码
- Oracle巡检脚本
- 无人车之父Sebastian Thrun：技术小白，也能从零开始造一辆无人车！
- Problem K 素土豆
- 上传项目到码云
- 机器学习从零开始系列连载(2)——线性回归
- 【VUE】使用Table组件进行数据双向绑定
- Paxos的工程实践之Chubby。
- android开发Enum （枚举）的更轻量级的替代方案 —— @IntDef的使用
- spring boot实战(第六篇)加载application资源文件源码分析
