oracle10g的rac的巡检

===========================SRVCTL============================
1.列出配置的所有数据库
srvctl config database

2.显示指定集群数据库的所有服务
srvctl config service -d GDTV

3.查看所有实例和服务的状态
srvctl status database -d GDTV

4.查看单个实例的状态
srvctl status service -d GDTV -s

5.特定节点上节点应用程序的状态
srvctl status nodeapps -n DBSERVER1
srvctl status nodeapps -n DBSERVER2

6.列出RAC数据库的配置
srvctl config database -d GDTV

7.显示节点应用程序的配置 —（VIP、GSD、ONS、监听器）
srvctl config nodeapps -n DBSERVER1 -a -g -s -l
srvctl config nodeapps -n DBSERVER2 -a -g -s -l

=========================================进程检查==================
8. Oracle进程检查

Ps –ef |grep ora_

9. CRS进程检查

ps -ef | grep oracm 

$ps –df |grep d.bin

       应有：crsd.bin ocssd.bin evmd.bin

crsctl check crs

crs_stat –t

crs_stat –ls

=================alert.log====================================

8.查看各个instance的alert.log

 

=====================instance 配置参数========================

9.查看spfile.ora  查看profile

====================参看监听状态==============================
10.
$hostname
$export ORACLE_SID=GDTV1
$lsnrctl status

$hostname
$export ORACLE_SID=GDTV2
$lsnrctl status

listener日志检查
/u01/app/oracle/product/9.2.0/network/log/listener.log

/u01/app/oracle/product/9.2.0/network/log/listener.log

================oracm状态检查===================================

    11.1 crs日志检查
ocssd.log
$tail -20  cm.log

======================SQL下检查==============================

$hostname
$export ORACLE_SID=GDTV1
sqlplus "/as sysdba"

or sqlplus

--run on db server as sysdba!!!
--collect by lyf 200609
set pagesize 1000
set linesize 120
set echo on
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE
col tablespace_name format a15
host hostname

1.集群中所有正在运行的实例

SELECT
inst_id
, instance_number inst_no
, instance_name inst_name
, parallel
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id;

INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST
-------- -------- ---------- --- ------- ------------ --------- -------
1 1 orcl1 YES OPEN ACTIVE NORMAL rac1
2 2 orcl2 YES OPEN ACTIVE NORMAL rac2

SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;

 

2.检查参数
show paramter

3.检查SGA和PGA

show sga
select name ,value/1024/1024/1024 from gv$sysstat where name like '%pga%';
select name ,value/1024/1024/1024 from v$sysstat where name like '%pga%';

 

4.检查查询服务器的运行模式和数据库安装选项
   set linesize 200
   select * from v$option;

5.用户检查
col temporary_tablespace for a21
select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;

select a.username , a.temporary_tablespace "Temporary Tablespace" , b.contents
from dba_users a , dba_tablespaces b
where a.temporary_tablespace=b.tablespace_name
and b.contents <> 'TEMPORARY';

6、控制文件检查

col name for a60
select * from v$controlfile;

7、无效对象检查
   col OBJECT_NAME for a24
SELECT owner , object_name, object_type,status ,LAST_DDL_TIME  FROM dba_objects WHERE status like 'INVALID';

8、表空间和数据文件检查

1）数据文件
col file_name for a56
set linesize 300
select file_id,file_name,tablespace_name,autoextensible from dba_data_files;

select count(*) from v$datafile;

show parameter db_files

select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile；

SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;

2）表空间
set linesize 300
col tablespace_name for a16
SELECT upper(f.tablespace_name) "tablespace_name",
       d.Tot_grootte_Mb "tablespace(M)",
       d.Tot_grootte_Mb - f.total_bytes "used(M)",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
       f.total_bytes "free_space(M)",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%",
       f.max_bytes "max_block(M)"
FROM     
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
      GROUP BY tablespace_name) f,
      (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
       FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name   
ORDER BY 4 DESC;
 

表空间的空间使用情况

SELECT   df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT   tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name ORDER BY ；

表空间可用性检查
select tablespace_name,status from dba_tablespaces;

临时表空间使用情况和性能检查

SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE CONTENTS = 'TEMPORARY';
SELECT username, default_tablespace, temporary_tablespace FROM dba_users;

    select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,status from dba_tablespaces order by  extent_management;

     select tablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

本篇文章来源于 Linux公社网站()  原文链接：

1、监控表的增长

select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='FOUNDER' ORDER BY bytes/1024/1024 desc；

2、表和索引分析信息

SELECT   'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'
UNION ALL
SELECT   'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';

3.未建索引的表

       SELECT   /*+ rule */
                owner, segment_name, segment_type, tablespace_name,
                TRUNC (BYTES / 1024 / 1024, 1) size_mb
           FROM dba_segments t
          WHERE NOT EXISTS (
                      SELECT 'x'
                       FROM dba_indexes i
                       WHERE t.owner = i.table_owner
                             AND t.segment_name = i.table_name)
            AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
            AND t.owner NOT IN ('SYS', 'SYSTEM')
       ORDER BY 5 DESC;

升序用ASC

 

9.sort_segment检查
     select tablespace_name,extent_size db_blocks_per_extent,total_extents,
        used_extents,free_extents from v$sort_segment;

10.数据库总大小
     select round(sum(space)) all_space_M from
(
select sum(bytes)/1024/1024 space from dba_data_files
union all
select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files
union all
select sum(bytes)/1024/1024 space from v$log
);

11.检测连接数情况
（1）

select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;

（2）
select count(*) from v$session;

（3）
select sid,serial#,username,program,machine,status from v$session

11.回滚段信息
1）信息1
col segment_name format a20
col tablespace_name format a20
select segment_name,owner,tablespace_name,
dba_rollback_segs.status
from dba_rollback_segs,v$Datafile where file_id=file#;
 
  2）信息2
select segment_name,initial_extent,next_extent,min_extents,
owner,dba_rollback_segs.status status,optsize
from dba_rollback_segs,v$rollstat
where dba_rollback_segs.segment_id=v$rollstat.usn;

3）信息3
col Rollback_Name for a16
select substr(V$rollname.NAME,1,20) "Rollback_Name",
        substr(V$rollstat.EXTENTS,1,6) "EXTENT",
        v$rollstat.RSSIZE, v$rollstat.WRITES,
        substr(v$rollstat.XACTS,1,6) "XACTS",
        v$rollstat.GETS,
        substr(v$rollstat.WAITS,1,6) "WAITS",
        v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
        substr(v$rollstat.WRAPS,1,6) "WRAPS",
        substr(v$rollstat.EXTENDS,1,6) "EXTEND",
        v$rollstat.AVESHRINK,
        v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;

4）信息4
select  r.name Rollback_Name,
      p.pid Oracle_PID,
        p.spid OS_PID,
        nvl(p.username,'NO TRANSACTION') Transaction,
        p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where   l.addr = p.addr(+)
        and trunc(l.id1(+)/65536)=r.usn
      and l.type(+) = 'TX'
        and l.lmode(+) = 6
order by r.name;

5）回滚段的争用情况
select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;

6）rollback信息
select  substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
        substr(sys.dba_segments.OWNER,1,8) "Owner",
        substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
        substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
        substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
        substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
        substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
        substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
        substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
        substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
        substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
        substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
      sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;

 

12.Redo log信息检查

1）Redo Log 文件状态
   col member for a56
select f.member "member",
       f.group# "group",
       l.bytes/1024/1024 "size",
       l.status
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#,f.member;

2）LogGroup信息
SELECT group#, sequence#, bytes, members, status from v$log;

3）关于log_buffer
        select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');

4）查询LOG大小及频率
set linesize 300
set pages 100
column d1 form a20 heading "Date"
column sw_cnt form 99999 heading 'Number|of|Switches'
column Mb form 999,999 heading "Redo Size"
column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"

break on report
compute sum of sw_cnt on report
compute sum of Mb on report

var redoMbytes number;
begin
   select max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/

print redoMbytes

select trunc(first_time) d1
       , count(*) sw_cnt
       , count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time)
/

 

13.IO情况检查

   col file_name for a46
select
df.name file_name,
fs.phyrds reads,
fs.phywrts writes,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime
from
v$datafile df,v$filestat fs
where df.file#=fs.file#
order by df.name;

select count(*) from v$session;

14.命中率相关检查

1）Shared Pool Size 命中率
   select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

2）数据字典命中率
select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;

3）锁竞争
select  substr(ln.name,1,25) Name,
        l.gets, l.misses,
        100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"
from v$latch l, v$latchname ln
where ln.name in ('cache buffers lru chain')
and ln.latch# = l.latch#;

4）排序命中率
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)" 
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

5）数据缓冲区命中率
select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio
from v$sysstat phy,v$sysstat cur,v$sysstat con
where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

6）Miss LRU Hit命中率
column "Miss LRU Hit%" format 99.9999999;
col name format a40
select name, (sleeps/gets) "Miss LRU Hit%"
from v$latch where name ='cache buffers lru chain';

7）检查内存排序性能
select a.name, to_char(value)
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');

8）redo log buffer retry ratio
select to_char(r.value/e.value) "redo log buffer retry ratio"
from v$sysstat r,v$sysstat e
where r.name='redo buffer allocation retries'
and e.name='redo entries';

9）wait等待检查
select count(*) total_in_wait from v$session_wait
where event='log buffer space';

select event,total_waits,time_waited,average_wait
from v$system_event
where event like '%undo%';

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

15、查询lock锁
   SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1, request;