Oracle - 42. page

该脚本适用于新环境数据库的初步检测。

数据库检查：check_db_stats.sql

Rem
Rem check_db_stats_v1.2.sql
Rem modify by ludatou
Rem
Rem only for single instance database
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 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" 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 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
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" 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" format 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 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, excluding waiting for user's message
prompt _________________

col event format a32
col wait_class format a18
col segment_name format a30
select event, count(*)
  from v$session_wait
 where event not in (select name from v$event_name where wait_class = 'Idle')
  group by event order by 2 desc;

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
;


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 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 tablespace_name tbsn, sum(bytes)/1024/1024 bytes
		, sum( decode(AUTOEXTENSIBLE,
			'YES', greatest(bytes, maxbytes),
			bytes))/1024/1024 max_b
		from dba_data_files
		group by tablespace_name
	) tb,
	(select tablespace_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	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;
  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
	dba_tablespaces
    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_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, '-')||' '
	||rpad('-',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
      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)
	||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;

脚本效果：

[ora10g@ludatou ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 28 17:25:00 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @check_db_heath.sql


Report produced at 2014-07-28 17:25:04

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

Database    Database  Open       Force
ID          Name      Mode       Logging
----------- --------- ---------- -------
 2580936880 LU10G     READ WRITE NO

Host       Instance   Instance Archiver
Name       Name       Status   Status   Running Time
---------- ---------- -------- -------- ------------------------------
ludatou    lu10g      OPEN     STOPPED  1 Days 18 Hours 9 Minutes


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

Buffer             Block       Consistent     Physical    Phy_Reads    Phy_Reads
Hit Rat             Gets             Gets        Reads       Direct   Direct_LOB
------- ---------------- ---------------- ------------ ------------ ------------
  98.8%          221,523        1,181,248       16,972           34            0

Libray
Hit Ratio
----------
  95.3%

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

    Sessions     Sessions
     Current   High Water
------------ ------------
           1           10


Session status
----------------

Session       Number
Status   of Sessions
-------- -----------
ACTIVE             1
         -----------
sum                1

Idle sessions
----------------

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

Online redo log file
----------------

Log Group       Bytes          Log File
   Number         (M) Status   Members
--------- ----------- -------- ---------------------------------------------
        1       50.00 CURRENT  /oradata/lu10g/redo01.log

        2       50.00 INACTIVE /oradata/lu10g/redo02.log

        3       50.00 INACTIVE /oradata/lu10g/redo03.log


Switch frequency
----------------

Begin Time (1 hour)       Switch times
------------------------- ------------
2014-07-24 13:00                     2
2014-07-26 23:00                     1

Tablespace usage
-------------------------------------------------------------------------------

Tablespace               Current  Free Space Free Space     Maximum
Name                     Size(M)     Size(M) persentage     Size(M)
-------------------- ----------- ----------- ---------- -----------
SYSTEM                    480.00        4.25       .89%   32,767.98
SYSAUX                    260.00        9.38      3.61%   32,767.98
UNDOTBS1                   25.00       21.88     87.50%   32,767.98
USERS                       5.00        4.56     91.25%   32,767.98

Table usage
----------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tablespace:   SYSAUX
Owner          Segment Name                  Par     Seg Type  Bytes(K)  Used(K)
-------------- ----------------------------- ------- --------- ---------  ------------
CTXSYS         DR$CLASS                      NULL    TABLE             64        64
CTXSYS         DR$INDEX                      NULL    TABLE             64        64
CTXSYS         DR$INDEX_ERROR                NULL    TABLE             64        24
CTXSYS         DR$INDEX_PARTITION            NULL    TABLE             64        24
CTXSYS         DR$INDEX_SET                  NULL    TABLE             64        64
CTXSYS         DR$INDEX_SET_INDEX            NULL    TABLE             64        24
CTXSYS         DR$INDEX_VALUE                NULL    TABLE             64        64
CTXSYS         DR$NUMBER_SEQUENCE            NULL    TABLE             64        64
CTXSYS         DR$NVTAB                      NULL    TABLE             64        24
CTXSYS         DR$OBJECT                     NULL    TABLE             64        64
CTXSYS         DR$OBJECT_ATTRIBUTE           NULL    TABLE             64        64
CTXSYS         DR$OBJECT_ATTRIBUTE_LOV       NULL    TABLE             64        64
CTXSYS         DR$POLICY_TAB                 NULL    TABLE             64        24
CTXSYS         DR$PREFERENCE                 NULL    TABLE             64        64
CTXSYS         DR$PREFERENCE_VALUE           NULL    TABLE             64        64
CTXSYS         DR$SECTION                    NULL    TABLE             64        24
CTXSYS         DR$SECTION_GROUP              NULL    TABLE             64        64
CTXSYS         DR$STATS                      NULL    TABLE             64        24
CTXSYS         DR$STOPLIST                   NULL    TABLE             64        64
CTXSYS         DR$SUB_LEXER                  NULL    TABLE             64        24
CTXSYS         DR$THS                        NULL    TABLE             64        24
CTXSYS         DR$THS_BT                     NULL    TABLE             64        24
CTXSYS         DR$THS_PHRASE                 NULL    TABLE             64        24
CTXSYS         DR$WAITING                    NULL    TABLE             64        24
CTXSYS         DRC$DEL_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IDX_COLSPEC               NULL    INDEX             64        32
CTXSYS         DRC$IDX_COLUMN                NULL    INDEX             64        32
CTXSYS         DRC$IDX_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXO_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXP_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXS_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXS_NAME                  NULL    INDEX             64        32
CTXSYS         DRC$IXX_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$OAT_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$OAT_NAME                  NULL    INDEX             64        32
...............