ITPub博客

首页 > IT职业 > IT生活 > 日常检查

日常检查

原创 IT生活 作者:pondka 时间:2007-08-17 16:32:53 0 删除 编辑

日常健康检查的脚本。

只对单实例系统有效,不能在RAC系统中使用。

[@more@]

Rem
Rem chk_health.sql
Rem 1.0
Rem by Pond Ka
Rem
Rem usage: sqlplus @chk_health.sql
Rem only for single instance database
Rem not suitable for RAC
Rem

Rem
Rem This script do health check .
Rem Must run under system or user has dba privilege.
Rem

set pages 0
set lines 1000
set trimspool on
set head off
set feedback off
set echo off
set verify off

Rem get report name based on database name and report date
Rem If run under Solaris , change this section
Rem
col logname noprint new_value log_name
select lower(name)||to_char(sysdate,'yyyymmddhh24mi')||'.txt' logname
from v$database;
spool &log_name


Rem
Rem report header
Rem
prompt
select 'Report produced at '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

prompt
prompt Basic information:
prompt --------------------------------------------------------------------------------

Rem
Rem Check database information
Rem
set head on
set pages 45
col dbid heading "Database|ID"
col name heading "Database|Name"
col open_mode heading "Open |Mode"
col force_logging heading "Force|Logging" format a7
select
to_char(dbid,9999999999) dbid
, name
, open_mode
, force_logging
from v$database
;

Rem
Rem Check instance information
Rem
col instance_name heading "Instance|Name" format a10
col host_name heading "Host|Name" format a10
col status heading "Instance|Status" format a8
col archiver heading "Archiver|Status" format a8
col up_time heading "Running Time" format a30
select
host_name
, instance_name
, status
, archiver
, trunc(sysdate - startup_time) || ' Days '
|| trunc(mod(sysdate-startup_time, 1) *24) || ' Hours '
|| trunc(mod((sysdate-startup_time)*24,1)*60) || ' Minutes '
up_time
from v$instance
;


prompt
prompt Check hit ratio
prompt These value expected higher than 90%
prompt --------------------------------------------------------------------------------

Rem
Rem Check buffer cache hit ratio
Rem
col pr heading "Physical|Reads"
col prd heading "Phy_Reads|Direct"
col prl heading "Phy_Reads|Direct_LOB"
col bg heading "Block|Gets"
col cg heading "Consistent|Gets"
col ht heading "Buffer|Hit Ratio"
select
to_char((1-((pr - prd - prl) / (bg + cg - prd - prl))) * 100, '999.9')||'%' ht
, bg, cg, pr, prd, prl
from
(select value pr from v$sysstat where name = 'physical reads') pr
, (select value prd from v$sysstat where name = 'physical reads direct') prd
, (select value prl from v$sysstat where name = 'physical reads direct (lob)') prl
, (select value bg from v$sysstat where name = 'db block gets') bg
, (select value cg from v$sysstat where name = 'consistent gets') cg
;

Rem
Rem check library hit ratio
Rem
col ht heading "Libray|Hit Ratio" format a10
select
to_char(sum(pinhits) / sum(pins) * 100, 999.9)||'%' ht
from v$librarycache
;


prompt
prompt Check session informations
prompt --------------------------------------------------------------------------------

Rem
Rem Check session high water mark
Rem
col sessions_current heading "Sessions|Current" format 999,999,999
col sessions_highwater heading "Sessions|High Water" format 999,999,999
select
sessions_current
, sessions_highwater
from v$license
;

Rem
Rem Check session wait events
Rem
col username heading "User Name" format a15
col program heading "Program" format a35
col event heading "Wait Event" format a25
prompt
prompt Session wait events
prompt
select
s.username, s.program, sw.event
from
v$session_wait sw
, v$session s
where
sw.sid = s.sid
and s.username is not null
and event not in ('SQL*Net message to client', 'SQL*Net message from client')
;

Rem
Rem Check session status
Rem
col status heading "Session|Status"
col nu heading "Number|of Sessions"
compute sum of nu on report
break on report
prompt
prompt Session status
prompt
select
status, count(*) nu
from v$session
where username is not null
group by status
;
clear break;

Rem
Rem detail information for sessions which idle for more than 4 hours
Rem
col lc heading "Idle Time|(Hours)" format a8
col username format a10 heading "Database|Username"
col machine format a17 heading "Machine"
col osuser format a10 heading "OS|Username"
col prg format a35 heading "Program"
prompt
prompt Idle sessions
prompt
select
username, machine, osuser, program prg
, to_char(trunc(last_call_et/3600, 1), 99999.9) lc
from v$session
where last_call_et > 14400
and username is not null
order by last_call_et desc
;


prompt
prompt Redo log files information
prompt --------------------------------------------------------------------------------

Rem
Rem Online redo logfile information
Rem
col grp format 99 heading "Log Group|Number"
col bytes format 999,999.99 heading "Bytes|(M)"
col status heading "Status"
col member heading "Log File|Members" format a45
break on grp on bytes on status skip 1
prompt
prompt Online redo log file
prompt
select
l.group# grp
, l.bytes/1024/1024 bytes
, l.status
, lf.member
from v$log l, v$logfile lf
where l.group# = lf.group#
order by 1
;
clear break;

Rem
Rem Online redo logfile switch frequency
Rem
col dt heading "Begin Time (1 hour)" format a25
col cnt heading "Switch times" format 999
prompt
prompt Switch frequency
prompt
select
to_char(trunc(first_time, 'hh'), 'yyyy-mm-dd hh24:mi') dt
, count(*) cnt
from
v$loghist
where first_time > sysdate - 30
group by trunc(first_time,'hh')
order by 1
;


spool off;
exit;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23822/viewspace-963882/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 更改当前schema
请登录后发表评论 登录
全部评论
  • 博文量
    11
  • 访问量
    34544