当前位置: 首页 > Conf, ORACLE > 正文

[Python] Oracle rac dg 巡检脚本

http://blog.csdn.net/m0_37313242/article/details/78206102

需求:

每天需要定时巡检分别跑在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备份、expdpdg同步等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

本文固定链接: http://t.yjsec.com/index.php/2017/12/19/187/ | 下一站

该日志由 admin 于2017年12月19日发表在 Conf, ORACLE 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: [Python] Oracle rac dg 巡检脚本 | 下一站
关键字:

[Python] Oracle rac dg 巡检脚本:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter