实战:oracle巡检脚本v1

栏目：互联网时间：2014-09-06 12:34:26

/*************************************************************************************************************************

该脚本仅仅是为了工作方便书写,主要是在shell里整合了OS的系统命令和oracle的常见巡检脚本;

脚本执行过程中需要调用OS的系统命令的权限,如果执行脚本的用户是普通账户需要考虑权限设置的问题!

***********************************************************************************************************************/

#!/bin/sh
#ocpyang@126.com
#Modified according to the actual situation oracle username and password

export black='33[0m'
export boldblack='33[1;0m'
export red='33[31m'
export boldred='33[1;31m'
export green='33[32m'
export boldgreen='33[1;32m'
export yellow='33[33m'
export boldyellow='33[1;33m'
export blue='33[34m'
export boldblue='33[1;34m'
export magenta='33[35m'
export boldmagenta='33[1;35m'
export cyan='33[36m'
export boldcyan='33[1;36m'
export white='33[37m'
export boldwhite='33[1;37m'

cecho ()

## -- Function to easliy print colored text -- ##

# Color-echo.
# 参数 $1 = message
# 参数 $2 = color
{
local default_msg="No message passed."

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
   printf "$black" ;;
boldblack)
   printf "$boldblack" ;;
red)
   printf "$red" ;;
boldred)
   printf "$boldred" ;;
green)
   printf "$green" ;;
boldgreen)
   printf "$boldgreen" ;;
yellow)
   printf "$yellow" ;;
boldyellow)
   printf "$boldyellow" ;;
blue)
   printf "$blue" ;;
boldblue)
   printf "$boldblue" ;;
magenta)
   printf "$magenta" ;;
boldmagenta)
   printf "$boldmagenta" ;;
cyan)
   printf "$cyan" ;;
boldcyan)
   printf "$boldcyan" ;;
white)
   printf "$white" ;;
boldwhite)
   printf "$boldwhite" ;;
esac
printf "%s " "$message"
tput sgr0   # tput sgr0即恢复默认值
printf "$black"

return
}

cechon ()

# Color-echo.
# 参数1 $1 = message
# 参数2 $2 = color
{
local default_msg="No message passed."
# Doesn't really need to be a local variable.

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
  printf "$black" ;;
boldblack)
  printf "$boldblack" ;;
red)
  printf "$red" ;;
boldred)
  printf "$boldred" ;;
green)
  printf "$green" ;;
boldgreen)
  printf "$boldgreen" ;;
yellow)
  printf "$yellow" ;;
boldyellow)
  printf "$boldyellow" ;;
blue)
  printf "$blue" ;;
boldblue)
  printf "$boldblue" ;;
magenta)
  printf "$magenta" ;;
boldmagenta)
  printf "$boldmagenta" ;;
cyan)
  printf "$cyan" ;;
boldcyan)
  printf "$boldcyan" ;;
white)
  printf "$white" ;;
boldwhite)
  printf "$boldwhite" ;;
esac
printf "%s" "$message"
tput sgr0   # tput sgr0即恢复默认值
printf "$black"

return
}

#1.the server infomation
echo "the system basic infomation:"
echo "***********************************************************************"
echo
hostname=`/bin/hostname` #主机名

ipaddress=`/sbin/ifconfig |grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'` #IP地址

gtway01=`cat /etc/sysconfig/network|grep GATEWAY|awk -F "=" '{print $2}'` #网关

gtway02=`/bin/netstat -rn | awk '/^0.0.0.0/ {print $2}'`

cpuinfo=`cat /proc/cpuinfo|grep "name"|cut -d: -f2 |awk '{print "*"$1,$2,$3,$4}'|uniq -c` #cpu

phmem=`dmidecode | grep -A 16 "Memory Device$" |grep Size:|grep -v "No Module Installed"|awk '{print "*" $2,$3}'|uniq -c` #物理内存数量

sysver=`cat /etc/issue | head -1` #--系统版本

kerver=`/bin/uname -a |awk '{print $3}'` #内核版本

#mem usage
mem_total=$(/usr/bin/free -m |grep Mem|awk '{print $2}')
mem_used=$(/usr/bin/free -m |grep Mem|awk '{print $3}')
mem_rate=`expr $mem_used/$mem_total*100|bc -l`

#mem_summary
mem_sum=`/usr/bin/free -m | xargs | awk '{print "Free/total memory: " $17 " / " $8 " MB"}'
| awk -F":" 'BEGIN{print " FREE / TOTAL " } {print $2 }'`

#disk space
dk_usage=`/bin/df -h | xargs | awk '{print "Free/total disk: " $11 " / " $9}'`

cechon "1.1 server hostname is:" red
echo ${hostname}

cechon "1.2 server ipaddree is: " red
echo ${ipaddress}

if [ "${gtway01}" = "" ];then
cechon "1.3 server gateway is:" red
echo ${gtway02}
else
cechon "1.3 server gateway is:" red
echo ${gtway01}
fi

cechon "1.4 server cpuinfo is: " red
echo ${cpuinfo}

cechon "1.5 server Physical memory number is: " red
echo ${phmem}

cechon "1.6 server version is: " red
echo ${sysver}

cechon "1.7 server system kernel version is: " red
echo ${kerver}

cechon "1.8 server memory Summary is: " red
echo ${mem_sum}

cechon "1.9 server memory usage rate is: " red
echo ${mem_rate:0:5}%

cechon "1.10 server disk usage is: " red
echo
cechon "detail: " green
echo
#disk usage
df -H |awk -F ' ' '{ print $1,$2,$3,$4,$5,$6}'
echo
cechon "Summary: " green
echo
echo ${dk_usage}

echo
cechon "1.11 server CPU load average is: " red
echo
/usr/bin/uptime | awk 'BEGIN{print "1min, 5min, 15min"} {print $10,$11,$12}'

echo
cechon "1.12 server started services is: " red
echo
/sbin/chkconfig --list | grep on
echo

echo
cechon "1.13 server CPU free is: " red
/usr/bin/top -b -n 1 | grep Cpu | awk '{print $5}' | cut -f 1 -d "."
echo

cechon "1.14 server ESTABLISHED TCP connect number is: " red
echo
/bin/netstat -n | awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'
echo

cechon "1.15 oracle ESTABLISHED connect is: " red
echo
/bin/netstat -an -t | grep ":1521" | grep ESTABLISHED | awk '{printf "%s %s ",$5,$6}' | sort |sed 's/^::ffff://'
echo

cechon "1.17 oracle LISTENER status is: " red
echo
/bin/ps -ef|grep lsn|grep -v grep
echo
lsnrctl status
echo

echo
echo "***********************************************************************"
echo

cechon "***********************************************************************" yellow
echo
cechon "2.Check the database basic situation:" green
echo
cechon "***********************************************************************" yellow
echo

#configure oracle user and password

ora_user="sys"
ora_pass="jinriDBAocpyang_jinri_ocp.com"

#2.1 oracle database version
echo
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 1.txt
select * from v$version;
spool off
exit;
!01

cechon "2.1 oracle database version is : " red
echo
cat 1.txt
echo
rm -rf 1.txt

#2.2 oracle database information

cechon "2.2 oracle database information is : " red
echo
cat 2.txt |xargs |awk '{print "dbid:"$1," name:"$2,
" created:"$3" "$4," log_mode:"$5," open_mode:"$6" "$7," db_unique_name:"$8}'
echo
rm -rf 2.txt

#2.3 oracle instance information

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 3.txt
select instance_name,host_name,version,startup_time,status,database_status,
instance_role from v$instance;
spool off
exit;
!01

cechon "2.3 oracle instance information is : " red
echo
cat 3.txt |xargs |awk '{print "instance_name:"$1," hostname:"$2,
" version:"$3," startup_time:"$4" "$5," status:"$6," database_status:"$7," instance_role:"$8}'
echo
rm -rf 3.txt

#2.4 数据库非缺省初始化参数

cechon "2.4 Database non-default initialization parameters is : " red
echo
cat 4.txt
echo
rm -rf 4.txt

#2.5 database default temp tempspace

cechon "2.5 database default temp tempspace is : " red
echo
cat 25.txt
echo
rm -rf 25.txt

#2.6 database trace position

SELECT P1.VALUE||''||P2.VALUE||'_ORA_'||P.SPID FILENAME
FROM
V$PROCESS P,
V$SESSION S,
V$PARAMETER P1,
V$PARAMETER P2
WHERE P1.NAME = 'user_dump_dest'
AND P2.NAME = 'db_name'
AND P.ADDR = S.PADDR
AND S.AUDSID = USERENV ('SESSIONID');

spool off
exit;
!01

cechon "2.6 database trace position is : " red
echo
cat 26.txt
echo
rm -rf 26.txt

cechon "***********************************************************************" yellow
echo
cechon "3.Check the database object stauts:" green
echo
cechon "***********************************************************************" yellow
echo

#3.1 control file

cechon "3.1 Database control file is : " red
echo
cat 31.txt
echo

rm -rf 31.txt

#3.2 Online Redo Logfiles Status

cechon "3.2 Online Redo Logfiles Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""group#","status","type","member" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 32.txt
echo
rm -rf 32.txt

#3.3 tablespace Status

cechon "3.3 tablespace Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""tablespace_name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 33.txt
echo
rm -rf 33.txt

#3.4 all datafile Status

cechon "3.4 all datafile Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 34.txt
echo
rm -rf 34.txt

#3.5 invalid objects

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 35.txt
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
spool off
exit;
!01

cechon "3.5 invalid objects is : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","object_name","object_type" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 35.txt
echo
rm -rf 35.txt

#3.6 undo segment status

cechon "3.6 undo segment status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""segment_name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 36.txt
echo
rm -rf 36.txt

#3.7 Tables and indexes in the same space object

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 37.txt
select a.owner,
a.tablespace_name tbsname ,
a.table_name tname,
b.index_name iname
from
     dba_tables a
     ,dba_indexes b
where
     a.tablespace_name=b.tablespace_name
     and b.table_name=a.table_name
     and a.owner=b.owner
     and b.owner NOT in
     ('SYS','SYSTEM','XDB','WMSYS','SYSMAN',
     'ORDSYS','OUTLN','ORDDATA')
     and a.tablespace_name not in('SYSTEM','SYSAUX','UNDOTBS1','USERS'
)
order by owner;
spool off
exit;
!01

cechon "3.7 Tables and indexes in the same space object is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","tablespace_name","tablename","index_name" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 37.txt
echo
rm -rf 37.txt

#3.8 tablespace Automatic expansion

select t.tablespace_name,d.file_name,d.autoextensible,d.status
from dba_tablespaces t,dba_data_files d
where t. tablespace_name =d. tablespace_name
order by tablespace_name,file_name;

spool off
exit;
!01

cechon "3.8 tablespace Automatic expansion is: " red
echo
cat 38.txt
echo
rm -rf 38.txt

cechon "***********************************************************************" yellow
echo
cechon "4.Check the database resource usage:" green
echo
cechon "***********************************************************************" yellow
echo

#4.1 tablespace usage

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 41.txt
select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";

spool off
exit;
!01

cechon "4.1 tablespace usage is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""tablespace_name","total","FREE","%FREE" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 41.txt
echo
rm -rf 41.txt

#4.2 resource limit

spool off
exit;
!01

cechon "4.2 resource limit is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""resource_name","max_utilization","initial_allocation","limit_value" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 42.txt
echo
rm -rf 42.txt

#4.3 session status

cechon "4.3 session status is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","serial#","username","program" ,"machine","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 43.txt
echo
rm -rf 43.txt

#4.5 check Non-system tables in system tablespace

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 45.txt
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
spool off
exit;
!01

cechon "4.5 check Non-system tables in system tablespace is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 45.txt
echo
rm -rf 45.txt

#4.6 temp tablespace usage

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 46.txt
select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char(sysdate,'yyyymmddhh24miss')
from
(select d.tablespace_name tablespace_name,
nvl(sum(used_blocks),0) tot_used_blocks,
sum(blocks) total_blocks
from v$sort_segment v ,dba_temp_files d
where d.tablespace_name=v.tablespace_name(+)
group by d.tablespace_name) s, v$database;
spool off
exit;
!01

cechon "4.6 check Non-system tables in system tablespace is: " red
echo
cat 46.txt
echo
rm -rf 46.txt

#4.7 database zombie processes

select pid, spid, username, terminal, program
from v$process
where addr not in (select paddr from v$session);

spool off
exit;
!01

cechon "4.7 database zombie processes is: " red
echo
cat 47.txt
echo
rm -rf 47.txt

cechon "***********************************************************************" yellow
echo
cechon "5.oracle database performance:" green
echo
echo 5.1-5.14 about sql
echo 5.15-5.20 about table
echo 5.21-5.30 about IO
echo 5.31-5.40 about REDO
echo 5.41-5.60 about HIT RATE
cechon "***********************************************************************" yellow
echo

##########5.1-5.14 about sql##########
#5.1 database Wait event

select sid,event,WAIT_TIME from v$session_wait
where event not like 'SQL%' and event not like 'rdbms%';

spool off
exit;
!01

cechon "5.1 database Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","event","wait_time" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 51.txt
echo
rm -rf 51.txt

#5.2 system Wait event

select EVENT,TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT from (
select * from v$system_event
where event not like '%rdbms%'
and event not like '%message%'
and event not like 'SQL*Net%'
order by total_waits desc
)
where rownum <=5 ;

spool off
exit;
!01

cechon "5.2 system Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:" "EVENT","TOTAL_WAITS", "TOTAL_TIMEOUTS", "TIME_WAITED", "AVERAGE_WAIT" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 52.txt
echo
rm -rf 52.txt

#5.3 inefficient sql statements

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

spool off
exit;
!01

cechon "5.3 inefficient sql statements is: " red
echo
cat 53.txt
echo
rm -rf 53.txt

#5.4 Long-running SQL

SELECT sql_text "SQL", executions "Number of runs",
buffer_gets / decode(executions, 0, 1, executions) / 4000 "Response time"
FROM v$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;

spool off
exit;
!01

cechon "5.4 Long-running SQL is: " red
echo
cat 54.txt
echo
rm -rf 54.txt

#5.5 top 10 Poor performance sql

SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;

spool off
exit;
!01

cechon "5.5 top 10 Poor performance sql is: " red
echo
cat 55.txt
echo
rm -rf 55.txt

#5.6 Long run SQL

select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
;

spool off
exit;
!01

cechon "5.6 Long run SQL is: " red
echo
cat 56.txt
echo
rm -rf 56.txt

#5.7 Most disk reads SQL

select
      st.sql_text
from
       v$sql s,
       v$sqlarea st
where
       s.address=st.address
&

------分隔线----------------------------

上一篇 谷歌亚马逊微软的烧钱游戏

下一篇 跨平台2D游戏引擎V-Play：3天开发一款原生游戏

------分隔线----------------------------