Oracle11gRAC數(shù)據(jù)庫(kù)專(zhuān)項(xiàng)巡檢手冊(cè)

1、ORACLE11g數(shù)據(jù)庫(kù)巡檢張浩數(shù)據(jù)庫(kù)檢測(cè)硬件機(jī)型HPDL580G7是否集群是系統(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|grepora_說(shuō)明Oracle10g后臺(tái)進(jìn)程SMON(SystemMonitor)用于執(zhí)仃歷程恢復(fù)、合并仝1可碎片并釋放臨時(shí)段。PMON(ProcessMonitor)用于監(jiān)視服務(wù)器進(jìn)程的執(zhí)行，摒棄在服務(wù)器進(jìn)程失敗時(shí)清除該服務(wù)器進(jìn)程。DBWR(DatabaseWriter)用于將數(shù)據(jù)庫(kù)緩存的臟緩沖區(qū)數(shù)據(jù)寫(xiě)入到數(shù)據(jù)文件中。LGWR(LogWriter)用于將

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

3、emanagerprocess),負(fù)責(zé)設(shè)置資源計(jì)劃和其他的資源管理的工作DIAG說(shuō)明：數(shù)據(jù)庫(kù)診斷進(jìn)程(Thediagnosibilityprocess),負(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ō)明：processspawner,附于產(chǎn)生oracle進(jìn)程SMCO說(shuō)明：spacemanagementcoordinator,該進(jìn)程負(fù)責(zé)空間管理協(xié)調(diào)管理工作，負(fù)責(zé)執(zhí)行空間的分配和回收。Wnnn說(shuō)明：命名為W000W001,W002.,由smcO動(dòng)態(tài)產(chǎn)生執(zhí)行上述相關(guān)任務(wù)。VKT

4、M說(shuō)明：virtualkeeperoftime,用丁提供wall-clocktime,(每秒鐘更新一次)。提供每一十量秒更新一次的reference-timecounter,看起來(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)離線(xiàn)的時(shí)候，該進(jìn)程負(fù)責(zé)asm的元文件的io讀寫(xiě)。MARK說(shuō)明：如果有向asm離線(xiàn)磁盤(pán)的missed與請(qǐng)求，該進(jìn)程將ASM分配的單元的狀態(tài)標(biāo)記為staleFBDA說(shuō)明：涉及到flashback-data-archive新特性的一個(gè)進(jìn)程，Theflashbackdataarchiverproces。用丁斗哥軌表(trac

5、kedtables)的歷史數(shù)據(jù)進(jìn)行歸檔。當(dāng)軌表”上的事務(wù)提交以后，fbda進(jìn)程負(fù)責(zé)將數(shù)據(jù)的前鏡像保存到flashbackarchive區(qū)域。該進(jìn)程還負(fù)責(zé)flashback的數(shù)據(jù)歸檔的空間管理、分配、保留，跟蹤trackedtransactions。什么是軌表(trackedtables):是指啟用了flashbackarchive特性的表。RMSn說(shuō)明：TheOracleRACmanagementprocesses,負(fù)責(zé)執(zhí)行OracleRAC的管理任務(wù)，比如RACf關(guān)資源的創(chuàng)建和集群中新實(shí)例的添加。DSKM說(shuō)明：Theslavediskmonprocess,負(fù)責(zé)oracle實(shí)例、asm實(shí)例和磁

6、盤(pán)的管理進(jìn)程之間的iofencing信息的交換。如果使用SAGE勺存儲(chǔ)，該進(jìn)程還負(fù)責(zé)SAG用儲(chǔ)的一些信息的管理。RAC進(jìn)程GSDglobalservicesdaemon全局服務(wù)守護(hù)進(jìn)程lockprocess(LCK)鎖管理進(jìn)程DIAG:DIAGNOSABILITYDAEMO陜敗進(jìn)程的診斷信息捕獲進(jìn)程O(píng)peratingSystem-Dependent(OSD)操作系統(tǒng)資源訪(fǎng)問(wèn)進(jìn)程LMS-GobalCacheServiceProcess全局緩存服務(wù)進(jìn)程LMD-GlobalEnqueueServiceDaemon全局查詢(xún)服務(wù)守護(hù)進(jìn)程LMON-GlobalEnqueueServiceMonitor全局

7、查詢(xún)服務(wù)監(jiān)視進(jìn)程LCK0-InstanceEnqueueProcess實(shí)例查詢(xún)進(jìn)程進(jìn)程狀態(tài)Space使用情況df-hListener監(jiān)聽(tīng)狀態(tài)命令：lsnrctlstatus配置正常運(yùn)行情況命令：sqlplus7assysdba”查看日志命令：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)Tnspingtnsname（數(shù)據(jù)庫(kù)實(shí)例名）Alert配置情況標(biāo)準(zhǔn)

8、配置/u01/app/oracle/diag/rdbms/trace/alert_sid.log運(yùn)行情況正常Morealert_sid.logCRS服務(wù)運(yùn)行情況crsctlcheckcrs檢查crs的健康情況資源運(yùn)行情況crs_statt用來(lái)查看RAC中各下點(diǎn)上resources的運(yùn)行狀況Resources的屬性等OCR運(yùn)行情況ocrcheck驗(yàn)證OCR勺狀態(tài)以及空間使用情況VotingDisk運(yùn)行情況crsctlquerycssvotediskVotingdisk狀態(tài)查詢(xún)ASM:運(yùn)行情況crs_stat-t|grepasm使用情況#su-grid$asmcmdASMCMDlsDATA/ORA

9、_DATA/ASMCMDlsdgora_data數(shù)據(jù)庫(kù)su-oraclesqlplus/assysdba”診斷結(jié)果及建議DBstatus數(shù)據(jù)庫(kù)狀態(tài)selectstatusfromv$instance;配置情況Select*fromv$parameter;運(yùn)行情況正常使用資源情況select*fromv$resource_limit;ASM使用情況selectgroup_number,name,total_mb,free_mbfromv$asm_diskgroup;DBfile數(shù)據(jù)文件狀態(tài)selectname,statusfromv$datafile;控制文件狀態(tài)selectstatus,nam

10、efromv$controlfile;日志文件狀態(tài)selectgroup#,members,archived,statusfromv$log;表空間使用率setpagesize50columnTablespaceformata13columnUsedMBformat99,999,999columnFreeMBformat99,999,999columnTotalMBformat99,999,999selectfs.tablespace_nameTablespace,(df.totalspace-fs.freespace)UsedMB,fs.freespaceFreeMB,df.totalspa

11、ceTotalMB,round(100*(fs.freespace/df.totalspace)PctFreefrom(selecttablespace_name,round(sum(bytes)/1048576)TotalSpacefromdba_data_filesgroupbytablespace_name)df,(selecttablespace_name,round(sum(bytes)/1048576)FreeSpacefromdba_free_spacegroupbytablespace_name)fswheredf.tablespace_name=fs.tablespace_n

12、ameorderbyPctFree/:運(yùn)行情況正常Sessionsselectcount(*)fromv$sessionwherestatus=ACTIVE;Redo使用情況selectb.THREAD#,a.GROUP#,a.STATUS,a.MEMBER,b.BYTES,b.ARCHIVED,b.STATUSfromv$logfilea,v$logbwherea.GROUP#=b.GROUP#;Performance:配置情況察看數(shù)據(jù)庫(kù)鎖表鎖表有時(shí)候是瞬間的，長(zhǎng)時(shí)間鎖定的表才可能是死鎖。selectl.*,s.OSUSER,s.ACTION,o.OBJECT_NAMEfromgv$lock

13、ed_objectl,gv_$sessions,all_objectsowherel.SESSION_ID=s.SIDando.OBJECT_ID=l.OBJECT_ID死鎖setlinesize200columnoracle_usernamefora16columnos_user_namefora12columnobject_namefora30SELECTl.xidusn,l.object_id,l.oracle_username,l.os_user_name,cess,l.session_id,s.serial#,l.locked_mode,o.object_nameFROMv

14、$locked_objectl,dba_objectso,v$sessionswherel.object_id=o.object_idands.sid=l.session_id;selectt2.username|t2.sid|t2.serial#|t2.logon_time|t3.sql_textfromv$locked_objectt1,v$sessiont2,v$sqltextt3wheret1.session_id=t2.sidandt2.sql_address=t3.addressorderbyt2.logon_time;enqueue等待Selecteq_typelock,tota

15、l_req#gets,total_wait#waits,cum_wait_timefromv$enqueue_statwheretotal_wait#0;等待事件setlinesize200columnusernamefora12columnprogramfora30columneventfora28columnp1textfora15columnp1for999,999,999,999,999selects.username,gram,sw.event,sw.p1text,sw.p1fromv$sessions,v$session_waitswwheres.sid=sw.sidan

16、ds.status=ACTIVEorderbysw.p1;selectevent,p1File#,p2Block#,p3ReasonCodefromv$session_waitorderbyevent;whereevent=bufferbusywaits;selectowner,segment_name,segment_type,file_id,block_idfromdba_extentswherefile_id=&P1and&P2betweenblock_idandblock_id+blocks-1;columneventfora35;columnp1textfora40;selectsi

17、d,event,p1,p1textfromv$session_waitorderbyevent;RAC全局等待事件select*fromv$event_namewhereNAMElikegc%andWAIT_CLASS=Cluster;數(shù)據(jù)文件IOname,f.phyblkrdpbr,f.phyblkwrtpbw,f.phyblkrdpyr,f.readtim,f.writetimfromv$filestatf,v$dbfilefswheref.file#=fs.file#orderby2,3,4desc;表空間IOselecttablespace_name,sum(

18、f.phyblkrd)pbr,sum(f.phyblkwrt)pbw,sum(f.phyblkrd)pyr,sum(f.readtim),sum(f.writetim)fromv$filestatf,dba_data_filesfswheref.file#=fs.file_idgroupbytablespace_nameorderby2,3,4desc;長(zhǎng)事務(wù),b.xacts,c.sid,c.serial#,c.username,d.sql_textfromv$rolln

19、amea,v$rollstatb,v$sessionc,v$sqltextd,v$transactionewherea.usn=b.usnandb.usn=e.XIDUSNandc.taddr=e.addrandc.sql_address=d.ADDRESSandc.sql_hashvalue=d.hash_,c.sid,d.piece;大事務(wù)selectsid,serial#,to_char(start_time,yyyy-mm-ddhh24:mi:ss)start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork)*100ratio,messagefromv$session_longopswheremessagelike%RMAN%;selectsid,serial#,to_char(start_time,yyyy-mm-ddhh24:mi:ss)start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork)*100ratio,messagefromv$session_longopswheresofart