oracle巡检脚本---hpux

oracle巡检脚本---linuxoracle巡检---python1.数据采集info.sh --- #采集操作系统信息#!/usr/bin/shtday=`date +%Y%m%d`sar_info=`sar -u 1 5`#tol_mem=`dmesg | grep Phy | awk '{print $2}'`tol_mem=268127072if [ ...

吥輕誩放棄

753人浏览 · 2018-07-30 11:20:37

吥輕誩放棄 · 2018-07-30 11:20:37 发布

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 ];then
  mv /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 ];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/inspection/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=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_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=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 ];then
  if [ -f /home/oracle/inspection/state/$tday/dg.check ];then
    num=`more /home/oracle/inspection/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/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" ];then
  echo "$err"
else
  echo 0
fi

CSDN学习社区

CSDN联合极客时间，共同打造面向开发者的精品内容学习社区，助力成长！

更多推荐

嵌入式作业（七）：基于Ardunio的STM32串口通信

嵌入式作业（七）0作业要求1Ardunio 完成STM32的串口通信（1）安装Ardunio IDE（2）stm32串口通信2关于 stduino IDE0作业要求安装 Ardunio IDE 和相关软件支持库，在Ardunio 完成STM32板子的串口通信程序：（1）持续向串口输出“Hello world！”；（2）当接收到“stop!”时，停止输出。网上有一个国人版的MCU集成开发平台， st

CSDN学习社区

JDBC详解

JDBC文章目录JDBC什么是JDBC?JDBC驱动程序:Java使用JDBC访问数据库的步骤:设置classpath:Oracle连接字符串的书写格式:简单的例子:常用数据库的驱动程序及JDBC URL:Oracle数据库:SQL Server数据库MySQL数据库Access数据库PreparedStatement接口:JNDI-数据源（Data Source）与连接池（Connection

CSDN学习社区

“模式识别与机器学习”学习笔记no2.再谈感知机

接**上篇：上篇主要进行了PLA，Pocket算法的理论过程分析和在给定数据集上利用pocket算法对数据集进行分类学习，得到错分数量最少的分类面。上篇中pocket算法的过程已经进行了编程和测试，框架已经建立了起来，这一篇主要上篇中没有提到或涉及不深的几个问题。1.数据集的构造。上篇是直接使用了题目给的向量，这次来根据正态分布来产生数据集。np.random.normal函数可以根据均值和方差生