Oracle11gRAC巡檢手冊(cè)

1、數(shù)據(jù)庫(kù)巡檢張浩數(shù)據(jù)庫(kù)檢測(cè)硬件機(jī)型HP DL580 G7是否集群是系統(tǒng)實(shí)際用戶數(shù)10數(shù)據(jù)庫(kù)進(jìn)程進(jìn)入操作系統(tǒng)，登陸Oracle用戶，命令：su - oracleProcess進(jìn)程情況進(jìn)入操作系統(tǒng)，登陸Oracle用戶命令：ps -ef|grep ora_說明Oracle 11g后臺(tái)進(jìn)程DBRM說明: 數(shù)據(jù)庫(kù)資源管理進(jìn)程(The database resource manager process),負(fù)責(zé)設(shè)置資源計(jì)劃和其他的資源管理的工作.DIAG說明: 數(shù)據(jù)庫(kù)診斷進(jìn)程(The diagnosibility process),負(fù)責(zé)維護(hù)管理各種用于診斷的轉(zhuǎn)儲(chǔ)文件,并執(zhí)行oradebug命令。DIA0說明

2、: 另一個(gè)數(shù)據(jù)庫(kù)診斷進(jìn)程，負(fù)責(zé)檢測(cè)Oracle數(shù)據(jù)庫(kù)中的掛起(hang)和死鎖的處理。PSP0說明: process spawner，用于產(chǎn)生oracle進(jìn)程SMCO說明: space management coordinator，該進(jìn)程負(fù)責(zé)空間管理協(xié)調(diào)管理工作，負(fù)責(zé)執(zhí)行空間的分配和回收。Wnnn說明: 命名為W000，W001，W002.，由smcO動(dòng)態(tài)產(chǎn)生執(zhí)行上述相關(guān)任務(wù)。VKTM說明: virtual keeper of time，用于提供wall-clock time，（每秒鐘更新一次）。提供每二十毫秒更新一次的reference-time counter，看起來有點(diǎn)類似計(jì)時(shí)器的功能。

3、GMON說明: 用于維護(hù)asm磁盤組的磁盤之間的關(guān)系。KATE說明: 當(dāng)ASM的磁盤離線的時(shí)候，該進(jìn)程負(fù)責(zé)asm的元文件的io讀寫。MARK說明: 如果有向asm離線磁盤的missed 寫請(qǐng)求，該進(jìn)程將ASM分配的單元的狀態(tài)標(biāo)記為staleFBDA說明: 涉及到flashback-data-archive新特性的一個(gè)進(jìn)程，The flashback data archiver proces。用于將"軌表"(trackedtables)的歷史數(shù)據(jù)進(jìn)行歸檔。當(dāng)"軌表"上的事務(wù)提交以后，fbda進(jìn)程負(fù)責(zé)將數(shù)據(jù)的前鏡像保存到flashback archive區(qū)域

4、。該進(jìn)程還負(fù)責(zé)flashback的數(shù)據(jù)歸檔的空間管理、分配、保留，跟蹤tracked transactions。什么是"軌表"(tracked tables): 是指啟用了flashback archive特性的表。RMSn說明: The Oracle RAC management processes,負(fù)責(zé)執(zhí)行Oracle RAC的管理任務(wù)，比如RAC相關(guān)資源的創(chuàng)建和集群中新實(shí)例的添加。DSKM說明: The slave diskmon process ， 負(fù)責(zé)oracle 實(shí)例、asm實(shí)例和磁盤的管理進(jìn)程之間的io fencing 信息的交換。如果使用SAGE的存儲(chǔ)，該進(jìn)

5、程還負(fù)責(zé)SAGE存儲(chǔ)的一些信息的管理。RAC 進(jìn)程GSD global services daemon 全局服務(wù)守護(hù)進(jìn)程lock process(LCK) 鎖管理進(jìn)程DIAG:DIAGNOSABILITY DAEMON 失敗進(jìn)程的診斷信息捕獲進(jìn)程Operating System-Dependent(OSD) 操作系統(tǒng)資源訪問進(jìn)程LMS - Gobal Cache Service Process 全局緩存服務(wù)進(jìn)程LMD - Global Enqueue Service Daemon全局查詢服務(wù)守護(hù)進(jìn)程LMON-Global Enqueue Service Monitor全局查詢服務(wù)監(jiān)視進(jìn)程LCK

6、0 - Instance Enqueue Process 實(shí)例查詢進(jìn)程進(jìn)程狀態(tài)Space使用情況df -hListener監(jiān)聽狀態(tài)命令：lsnrctl status配置正常運(yùn)行情況命令：sqlplus “/as sysdba” 查看日志命令：More /u01/app/oracle/product/11.2.0/db_1/network/log/sqlnet.log有錯(cuò)誤才有日志Tnsname配置情況標(biāo)準(zhǔn)配置位置：/u01/app/oracle/product/11.2.0/db_1/network/admin運(yùn)行情況正常查看狀態(tài)Tnsping tnsname(數(shù)據(jù)庫(kù)實(shí)例名)Alert配置情況

7、標(biāo)準(zhǔn)配置/u01/app/oracle/diag/rdbms/<SID>/<INSTANCE>/trace/alert_sid.log運(yùn)行情況正常More alert_sid.logCRS服務(wù)運(yùn)行情況crsctl check crs 檢查crs的健康情況資源運(yùn)行情況crs_stat t用來查看RAC中各節(jié)點(diǎn)上resources的運(yùn)行狀況,Resources的屬性等OCR運(yùn)行情況ocrcheck驗(yàn)證OCR的狀態(tài)以及空間使用情況VotingDisk運(yùn)行情況crsctl query css votediskVotingdisk 狀態(tài)查詢ASM運(yùn)行情況crs_stat -t |

8、 grep asm使用情況# su - grid$ asmcmdASMCMD> ls DATA/ORA_DATA/ASMCMD> lsdg ora_data數(shù)據(jù)庫(kù)su oraclesqlplus “/as sysdba”診斷結(jié)果及建議DBstatus數(shù)據(jù)庫(kù)狀態(tài)select status from v$instance;配置情況Select * from v$parameter;運(yùn)行情況正常使用資源情況select * from v$resource_limit；ASM使用情況select group_number,name,total_mb,free_mb from v$asm_d

9、iskgroup;DBfile數(shù)據(jù)文件狀態(tài)select name,status from v$datafile;控制文件狀態(tài)select status,name from v$controlfile;日志文件狀態(tài)select group#,members,archived,status from v$log;表空間使用率set pagesize 50column "Tablespace" format a13column "Used MB" format 99,999,999column "Free MB" format 99,999

10、,999column "Total MB" format 99,999,999select 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 ta

11、blespace_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_nameorder by "Pct Free" /運(yùn)行情況正常Sessio

12、ns并發(fā)數(shù)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 a.GROUP#=b.GROUP#;Performance配置情況察看數(shù)據(jù)庫(kù)鎖表鎖表有時(shí)候是瞬間的，長(zhǎng)時(shí)間鎖定的表才可能是死鎖。select l.*, s.OSUSER, s.ACTION, o.OBJECT_NAME from gv$locked_

13、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 object_name for a30SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,cess,l.session_id,s.serial#, l.locked_mod

14、e,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|'   '|t2.serial#|'   '|t2.logon_time|'   '|t3.sql_text   

15、0;  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;enqueue等待Select eq_type "lock",total_req# "gets",to

16、tal_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 p1text for a15column p1 for 999,999,999,999,999select s.username,gram,sw.event,sw.p1text,sw.p1 from v$session s,v$sess

17、ion_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_waitorder by event;where event = 'buffer busy waits'select owner,segment_name,segment_type,file_id,block_id from dba_e

18、xtentswhere 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'數(shù)據(jù)文件IOselect

19、 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.writetim)from v$filestat f, dba_data

20、_files fswhere f.file# = fs.file_idgroup by tablespace_nameorder by 2,3,4 desc;長(zhǎng)事務(wù)set linesize 200column name for a16column username for a10select ,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.usnand b.usn=e.XI

21、DUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hashvalue=d.hash_valueorder by ,c.sid,d.piece;大事務(wù)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_longopswhere mes

22、sage 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_longopswhere sofar <> totalwork;where (sofar/totalwork)*100 < 100;察看用戶session數(shù)量Select count(*)

23、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-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

24、 - a.first_time)*24*60,2) minutesfrom v$log_history a,v$log_history bwhere b.recid = a.recid+1;緩沖區(qū)命中率SELECT (1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0) /(SUM(DECODE(NAME, 'db block gets', VALUE, 0) +SUM(DECODE(NAME, 'consistent gets', VALUE, 0) * 100"緩沖區(qū)命中率"F

25、ROM 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/EXECUTIONS,2) Reads_per_run,SQL_TEXTFROM V$SQLARE