移動(dòng)ngboss系統(tǒng)數(shù)據(jù)庫(kù)常見(jiàn)故障應(yīng)急手冊(cè)

稱OSOracleGZGZ是OracleEEGZ是OracleEESTST是OracleEEST是OracleEESZSZ是OracleEESZ是OracleEEDGDG是OracleEEDG是OracleEEFSFS是OracleEEFS是OracleEEJMJM是OracleEEJM是OracleEE五.應(yīng)對(duì)緊急六.策略業(yè)務(wù)軟件業(yè)務(wù)是否異常七.啟動(dòng)條八.各場(chǎng)景處理步RAC環(huán)境下進(jìn)行表空間擴(kuò)容（添加數(shù)據(jù)文件）時(shí)，兩個(gè)節(jié)點(diǎn)都沒(méi)有建立軟連接，只OracledatabaseRAC+ASM環(huán)境下，數(shù)據(jù)庫(kù)有歸檔，添加數(shù)據(jù)文件至本地（場(chǎng)景一）1alteralterdatabasedatafile'XXX'2lnln 3recoverrecoverdatafile4、將數(shù)據(jù)文件改為狀alteralterdatabasedatafile'XXX'5（場(chǎng)景二）1alteralterdatabasedatafile'XXX'2lnln 3、通過(guò)ALTERDATABASECREATEDATAFILE'源文件'AS'目標(biāo)文件'; copy數(shù)據(jù)ALTERALTERDATABASECREATEDATAFILE源文件AS目標(biāo)文件4recoverrecoverdatafile目標(biāo)文件5、將數(shù)據(jù)文件改為狀alteralterdatabasedatafile目標(biāo)文件7（場(chǎng)景三）（場(chǎng)景四） SQL>selectfile_name,file_namelike'/oracle%';from/oracle/products/oracle/dbs/WPS_CEIDB_cei_ts_extendedalteralterdatabasedatafile253、使用asmcmdcopy數(shù)據(jù)文件至ASMCMD>cdASMCMD>cd 4、rename5、recoverdatafile <=這個(gè)時(shí)候會(huì)報(bào)錯(cuò)，提示找不到其他節(jié)點(diǎn)的的歸檔文件rcprcp .dbforacle@s1-esop-db-7、recoverdatafile alteralterdatabasedatafile25(場(chǎng)景一)srvctlsrvctlstartservice-d<db_name>[-s<service_name>[-srvctlsrvctlstartservice-dfsdb-ssmm–i當(dāng)上面命令無(wú)法成功,可以使用以下命令進(jìn)行強(qiáng)務(wù)切換alteraltersystemsetSQL>show$lsnrctl$lsnrctl$sqlplusselectselectinstance_number,status,startup_timefromselectselectinst_id,open_modefromalert.log日志CompletedCompletedALTERDATABASEOPEN是否完成srvctlsrvctlstatusservice-dsrvctlsrvctlconfigservice-d<db_name>- columncolumneventformata30columnsessformata20setlinesize250setpagesize0breakonid1skipselectdecode(request,0,'Holder:','Waiter:')||s.inst_id||||s.sid||','||s.serial#--fromgv$lockl,gv$sessionswhere(id1,id2,l.type)in(selectid1,id2,typefromgv$lockwhere)andandorderbyid1,ctimedesc,/Holder不變，則KILL4、Latch應(yīng)急處數(shù)據(jù)庫(kù)大量latch等待，系統(tǒng)資源消耗高，cpu負(fù)載接近手動(dòng)執(zhí)行hang查殺/usr/bin/ksh/usr/bin/ksh 觀察幾分鐘，如果latch等待一直沒(méi)有下降，則執(zhí)行第二步查詢當(dāng)前activestatus='ACTIVE'havingcount(*)>6groupbyusername,machineby、Cachebufferchains數(shù)據(jù)庫(kù)大量cachebufferchains等待，系統(tǒng)資源消耗高，cpuA.低效的SQLcachebufferschains(熱塊爭(zhēng)用鎖存器爭(zhēng)用的最重要原因。B.多個(gè)進(jìn)程同時(shí)掃描大范圍的索引或表時(shí)，可能廣泛cachebufferschains鎖存器C.SQLSQL語(yǔ)句都1.查看latch:cachebufferschains相關(guān)的會(huì)話信息tfromv$sessionwhereevent='latch:cachebufferschains';ora命令oraget_kill_sh&sql_id&username進(jìn)行查殺.sql語(yǔ)句,丟給相應(yīng)的開(kāi)發(fā)商處理selectselectsql_id,count(*)fromv$sessionwhereevent='latch:bufferschains'groupbysql_idorderby、Librarycachelocklibrarycachelock等待，系統(tǒng)資源消耗高，cpuidlelibrarycachelockB、sharedpool1SQLselectselectsql_id,count(*)fromv$sessionwhereevent='librarylock'groupbysql_idorderbyaltersystemflushsharedpool;（該操作需要向直 2sharedpool的altersystemflushsharedpool;（該操作需要向直 、gcbufferbusyCPU較高，IO較忙，處理方法與cachebufferchainsgcbufferbusyRACBOSS是對(duì)業(yè)務(wù)做了分離的，查看latch:cachebufferschains相關(guān)的會(huì)話信息tfromv$sessionwhereevent='gcbufferbusy';ora命令oraget_kill_sh&sql_id&username進(jìn)行查殺.sql語(yǔ)句,丟給相應(yīng)的開(kāi)發(fā)商處理selectselectsql_id,count(*)fromv$sessionwhereevent='gcbuffer'groupbysql_idorderbyshowshowparameterselectcount(*)fromsqlplus/assysdbaKILLLOCAL=NO的會(huì)話，然pspsef|grepLOCAL=NOPID，KILL machine,programorderby3;--找出較多的模塊2lsnrctllsnrctl、IOhang的情況影響因素IOpsps-ef|grepLOCAL=NO|awk'{print$2}'|xargskill-、PGASQLPGA1PGAselectselect/1024fromv$processsetsetlinecolMACHINEforcolPROGRAMfora25colUSERNAMEgram,cess,gram,cess,s.sql_id,fromv$sessions,v$processpwhereandorderby7alteraltersystemkillsessionHANG分析，查找頂級(jí)阻塞的會(huì)話。HANG分析：參考業(yè)支HANGoradebugoradebugsetmypid(oradebugsetospid3188)oradebugunlimitoradebugsetinstoradebug-gdefyzeoradebugoradebugpsps-ef|grepLOCAL=NO|awk'{print$2}'|xargskill-、CPUusr%90ASQLselectevent,count(*),wait_classfromv$sessiongroupevent,wait_classselectevent,count(*),wait_classfromv$sessiongroupevent,wait_classorderby如果有，根據(jù)相關(guān)等待分析問(wèn)題。也可以通過(guò)HANG分析，進(jìn)行阻塞會(huì)話3、當(dāng)CPU出現(xiàn)高負(fù)載的時(shí)候，檢查發(fā)現(xiàn)當(dāng)前數(shù)據(jù)庫(kù)并無(wú)任何異常等待，當(dāng)前活動(dòng)SQL語(yǔ)句與平時(shí)差別很大，我們可以關(guān)閉，檢查是否由于連接造成的。、SYSAUX表空間爆滿沒(méi)有擴(kuò)容應(yīng)急處如果SYSAUX表空間不可用時(shí)，數(shù)據(jù)庫(kù)的功能還是可以繼續(xù)運(yùn)行的。只是一些SYSAUXOEM。SYSAUX中的對(duì)象，是哪個(gè)對(duì)象占用了大量空間.檢查時(shí)業(yè)務(wù)表還是輔助表，確colcolsegment_nameformata30colsegment_typeformata30setlinesize300selectOWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024fromwhereorderbydf備份文件、dump文件、trace文件等大量產(chǎn)生造成例如：查看 的大dudu-sk某個(gè)oracleprocess CPU使用率非常高。某個(gè)oracle MEM使用率非常高SID號(hào)SelectSelectsid,sql_id,event,statusfromv$sessionwherepaddr(selectaddrfromv$processwherespid=&進(jìn)程號(hào)setsetlinecolMACHINEforcolPROGRAMfora25colUSERNAMEgram,cess,s.sql_id,fromv$sessions,v$processpwhereandorderby73 對(duì)相關(guān)進(jìn)程和會(huì)話進(jìn)行分析，決定是否RAC環(huán)境單節(jié)點(diǎn)crash，RAC環(huán)境所有節(jié)點(diǎn)1 數(shù)據(jù)庫(kù)2 3 硬件故遇到實(shí)例crash1、能重新啟動(dòng)的情況下，知會(huì)相關(guān),事后分析重啟原因、“cursor:pinSwaitonX”應(yīng)急處cursor:pinSwaitonXlibrarycachelock2、HighVersionCountssidfromv$sessionwhereevent='cursor:pinSwaitonX';fromv$sessionwhere3IBM重啟相關(guān)節(jié)點(diǎn)，然后將數(shù)據(jù)庫(kù)服務(wù)切換至另1 2 Oraclebug導(dǎo)致立刻通知IBM、SQLSQL2、SQLsetsetlinescolexecsforcolavg_etimefor999,999.999colavg_liofor999,999,999.9colbegin_interval_timefora30colnodeforbreakn_hash_valueonstartup_timeskipselectss.instance_numbernode,nvl(executions_delta,0)execs,(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/(buffer_gets_deltadecode(nvl(buffer_gets_delta,0),0,1,executions_delta))(disk_reads_deltadecode(nvl(buffer_g