數(shù)據(jù)庫(kù)巡檢手冊(cè)

1、數(shù)據(jù)庫(kù)巡檢張浩數(shù)據(jù)庫(kù)檢測(cè)硬件機(jī)型HP DL580 G7是否集群是系統(tǒng)實(shí)際用戶(hù)數(shù)10數(shù)據(jù)庫(kù)進(jìn)程進(jìn)入操作系統(tǒng)，登陸 Oracle用戶(hù)，命令：su - oracleProcess進(jìn)程情況進(jìn)入操作系統(tǒng)，登陸Oracle用戶(hù) 命令：ps -ef|grep ora_說(shuō)明Oracle 10g后臺(tái)進(jìn)程SMON(System Monitor)用于執(zhí)行歷程恢復(fù)、合并空間碎片并釋放臨時(shí)段。 PMON(Process Monitor)用于監(jiān)視服務(wù)器進(jìn)程的執(zhí)行，摒棄在服務(wù)器進(jìn)程 失敗時(shí)清除該服務(wù)器進(jìn)程。DBWR(Database Writer)用于將數(shù)據(jù)庫(kù)緩存的臟緩沖區(qū)數(shù)據(jù)寫(xiě)入到數(shù)據(jù)文 件中。LGWR(Log Writ

2、er)用于將重做日志緩沖區(qū)所記載的全部?jī)?nèi)容與入到充作 日志文件中。CKPT(Checkpoint Process)用于發(fā)出檢查點(diǎn)(Checkpoint),檢杳點(diǎn)會(huì)同步 數(shù)據(jù)庫(kù)的數(shù)據(jù)文件、控制文件和重做日志，當(dāng)發(fā)出檢杳點(diǎn)時(shí)，后臺(tái)進(jìn)程 CKPT將檢杳點(diǎn)時(shí)刻的 SCN(System Change Number)寫(xiě)入到控制文件和數(shù) 據(jù)文件頭部，同時(shí)促使后臺(tái)進(jìn)程DBWI將所有臟緩沖區(qū)寫(xiě)入到數(shù)據(jù)文件中。ARCH(Archive Process)用于將重做日志的事物變化復(fù)制到歸檔日志中， 該后臺(tái)進(jìn)程只有在 archivelog模式下才有意義。Oracle 11g新增的后臺(tái)進(jìn)程DBRM說(shuō)明：數(shù)據(jù)庫(kù) 資源管理進(jìn)

3、程(The database resource man ager process),負(fù)責(zé)設(shè)置資源計(jì)劃和其他的資源管理的工作DIAG說(shuō)明：數(shù)據(jù)庫(kù)診斷進(jìn)程(The diagnosibility process),負(fù)責(zé)維護(hù)管理各種用于診斷的轉(zhuǎn)儲(chǔ)文件，并執(zhí)行oradebug命令。DIA0說(shuō)明：另一個(gè)數(shù)據(jù)庫(kù)診斷進(jìn)程，負(fù)責(zé)檢測(cè)Oracle數(shù)據(jù)庫(kù)中的掛起(hang)和死鎖的處理。PSP0 說(shuō)明:process spawner，用于產(chǎn)生 oracle 進(jìn)程SMCO說(shuō)明：space management coordinator ，該進(jìn)程負(fù)責(zé)空間管理協(xié)調(diào)管理 工作，負(fù)責(zé)執(zhí)行空間的分配和回收。Wnnn說(shuō)明：命名為

4、W000 W001, W002，由smcO動(dòng)態(tài)產(chǎn)生執(zhí)行上述相 關(guān)任務(wù)。VKTM說(shuō)明：virtual keeper of time，用于提供 wall-clock time,(每秒鐘更新一次)。提供每二十毫秒更新一次的refere nce-time cou nter，看起來(lái)有點(diǎn)類(lèi)似計(jì)時(shí)器的功能。GMON說(shuō)明：用于維護(hù)asm磁盤(pán)組的磁盤(pán)之間的關(guān)系。KATE說(shuō)明：當(dāng)ASM的磁盤(pán)離線的時(shí)候，該進(jìn)程負(fù)責(zé)asm的兀文件的io讀與。 MARK說(shuō)明：如果有向asm離線磁盤(pán)的 missed與請(qǐng)求，該進(jìn)程將ASM分配的 單元的狀態(tài)標(biāo)記為 staleFBDA說(shuō)明：涉及到 flashback-data-archive

5、新特性的 一個(gè)進(jìn)程，Theflashback data archiver proces。用于彳將"軌表"(trackedtables)的歷史數(shù)據(jù)進(jìn)行歸檔。當(dāng)”軌表"上的事務(wù)提交以后，fbda進(jìn)程負(fù)責(zé)將數(shù)據(jù)的前鏡像保存到flashback archive區(qū)域。該進(jìn)程還負(fù)責(zé) flashback 的數(shù)據(jù)歸檔的空間管理、分配、保留，跟蹤 tracked tran sact ions。什么是"軌表"(tracked tables):是指啟用了 flashback archive 特性的表。RMS n說(shuō)明：The Oracle RAC management

6、 processes,負(fù)責(zé)執(zhí)行 Oracle RAC的管理任務(wù)，比如RAC相關(guān)資源的創(chuàng)建和集群中新實(shí)例的添加。DSKM說(shuō)明:The slave diskmon process ,負(fù)責(zé) oracle 實(shí)例、asm實(shí)例禾口 磁盤(pán)的管理進(jìn)程之間的io fencing信息的交換。如果使用SAGE的存儲(chǔ)，該進(jìn)程還負(fù)責(zé)SAGE存儲(chǔ)的一些信息的管理。RAC進(jìn)程GSD global services daemon全局服務(wù)守護(hù)進(jìn)程lock process(LCK)鎖管理進(jìn)程DIAG:DIAGNOSABILITY DAEMO失敗進(jìn)程的診斷信息捕獲進(jìn)程 Operati ng System-Depe nden t(O

7、SD)操作系統(tǒng)資源訪問(wèn)進(jìn)程LMS - Gobal Cache Service Process全局緩存服務(wù)進(jìn)程LMD - Global En queue Service Daemon全局查詢(xún)服務(wù)守護(hù)進(jìn)程LMON-Global En queue Service Mo nitor全局查詢(xún)服務(wù)監(jiān)視進(jìn)程LCK0 - In sta nee En queue Process實(shí)例查詢(xún)進(jìn)程進(jìn)程狀態(tài)Space使用情況df -hListe ner監(jiān)聽(tīng)狀態(tài)命令：Isnrctl status配置正常運(yùn)行情況命令：sqlplus"/as sysdba ”查看日志命令：More /u01/app/oracle/p

8、roduct/11.2.0/db 1/network/log/sql net.log有錯(cuò)誤才有日志Tnsn ame配置情況標(biāo)準(zhǔn)配置位置：/u01/app/oracle/product/11.2.0/db 1/network/admi n運(yùn)行情況正常查看狀態(tài)Tnsping tnsname（數(shù)據(jù)庫(kù)實(shí)例名）Alert配置情況標(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的健康情況資源

9、運(yùn)行情況crs_stat - t用來(lái)查看 RAC中各節(jié)點(diǎn)上resources 的運(yùn)行狀況Resources的屬性等OCR運(yùn)行情況ocrcheck驗(yàn)證OCR的狀態(tài)以及空間使用情況Voti ng Disk運(yùn)行情況crsctl query css votedisk Voti ngdisk狀態(tài)查詢(xún)ASM運(yùn)行情況crs_stat -t | grep asm使用情況# su - grid$ asmcmdASMCMD> lsDATA/ORA_DATA/ASMCMD> lsdg ora data數(shù)據(jù)庫(kù)su - oraclesqlplus“/as sysdba ”診斷結(jié)果及建議DBstatus數(shù)據(jù)庫(kù)

10、狀 態(tài)select status from v$in sta nee;配置情況Select * from v$parameter;運(yùn)行情況正常使用資源情況select * from v$resource_limit；ASM使用情況select group nu mber, name,total mb,free mb from v$asm diskgroup;DBfile數(shù)據(jù)文件 狀態(tài)select n ame,status from v$datafile;控制文件 狀態(tài)select status ,n ame from v$con trolfile;日志文件 狀態(tài)select group#,me

11、mbers,archived,status from v$log;表空間使 用率set pagesize 50colu mn "Tablespace" format a13 colu mn "UsedMB" format 99,999,999 colu mn "FreeMB" format 99,999,999 colu mn "Total MB" format 99,999,999 selectfs.tablespace_ name "Tablespace", (df.totalspace -

12、 fs.freespace) "Used MB", fs.freespace "FreeMB", df.totalspace "Total MB",roun d(100 * (fs.freespace / df.totalspace) "Pct Free" from(selecttablespace_ name,rou nd(sum(bytes)/1048576) TotalSpace fromdba_data_filesgroup bytablespace_ name)df,(selecttablespace_

13、name,rou nd(sum(bytes)/1048576) FreeSpace fromdba_free_spacegroup bytablespace_ name)fswhere df.tablespace_ name=fs.tablespace_ name order by "Pct Free"/運(yùn)行情況正常Sessi ons并發(fā)數(shù)select coun t(*) from v$sessi on where status='ACTIVE：Redo使用情況selectb.THREAD#,a.GROUP#,a.STATUS,a.MEMBER,b.BYTES,b.

14、ARCHIVED,b.STATUSfrom v$logfile a,v$log b where a.GROUP#=b.GROUP#;Performa nce配置情況察看數(shù)據(jù) 庫(kù)鎖表鎖表有時(shí)候是瞬間的，長(zhǎng)時(shí)間鎖定的表才可能是死鎖。select l.*, s.OSUSER, s.ACTION, o.OBJECT_NAME from gv$locked_object l, gv_$sessi on s, all_objects o where l.SESSIO ND=s.SID and o.OBJECT ID=l.OBJECT ID死鎖set lin esize 200colu mn oracle_

15、user name for a16 colu mn os_user_ name for a12colu mn object_ name for a30SELECTl.xidus n,l.object_id,l.oracle_user name,l.os_user_ name,cess, l.sessi on _id,s.serial#, l.lo cked_mode,o.object_ name FROM v$locked_object l,dba_objects o,v$sessi on s where l.object_id = o.object_id and s.sid = l

16、.sessi on _id;selectt2.username'|t2.sid'|t2.serial#2.logon_time'|t3.sql_text'litfrom v$locked_object t1,v$session t2,v$sqltext t3where t1.sessi on _id=t2.sidand t2.sql_address=t3.address order by t2.logon _time;enqueueSelecteq_type"lock",total_req# "gets",total_wa

17、it#等待"waits",cum_wait_time from v$e nq ueue_stat where total_wait#>0;等待事件set lin esize 200colu mn user name for a12 colu mn program for a30 colu mn event for a28 colu mn p1text for a15 column p1 for 999,999,999,999,999 selects.username,gram,sw.eve nt,sw.p1text,sw.p1v$sessi on s,v$s

18、essi on _wait sw where s.sid=sw.sid and s.status='ACTIVE' order by sw.p1;fromselect even t,p1 "File #",p2 "Block #",p3 "ReasonCode"fromv$sessi on _waitorder by eve nt;where eve nt = 'buffer busy waits'selectown er,segme nt_n ame,segme nt_type,file_id,blo

19、ck_idfromdba_exte ntswhere file_id = &P1 and &P2 between block_id and block_id + blocks-I,colu mn event for a35;colu mn pltext for a40;select sid,eve nt,p1,p1text from v$sessi on wait order by eve nt;RAC全局等待事 件select* from v$event_name where NAME likeWAIT_CLASS='Cluster：'gc%'and數(shù)

20、據(jù)文件selectfs.n ame n ame,f.phyblkrdpbr,f.phyblkwrtpbw,f.phyblkrd10pyr,f.readtim,f.writetimfrom v$filestat f, v$dbfile fs where f.file# = fs.file# order by 2,3,4 desc;表空間IOselecttablespace_ name,sum(f.phyblkrd)pbr,sum(f.phyblkwrt)pbw,sum(f.phyblkrd) pyr,sum(f.readtim),sum(f.writetim)from v$filestat f,

21、 dba_data_files fswhere f.file# = fs.file_idgroup by tablespace_ nameorder by 2,3,4 desc;長(zhǎng)事務(wù)set lin esize 200colu mn n ame for a16colu mn user name for a10select a.n ame,b.xacts,c.sid,c.serial#,c.username,d.sql_textfromv$roll namea,v$rollstatb,v$sessi onc,v$sqltextd,v$tra nsacti on e where a.us n=b.

22、us n and b.us n=e.XIDUSN and c.taddr=e.addr and c.sql_address=d.ADDRESS and c.sql_hashvalue=d.hash_value order by a.n ame,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,totalwor k)*100 ratio,message from

23、v$sessi on_lon gopswhere 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,totalwor k)*100 ratio,message from v$sessi on_lon gopswhere sofar <> totalwork;where (sofar/totalwork)*100 < 10

24、0;察看用戶(hù)sessi on數(shù)量Select coun t(*) from v$sessi on;如果session 過(guò)多，察看 select * from v$session，察看是什么程序歸檔的生 成頻率set lin esize 120colu mn begi n_time for a26colu mn en d_time for a26selecta.recid,to_char(a.first_time,'yyyy-mm-ddhh24:mi:ss')begi n_time,b.recid,to_char(b.first_time,'yyyy-mm-ddhh24:

25、mi:ss')en d_time,round(b.first_time - a.first_time)*24*60,2) minutesfrom v$log_history a,v$log_history b where 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, 'con siste nt g

26、ets', VALUE, 0) * 100"緩沖區(qū)命中率”FROM V$SYSSTAT;重做日志 命中率SELECT n ame, gets, misses, immediate_gets, immediate_misses,100 - Decode(gets,0,0,misses/(gets+misses)*100 ratiol,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) R