需求：
每天需要定时巡检分别跑在linux与 hpux 上的 几套 rac 与 dg 状态并 做成 excel 发给相关责任人...
有了上篇 网络设备巡检 脚本的 基础，这个也可以玩一会了。
1、数据采集
hpux 版本：
info.sh # 采集CPU、内存使用率
| #!/usr/bin/sh tday=`date +%Y%m%d` sar_info=`sar -u 1 5` #tol_mem=`dmesg | grep Phy | awk '{print $2}'` tol_mem=268127072 if [ -f /home/oracle/xunjian/info/info.$tday ];then mv /home/oracle/xunjian/info/info.$tday /home/oracle/xunjian/info/info.bak.$tday fi /usr/bin/top -h -d1 -f /home/oracle/xunjian/info/info.$tday cpu_idle=`echo "$sar_info" | grep Average | awk '{print $NF}'` cpu_used=`expr 100 - $cpu_idle` mem_free=`cat /home/oracle/xunjian/info/info.$tday | grep Memory | awk '{print $8}' | sed 's/K//g'` mem_used=`awk 'BEGIN{printf "%.2f%\n",100-'$mem_free'/'$tol_mem'*100}'` echo "$cpu_used% $mem_used" |
asm.sh # 采集 ASM 磁盘使用情况
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
sqlplus / as sysdba <<EOF col percent for a30 col name for a10 set linesize 1000 select group_number,name,TOTAL_MB,FREE_MB,USABLE_FILE_MB,to_char(round(free_mb/total_mb*100,2))||'%' percent,STATE from v\$asm_diskgroup; EOF |
unusable_object.sh # 采集 失效对象
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
sqlplus / as sysdba <<EOF col object_name for a30 set linesize 1000 col TO_CHAR(LAST_DDL_TIME,'YYYYMMDDHH24:MI') for a30 col owner for a10 select object_type,owner,object_name,to_char(last_ddl_time,'yyyymmdd hh24:mi') from dba_objects where owner!='SYS' and status!='VALID' order by object_type / EOF |
parallel.sh # 采集 并行度 > 1 的索引
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
sqlplus / as sysdba <<EOF select table_name,owner,index_name,degree from dba_indexes where rtrim(ltrim(degree)) not in ('0','1','DEFAULT') / EOF |
unusable_index.sh # 采集 无效索引
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
sqlplus / as sysdba <<EOF col owner for a10 col index_name for a40 col table_owner for a10 col table_name for a30 col tablespace_name for a20 set linesize 1000 select owner,index_name,index_type,table_owner,table_name,tablespace_name,status from dba_indexes where status!='N/A' and status<>'VALID' / select index_owner,index_name,tablespace_name,status from dba_ind_partitions where status!='USABLE' / EOF |
state.sh # 检查数据库基本状况
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
tday=`date +%Y%m%d` mkdir -p /home/oracle/xunjian/state/$tday sqlplus / as sysdba <<EOF set linesize 1000 col host_name for a20 col instance_name for a20 spool /home/oracle/xunjian/state/$tday/state.shili select instance_name,host_name,startup_time,status,database_status from v\$instance; spool off spool /home/oracle/xunjian/state/$tday/curr.session select count(*) from v\$session; spool off spool /home/oracle/xunjian/state/$tday/max.session show parameter sessions; spool off spool /home/oracle/xunjian/state/$tday/max.process select value from v\$parameter where name='processes'; spool off spool /home/oracle/xunjian/state/$tday/state.tablespace select tablespace_name,status from dba_tablespaces; spool off col name for a50 spool /home/oracle/xunjian/state/$tday/state.datefile select name,status from v\$datafile; spool off spool /home/oracle/xunjian/state/$tday/state.logfile select group#,status,type,member from v\$logfile; spool off EOF |
crs.sh
| #!/usr/local/bin/bash export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/11.2/grid export ORACLE_SID=+ASM1 export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
crsctl stat res -t |
basic_status.sh # 检查数据库基本状况
| #!/usr/bin/sh tday=`date +%Y%m%d` dir="/home/oracle/xunjian/state/$tday"
su - oracle "/home/oracle/xunjian/state.sh" > /dev/null 2>&1 if [ $? -ne 0 ];then echo "state.sh error" else
sl_state=`more $dir/state.shili | awk '{print $NF}' | sed -n '5p'` if [ $sl_state = "ACTIVE" ];then sl_newstate=0 fi curr_session=`more $dir/curr.session | awk '{print $NF}' | sed -n '5p'` max_session=`more $dir/max.session | grep "^session" | awk '{print $NF}'` max_process=`more $dir/max.process | awk '{print $NF}' | grep "^[0-9]"` state_space=`more $dir/state.tablespace | awk '{print $2}' | egrep -v "select|---|STATUS|rows|spool|^ONLINE|^$" | wc -l` state_datefile=`more $dir/state.datefile | awk '{print $NF}' | egrep -v "^ONLINE|SYSTEM|STATUS|dbf|----|selected|off|datafile|^$" | wc -l` state_log=`more $dir/state.logfile | awk '{print $2}' | egrep -v "select|^ONLINE|STATUS|^STANDBY|---|rows|spool|^$" | wc -l`
su - grid "/home/oracle/xunjian/crs.sh" > $dir/state.crs if [ $? -ne 0 ];then echo "crs.sh error" else ora_asm=`more $dir/state.crs | awk '{print $NF}' | grep Started | wc -l` ora_orcl_db=`more $dir/state.crs | awk '{print $NF}' | grep ^Open$ | wc -l` if [ $ora_asm -eq 2 ] && [ $ora_orcl_db -eq 2 ];then state_crs=0 else state_crs=1 fi
echo "$sl_newstate $curr_session $max_session $max_process $state_space $state_datefile $state_log $state_crs" fi fi |
tablespace_use.sh # 表空间大小统计
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
sqlplus / as sysdba <<EOF col TBS_NAME for a30 SELECT UPPER(F.TABLESPACE_NAME) "TBS_NAME", D.TOT_GROOTTE_MB "SIZE(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USED(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "USED%" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC / EOF |
rman_log.sh # 检查 rman备份、expdp，dg同步等log是否存在 error
| #!/usr/bin/sh tday=`date +%Y%m%d` rman_log=/backup/rman/rmanlog.log ora_err=`grep "ORA-" $rman_log | awk -F : '{print $1}'` if [ -n $ora_err ];then a=$ora_err else a='0' fi echo $a,'N|L' |
dgcheck.sh
| #!/usr/bin/sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2 export ORACLE_SID=jiaoyi1 export ORACLE_UNQNAME=jiaoyi export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin export ORACLE_TERM=xterm export TNS_ADMIN=$ORACLE_HOME/network/admin #export ORA_NLS10=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export CLASSPATH=$ORACLE_HOME/JRE export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib export THREADS_FLAG=native export TEMP=/tmp export TMPDIR=/tmp export PS1=`hostname`:'$PWD'"$"
tday=`date +%Y%m%d` mkdir -p /home/oracle/xunjian/state/$tday sqlplus / as sysdba <<EOF set linesize 500 col dest_name for a30 col error for a50 spool /home/oracle/xunjian/state/$tday/dg.check select dest_id, dest_name, status, error from v\$archive_dest order by dest_id / spool off EOF |
dg.sh
| #!/usr/bin/sh tday=`date +%Y%m%d` su - oracle "/home/oracle/xunjian/dgcheck.sh" >/dev/null 2>&1 if [ $? -eq 0 ];then if [ -f /home/oracle/xunjian/state/$tday/dg.check ];then num=`more /home/oracle/xunjian/state/$tday/dg.check | awk '{print $3}' | grep ^INVALID | wc -l` if [ $num -eq 0 ];then a='0' else a='1' fi fi fi echo $a |
ora_err.sh # ORA 报错统计
| #!/usr/bin/sh dt=`date | awk '{print $3}'` if [ $dt -lt 10 ];then td=`date | awk '{print $1,$2,0$3}'` else td=`date | awk '{print $1,$2,$3}'` fi dir=/u01/app/oracle/diag/rdbms/jiaoyi/jiaoyi2/trace/alert_jiaoyi2.log begin=`grep -n "$td" $dir | head -1 | awk -F : '{print $1}'` err=`sed -n ''"$begin"',$p' $dir | grep ORA-` if [ -n "$err" ];then echo "$err" else echo 0 fi |
linux 版本：
info.sh
| #!/bin/bash mem_total=`free -m | grep Mem | awk '{print $2}'` mem_use=`free -m | grep cache: | awk '{print $3}'` mem_used=`awk 'BEGIN{printf "%.2f%\n",'$mem_use'/'$mem_total'*100}'`
sar_info=`sar -u 1 5` cpu_idle=`echo "$sar_info" | sed -n '$p' | awk '{print $NF}'` cpu_used=`awk 'BEGIN{printf "%.2f%\n",100-'$cpu_idle'}'` echo -e "$cpu_used\t$mem_used" |
asm.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
sqlplus / as sysdba <<EOF col name for a10 set linesize 1000 select group_number,name,TOTAL_MB,FREE_MB,USABLE_FILE_MB,to_char(round(free_mb/total_mb*100,2))||'%' percent,STATE from v\$asm_diskgroup; EOF |
unusable_object.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
sqlplus / as sysdba <<EOF col object_name for a30 set linesize 1000 col TO_CHAR(LAST_DDL_TIME,'YYYYMMDDHH24:MI') for a30 col owner for a10 select object_type,owner,object_name,to_char(last_ddl_time,'yyyymmdd hh24:mi') from dba_objects where owner!='SYS' and status!='VALID' order by object_type / EOF |
parallel.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
sqlplus / as sysdba <<EOF select table_name,owner,index_name,degree from dba_indexes where rtrim(ltrim(degree)) not in ('0','1','DEFAULT') / EOF |
unusable_index.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
sqlplus / as sysdba <<EOF col owner for a10 col index_name for a40 col table_owner for a10 col table_name for a30 col tablespace_name for a20 set linesize 1000 select owner,index_name,index_type,table_owner,table_name,tablespace_name,status from dba_indexes where status!='N/A' and status<>'VALID' / select index_owner,index_name,tablespace_name,status from dba_ind_partitions where status!='USABLE' / EOF |
state.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
tday=`date +%Y%m%d` mkdir -p /home/oracle/xunjian/state/$tday sqlplus / as sysdba <<EOF set linesize 1000 col host_name for a20 col instance_name for a20 spool /home/oracle/xunjian/state/$tday/state.shili select instance_name,host_name,startup_time,status,database_status from v\$instance; spool off spool /home/oracle/xunjian/state/$tday/curr.session select count(*) from v\$session; spool off spool /home/oracle/xunjian/state/$tday/max.session show parameter sessions; spool off spool /home/oracle/xunjian/state/$tday/max.process select value from v\$parameter where name='processes'; spool off spool /home/oracle/xunjian/state/$tday/state.tablespace select tablespace_name,status from dba_tablespaces; spool off col name for a50 spool /home/oracle/xunjian/state/$tday/state.datefile select name,status from v\$datafile; spool off spool /home/oracle/xunjian/state/$tday/state.logfile select group#,status,type,member from v\$logfile; spool off EOF |
basic_status.sh
| #!/bin/bash tday=`date +%Y%m%d` dir="/home/oracle/xunjian/state/$tday"
su - oracle "/home/oracle/xunjian/state.sh" > /dev/null 2>&1 if [ $? -ne 0 ];then echo "state.sh error" else sl_state=`more $dir/state.shili | awk '{print $NF}' | grep ^ACTIVE$ | wc -l` if [[ $sl_state -eq 1 ]];then sl_newstate=0 fi curr_session=`grep [0-9] $dir/curr.session| awk '{print $1}'` max_session=`more $dir/max.session | grep "^session" | awk '{print $NF}'` max_process=`more $dir/max.process | awk '{print $NF}' | grep "^[0-9]"` state_space=`more $dir/state.tablespace | awk '{print $2}' | egrep -v "select|---|STATUS|rows|spool|^ONLINE|^$" | wc -l` state_datefile=`more $dir/state.datefile | awk '{print $2}' | egrep -v "^ONLINE|SYSTEM|STATUS|----|select|rows|spool|off|datafile|^$" | wc -l` state_log=`more $dir/state.logfile | awk '{print $2}' | egrep -v "select|^ONLINE|STATUS|^STANDBY|---|rows|spool|^$" | wc -l`
su - grid "/home/oracle/xunjian/crs.sh" > $dir/state.crs if [ $? -ne 0 ];then echo "crs.sh error" else ora_asm=`more $dir/state.crs | awk '{print $NF}' | grep Started | wc -l` ora_orcl_db=`more $dir/state.crs | awk '{print $NF}' | grep ^Open$ | wc -l` if [ $ora_asm -eq 2 ] && [ $ora_orcl_db -eq 2 ];then state_crs=0 else state_crs=1 fi
echo "$sl_newstate $curr_session $max_session $max_process $state_space $state_datefile $state_log $state_crs" fi fi |
tablespace_use.sh
| #!/bin/bash PATH=$PATH:$HOME/bin
export PATH ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db export ORACLE_HOME ORACLE_SID=malldb1 export ORACLE_SID LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib:. export LD_LIBRARY_PATH SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:. export SHLIB_PATH NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_LANG PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/usr/sbin:/usr/bin/X11:$PATH:. export PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export CLASSPATH PS1=$LOGNAME@`hostname`:'$PWD''$ ' export PS1
sqlplus / as sysdba <<EOF SELECT UPPER(F.TABLESPACE_NAME) "TBS_NAME", D.TOT_GROOTTE_MB "SIZE(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USED(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "USED%" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC / EOF |
rman_log.sh
| #!/bin/bash tday=`date +%Y-%m-%d` rman_log=/bak/ora_rman_backup/log/rman.log expdp_log=/logdata/oraback ora_err=`grep "ORA-" $rman_log | awk -F : '{print $1}'` if [ -n "$ora_err" ];then a=$ora_err else a='0' fi
log_num=`ls $expdp_log/*$tday.log | wc -l` succ_num=`cat $expdp_log/*$tday.log | grep "successfully loaded/unloaded" | wc -l` if [ $log_num -eq 0 ];then b='nodata' else if [ -f $expdp_log/export_$tday.log ];then num=`expr $log_num - 1` else num=$log_num fi if [ $succ_num -eq $num ];then b='0' else b='1' fi fi echo "$a,$b,N|L,N|L" |
ora_err.sh
| #!/bin/bash dt=`env LANG=en_US.UTF-8 date | awk '{print $3}'` if [ $dt -lt 10 ];then td=`env LANG=en_US.UTF-8 date | awk '{print $1,$2,0$3}'` else td=`env LANG=en_US.UTF-8 date | awk '{print $1,$2,$3}'` fi dir=/oracle/app/oracle/diag/rdbms/malldb/malldb1/trace/alert_malldb1.log begin=`grep -n "$td" $dir | head -1 | awk -F : '{print $1}'` err=`sed -n ''"$begin"',$p' $dir | grep ORA-` if [ -n "$err" ];then echo "$err" else echo 0 fi |
2、数据处理
oracle.py
| #!/usr/bin/env python # -*- coding:utf-8 -*- import sys reload(sys) sys.setdefaultencoding('utf8')
import xlsxwriter,os,datetime
#os.system('/bin/sh /usr/sh/shell/linux/oracle/oracle.sh >/dev/null 2>&1')
tday=datetime.date.today().strftime('%Y%m%d') workbook=xlsxwriter.Workbook("/usr/sh/shell/linux/oracle/report/数据库每日检查报告_%s.xlsx"%tday,{'strings_to_numbers':True})
worksheet0=workbook.add_worksheet("数据库性能") worksheet1=workbook.add_worksheet("ASM磁盘使用情况") worksheet2=workbook.add_worksheet("失效对象") worksheet3=workbook.add_worksheet("并行度>1的索引") worksheet4=workbook.add_worksheet("无效索引") worksheet5=workbook.add_worksheet("检查数据库基本状况") worksheet6=workbook.add_worksheet("表空间大小统计") worksheet7=workbook.add_worksheet("数据库备份") worksheet8=workbook.add_worksheet("ORA报错统计")
colour='#DBE4E3'
format_title=workbook.add_format() format_title.set_border(1) format_title.set_bold(1) format_title.set_bg_color(colour) format_title.set_align('center') format_title.set_valign('vcenter')
format_nr=workbook.add_format() format_nr.set_border(1) format_nr.set_align('center') format_nr.set_valign('vcenter')
format_red=workbook.add_format() format_red.set_font_color('red') format_red.set_border(1) format_red.set_align('center')
sql_list=['Oracle','Authorized','GROUP_NUMBER','\xd2\xd1\xd1\xa1\xd4\xf139\xd0\xd0\xa1\xa3','\xd2\xd1\xd1\xa1\xd4\xf140\xd0\xd0\xa1\xa3','\xd2\xd1\xd1\xa1\xd4\xf165\xd0\xd0\xa1\xa3','\xce\xb4\xd1\xa1\xb6\xa8\xd0\xd0', 'OWNER', 'no','SQL*Plus:','\xd2\xd1\xd1\xa1\xd4\xf163\xd0\xd0\xa1\xa3','\xd2\xd1\xd1\xa1\xd4\xf163\xd0\xd0\xa1\xa3','\xc1\xac\xbd\xd3\xb5\xbd:','\xd2\xd1\xd1\xa1\xd4\xf17\xd0\xd0\xa1\xa3','\x1b2','Copyright', '(c)Copyright', 'Connected', 'Oracle', 'With', 'SQL>', 'TBS_NAME', 'Data', 'Confidential', 'possession,', 'Commercial', 'Technical', 'under','You','logout','OBJECT_TYPE']
#sheet:-----------数据库性能--------- per_title=[u'主机',u'IP',u'CPU使用率',u'内存使用率'] per_host=[u'DB01',u'DB02',u'wmcxdb03',u'wmcxdb04',u'Jiaoyi1',u'Jiaoyi2',u'wussdb1',u'wussdb2',u'wreport'] xn_list=['ip列表']
worksheet0.write_row('A1',per_title,format_title) worksheet0.write_column('A2',per_host,format_title) worksheet0.set_column('A:D',20) worksheet0.write_column('B2',xn_list,format_nr)
try: nu=2 for i in range(len(xn_list)): with open("/usr/sh/shell/linux/oracle/info/"+xn_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split() worksheet0.write_row('C'+str(nu),num,format_nr) nu+=1 except OSError as reason: print "oracle info 出错啦:"+str(reason) #sheet:------ASM磁盘使用情况--------- asm_title=[u'主机',u'磁盘组编号',u'磁盘组名',u'总大小(MB)',u'空闲大小(MB)',u'可用大小(MB)',u'剩余空间比重',u'状态'] asm_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2',u'jiaoyidg'] asm_list=['ip']
worksheet1.write_row('A1',asm_title,format_title) worksheet1.set_column('A:A',20) worksheet1.set_column('B:H',15) try: begin=2 for i in range(len(asm_host)): begin_new=begin with open("/usr/sh/shell/linux/oracle/asm/"+asm_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split() if num!=[]: if num[0] not in sql_list: if num[0][:6] != "------": if num[1] != "rows": worksheet1.write_row('B'+str(begin),num,format_nr) begin+=1 worksheet1.merge_range('A'+str(begin_new)+':A'+str(begin-1),asm_host[i],format_title) except OSError as reason: print "oracle ASM 出错啦:"+str(reason)
#sheet:----------失效对象------------ invalid_title=[u'主机名',u'对象类型',u'用户',u'失效对象数量'] invalid_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2','wreport'] invalid_list=[ip'] worksheet2.write_row('A1',invalid_title,format_title) worksheet2.set_column('A:D',20) try: begin=2 for inum in range(len(invalid_host)): with open("/usr/sh/shell/linux/oracle/unusable_object/"+invalid_list[inum]+'.'+tday) as f: dict0={} dict1={} for line in f: num=line.split() if num!=[] and len(num) > 2: if num[0] not in sql_list: if num[0][:6] != "------": if num[1] != "rows": if num[1] == "VIEW" or num[1] == "BODY": one=num[0]+' '+num[1] two=num[2] if one not in dict1: dict1.setdefault(one,{two:1}) else: dict0=dict1[one] if two in dict0: dict0[two]+=1 dict1.update({one:dict0}) else: dict0.setdefault(two,1) dict1.update({one:dict0}) else: if num[0] not in dict1: dict1.setdefault(num[0],{num[1]:1}) else: dict0=dict1[num[0]] if num[1] in dict0: dict0[num[1]]+=1 dict1.update({num[0]:dict0}) else: dict0.setdefault(num[1],1) dict1.update({num[0]:dict0}) if dict1=={}: worksheet2.write('A'+str(begin),invalid_host[inum],format_title) worksheet2.write('B'+str(begin),'N/A',format_nr) worksheet2.write('C'+str(begin),'N/A',format_nr) worksheet2.write('D'+str(begin),'N/A',format_nr) begin+=1 else: title_begin=begin for i,j in dict1.items(): begin_new=begin qq=0 worksheet2.write('B'+str(begin),i,format_nr) for m,n in j.items(): worksheet2.write('B'+str(begin),i,format_nr) worksheet2.write('C'+str(begin),m,format_nr) worksheet2.write('D'+str(begin),n,format_nr) begin+=1 qq+=1 if qq >= 2: worksheet2.merge_range('B'+str(begin_new)+':B'+str(begin-1),i,format_nr) if begin-1-title_begin >= 1: worksheet2.merge_range('A'+str(title_begin)+':A'+str(begin-1),invalid_host[inum],format_title) else: worksheet2.write('A'+str(title_begin),invalid_host[inum],format_title) except OSError as reason: print "unusable_object 出错啦:"+str(reason)
#sheet:------并行度>1的索引---------- degree_title=[u'主机名',u'OWNER',u'索引名称',u'并行数量'] degree_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2',u'wreport'] degree_list=['ip']
worksheet3.write_row('A1',degree_title,format_title) worksheet3.set_column('A:D',20) worksheet3.write_column('A2',degree_host,format_title)
try: begin=2 for i in range(len(degree_host)): begin_new=begin file_object=open("/usr/sh/shell/linux/oracle/parallel_index/"+degree_list[i]+'.'+tday) try: f=file_object.read() if '-----' not in f: worksheet3.write('B'+str(begin),"N/A",format_nr) worksheet3.write('C'+str(begin),"N/A",format_nr) worksheet3.write('D'+str(begin),"N/A",format_nr) begin+=1 else: worksheet3.write('B'+str(begin),'error',format_nr) begin+=1 finally: file_object.close() except OSError as reason: print "并行度〉1的索引 出错啦:"+str(reason) #sheet:----------无效索引------------ index_title=[u'主机名',u'OWNER',u'INDEX_NAME',u'INDEX_TYPE',u'TABLE_OWNE',u'TABLE_NAME',u'TABLESPACE_NAME',u'status'] index_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2',u'wreport'] index_list=['172.18.13.20','172.18.13.10','172.18.102.1','172.18.101.2','192.100.7.27'] worksheet4.write_row('A1',index_title,format_title) worksheet4.set_column('A:I',20) worksheet4.write_column('A2',index_host,format_title)
try: begin=2 for i in range(len(index_host)): begin_new=begin file_object=open("/usr/sh/shell/linux/oracle/unusable_index/"+index_list[i]+'.'+tday) try: f=file_object.read() if 'UNUSABLE' not in f: worksheet4.write_blank('B'+str(begin),None,format_nr) worksheet4.write_blank('C'+str(begin),None,format_nr) worksheet4.write_blank('D'+str(begin),None,format_nr) worksheet4.write_blank('E'+str(begin),None,format_nr) worksheet4.write_blank('F'+str(begin),None,format_nr) worksheet4.write_blank('G'+str(begin),None,format_nr) worksheet4.write_blank('H'+str(begin),None,format_nr) begin+=1 else: with open("/usr/sh/shell/linux/oracle/unusable_index/"+index_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split() if num!=[]: if num[0] not in sql_list: if num[0][:6] != "------": if num[1] != "rows": worksheet4.write_row('B'+str(begin),num,format_nr) begin+=1 if begin-1-begin_new >= 1: worksheet4.merge_range('A'+str(begin_new)+':A'+str(begin-1),index_host[i],format_title) finally: file_object.close() except OSError as reason: print "无效索引 出错啦:"+str(reason)
#sheet:-------检查数据库基本状况---------- state_title=[u'主机名',u'检查Oracle实例状态',u'当前session数',u'最大允许session数',u'Oracle最大进程树',u'检查Oracle表空间的状态',u'检查Oracle所有数据文件状态',u'检查Oracle在线日志状态',u'集群状态检查'] state_host=[u'DB01',u'DB02',u'wmcxdb03',u'wmcxdb04',u'Jiaoyi1',u'Jiaoyi2',u'wussdb1',u'wussdb2',u'wreport'] state_list=['ip'] status="正常" status_err="不正常"
worksheet5.write_row('A1',state_title,format_title) worksheet5.set_column('B:I',20) worksheet5.set_column('F:H',30) worksheet5.write_column('A2',state_host,format_title) try: begin=2 for i in range(len(state_list)): with open("/usr/sh/shell/linux/oracle/basic_status/"+state_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split() if num!=[]: if num[0] == '0': worksheet5.write('B'+str(begin),status,format_nr) else: worksheet5.write('B'+str(begin),status_err,format_red) if num[4] == '0': worksheet5.write('F'+str(begin),status,format_nr) else: worksheet5.write('F'+str(begin),status_err,format_red) if num[5] == '0': worksheet5.write('G'+str(begin),status,format_nr) else: worksheet5.write('G'+str(begin),status_err,format_red) if num[6] == '0': worksheet5.write('H'+str(begin),status,format_nr) else: worksheet5.write('H'+str(begin),status_err,format_red) if num[7] == '0': worksheet5.write('I'+str(begin),status,format_nr) elif num[7] == '1': worksheet5.write('I'+str(begin),status_err,format_red) else: worksheet5.write('I'+str(begin),"N/A",format_nr) worksheet5.write('C'+str(begin),num[1],format_nr) worksheet5.write('D'+str(begin),num[2],format_nr) worksheet5.write('E'+str(begin),num[3],format_nr) begin+=1 except OSError as reason: print "basic_status.sh 出错啦:"+str(reason) #sheet:-------表空间大小统计---------------- space_title=[u'主机名',u'表空间',u'总大小_M',u'used_M',u'使用率_%'] space_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2',u'wreport'] sapce_list=[ip'] worksheet6.write_row('A1',space_title,format_title) worksheet6.set_column('A:E',20) try: begin=2 for i in range(5): with open("/usr/sh/shell/linux/oracle/space_use/"+sapce_list[i]+'.'+tday) as f: begin_new=begin for each_line in f: num=each_line.split() if num!=[]: if num[0] not in sql_list: if num[0][:6] != "------": if num[1] != "rows": worksheet6.write_row('B'+str(begin),num,format_nr) used=float(num[3]) if used > 85: worksheet6.write('E'+str(begin),num[3],format_red) begin+=1 worksheet6.merge_range('A'+str(begin_new)+':A'+str(begin-1),space_host[i],format_title)
except OSError as reason: print "tablespace_use 出错啦:"+str(reason) #sheet:----------------数据库备份------------------ rman_title=[u'主机名',u'RMAN备份log是否存在error',u'expdp检查日志是否有错误',u'DG同步检查',u'DG归档查询'] rman_host=[u'DB01/DB02',u'wmcxdb03/wmcxdb04',u'Jiaoyi1/Jiaoyi2',u'wussdb1/wussdb2',u'wreport'] rman_list=['ip7'] sync_list=['ip']
worksheet7.write_row('A1',rman_title,format_title) worksheet7.set_column('B:E',30) worksheet7.set_column('A:A',20) worksheet7.write_column('A2',rman_host,format_title) try: begin=2 for i in range(len(rman_host)): with open("/usr/sh/shell/linux/oracle/rman_log/"+rman_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split(',') if num!=[]: worksheet7.write_row('B'+str(begin),num,format_nr) if num[0]=='0': worksheet7.write('B'+str(begin),status,format_nr) num[1]=num[1].strip() if num[1]=='0': worksheet7.write('C'+str(begin),status,format_nr) elif num[1]=='1': worksheet7.write('C'+str(begin),status_err,format_nr) begin+=1 except OSError as reason: print "rman.sh 出错啦:"+str(reason)
try: sync=[] for i in range(len(sync_list)): with open("/usr/sh/shell/linux/oracle/sync_dg/"+sync_list[i]+'.'+tday) as f: for each_line in f: num=each_line.split() if num!=[]: sync.append(num[0]) if sync[0]=='0' and sync[1]=='0': worksheet7.write('D4',status,format_nr) else: worksheet7.write('D4',status_err,format_nr) if sync[2]=='0': worksheet7.write('E4',status,format_nr) else: worksheet7.write('E4',status_err,format_nr) except OSError as reason: print "rman.sh 出错啦:"+str(reason)
#sheet:---------------------ORA报错统计------------------------------------ ora_title=[u'主机名',u'ORA日志内容'] ora_host=[u'DB01',u'DB02',u'wmcxdb03',u'wmcxdb04',u'Jiaoyi1',u'Jiaoyi2',u'wussdb1',u'wussdb2',u'wreport'] ora_list=['ip']
worksheet8.write_row('A1',ora_title,format_title) worksheet8.set_column('B:B',100) try: begin=2 for i in range(len(ora_list)): begin_new=begin with open("/usr/sh/shell/linux/oracle/ora_err/"+ora_list[i]+'.'+tday) as f: for each_line in f: num=each_line.decode('gb2312') if num!=[]: if num[0] != '0': worksheet8.write('A'+str(begin),ora_host[i],format_nr) worksheet8.write('B'+str(begin),num,format_nr) begin+=1 if begin-1-begin_new >= 1: worksheet8.merge_range('A'+str(begin_new)+':A'+str(begin-1),ora_host[i],format_nr) except OSError as reason: print "oracle ora_log_err 出错啦:"+str(reason) workbook.close() |
3、数据展示
mail.py
| #!/usr/bin/python # -*- coding: utf-8 -*- from email.header import Header import smtplib import email.MIMEMultipart# import MIMEMultipart import email.MIMEText# import MIMEText import email.MIMEBase# import MIMEBase import os.path import sys import mimetypes import email.MIMEImage# import MIMEImage import datetime
tday=datetime.date.today().strftime('%Y/%m/%d') #命令 mail.py <1:发送方（回复地址）10000@qq.com> <2:发送地址，多个以;隔开> <3:发送文件> From = "%s<发送邮箱@139.com>" % Header("我的名字","utf-8") ReplyTo=sys.argv[1] To = sys.argv[2] file_name = sys.argv[3]#附件名 file_name1 = sys.argv[4] server = smtplib.SMTP("smtp.139.com",25) server.login("登录账户@139.com","密码") #仅smtp服务器需要验证时
# 构造MIMEMultipart对象做为根容器 main_msg = email.MIMEMultipart.MIMEMultipart()
# 构造MIMEText对象做为邮件显示内容并附加到根容器 text_msg = email.MIMEText.MIMEText("数据库每日巡检报告",_charset="utf-8") main_msg.attach(text_msg)
# 构造MIMEBase对象做为文件附件内容并附加到根容器 ctype,encoding = mimetypes.guess_type(file_name) if ctype is None or encoding is not None: ctype='application/octet-stream' maintype,subtype = ctype.split('/',1) file_msg=email.MIMEImage.MIMEImage(open(file_name,'rb').read(),subtype) file_msg1=email.MIMEImage.MIMEImage(open(file_name1,'rb').read(),subtype)
## 设置附件头 basename = os.path.basename(file_name) file_msg.add_header('Content-Disposition','attachment', filename = basename)#修改邮件头 main_msg.attach(file_msg)
basename1 = os.path.basename(file_name1) file_msg1.add_header('Content-Disposition','attachment', filename = basename1)#修改邮件头 main_msg.attach(file_msg1)
# 设置根容器属性 main_msg['From'] = From main_msg['Reply-to'] = ReplyTo #main_msg['To'] = To main_msg['Subject'] = u"[每日巡检] %s数据库每日巡检报告" %tday main_msg['Date'] = email.Utils.formatdate()
#main_msg['Bcc'] = To # 得到格式化后的完整文本 fullText = main_msg.as_string( )
# 用smtp发送邮件 try: server.sendmail(From, To.split(';'), fullText) finally: server.quit() |
# 主程序入口
oracle.sh
| #!/bin/bash tday=`date +%Y%m%d` dir=/usr/sh/shell/linux/oracle/oracle_list asmdir=/home/oracle/xunjian/asm.sh infodir=/home/oracle/xunjian/info.sh spaceusedir=/home/oracle/xunjian/tablespace_use.sh log="/usr/sh/shell/linux/oracle" #------------------oracle xingneng---------------------- xn_host=(多个ip以空格分割) for xn_line in ${xn_host[@]}; do passwd=`more $dir | grep $xn_line | grep -v "$xn_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $xn_line "sh $infodir" >$log/info/$xn_line.$tday if [ $? -ne 0 ];then echo "$xn_line info.sh error" >>$log/err.$tday fi done
#------------------ASM------------------------- asm_host=(多个ip以空格分割) for asm_line in ${asm_host[@]}; do passwd=`more $dir | grep $asm_line | grep -v "$asm_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $asm_line "su - oracle "$asmdir"" > $log/asm/$asm_line.$tday if [ $? -ne 0 ];then echo "$asm_line asm.sh error" >> $log/err.$tday fi done
#-------------------unusable object------------------------- object_host=(多个ip以空格分割) txtdir=$log/数据库失效对象.txt
echo "" >$txtdir for object_line in ${object_host[@]}; do passwd=`more $dir | grep $object_line | grep -v "$object_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $object_line "su - oracle "/home/oracle/xunjian/unusable_object.sh"" >$log/unusable_object/$object_line.$tday if [ $? -ne 0 ];then echo "$object_line unusable_object.sh error" >> $log/err.$tday fi if [ "$object_line" = "ip1" ];then echo "asd DB" >> $txtdir elif [ "$object_line" = "ip2" ];then echo "as wmcxdb" >> $txtdir elif [ "$object_line" = "ip3" ];then echo "ds Jiaoyi" >> $txtdir elif [ "$object_line" = "ip4" ];then echo "ds wussdb" >> $txtdir else echo "sdds wreport" >> $txtdir fi echo -e "\n" >> $txtdir more $log/unusable_object/$object_line.$tday | egrep "201[0-9][0-9]|TO_CHAR|---" >> $txtdir echo -e "\n\n" >> $txtdir done
#-------------------parallel index------------------------- parallel_host=(多个ip以空格分割) for parallel_line in ${parallel_host[@]}; do passwd=`more $dir | grep $parallel_line | grep -v "$parallel_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $parallel_line "su - oracle "/home/oracle/xunjian/parallel.sh"" >$log/parallel_index/$parallel_line.$tday if [ $? -ne 0 ];then echo "$parallel_line parallel.sh error" >> $log/err.$tday fi done
#-------------------unusable index------------------------- index_host=(多个ip以空格分割) for index_line in ${index_host[@]}; do passwd=`more $dir | grep $index_line | grep -v "$index_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $index_line "su - oracle "/home/oracle/xunjian/unusable_index.sh"" >$log/unusable_index/$index_line.$tday if [ $? -ne 0 ];then echo "$index_line unusable_index.sh error" >> $log/err.$tday fi done
#-------------------basic status------------------------- status_host=(多个ip以空格分割) for status_line in ${status_host[@]}; do passwd=`more $dir | grep $status_line | grep -v "$status_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $status_line "sh /home/oracle/xunjian/basic_status.sh 2>/dev/null" >$log/basic_status/$status_line.$tday if [ $? -ne 0 ];then echo "$status_line basic_status.sh error" >> $log/err.$tday fi done
#------------------tablespace_use------------------------- space_host=多个ip以空格分割) for space_line in ${space_host[@]}; do passwd=`more $dir | grep $space_line | grep -v "$space_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $space_line "su - oracle "/home/oracle/xunjian/tablespace_use.sh"" >$log/space_use/$space_line.$tday if [ $? -ne 0 ];then echo "$space_line tablespace_use.sh error" >> $log/err.$tday fi done
#--------------------oracle rman------------------------- rman_host=(多个ip以空格分割) for rman_line in ${rman_host[@]}; do passwd=`more $dir | grep $rman_line | grep -v "$rman_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $rman_line "sh /home/oracle/xunjian/rman_log.sh" >$log/rman_log/$rman_line.$tday if [ $? -ne 0 ];then echo "$rman_line rman_log.sh error" >> $log/err.$tday fi done
#--------------------oracle dg rsync------------------------- sync_host=(多个ip以空格分割) for sync_line in ${sync_host[@]}; do passwd=`more $dir | grep $sync_line | grep -v "$sync_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $sync_line "sh /home/oracle/xunjian/dg.sh" >$log/sync_dg/$sync_line.$tday if [ $? -ne 0 ];then echo "$sync_line sync_dg.sh error" >> $log/err.$tday fi done
#--------------------ora_log err------------------------- ora_host=(多个ip以空格分割) for ora_line in ${ora_host[@]}; do passwd=`more $dir | grep $ora_line | grep -v "$ora_line[0-9]" | awk '{print $3}'` /usr/local/bin/sshpass -p $passwd ssh -n $ora_line "sh /home/oracle/xunjian/ora_err.sh" >$log/ora_err/$ora_line.$tday if [ $? -ne 0 ];then echo "$ora_line ora_err.sh error" >> $log/err.$tday fi done
#_____________________________________________________________
if [ -f $log/err.$tday ];then /usr/bin/python $log/mall/1.py >/dev/null 2>&1 else /usr/bin/python $log/oracle.py if [ -f $log/report/数据库每日检查报告_$tday.xlsx ];then /usr/bin/python $log/mall/mail.py asda@139.com "收件人，多个以分号分隔" "$log/report/数据库每日检查报告_$tday.xlsx" "$log/数据库失效对象.txt" else /usr/bin/python $log/mall/2.py >/dev/null 2>&1 fi fi |
# 最后上几张成品图
999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
