Oracle 10G RAC巡检脚本===========================SRVCTL============================1.列出配置的所有数据库srvctl config database2.显示指定集群数据库的所有服务srvctl config service -d GDTV3.查看所有实例和服务的状态srvctl status database -d GDTV4.查看单个实例的状态srvctl status service -d GDTV -s <service_name>5.特定节点上节点应用程序的状态srvctl status nodeapps -n DBSERVER1srvctl status nodeapps -n DBSERVER26.列出RAC数据库的配置srvctl config database -d GDTV7.显示节点应用程序的配置—（VIP、GSD、ONS、监听器）srvctl config nodeapps -n DBSERVER1 -a -g -s -lsrvctl 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.bincrsctl check crscrs_stat –tcrs_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 statuslistener日志检查/u01/app/oracle/product/9.2.0/network/log/listener.log/u01/app/oracle/product/9.2.0/network/log/listener.log================oracm状态检查===================================11.1c rs日志检查ocssd.log</u01/product/Oracle/oracm/log>$tail -20 cm.log======================SQL下检查============================== $hostname$export ORACLE_SID=GDTV1sqlplus "/as sysdba"or sqlplus "/as sysdba"@GDTV1--run on db server as sysdba!!!--collect by lyf 200609set pagesize 1000set linesize 120set echo onCOLUMN name_col_plus_show_param FORMAT a36 HEADING NAMECOLUMN value_col_plus_show_param FORMAT a30 HEADING VALUEcol tablespace_name format a15host hostname1.集群中所有正在运行的实例SELECTinst_id, instance_number inst_no, instance_name inst_name, parallel, status, database_status db_status, active_state state, host_name hostFROM gv$instanceORDER BY inst_id;INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST-------- -------- ---------- --- ------- ------------ --------- -------1 1 orcl1 YES OPEN ACTIVE NORMAL rac12 2 orcl2 YES OPEN ACTIVE NORMAL rac2SELECT 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 paramter3.检查SGA和PGAshow sgaselect 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 200select * from v$option;5.用户检查col temporary_tablespace for a21select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;select ername , a.temporary_tablespace "Temporary Tablespace" , b.contentsfrom dba_users a , dba_tablespaces bwhere a.temporary_tablespace=b.tablespace_nameand b.contents <> 'TEMPORARY';6、控制文件检查col name for a60select * from v$controlfile;7、无效对象检查col OBJECT_NAME for a24SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';8、表空间和数据文件检查1）数据文件col file_name for a56set linesize 300select file_id,file_name,tablespace_name,autoextensible from dba_data_files;select count(*) from v$datafile;show parameter db_filesselect name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect 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 300col tablespace_name for a16SELECT 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)t otal_bytes,round(MAX(bytes)/(1024*1024),2)m ax_bytesFROM s ys.dba_free_spaceGROUP B Y t ablespace_name)f,(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_MbFROM sys.dba_data_files d dGROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_nameORDER 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_freeFROM 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) freeWHERE 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;selecttablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,stat us 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 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 ALLSELECT 'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';3.未建索引的表SELECT/*+r ule*/owner,s egment_name,s egment_type,t ablespace_name,TRUNC(BYTES/1024/1024,1)s ize_mbFROM d ba_segments tWHERE N OT E XISTS(SELECT'x'FROM d ba_indexes iWHERE t.owner=i.table_ownerAND t.segment_name=i.table_name)AND t.segment_type I N('TABLE','TABLE P ARTITION')AND t.owner N OT I N('SYS','SYSTEM')ORDER B Y5D ESC;升序用ASC9.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_filesunion allselect nvl(sum(bytes)/1024/1024,0) space from dba_temp_filesunion allselect sum(bytes)/1024/1024 space from v$log);11.检测连接数情况（1）select SW.Sid,ername,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT from v$session S,v$session_wait SW where ername is not null and SW.Sid=S.Sidand 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）信息1col segment_name format a20col tablespace_name format a20select segment_name,owner,tablespace_name,dba_rollback_segs.statusfrom dba_rollback_segs,v$Datafile where file_id=file#;2）信息2select segment_name,initial_extent,next_extent,min_extents, owner,dba_rollback_segs.status status,optsizefrom dba_rollback_segs,v$rollstatwhere dba_rollback_segs.segment_id=v$n;3）信息3col Rollback_Name for a16select substr(V$,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.AVEACTIVEfrom v$rollname, v$rollstatwhere v$N = v$Norder by v$N;4）信息4select Rollback_Name,p.pid O racle_PID,p.spid O S_PID,nvl(ername,'NO T RANSACTION')T ransaction,p.terminal T erminalfrom v$lock l, v$process p, v$rollname rwhere l.addr = p.addr(+)and t runc(l.id1(+)/65536)=nand l.type(+)='TX'and l.lmode(+)=6order by ;5）回滚段的争用情况select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where n=n; 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 N ame",substr(sys.dba_segments.SEGMENT_NAME,1,12)"Rollback N ame",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_segswhere 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 a56select f.member "member",f.group#"group",l.bytes/1024/1024 "size",l.statusfrom v$logfile f, v$log lwhere f.group#=l.group#order by f.group#,f.member;2）LogGroup信息SELECT group#, sequence#, bytes, members, status from v$log;3）关于log_bufferselect name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');4）查询LOG大小及频率set linesize 300set pages 100column 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 reportcompute sum of sw_cnt on reportcompute sum of Mb on reportvar redoMbytes number;beginselect max(bytes)/1024/1024 into :redoMbytes from v$log;end;/print redoMbytesselect trunc(first_time) d1,c ount(*)s w_cnt,c ount(*)*:redoMbytes M bfrom v$log_historygroup by trunc(first_time)/13.IO情况检查col file_name for a46select 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)) writetimefromv$datafile df,v$filestat fswhere df.file#=fs.file#order by ;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 namespacein ('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(,1,25) Name,l.gets,l.misses,100*(l.misses/l.gets)"%R atio(STAY U NDER1%)"from v$latch l, v$latchname lnwhere in ('cache buffers lru chain')and tch# = tch#;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 bwhere = 'sorts (disk)'and = 'sorts (memory)';5）数据缓冲区命中率select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratiofrom v$sysstat phy,v$sysstat cur,v$sysstat conwhere ='physical reads' and ='db block gets' and ='consistent gets'; 6）Miss LRU Hit命中率column "Miss LRU Hit%" format 99.9999999;col name format a40select name, (sleeps/gets) "Miss LRU Hit%"from v$latch where name ='cache buffers lru chain';7）检查内存排序性能select , to_char(value)from v$statname a, v$sysstatwhere a.statistic# = v$sysstat.statistic#and in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');8）redo log buffer retry ratioselect to_char(r.value/e.value) "redo log buffer retry ratio"from v$sysstat r,v$sysstat ewhere ='redo buffer allocation retries'and ='redo entries';9）wait等待检查select count(*) total_in_wait from v$session_waitwhere event='log buffer space';select event,total_waits,time_waited,average_waitfrom v$system_eventwhere 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, typeFROM V$LOCKWHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)ORDER BY id1, request;linux。
