ORACLE 10G RAC巡检脚本

ORACLE 10G RAC巡检脚本！

2011-01-21 17:36

===========================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 "/as sysdba"@GDTV1 --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;   表： 1、监控表的增长 select segment_name,segment_type,bytes/1024/1024 from dba_segments where wner='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;

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

转载于:http://blog.itpub.net/25243263/viewspace-696474/