数据库巡检手册(共7页)

1、数据库巡检张浩数据库检测硬件机型HP DL580 G7是否集群是系统实际用户数10数据库进程进入操作系统，登陆Oracle用户，命令：su - oracleProcess进程情况进入操作系统，登陆Oracle用户命令：ps -ef|grep ora_说明Oracle 10g 后台进程SMON(System Monitor)用于执行历程恢复、合并空间碎片并释放临时段。PMON(Process Monitor)用于监视服务器进程的执行，摒弃在服务器进程失败时清除该服务器进程。DBWR(Database Writer)用于将数据库缓存的脏缓冲区数据写入到数据文件中。LGWR(Log Writer)用

2、于将重做日志缓冲区所记载的全部内容写入到充作日志文件中。CKPT(Checkpoint Process)用于发出检查点(Checkpoint)，检查点会同步数据库的数据文件、控制文件和重做日志，当发出检查点时，后台进程CKPT将检查点时刻的SCN(System Change Number)写入到控制文件和数据文件头部，同时促使后台进程DBWR将所有脏缓冲区写入到数据文件中。ARCH(Archive Process)用于将重做日志的事物变化复制到归档日志中，该后台进程只有在archivelog模式下才有意义。Oracle 11g新增的后台进程DBRM说明: 数据库资源管理进程(The datab

3、ase resource manager process),负责设置资源计划和其他的资源管理的工作.DIAG说明: 数据库诊断进程(The diagnosibility process),负责维护管理各种用于诊断的转储文件,并执行oradebug命令。DIA0说明: 另一个数据库诊断进程，负责检测Oracle数据库中的挂起(hang)和死锁的处理。PSP0说明: process spawner，用于产生oracle进程SMCO说明: space management coordinator，该进程负责空间管理协调管理工作，负责执行空间的分配和回收。Wnnn说明: 命名为W000，W001，W0

4、02.，由smcO动态产生执行上述相关任务。VKTM说明: virtual keeper of time，用于提供wall-clock time，（每秒钟更新一次）。提供每二十毫秒更新一次的reference-time counter，看起来有点类似计时器的功能。GMON说明: 用于维护asm磁盘组的磁盘之间的关系。KATE说明: 当ASM的磁盘离线的时候，该进程负责asm的元文件的io读写。MARK说明: 如果有向asm离线磁盘的missed 写请求，该进程将ASM分配的单元的状态标记为staleFBDA说明: 涉及到flashback-data-archive新特性的一个进程，The fl

5、ashback data archiver proces。用于将"轨表"(trackedtables)的历史数据进行归档。当"轨表"上的事务提交以后，fbda进程负责将数据的前镜像保存到flashback archive区域。该进程还负责flashback的数据归档的空间管理、分配、保留，跟踪tracked transactions。什么是"轨表"(tracked tables): 是指启用了flashback archive特性的表。RMSn说明: The Oracle RAC management processes,负责执行Or

6、acle RAC的管理任务，比如RAC相关资源的创建和集群中新实例的添加。DSKM说明: The slave diskmon process ， 负责oracle 实例、asm实例和磁盘的管理进程之间的io fencing 信息的交换。如果使用SAGE的存储，该进程还负责SAGE存储的一些信息的管理。RAC 进程GSD global services daemon全局服务守护进程lock process(LCK) 锁管理进程DIAG:DIAGNOSABILITY DAEMON失败进程的诊断信息捕获进程Operating System-Dependent(OSD) 操作系统资源访问进程LMS -

7、 Gobal Cache Service Process 全局缓存服务进程LMD - Global Enqueue Service Daemon全局查询服务守护进程LMON-Global Enqueue Service Monitor全局查询服务监视进程LCK0 - Instance Enqueue Process 实例查询进程进程状态Space使用情况df -hListener监听状态命令：lsnrctl status配置正常运行情况命令：sqlplus “/as sysdba”查看日志命令：More /u01/app/oracle/product/11.2.0/db_1/network/l

8、og/sqlnet.log有错误才有日志Tnsname配置情况标准配置位置：/u01/app/oracle/product/11.2.0/db_1/network/admin运行情况正常查看状态Tnsping tnsname(数据库实例名)Alert配置情况标准配置/u01/app/oracle/diag/rdbms/<SID>/<INSTANCE>/trace/alert_sid.log运行情况正常More alert_sid.logCRS服务运行情况crsctl check crs检查crs的健康情况资源运行情况crs_stat t用来查看RAC中各节点上resou

9、rces的运行状况,Resources的属性等OCR运行情况ocrcheck验证OCR的状态以及空间使用情况VotingDisk运行情况crsctl query css votediskVotingdisk 状态查询ASM运行情况crs_stat -t | grep asm使用情况# su - grid$ asmcmdASMCMD> ls DATA/ORA_DATA/ASMCMD> lsdg ora_data数据库su oraclesqlplus “/as sysdba”诊断结果及建议DBstatus数据库状态select status from v$instance;配置情况Se

10、lect * from v$parameter;运行情况正常使用资源情况select * from v$resource_limit；ASM使用情况select group_number,name,total_mb,free_mb from v$asm_diskgroup;DBfile数据文件状态select name,status from v$datafile;控制文件状态select status,name from v$controlfile;日志文件状态select group#,members,archived,status from v$log;表空间使用率set pagesiz

11、e 50column "Tablespace" format a13column "UsedMB" format 99,999,999column "FreeMB" format 99,999,999column "Total MB" format 99,999,999select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free

12、MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace) "Pct Free" from (select tablespace_name, round(sum(bytes)/1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes)/1048576) FreeSpace from dba_fr

13、ee_space group by tablespace_name ) fs where df.tablespace_name=fs.tablespace_nameorder by "Pct Free" /运行情况正常Sessions并发数select count(*) from v$session where status='ACTIVE'Redo使用情况select b.THREAD#,a.GROUP#,a.STATUS,a.MEMBER,b.BYTES,b.ARCHIVED,b.STATUSfrom v$logfile a,v$log b where

14、a.GROUP#=b.GROUP#;Performance配置情况察看数据库锁表锁表有时候是瞬间的，长时间锁定的表才可能是死锁。select l.*, s.OSUSER, s.ACTION, o.OBJECT_NAME from gv$locked_object l, gv_$session s, all_objects 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 ob

15、ject_name for a30SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,cess,l.session_id,s.serial#, l.locked_mode,o.object_nameFROM v$locked_object l,dba_objects o,v$session swhere l.object_id = o.object_id and s.sid = l.session_id;select t2.username|'   '|t2.sid|'

16、   '|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

17、      order by t2.logon_time;enqueue等待Selecteq_type"lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;等待事件set linesize 200column username for a12column program for a30column event for a28column

18、p1text for a15column p1 for 999,999,999,999,999select s.username,gram,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait swwhere s.sid=sw.sid and s.status='ACTIVE'order by sw.p1;select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wai

19、torder by event;where event = 'buffer busy waits'select 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 ord

20、er by event；RAC全局等待事件select * from v$event_name where NAME like 'gc%' and WAIT_CLASS='Cluster'数据文件IOselect 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_

21、name,sum(f.phyblkrd) pbr,sum(f.phyblkwrt) pbw,sum(f.phyblkrd) pyr,sum(f.readtim),sum(f.writetim)from v$filestat f, dba_data_files fswhere f.file# = fs.file_idgroup by tablespace_nameorder by 2,3,4 desc;长事务set linesize 200column name for a16column username for a10select ,b.xacts,c.sid,c.serial#

22、,c.username,d.sql_textfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere a.usn=b.usnand b.usn=e.XIDUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hashvalue=d.hash_valueorder by ,c.sid,d.piece;大事务select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:s

23、s') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork)*100 ratio,message from v$session_longopswhere message like '%RMAN%'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,mess

24、age from v$session_longopswhere sofar <> totalwork;where (sofar/totalwork)*100 < 100;察看用户session数量Select count(*) from v$session;如果session过多，察看select * from v$session，察看是什么程序归档的生成频率set linesize 120column begin_time for a26column end_time for a26select a.recid,to_char(a.first_time,'yyyy-

25、mm-dd hh24:mi:ss') begin_time,b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') 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(DECO

26、DE(NAME, 'db block gets', VALUE, 0) +SUM(DECODE(NAME, 'consistent gets', VALUE, 0) * 100"缓冲区命中率"FROM V$SYSSTAT;重做日志命中率SELECT name, gets, misses, immediate_gets, immediate_misses,100 - Decode(gets,0,0,misses/(gets+misses)*100 ratio1,100 - Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100 ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');低效SQLSELECT EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND(BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,ROUND(DISK_READS/E