數(shù)據(jù)庫的運(yùn)維分享

眾所周知，數(shù)據(jù)庫的運(yùn)維既是個(gè)技術(shù)活兒也是個(gè)苦差事，不僅要有廣闊的知識面，強(qiáng)大的技術(shù)能力，主機(jī)、存儲(chǔ)、網(wǎng)絡(luò)、操作系統(tǒng)最好樣樣精通，而且要會(huì)寫SQL、shell、最好也會(huì)Java…同時(shí)，還需要擁有超強(qiáng)的耐心、謹(jǐn)慎的態(tài)度以及強(qiáng)健的體魄。首先跟大家分享下我在運(yùn)維過程二個(gè)案例1、我的第一個(gè)運(yùn)維工具：ora2008年剛進(jìn)公司轉(zhuǎn)做專職DBA,發(fā)現(xiàn)DBA竟然比以前干程序員還苦逼，通宵施工如家常便飯，而且有大量的重復(fù)工作。當(dāng)時(shí)每個(gè)dba在共享服務(wù)器上都有自己的腳本集，每當(dāng)應(yīng)用側(cè)有任何異動(dòng)DBA們就找到自己的腳本集文件，然后替換條件復(fù)制粘貼執(zhí)行，遇到?jīng)]找到的就一頓狂敲鍵盤輸SQL。特別是在遇到大故障時(shí)，身后便會(huì)圍著一群人，有各方領(lǐng)導(dǎo)，還有開發(fā)商，里外好幾層。那可真是令人抓狂，因?yàn)樽鲞^幾年的開發(fā)，我便想，為何不做一個(gè)shell程序，統(tǒng)一的入口，只要傳入?yún)?shù)即可。于是我開發(fā)了我的第一款簡單的Oracle運(yùn)維工具，當(dāng)時(shí)腳本集就叫ora。這個(gè)工具后來在運(yùn)維團(tuán)隊(duì)不斷被完善、擴(kuò)散。至今該工具還在使用。

h匚costvalueOra腳本集的優(yōu)點(diǎn):Dartitiontablecolumnenq：1ah匚costvalueOra腳本集的優(yōu)點(diǎn):Dartitiontablecolumnenq：1a一row1ockcont已rLtionmtdTiDationparttabownertabnametKlockinfowait,sidallwaiteventsessianinfoof-：:：ogtnnrethancostvalueSL--LoginasOSUs已thighp-ara--gethightpararlLeLwduleactive--GetActiveSessionsizetabnaireLownerj--GetSizeoftables/indexesidxdes匚tabnameowner~DisplayindeistructuretsfreeLtsnameJ--GetIat：leapac：已UmaLgimtablespacetsnams--TablsspacaLnfarirstiontsnaine--ListdatafilesbytablespacesqltextSQLID一一GetSQLTextbyhashvalueplanJ.LIDG=ttxzcuie1'ls.nbvUUL11'lockL二二--Get1匚ickinfurmationbysidlockwait--Getlockrequestor/blockerik-1patternL口wner」--GetobjectbytiairepatterntablikepatternLowner」=GettablEbynamepatterntstattabnaRieowner--GettablestatisticsistattabnaEie□wnar--GetindeKstatisticsipstatIndnaireowner--Getindeicpartitionstatistics口bjsql□bjnameorner一一GetSQLsbyobje匚tnamelongops=Getlongrunquerytran--GetallthetransactinngdependobjnaKfiLowner」--Getdependencyinformation1吐七匚hlaiuh*一一Get1atuhnarnEbylatchidluld□bjectid-Yhohavelocfcangi^enobject?sort--￥huisrurmlngsortoperatian?dssctabnaRisLowner」--DescribeTabl0StructuresegirentLsizeJ--SegircntlargethangivensleaseqLikepatternLowne匸」=Getsequencebynamepeitt已匸口tabpiarttabnaRELowner」--Listtablepartition呂viewpattarnLowner」--Listviewbynanspattsrnparampattern--ListOracleparajreters_parampattern__ListOraclehiddenparametersgrant□bjnaEieLowner」--Get§r-mtinfDrnationunusab1已Lower」--Listunusisbl已indeK巳呂invalidLawner」--Listinvalidobjects□sure getthecreateobjectgqlListalldxLockwait.?讓日常監(jiān)控、維護(hù)操作等標(biāo)準(zhǔn)化。?減少出錯(cuò)機(jī)會(huì)，提高效率。?讓DBA從容應(yīng)對故障應(yīng)急。當(dāng)然缺點(diǎn)也是明顯的，正是有了這個(gè)工具，現(xiàn)在很多DBA們到了非駐場的服務(wù)現(xiàn)場就不會(huì)寫SQL了。（怪我嘍…）2、智能HANG分析在運(yùn)維期間碰到系統(tǒng)常發(fā)生HANG,當(dāng)數(shù)據(jù)庫發(fā)生在爭奪內(nèi)核級別的資源時(shí)，比如Latch等，在11G之前oracle不能自動(dòng)的檢測并處理這種死鎖。這時(shí)候需用Hanganalyze工具dump資源持有的相互關(guān)系。而這時(shí)候當(dāng)二線DBA到場時(shí)已基本Hang死，或無法登陸，即使能做出dumptrace也無法反映真實(shí)原因。另外分析trace定位堵塞源也要一定時(shí)間。所以分析出結(jié)果時(shí)往往應(yīng)用已中斷。既然hang住后要重啟或終止掉所有前臺發(fā)起數(shù)據(jù)庫進(jìn)程才能解決，何不在hang開始初期就發(fā)起自動(dòng)hang分析，識別引起hang的源頭，記錄相關(guān)信息，終止源頭。具體過程如下：1?通過等待事件識別Hang癥狀2?根據(jù)上一步驟判斷觸發(fā)搜集hanganalyze3.分析hang的dump信息，并確認(rèn)是否存在hang。4?識別hang的源頭記錄相關(guān)信息并解決hang問題。這是我編寫的第二個(gè)程序（由于該程序已申請了專利，代碼在此就不分享了）。注：在Oracle11g11.2.0.2版本發(fā)布后，在其新特性中才出現(xiàn)了hang管理器（HangManager），HM配置參數(shù)（開啟后會(huì)根據(jù)配置終止實(shí)例或進(jìn)程，請謹(jǐn)慎使用）：_hang_resolution=TRUE或者FALSE。這個(gè)參數(shù)用于控制HM是否解決hang。_hang_resoluti_scope=OFF,PORCESS或者INSTANCE。這個(gè)參數(shù)用于控制HM解決問題的范圍。_hang_detection二〈number〉。HM檢測hang的時(shí)間間隔，默認(rèn)值為30（秒）。小結(jié)后面只要碰到能重復(fù)的、具有一定規(guī)則的工程,如長事務(wù)分析、二階段事務(wù)（DX鎖）分析、自動(dòng)生命周期管理、自動(dòng)優(yōu)化調(diào)度、巡檢工具、離線巡檢工具等等。如果你能把你日常需要做的工具化或自動(dòng)化了，DBA還是苦差活么？那么就有更多時(shí)間研究更深層次技術(shù)。我只是一個(gè)不安分的、會(huì)寫程序的、“會(huì)偷懶”的DBA。二、OraZ之路至此越來越想做一個(gè)較為完整能幫助DBA的工具，該工具將運(yùn)行SQL查詢視圖監(jiān)控?cái)?shù)據(jù)庫的性能，識別數(shù)據(jù)庫存在隱患。在數(shù)據(jù)庫的運(yùn)維工作包括部署安裝、性能優(yōu)化、備份容災(zāi)、故障恢復(fù)、預(yù)防性巡檢等工作，而這幾個(gè)方面都存在不少重復(fù)度高、工作量大的任務(wù)，有的甚至還可以并行處理，這些都是該工具需解決目標(biāo)。1、 運(yùn)行需求?Oraz是基于JDBC+SSH的JAVA應(yīng)用，監(jiān)測和分析數(shù)據(jù)庫實(shí)例活動(dòng)，系統(tǒng)要求是相當(dāng)簡單，只需jdbc能連接上數(shù)據(jù)庫即可，該工具不會(huì)安裝任何額外軟件在你的服務(wù)器和終端上。2、 Oraz目前能做什么?有關(guān)數(shù)據(jù)庫和實(shí)例的一般信息。?有關(guān)數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)存儲(chǔ)的詳細(xì)信息：表空間，數(shù)據(jù)庫文件重做日志、歸檔的日志等。表空間/數(shù)據(jù)文件使用情況和可用空間內(nèi)存信息：SGA/PGA組件和大小，共享的池和緩沖區(qū)緩存統(tǒng)計(jì)數(shù)據(jù)。?實(shí)例活動(dòng)洞察-CPU消耗、等待事件、頂級的會(huì)話、頂級SQL語句等。會(huì)話信息-活動(dòng)會(huì)話，排在前面的會(huì)話等。頂級的SQL語句和有關(guān)每個(gè)語句包括語句活動(dòng)、執(zhí)行統(tǒng)計(jì)信息、資源消耗、執(zhí)行計(jì)劃、版本等詳細(xì)的信息。Oracle數(shù)據(jù)庫全系統(tǒng)統(tǒng)計(jì)信息、操作系統(tǒng)統(tǒng)計(jì)、指標(biāo)和時(shí)間模型。3、 DBA日常運(yùn)維之巡檢規(guī)避系統(tǒng)風(fēng)險(xiǎn)運(yùn)維自動(dòng)化體系形成之前，我們DBA的日常例行工作在總工作量中占比較高，很消耗人力，員工疲于奔命但工作效率不高，也很容易出差錯(cuò)。自動(dòng)化平臺把我們的員工從繁瑣的常規(guī)工作中解放出來，更專注于做架構(gòu)優(yōu)化類有創(chuàng)造性的工作，效率也有了進(jìn)一步的改善.每日檢查是工程師上班的第一件事，通過腳本來進(jìn)行，腳本輸出僅提示異常部分，檢查內(nèi)容例如：數(shù)據(jù)庫檢查內(nèi)容：數(shù)據(jù)庫/系統(tǒng)是否處于歸檔模式數(shù)據(jù)文件是否有offline或處于備份狀態(tài)文件系統(tǒng)使用情況（90%為告警閥值）alert_SID.log文件是否有錯(cuò)誤或告警備份是否正常表空間使用情況（90%為告警閥值）數(shù)據(jù)庫/系統(tǒng)對象的存儲(chǔ)參數(shù)設(shè)置檢查是否有失效的索引檢查是否有無效的對象檢查有無運(yùn)行失敗的JOB檢查回滾段使用情況數(shù)據(jù)庫/系統(tǒng)用戶情況等，編寫對應(yīng)查詢SQL,再通過JDBC訪問遠(yuǎn)程服務(wù)器獲取該值進(jìn)行判斷，如分析指定用戶下是否存在失效主鍵：SELECTowner,constraint_name,table_name,statusFROMall_constraintsWHEREowner='&OWNER'ANDstatus='DISABLED'ANDconstraint_type=P;建立一系列巡檢規(guī)則，實(shí)現(xiàn)如360式的一鍵體檢方式：?共檢查了1臺ORACLE進(jìn)狷庫，詬査看：03xi&^垂引信息其他O^CLEW?如庫信息圖列出體檢結(jié)果，黃色需dba關(guān)注的，為不匹配預(yù)定檢查項(xiàng)的，點(diǎn)擊對應(yīng)圖標(biāo)，可以看到DBA+P -X*?>c已經(jīng)對［臺數(shù)據(jù)庫完成讖檢！返回 /邂檢克成！▼共槍査了1諭堀障，以下1合族論庫有問題，清沓若:具體體檢結(jié)果。通過該體檢功能可快速檢測數(shù)據(jù)庫問題；目前該巡檢暫不支持自定義，如需可以考慮建立可分享的自定義巡檢項(xiàng)，這視大家的反饋情況而定。4、實(shí)例活動(dòng)洞察實(shí)例活動(dòng)洞察分析功能當(dāng)前已同步發(fā)布更新，在很多情況下，當(dāng)數(shù)據(jù)庫發(fā)生性能問題的時(shí)候，我們是來不及收集足夠的診斷信息，或者收到告警，甚至問題發(fā)生的時(shí)候DBA根本不在場。這給我們診斷問題帶來很大的困難。那么在這種情況下，我們是否能在事后收集一些信息來分析問題的原因呢首先我們看看Oracle重器oem，而TopActivity功能是使用最為頻繁的功能點(diǎn):3.67.96負(fù)載!.12SS532425DetailforSelected5MinuteInterval]￡0L丫加AciMryJ%5bh1umdp41hrajSELECTr 『；；Idd^afipieCaml1.Sfi?HUlTiEiL口」D6CThir：ClwniEi曲L沁M(jìn)■址肚皿□Sch亡dul燈?aSlbflSLNefcWfark■酯仃価巧U菽曲Qoufinur也加一■中血匹—AepJk冊顧C(jī)vnturi?nLyLfCL12.14弘鴨頃計(jì)畐兇5PUSCLEXECUTEVIex*|TopSessinns二Sess]??h (TM￡V1-■M181176則哺合qiwdan PLTSQLEI€CUTEjecti73flhwhmrttflS￡LECi50>/5飾2^1用1_1詔SELECT(O I怕隨4也匹呷SELECTQ —工|煌4申山旳01SELECTADEVSRVgelcuitsnllwg@矽堆侶&?(TNSVl-vajYKLJhlA^BWd川036WUPEDDAN及sqlplu：^.h魅TopActivityDragtheshadedba対1口change1即mEimeperiodfarthed<e1ailseelionhelm.°m%.C75.10 5:15 520 525 6.30 5.35 5.40 5.^5 5.50 5:55 缸0 5.05MOv12.2W”聞:SQLTimlMQAd旳叩「「E溟LplTi」葉閃？yj|MLH>Dhgn時(shí)泅：卩卄SELECT也門乩I蚯;SELECTSMiCarfaiaselect；|B^triign詢[iic刖曲_a■皿聯(lián)S79hj847對于分析指定時(shí)段內(nèi)的頂級消耗、會(huì)話等一目了然。上圖中負(fù)載均以AverageActiveSessions(AAS)平均活動(dòng)會(huì)話進(jìn)行計(jì)算。每一個(gè)會(huì)話執(zhí)行過程如下：Query1,Query1,jL.Qua72而每一個(gè)語句在執(zhí)行過程又可以分解為不同活動(dòng)時(shí)間：CPU執(zhí)行中、等待IO或其它資源中,即可分為CPU、IO、WaitDatabaseDatabaseCPUI10Wait當(dāng)有多個(gè)會(huì)話連接到庫，并活動(dòng)時(shí)：

時(shí)間210:C5|jWaitingIIReading(I/O)口Working(CPU)OJdl&Se^^joti2Session2Session時(shí)間210:C5|jWaitingIIReading(I/O)口Working(CPU)OJdl&Se^^joti2Session2Session410:15SnapshotTimes10:10UH14AaJNW* ?h*??1i?M**f ■*?**! ■■*■**■ ■■*1^AiMM-HI■■'TP'fhFWV"?■????ftBfiMMgiAuHwa*.saidurtMfA>r?^gIeHvRnr^ Rjjmf;酈區(qū)華1遇塾蚤半護(hù)基命圈⑥矗半C前圏怡會(huì)F圏l*rFin.*.Eii?4n^Mia h?w■■*4^Ldit（dis?j*基丸辦紀(jì)％e晝先譏c悅怡區(qū)i^c呪憶通過時(shí)間片段來看同一時(shí)刻有多少會(huì)話處于活動(dòng)狀態(tài)，而該值為AAS值，以相同方法以sql語句維度統(tǒng)計(jì)該時(shí)刻活動(dòng)，則找出頂級活動(dòng)SQL,同樣可以計(jì)算頂級活動(dòng)program、user、會(huì)話等待。。由于DBTime-某一時(shí)段時(shí)間總和，故頂級活動(dòng)SQL即為TOPSQL，所以AAS=DBTime/elapsedtime（歷時(shí)），之所以該指標(biāo)叫做黃金指標(biāo)，通過AAS指標(biāo)可以衡量一個(gè)系統(tǒng)的繁忙程度，這里有個(gè)CPU時(shí)間片概念，每一個(gè)CPU時(shí)間由操作系統(tǒng)分成CPU時(shí)間片，然后CPU時(shí)間片輪詢模式分配給線程或進(jìn)程（視操作新系統(tǒng)而定），在最小單位CPU片段內(nèi)整個(gè)系統(tǒng)允許的最大允許數(shù)為cpu個(gè)數(shù)，故通過比較AAS值與CPU之間可以衡量數(shù)據(jù)庫繁忙度，與CPU數(shù)量關(guān)聯(lián)分析：AAS/CPU_Count?=0非?？臻eAAS/CPU_Countv=0.5沒堵塞AAS/CPU_Count<1部分進(jìn)程已達(dá)100%，應(yīng)用開始出現(xiàn)緩慢AAS/CPU_Count>或>>1出現(xiàn)性能問題或堵死、HANG狀態(tài)AAS在Oracle中OEM、ASH中的應(yīng)用：OEM中:ActiveSessionsIMajiimum亡尸UZ7.0S.IJ5.0ActiveSessionsIMajiimum亡尸UZ7.0S.IJ5.0402Q2010QQ1：10pm 1：2Qprn 1-^Opm lYQprn -l-SiJpfli HGOpmi-S3UserIK>SystemI/O河Schsdjler◎Uh酣rjutworkC&nligur^rtji&nConcurrencyGqeti：.母ppfEtkiiiA-drmi^rdti'eCPUUsedSOL尺弓背pwnw》Tim?%Barelinei爭'■empty.j-0.75HiighAvailabilityInstance1RigcoveryTime(eqdJi56L?islBackup n怙UssbleFlashRecovefyArsa(%) 100尸l^^lhb^ck pi導(dǎo)ASH：CPUsSGASizeBufferCacheSharedPoolASHBufferSize304293.595M(100%}165.376M(56.3%)27.739M(9.4%)H(%}SampleTimeDataSourceAnalysisBeginTime:07-Jun-1417:10:00□BAHISTACTIVESESSHISTORYinAWRsnapshot17358AnalysisEndTime:O7-Jun-1417:30:00□BA_HIST_ACTIVE_SESS_HISTORYinAWRsnapshot17353+V$ACTIVESESSI0NHISTORYElapsedTime:20.0(mins}SampleCount:69,076AverageActiveSessions:575.63Avg.ActiveSessionperCPU:1.50ReportTarget:NonEspecified

TopUserEventsEventEventClass%EventAvgActivelibrarycachelockConcurrency&4.41313.19cursor:pinSwaitonXConcurrency11.6166.86librarycache;mutexXConcurrency10.175S.53latchfreeOther6.4437.07CPU+WaitforCPUCPU4.53^6.10從Oracle數(shù)據(jù)庫10g開始增加V$ACTIVE_SESSION_HISTORY視圖，通過它可以容易地得知當(dāng)前Instance的活動(dòng)狀態(tài)，主要是各個(gè)時(shí)刻系統(tǒng)都在等待哪些事件，通過對這些等待事件和相應(yīng)等待次數(shù)的統(tǒng)計(jì)，就可以清晰地了解系統(tǒng)的歷史工作負(fù)載特征和壓力情況。此視圖提供了大量寶貴的信息，而且不需要繁重的跟蹤活動(dòng)ASH數(shù)據(jù)采集有mmon進(jìn)程與mmnl進(jìn)程負(fù)責(zé)：快照由MMON和MMNL后臺進(jìn)程自動(dòng)地每隔固定時(shí)間采樣一次。MMON進(jìn)程負(fù)責(zé)：當(dāng)某個(gè)測量值(metrics)超過了預(yù)設(shè)的限定值(thresholdvalue)后提交警告創(chuàng)建新的MMON隸屬進(jìn)程(MMONslaveprocess)來進(jìn)行快照(snapshot)捕獲最近修改過的SQL對象的統(tǒng)計(jì)信息MMNL進(jìn)程負(fù)責(zé)執(zhí)行輕量級的且頻率較高的后臺任務(wù)，如捕獲會(huì)話歷史信息，測量值計(jì)算等。AWR的采樣工作由MMON進(jìn)程每個(gè)1小時(shí)執(zhí)行一次,ASH信息同樣會(huì)被采樣寫出到AWR負(fù)載庫中。ASHbuffer根據(jù)被設(shè)計(jì)為保留1小時(shí)的信息，但很多時(shí)候這個(gè)內(nèi)存是不夠的，當(dāng)ASHbuffer寫滿后，另外一個(gè)后臺進(jìn)程MMNL將會(huì)主動(dòng)將ASH信息寫出。ASHbuffer大小-按照公式SizeofASHCircularBuffer=Max[Min[#CPUs*2MB,5%ofSharedPoolSize,30MB],1MB]計(jì)算，默認(rèn)1M左右，該參數(shù)可以同隱含參數(shù)進(jìn)行調(diào)整："_ash_size"隱含參數(shù)控制ashbuffer的大小ASH對應(yīng)視圖關(guān)系為:通過按分鐘從v$active_session_history視圖采集數(shù)據(jù)，展示如下:T192160111143箋T192160111143箋.F會(huì)話?1&QLID<w54b7用口svs詛L襄主SELECT19.5yz^sSudb^^fxqx-SYSSELECT9_43AvatZrfMgvt^Wvat2dlORACLE143SELECT■5?Q