Oracle11gRAC数据库巡检手册Word文档格式.docx

1、reference-time counter，看起来有点类似计时器的功能。GMON 用于维护asm磁盘组的磁盘之间的关系。KATE 当ASM的磁盘离线的时候，该进程负责asm的元文件的io读写。MARK 如果有向asm离线磁盘的missed 写请求，该进程将ASM分配的单元的状态标记为staleFBDA 涉及到flashback-data-archive新特性的一个进程，The flashback data archiver proces。用于将轨表（trackedtables）的历史数据进行归档。当上的事务提交以后，fbda进程负责将数据的前镜像保存到flashback archive区域。

2、该进程还负责flashback的数据归档的空间管理、分配、保留，跟踪tracked transactions。什么是（tracked tables）: 是指启用了flashback archive特性的表。RMSn The Oracle RAC management processes,负责执行Oracle RAC的管理任务，比如RAC相关资源的创建和集群中新实例的添加。DSKM The slave diskmon process ， 负责oracle 实例、asm实例和磁盘的管理进程之间的io fencing 信息的交换。如果使用SAGE的存储，该进程还负责SAGE存储的一些信息的管理。RA

3、C 进程GSD global services daemon 全局服务守护进程lock process（LCK） 锁管理进程DIAG:DIAGNOSABILITY DAEMON 失败进程的诊断信息捕获进程Operating System-Dependent（OSD） 操作系统资源访问进程LMS - Gobal Cache Service Process 全局缓存服务进程LMD - Global Enqueue Service Daemon全局查询服务守护进程LMON-Global Enqueue Service Monitor全局查询服务监视进程LCK0 - Instance Enqueue

4、Process 实例查询进程进程状态Space使用情况df -hListener监听状态lsnrctl status配置正常运行情况sqlplus “/as sysdba” 查看日志More /u01/app/oracle/product/11.2.0/db_1/network/log/sqlnet.log有错误才有日志Tnsname配置情况标准配置位置：/u01/app/oracle/product/11.2.0/db_1/network/admin正常查看状态Tnsping tnsname（数据库实例名）Alert/u01/app/oracle/diag/rdbms/trace/alert

5、_sid.logMore alert_sid.logCRS服务运行情况crsctl check crs 检查crs的健康情况资源运行情况crs_stat t用来查看RAC中各节点上resources的运行状况,Resources的属性等OCRocrcheck验证OCR的状态以及空间使用情况VotingDiskcrsctl query css votediskVotingdisk 状态查询ASMcrs_stat -t | grep asm# su - grid$ asmcmdASMCMD ls DATA/ORA_DATA/ lsdg ora_data ls -s DATA.354.7741969

6、81数据库su oracle诊断结果及建议DBstatus数据库状态select status from v$instance;Select * from v$parameter;察看参数隐含参数set linesize 132column name format a30column value format a25selectx.ksppinmname,y.ksppstvlvalue,y.ksppstdfisdefault,decode（bitand（y.ksppstvf,7）,1,MODIFIED,4,SYSTEM_MOD,FALSE）ismod,decode（bitand（y.kspps

7、tvf,2）,2,TRUEisadjfromsys.x$ksppi x,sys.x$ksppcv ywherex.inst_id = userenv（Instance） andy.inst_id = userenv（x.indx = y.indx andx.ksppinm like %_&par%order bytranslate（x.ksppinm, _, ）/使用资源情况select * from v$resource_limit；select group_number,name,total_mb,free_mb from v$asm_diskgroup;DBfile数据文件状态selec

8、t name,status from v$datafile;控制文件状态select status,name from v$controlfile;日志文件状态select group#,members,archived,status from v$log;表空间使用率set pagesize 50 column Tablespace format a13Used MB format 99,999,999Free MBTotal MB select fs.tablespace_name , （df.totalspace - fs.freespace） fs.freespace df.total

9、space round（100 * （fs.freespace / df.totalspace） Pct Free from （select tablespace_name, round（sum（bytes）/1048576） TotalSpace dba_data_files group by tablespace_name ） df, round（sum（bytes）/1048576） FreeSpace dba_free_space ） fs where df.tablespace_name=fs.tablespace_name order by /Sessions并发数select c

10、ount（*） from v$session where status=ACTIVE;Redoselect b.THREAD#,a.GROUP#,a.STATUS,a.MEMBER,b.BYTES,b.ARCHIVED,b.STATUSfrom v$logfile a,v$log b where a.GROUP#=b.GROUP#;Performance察看数据库锁表锁表有时候是瞬间的，长时间锁定的表才可能是死锁。select l.*, s.OSUSER, s.ACTION, o.OBJECT_NAME from gv$locked_object l, gv_$session s, all_o

11、bjects o where l.SESSION_ID=s.SID and o.OBJECT_ID=l.OBJECT_ID死锁set linesize 200column oracle_username for a16column os_user_name for a12column object_name for a30SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,l.session_id,s.serial#, l.locked_mode,o.object_nameFROM v$locked_o

12、bject l,dba_objects o,v$session swhere l.object_id = o.object_id and s.sid = l.session_id;select t2.username|t2.sid|t2.serial#|t2.logon_time|t3.sql_text from v$locked_object t1,v$session t2,v$sqltext t3 where t1.session_id=t2.sid and t2.sql_address=t3.address order by t2.logon_time;latch1、从V$LATCH,V

13、$LATCH_CHILDREN,V$LATCH_MISSES查看有关LATCH的统计信息例：SELECT latch#, name, gets, misses, sleeps FROM v$latchWHERE sleepsORDER BY sleeps;2、在PEAK TIME，查看当前的LATCH竞争select count（*）, name latchname from v$session_wait, v$latchnamewhere event=latch free and state=WAITING and p2=latch#group by name order by 1 desc

14、;3、确定影响系统的主要LATCH CONTENTION，并针对具体LATCH竞争采取不同的ACTION如果LATCH FREE的等待事件在WAIT EVENT占了比较大的比重，需要检查具体的LATCH竞争的情况。最常见的LATCH竞争是LIBRARY CACHE、SHARED POOL、CACHE BUFFER CHAINS和CACHE BUFFERS LRU CHAINS。前两者是关于SHARED POOL（SHARED_POOL_SIZE）的，后两者是SHARED BUFFER（DB_BLOCK_BUFFERS）的/*显示系统范围内的latch统计信息*/columnnameformat

15、A32truncateheadingLATCHNAMEpidHOLDERPIDselectc.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses,b.pidfromv$latcha,v$latchholderb,v$latchnamecwherea.addr=b.laddr（+）anda.latch#c.latch#orderbya.latch#;给出一个latch地址，查出相应的latch名称*/a64Namebb.addr&addrb.latch#=a.latch#;显示latch统计信息*/a3

16、2NAMEPIDc.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses,b.pidc.namelikelatch_name%enqueue等待Select eq_type lock,total_req# gets,total_wait# waits,cum_wait_time from v$enqueue_stat where total_wait#0;等待事件column username for a12column program for a30column event for a28column

17、 p1text for a15column p1 for 999,999,999,999,999select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait swwhere s.sid=sw.sid and s.status=order by sw.p1;select event,p1 File #,p2 Block #,p3 Reason Code from v$session_waitorder by event;where event = buffer busy waitssele

18、ct owner,segment_name,segment_type,file_id,block_id from dba_extentswhere file_id = &P1 and &P2 between block_id and block_id + blocks -1;column event for a35;column p1text for a40;select sid,event,p1,p1text from v$session_wait order by event；RAC全局等待事件select * from v$event_name where NAME like gc% a

19、nd WAIT_CLASS=Cluster数据文件IOselect fs.name name,f.phyblkrd pbr,f.phyblkwrt pbw,f.phyblkrd pyr,f.readtim,f.writetimfrom v$filestat f, v$dbfile fswhere f.file# = fs.file# order by 2,3,4 desc;表空间IOselect tablespace_name,sum（f.phyblkrd） pbr,sum（f.phyblkwrt） pbw,sum（f.phyblkrd） pyr,sum（f.readtim）,sum（f.wr

20、itetim）from v$filestat f, dba_data_files fswhere f.file# = fs.file_idgroup by tablespace_nameorder by 2,3,4 desc;长事务column name for a16column username for a10select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_textfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere a.usn=b

21、.usnand b.usn=e.XIDUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hash_value=d.hash_valueorder by a.name,c.sid,d.piece;大事务select sid,serial#,to_char（start_time,yyyy-mm-dd hh24:mi:ss） start_time,sofar,totalwork,（sofar/decode（totalwork,0,1,totalwork）*100 ratio,message from v$session_longops

22、where message like %RMAN%where sofar totalwork;where （sofar/totalwork）*100 100;察看用户session数量Select count（*） from v$session;如果session过多，察看select * from v$session，察看是什么程序察看用户session使用内存大小SELECT username,sess.sid,sum（trunc（value/1024） MEM_USEDFROM v$session sess, v$sesstat stat, v$statname name WHERE s

23、ess.sid = stat.sid AND stat.statistic# = name.statistic# AND name.name in （session uga memory ,session pga memorygroup by username,sess.sidorder by mem_used归档的生成频率set linesize 120column begin_time for a26column end_time for a26select a.recid,to_char（a.first_time,） begin_time,b.recid,to_char（b.first_time,） end_time,round（b.first_time - a.first_time）*24*60,2） minutesfrom v$log_history a,v$log_history bwhere b.recid = a.recid+1;缓冲区命中率SELECT （1 - （SUM（DECODE（NAME, physical reads, VALUE, 0） /（SUM（DECODE（NAME, db