oracle巡检脚本---hpux_综合

oracle巡检脚本---linux

oracle巡检---python

1.数据采集

info.sh --- #采集操作系统信息

#!/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/inspection/info/info.$tday ];thenmv /home/oracle/inspection/info/info.$tday /home/oracle/inspection/info/info.bak.$tday
fi
/usr/bin/top -h -d1 -f /home/oracle/inspection/info/info.$tday
cpu_idle=`echo "$sar_info" | grep Average | awk '{print $NF}'`
cpu_used=`expr 100 - $cpu_idle`
mem_free=`cat /home/oracle/inspection/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=rac1
export ORACLE_UNQNAME=rac
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=rac1
export ORACLE_UNQNAME=rac
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=rac1
export ORACLE_UNQNAME=rac
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=rac1
export ORACLE_UNQNAME=rac
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=rac1
export ORACLE_UNQNAME=rac
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/inspection/state/$tday
sqlplus / as sysdba <<EOF
set linesize 1000
col host_name for a20
col instance_name for a20
spool /home/oracle/inspection/state/$tday/state.shili
select instance_name,host_name,startup_time,status,database_status from v\$instance;
spool off
spool /home/oracle/inspection/state/$tday/curr.session
select count(*) from v\$session;
spool off
spool /home/oracle/inspection/state/$tday/max.session
show parameter sessions;
spool off
spool /home/oracle/inspection/state/$tday/max.process
select value from v\$parameter where name='processes';
spool off
spool /home/oracle/inspection/state/$tday/state.tablespace
select tablespace_name,status from dba_tablespaces;
spool off
col name for a50
spool /home/oracle/inspection/state/$tday/state.datefile
select name,status from v\$datafile;
spool off
spool /home/oracle/inspection/state/$tday/state.logfile
select group#,status,type,member from v\$logfile;
spool off
EOF

crs.sh --- #采集crs状态信息

#!/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/inspection/state/$tday"su - oracle "/home/oracle/inspection/state.sh" > /dev/null 2>&1
if [ $? -ne 0 ];thenecho "state.sh error"
elsesl_state=`more $dir/state.shili | awk '{print $NF}' | sed -n '5p'`
if [ $sl_state = "ACTIVE" ];thensl_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/inspection/crs.sh" > $dir/state.crs
if [ $? -ne 0 ];thenecho "crs.sh error"
elseora_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 ];thenstate_crs=0 elsestate_crs=1fiecho "$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=rac1
export ORACLE_UNQNAME=rac
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_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME =  F.TABLESPACE_NAMEORDER 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 ];thena=$ora_err
elsea='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=rac1
export ORACLE_UNQNAME=rac
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/inspection/state/$tday
sqlplus / as sysdba <<EOF
set linesize 500
col dest_name for a30
col error for a50
spool /home/oracle/inspection/state/$tday/dg.check
select dest_id, dest_name, status, error from v\$archive_dest order by dest_id
/
spool off
EOF

dg.sh --- #检查dg状态

#!/usr/bin/sh
tday=`date +%Y%m%d`
su - oracle "/home/oracle/inspection/dgcheck.sh" >/dev/null 2>&1
if [ $? -eq 0 ];thenif [ -f /home/oracle/inspection/state/$tday/dg.check ];thennum=`more /home/oracle/inspection/state/$tday/dg.check | awk '{print $3}' | grep ^INVALID | wc -l`if [ $num -eq 0 ];thena='0'elsea='1'fifi
fi
echo $a

ora_err.sh --- # ORA 报错统计

#!/usr/bin/sh
dt=`date | awk '{print $3}'`
if [ $dt -lt 10 ];thentd=`date | awk '{print $1,$2,0$3}'`
elsetd=`date | awk '{print $1,$2,$3}'`
fi
dir=/u01/app/oracle/diag/rdbms/rac/rac2/trace/alert_rac2.log
begin=`grep -n "$td" $dir | head -1 | awk -F : '{print $1}'`
err=`sed -n ''"$begin"',$p' $dir | grep ORA-`
if [ -n "$err" ];thenecho "$err"
elseecho 0
fi