2022年ORACLE健康检查脚本 .pdf

《2022年ORACLE健康检查脚本 .pdf》由会员分享，可在线阅读，更多相关《2022年ORACLE健康检查脚本 .pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Rem Rem chk_health.sql Rem 1.0 Rem by Pond Ka Rem Rem usage: sqlplus system/password_of_systemconnect_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 p

2、rivilege. 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 logname noprint new_value log_name select lower(name)|to_char(sysdate,yyyymmddhh24mi)|.txt logname from v$database; s

3、pool &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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 9 页 - - - -

4、 - - - - - Rem set head on set pages 45 col dbid heading Database|ID format a11 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 in

5、stance 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 , arc

6、hiver , 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 prompt Check hit ratio prompt These value expected higher than 90% prompt - Rem Rem Check buffer cache hit rati

7、o 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 9 页 - - - - - - - - - Rem col pr heading Physical|Reads format 999,999,999 col prd heading Phy_Reads|Direct format 999,999,999 col prl heading Phy_Reads|Direct_LOB format 999,999,999 col bg heading Block|Gets for

8、mat 999,999,999,999 col cg heading Consistent|Gets format 999,999,999,999 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

9、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

10、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_highwa

11、ter heading Sessions|High Water format 999,999,999 select sessions_current , sessions_highwater from v$license ; Rem 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 9 页 - - - - - - - - - Rem Check session wait events Rem col username heading User Name format a15

12、 col program heading Program format a35 col event heading Wait Event format a25 prompt prompt Session wait events, excluding waiting for users message 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

13、 (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

14、 ; 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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 9 页 - - - - - - - - - col ma

15、chine 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

16、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

17、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; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - -

18、- - 第 5 页，共 9 页 - - - - - - - - - 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

19、where first_time sysdate - 30 group by trunc(first_time,hh) order by 1 ; Rem Rem Space usage check Rem prompt prompt Tablespace usage prompt - col tbsn heading Tablespace|Name format a20 col bytes heading Current|Size(M) format 999,999.99 col max_b heading Maximum|Size(M) format 999,999.99 col fre_b

20、 heading Free Space|Size(M) format 999,999.99 col usg heading Free Space|persentage format a10 col em heading Extent|Management format a10 col ssm heading Segment|Management format a10 select tb.tbsn , bytes , fre_b , lpad(to_char(nvl(fre_b,0)/bytes*100, 999.99)|%,10) usg , max_b from (select tables

21、pace_name tbsn, sum(bytes)/1024/1024 bytes , sum( decode(AUTOEXTENSIBLE, YES, greatest(bytes, maxbytes), 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 9 页 - - - - - - - - - bytes)/1024/1024 max_b from dba_data_files group by tablespace_name ) tb, (select table

22、space_name tbsn, sum(bytes)/1024/1024 fre_b from dba_free_space group by tablespace_name ) fre where tb.tbsn = fre.tbsn (+) order by 4 ; prompt prompt Table usage prompt - set serveroutput on exec dbms_output.enable(100000000); declare type seg is record ( seg_owner dba_segments.owner%type, seg_name

23、dba_segments.segment_name%type, par_name dba_segments.PARTITION_NAME%type, seg_type dba_segments.segment_type%type, tbs_name dba_segments.TABLESPACE_NAME%type ); type usg is record ( t_bck number, t_byt number, u_bck number, u_byt number, luefi number, luebi number, lub number); v_seg seg; v_usg usg

24、; v_tbsname dba_tablespaces.tablespace_name%type; v_tbsseg dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%type; cursor c_tbs is select tablespace_name, SEGMENT_SPACE_MANAGEMENT from 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 9 页 - - - - - - - - - dba_tablespaces

25、where CONTENTS = PERMANENT order by 1 ; cursor c_seg is select owner, segment_name, PARTITION_NAME, segment_type from dba_segments where owner not in (SYS, SYSTEM, OUTLN, DBSNMP, WMSYS) and tablespace_name = v_tbsname order by 1, 2, 3 ; begin open c_tbs; fetch c_tbs into v_tbsname, v_tbsseg; while c

26、_tbs%found loop dbms_output.put_line(); dbms_output.put_line(Tablespace: |v_tbsname); dbms_output.put_line ( rpad(Owner,15) |rpad(Segment Name, 30) |rpad(Par, 8) |rpad(Seg Type, 10) |rpad(Bytes(K),10) |Used(K) ); dbms_output.put_line ( rpad(-,14, -)| |rpad(-,29, -)| |rpad(-,7, -)| |rpad(-, 9, -)| |r

27、pad(-,9, -)| | - ); open c_seg; fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type; while c_seg%found loop if v_seg.seg_type in (TABLE, TABLE PARTITION, TABLE SUBPARTITION, INDEX, INDEX PARTITION, INDEX SUBPARTITION, CLUSTER,LOB) then 名师资料总结 - - -精品资料欢迎下载 - - - - - - -

28、- - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 9 页 - - - - - - - - - dbms_space.unused_space ( v_seg.seg_owner, v_seg.seg_name, v_seg.seg_type , v_usg.t_bck, v_usg.t_byt, v_usg.u_bck, v_usg.u_byt , v_usg.luefi, v_usg.luebi, v_usg.lub, v_seg.par_name); dbms_output.put_line(rpad(v_seg.seg_owner,15

29、) |rpad(v_seg.seg_name, 30) |rpad(substr(nvl(v_seg.par_name,NULL),1,7), 8) |rpad(substr(v_seg.seg_type, 1, 9), 10) |to_char(v_usg.t_byt/1024, 9,999,999) |to_char(v_usg.t_byt-v_usg.u_byt)/1024, 9,999,999) ); end if; fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type; end loop; close c_seg; fetch c_tbs into v_tbsname, v_tbsseg; end loop; close c_tbs; end; / spool off; exit; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 9 页 - - - - - - - - -