Oracle11gRAC数据库巡检手册.pdf

数据库巡检 数据库检测 硬件机型 HP DL580 G7 是否集群 是 系统实际用户数 10 数据库进程 进入操作系统，登陆 Oracle 用户，命令：su-oracle Process 进程情况 进入操作系统，登陆 Oracle 用户 命令：ps-ef|grep ora_ 说明 Oracle 10g 后台进程 SMON(System Monitor)用于执行历程恢复、合并空间碎片并释放临时段。PMON(Process Monitor)用于监视服务器进程的执行，摒弃在服务器进程失败时清除该服务器进程。DBWR(Database Writer)用于将数据库缓存的脏缓冲区数据写入到数据文件中。LGWR(Log Writer)用于将重做日志缓冲区所记载的全部内容写入到充作日志文件中。CKPT(Checkpoint Process)用于发出检查点(Checkpoint)，检查点会同步数据库的数据文件、控制文件和重做日志，当发出检查点时，后台进程CKPT 将检查点时刻的 SCN(System Change Number)写入到控制文件和数据文件头部，同时促使后台进程 DBWR 将所有脏缓冲区写入到数据文件中。ARCH(Archive Process)用于将重做日志的事物变化复制到归档日志中，该后台进程只有在 archivelog 模式下才有意义。Oracle 11g 新增的后台进程 DBRM 说 明:数 据 库 资 源 管 理 进 程(The database resource manager process),负责设置资源计划和其他的资源管理的工作.DIAG 说明:数据库诊断进程(The diagnosibility process),负责维护管理各种用于诊断的转储文件,并执行 oradebug 命令。DIA0 说明:另一个数据库诊断进程，负责检测 Oracle 数据库中的挂起(hang)和死锁的处理。PSP0 说明:process spawner，用于产生 oracle 进程 SMCO 说明:space management coordinator，该进程负责空间管理协调管理工作，负责执行空间的分配和回收。Wnnn 说明:命名为 W000，W001，W002.，由 smcO 动态产生执行上述相 关任务。VKTM 说明:virtual keeper of time，用于提供 wall-clock time，（每秒钟更新一次）。提供每二十毫秒更新一次的 reference-time counter，看起来有点类似计时器的功能。GMON 说明:用于维护 asm 磁盘组的磁盘之间的关系。KATE 说明:当 ASM 的磁盘离线的时候，该进程负责 asm 的元文件的 io 读写。MARK 说明:如果有向 asm 离线磁盘的 missed 写请求，该进程将 ASM 分配的单元的状态标记为 stale FBDA 说明:涉及到 flashback-data-archive 新特性的一个进程，The flashback data archiver proces。用于将轨表(tracked tables)的历史数据进行归档。当轨表上的事务提交以后，fbda 进程负责将数据的前镜像保存到 flashback archive 区域。该进程还负责 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 存储的一些信息的管理。RAC 进程 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 Process 实例查询进程 进程状态 Space 使用情况 df-h Listener 监听状态 命令：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_sid.log 运行情况 正常 More alert_sid.log CRS 服务运行情况 crsctl check crs 检查 crs 的健康情况 资源运行情况 crs_stat t 用来查看 RAC 中各节点上 resources 的运行状况,Resources 的属性等 OCR 运行情况 ocrcheck 验证 OCR 的状态以及空间使用情况 Voting Disk 运行情况 crsctl query css votedisk Votingdisk 状态查询 ASM 运行情况 crs_stat-t|grep asm 使用情况#su-grid$asmcmd ASMCMD ls DATA/ORA_DATA/ASMCMD lsdg ora_data ASMCMD ls-s DATA.354.774196981 数据库 su oracle sqlplus“/as sysdba”诊断结果及建议 DBstatus 数据库状态 select status from v$instance;配置情况 Select*from v$parameter;运行情况 正常 察看参数 隐含参数 set linesize 132 column name format a30 column value format a25 select x.ksppinm name,y.ksppstvl value,y.ksppstdf isdefault,decode(bitand(y.ksppstvf,7),1,MODIFIED,4,SYSTEM_MOD,FALSE)ismod,decode(bitand(y.ksppstvf,2),2,TRUE,FALSE)isadj from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv(Instance)and y.inst_id=userenv(Instance)and x.indx=y.indx and x.ksppinm like%_&par%order by translate(x.ksppinm,_,)/使用资源情况 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 pagesize 50 column Tablespace format a13 column Used MB format 99,999,999 column Free MB format 99,999,999 column Total MB format 99,999,999 select fs.tablespace_name Tablespace,(df.totalspace-fs.freespace)Used MB,fs.freespace Free 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_free_space group by tablespace_name )fs where df.tablespace_name=fs.tablespace_name order 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.STATUS from v$logfile a,v$log b where a.GROUP#=b.GROUP#;Performan配置情况 ce 察看数据库锁表 锁表有时候是瞬间的，长时间锁定的表才可能是死锁。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 200 column oracle_username for a16 column os_user_name for a12 column object_name for a30 SELECT l.xidusn,l.object_id,l.oracle_username,l.os_user_name,l.process,l.session_id,s.serial#,l.locked_mode,o.object_name FROM v$locked_object l,dba_objects o,v$session s where 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;latch 1、从 V$LATCH,V$LATCH_CHILDREN,V$LATCH_MISSES 查看有关 LATCH 的统计信息 例：SELECT latch#,name,gets,misses,sleeps FROM v$latch WHERE sleeps0 ORDER BY sleeps;2、在 PEAK TIME，查看当前的 LATCH 竞争 例：select count(*),name latchname from v$session_wait,v$latchname where event=latch free and state=WAITING and p2=latch#group by name order by 1 desc;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 统计信息*/column name format A32 truncate heading LATCH NAME column pid heading HOLDER PID select c.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses,b.pid from v$latch a,v$latchholder b,v$latchname c where a.addr=b.laddr(+)and a.latch#=c.latch#order by a.latch#;/*给 出 一 个latch 地 址，查 出 相 应 的latch名 称*/column name format a64 heading Name select name from v$latchname a,v$latch b where b.addr=&addr and b.latch#=a.latch#;/*显示 latch 统计信息*/column name format a32 heading LATCH NAME column pid heading HOLDER PID select c.name,a.addr,a.gets,a.misses,a.sleeps,a.immediate_gets,a.immediate_misses,b.pid from v$latch a,v$latchholder b,v$latchname c where a.addr =b.laddr(+)and a.latch#=c.latch#and c.name like&latch_name%order by a.latch#;enqueue等待 Select eq_type lock,total_req#gets,total_wait#waits,cum_wait_time from v$enqueue_stat where total_wait#0;等待事件 set linesize 200 column username for a12 column program for a30 column event for a28 column p1text for a15 column p1 for 999,999,999,999,999 select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw where s.sid=sw.sid and s.status=ACTIVE order by sw.p1;select event,p1 File#,p2 Block#,p3 Reason Code from v$session_wait order by event;where event=buffer busy waits;select owner,segment_name,segment_type,file_id,block_id from dba_extents where 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%and WAIT_CLASS=Cluster;数据文件IO select fs.name name,f.phyblkrd pbr,f.phyblkwrt pbw,f.phyblkrd pyr,f.readtim,f.writetim from v$filestat f,v$dbfile fs where f.file#=fs.file#order by 2,3,4 desc;表空间 IO select tablespace_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 fs where f.file#=fs.file_id group by tablespace_name order by 2,3,4 desc;长事务 set linesize 200 column name for a16 column username for a10 select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e where a.usn=b.usn and b.usn=e.XIDUSN and c.taddr=e.addr and c.sql_address=d.ADDRESS and c.sql_hash_value=d.hash_value order 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 where 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,message from v$session_longops where sofar totalwork;where(sofar/totalwork)*100 0 AND BUFFER_GETS 0 AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS 1000000 order by 4 desc；查看 oracle sga 使用情况,及分析 select round(sga,0)sga,round(cache_size,0)cache_size,round(coun/cache_size*100,0)|%cache_used,round(shared_size,0)shared_size,round(freemb/shared_size*100,0)|%free_shared,round(java_size,0)java,round(large_pool_size,0)large from (select bytes/1024/1024 sga from v$sgainfo where name=Maximum SGA Size),(select bytes/1024/1024 shared_size from v$sgainfo where name=Shared Pool Size),(select bytes/1024/1024 cache_size from v$sgainfo where name=Buffer Cache Size),(select bytes/1024/1024 java_size from v$sgainfo where name=Java Pool Size),(select bytes/1024/1024 large_pool_size from v$sgainfo where name=Large Pool Size),(select bytes/1024/1024 freemb from v$sgastat s where s.pool=shared pool and s.name=free memory),(select sum(count(*)*8/1024 coun from v$bh where statusfree group by status);注:sga:系统全局区大小 cache_size:数据缓冲区大小 shared_size:共享缓冲区大小 cache_used:数据缓冲区使用率 free_shared:共享缓冲区空闲率 上面的均以 MB 为单位 分析:上面的 数据缓冲区使用率统计可能会不太精确,你会发现当没有 v$bh 视图里状态没有 free 的时候使用率,也就是全被用的时候,使用率也不是100%.如果看更详细的数据缓冲区使用情况可以用下面的 sql,我们可以主要看是否有 free,关于 v$bh 视图可以去网上找找相关文章.select count(*),status from v$bh group by status;没有free不一定说明需要增加,还要结合当前cache_size的大小,缓冲区命中率,等待事件(如:free buffer waits)来判断是否需要增加大小,但多数的时候添加缓冲区的大小不会解决质的问题,而真正的问题主要是对 sql 语句的优化(如:是否存在大量的全表扫描等),.我在生产中遇到过类似的问题,200M的cache_size,命中率很低21%,但通过对 sql 语句的优化(添加索引,避免全表扫描),命中率增加到 96%,程序运行时间由原来的 2 小时减少到不到 10 分钟.关 于 数 据 缓 冲 区 的 优 化 可 以 参 考 我 的 另 外 一 篇 文 章：http:/scn.org/htm_data/586/0904/46110.html 共享缓冲区空闲率也是一样的道理.(如:sql 语句是否没有绑定变量等)临时表空间情况 SELECT se.username 用户名,se.sid,se.serial#,se.sql_address,se.machine,se.program,su.tablespace,su.blocks*8192/1024/1024 Used Space(M),su.segtype,su.contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr;在线日志情况 SELECT B.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND(A.FIRST_TIME-B.FIRST_TIME)*24*60,2)MINATES FROM V$LOG_HISTORY A,V$LOG_HISTORY B WHERE A.RECID=B.RECID+1 AND A.FIRST_TIMESYSDATE-20 AND ROUND(A.FIRST_TIME-B.FIRST_TIME)*24*60,2)to_date(2013-03-04 00:00:00,YYYY-MM-DD HH24:MI:SS)and first_time 50 order by 1,2；索引使用情况 col c1 heading Object|Name format a30 col c2 heading Operation format a15 col c3 heading Option format a15 col c4 heading Index|Usage|Count format 999,999 break on c1 skip 2 break on c2 skip 2 select p.object_name c1,p.operation c2,p.options c3,count(1)c4 from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner SYS and p.operation like%INDEX%and p.sql_id=s.sql_id group by p.object_name,p.operation,p.options order by 1,2,3;无效索引 SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE STATUS=UNUSABLE;SQL 执 行计划 查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。使用如下 sql 可以发现某个 sql 的执行计划什么时候发生了变化！select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,yyyymmdd hh24:mi:ss)TIMESTAMP from dba_hist_sql_plan where SQL_ID=68wnxdjxwwn2h order by TIMESTAMP;查看出来执行计划的变化之后 可以使用如下 sql 查看发生了那些变化！col options for a15 col operation for a20 col object_name for a20 select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,yyyymmdd hh24:mi:ss)from DBA_HIST_SQL_PLAN where sql_id=68wnxdjxwwn2h and plan_hash_value in(1542630049,2754593971,2620382595)order by ID,TIMESTAMP;解 析 慢sql SELECT SQL_ID,AVG_HARD_PARSE_TIME_MS FROM(SELECT ROUND(AVG_HARD_PARSE_TIME/1000,2)AVG_HARD_PARSE_TIME_MS,SQL_ID,EXECUTIONS FROM V$SQLSTATS ORDER BY AVG_HARD_PARSE_TIME DESC)WHERE ROWNUM=10;解析次 数过 多 的语句 SELECT SUBSTR(SQL_TEXT,1,180)SQL,COUNT(*)记录数,SUM(EXECUTIONS)执行次数 FROM V$SQLAREA-WHERE EXECUTIONS 30 ORDER BY 2 DESC;SELECT SUBSTR(sql_text,1,100),module,COUNT(*)total_number FROM v$sql GROUP BY module,SUBSTR(sql_text,1,100)HAVING COUNT(*)50 ORDER BY 3 DESC select substr(a.SQL_TEXT,1,70),count(*)from v$sqlarea a group by substr(a.SQL_TEXT,1,70)having count(*)10;select address,CHILD_ADDRESS from v$sql where address in (select address from v$sql group by address having count(*)in(select max(count(*)from v$sql group by address);查看解 析语 句 未能 共 享原因 select address,auth_check_mismatch,translation_mismatch,optimizer_mismatch from v$sql_shared_cursor where address in(select address from v$sql where upper(sql_text)like SELECT/*OPT_DYN_SAMP*/*+ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB)查看表 碎片 SELECT table_name,ROUND(blocks*8),2)高水位空间 k,ROUND(num_rows*avg_row_len/1024),2)真实使用空间 k,ROUND(blocks*10/100)*8,2)预 留空 间(pctfree)k,ROUND(blocks*8-(num_rows*avg_row_len/1024)-blocks*8*10/100),2)浪费空间 k FROM dba_tables WHERE table_name=BP_RESERVE_ORDERLIST;或者 owner=没有主键的表 SELECT OWNER,table_name,NUM_ROWS FROM dba_tables t WHERE NOT EXISTS(SELECT table_name FROM dba_constraints c WHERE constraint_type=P AND t.table_name=c.table_name)AND OWNER=YKSOFT AND TABLE_NAME NOT LIKE%TEMP%AND TABLE_NAME NOT LIKE%TMP%AND TABLE_NAME NOT LIKE%#%ORDER BY NUM_ROWS DESC;估算表在高水位线下还有多少空间是无数据的，但在全表扫描时又需要做无用功的数据 SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)-(NUM_ROWS*AVG_ROW_LEN/1024/1024)Data lower than HWM in MB FROM DBA_TABLES WHERE table_name IN (select table_name from dba_tables where OWNER=YKSOFT AND TABLE_NAME NOT LIKE%TEMP%AND TABLE_NAME NOT LIKE%TMP%AND TABLE_NAME NOT LIKE%#%);Tom 提供的脚本，用于查询哪些外键没有创建相应的索引，避免由于外键的问题导致删除或更新主键时发生锁表的问题 SELECT TABLE_NAME,CONSTRAINT_NAME,CNAME1|NVL2(CNAME2,|CNAME2,NULL)|NVL2(CNAME3,|CNAME3,NULL)|NVL2(CNAME4,|CNAME4,NULL)|NVL2(CNAME5,|CNAME5,NULL)|NVL2(CNAME6,|CNAME6,NULL)|NVL2(CNAME7,|CNAME7,NULL)|NVL2(CNAME8,|CNAME8,NULL)COLUMNS FROM(SELECT B.TABLE_NAME,B.CONSTRAINT_NAME,MAX(DECODE(POSITION,1,COLUMN_NAME,NULL)CNAME1,MAX(DECODE(POSITION,2,COLUMN_NAME,NULL)CNAME2,MAX(DECODE(POSITION,3,COLUMN_NAME,NULL)CNAME3,MAX(DECODE(POSITION,4,COLUMN_NAME,NULL)CNAME4,MAX(DECODE(POSITION,5,COLUMN_NAME,NULL)CNAME5,MAX(DECODE(POSITION,6,COLUMN_NAME,NULL)CNAME6,MAX(DECODE(POSITION,7,COLUMN_NAME,NULL)CNAME7,MAX(DECODE(POSITION,8,COLUMN_NAME,NULL)CNAME8,COUNT(*)COL_CNT FROM(SELECT SUBSTR(TABLE_NAME,1,30)TABLE_NAME,SUBSTR(CONSTRAINT_NAME,1,30)CONSTRAINT_NAME,SUBSTR(COLUMN_NAME,1,30)COLUMN_NAME,POSITION FROM USER_CONS_COLUMNS)A,USER_CONSTRAINTS B WHERE A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE=R GROUP BY B.TABLE_NAME,B.CONSTRAINT_NAME)CONS WHERE COL_CNT ALL(SELECT COUNT(*)FROM USER_IND_COLUMNS I WHERE I.TABLE_NAME=CONS.TABLE_NAME AND I.COLUMN_NAME IN(CNAME1,CNAME2,CNAME3,CNAME4,CNAME5,CNAME6,CNAME7,CNAME8)AND I.COLUMN_POSITION SCHEMA_NAME);察看硬解析 select substr(a.SQL_TEXT,1,70),count(*)from v$sqlarea a group by substr(a.SQL_TEXT,1,70)having count(*)10;大小排名前十的表和索引 select*from(select owner,segment_name,segment_type,sum(bytes)/1048576 TABLE_SIZE_M from dba_segments where segment_type in(INDEX,TABLE)group by owner,segment_name