ORACLE健康检查脚本.docVIP

Rem Rem chk_hcalth.sql Rem 1.0 Rem by Pond Ka Rem Rem usage: sqlplus system/password_of_system@connect_string @chk_health.sql Rem only for single instance database Rem not suitable for RAC Rem only tested under 9.2.0 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 col lognamc noprint ncw_valuc log_namc select lower(name)llto_char(sysdate/yyyymmddhh24mi,)ir.txtl logname from v$database; spool log_name Rem Rem report header Rem prompt select Report produced at llto_char(sysdatcjyyyy?mm?dci 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 HDatabaselIDM format al 1 col name heading nDatabaselNameM col open_mode heading Open IModeH col forcc_logging heading HForcelLoggingH format a7 select to_char(dbid,9999999999) dbid ,name ,open_mode ,forcc_logging from v$database Rem Rem Check instance information Rem col instance_name heading uInstancelNameM format alO col host_name heading nHostlNamen format alO col status heading MInstancelStatusM format a8 col archiver heading HArchivcrlStatusn format a8 col up_time heading ^Running Time format a30 select host_name ,instance_name ,status ,archiver ,trunc(sysdate - startup_time) II1 Days * II trunc(mod(sysdate-startup_time, 1) *24) II1 Hours 1 II trunc(mod((sysdate-startup_time)*24,1 )*60) II Minutes up_timc from v$instance prompt prompt prompt Check hit ratio prompt These value expected higher than 90% prompt Rem Rem Check buffer cache hit ratio Rem col pr heading PhysicalIRcads format 999,999,999 col prd heading Phy_ReadslDirect format 999,999,999 col prl heading ,,Phy_ReadslDirect_LOB format 999,999,999 col bg heading BlockIGets format 999,999,999,999 col eg heading ConsistentlGets0 format 999,999,999,999 col ht hea