數(shù)據(jù)庫(kù)維護(hù)工作手冊(cè)

數(shù)據(jù)庫(kù)維護(hù)工作手冊(cè)Lastrevisiondate:13December2020.

數(shù)據(jù)庫(kù)維護(hù)工作手冊(cè)文檔編號(hào)：文檔名稱：編寫：審核：批準(zhǔn)：批準(zhǔn)日期：1概述數(shù)據(jù)庫(kù)的日常監(jiān)控是使管理員及時(shí)了解系統(tǒng)異常的手段。大部分情況下，系統(tǒng)總是正常運(yùn)行的。只有對(duì)正常情況的充分了解，才能通過(guò)對(duì)比正常情況發(fā)現(xiàn)異常情況。對(duì)于數(shù)據(jù)庫(kù)的日常監(jiān)控要有記錄，文字記錄或者電子文檔保存。對(duì)于數(shù)據(jù)庫(kù)異常進(jìn)行分析，提出解決方案。日常工作包括監(jiān)控和維護(hù)兩個(gè)部分。此文檔中關(guān)于數(shù)據(jù)庫(kù)的運(yùn)行命令示例主要針對(duì)于ORACLE數(shù)據(jù)庫(kù)，但對(duì)于SYBASE數(shù)據(jù)庫(kù)同樣有參考價(jià)值，只要換用相對(duì)應(yīng)的語(yǔ)句即可。數(shù)據(jù)庫(kù)監(jiān)控2數(shù)據(jù)庫(kù)監(jiān)控?cái)?shù)據(jù)庫(kù)監(jiān)控工作內(nèi)容制定和改進(jìn)監(jiān)控方案，編寫監(jiān)控腳本。對(duì)于數(shù)據(jù)庫(kù)進(jìn)行日常監(jiān)測(cè)，提交記錄，根據(jù)監(jiān)測(cè)結(jié)果進(jìn)行分析、預(yù)測(cè)，提交相應(yīng)的系統(tǒng)改進(jìn)建議方案。數(shù)據(jù)庫(kù)監(jiān)控工作步驟2.1.1查看數(shù)據(jù)庫(kù)日志數(shù)據(jù)庫(kù)的日志上會(huì)有大量對(duì)于管理員有用的信息。ORACLE的Alert日志紀(jì)錄了數(shù)據(jù)庫(kù)系統(tǒng)所報(bào)的系統(tǒng)級(jí)錯(cuò)誤信息，以及數(shù)據(jù)塊失效等嚴(yán)重錯(cuò)誤信息。錯(cuò)誤信息的產(chǎn)生，會(huì)產(chǎn)生相應(yīng)的跟蹤文件，通過(guò)查看警告日志和跟蹤文件可查找錯(cuò)誤原因，對(duì)于發(fā)現(xiàn)的問(wèn)題應(yīng)及時(shí)解決和匯報(bào)。如：.表空間是否滿，是否需要進(jìn)行添加或者擴(kuò)展。Alert文件中會(huì)顯示有表塊無(wú)法擴(kuò)展的提示。.表的塊或者頁(yè)面是否損壞。(往往這時(shí)alert文件中會(huì)顯示ora-600的錯(cuò)誤。).數(shù)據(jù)庫(kù)是否進(jìn)行了異常操作。(如：droptablespace等等)。實(shí)用命令：報(bào)警日志文件(alert,log或alrt<SID>.ora)記錄數(shù)據(jù)庫(kù)啟動(dòng)，關(guān)閉和一些重要的出錯(cuò)信息。數(shù)據(jù)庫(kù)管理員應(yīng)該經(jīng)常檢查這個(gè)文件，并對(duì)出現(xiàn)的問(wèn)題作出即使的反應(yīng)?？梢酝ㄟ^(guò)以下SQL找到他的路徑selectvaluefromvSparameterwhereupper(name)BACKGROUND_DUMP_DEST,,或通過(guò)參數(shù)文件獲得其路徑，或者showparameterBACKGROUND_DUMP_DEST。后臺(tái)跟蹤文件路徑與報(bào)警文件路徑一致，記載了系統(tǒng)后臺(tái)進(jìn)程出錯(cuò)時(shí)寫入的信息。用戶跟蹤文件記載了用戶進(jìn)程出錯(cuò)時(shí)寫入的信息，一般不可能讀懂，可以通過(guò)ORACLE的TKPROF工具轉(zhuǎn)化為可以讀懂的格式。用戶跟蹤文件的路徑，你可以通過(guò)以下SQL找到他的路徑selectvaluefromv$parameterwhereupper(name)二'USER_DUMP_DEST,或通過(guò)參數(shù)文件獲得其路徑，或者showparameterUSER_DUMP_DESTO京以插過(guò)設(shè)置用戶跟蹤或dump命令來(lái)產(chǎn)生用戶跟蹤文件，一般在調(diào)試、優(yōu)化、系統(tǒng)分析中有很大的作用?？稍趨?shù)文件種用SQL_TRACE二TRUE打開該文件(對(duì)所有用戶)，也可用altersessionsetsql_trace二true打開當(dāng)前會(huì)話，也可用executedbms_system.set_sql_trace_in_session(sid,serial#,true)打開指定會(huì)話。2.1.2檢查是否有失效的數(shù)據(jù)庫(kù)對(duì)象主要關(guān)注索引，觸發(fā)器，存儲(chǔ)過(guò)程，函數(shù)等等。如：查找user_objects數(shù)據(jù)字典，看其中是否有狀態(tài)為invalid的對(duì)象。判斷失效原因(如：視圖失效的原因有可能是由于創(chuàng)建視圖的基表被刪除等等)，找出原因可進(jìn)行對(duì)象重建或修復(fù)。實(shí)用命令：Selectobject_name,object_typeFromuser_objectsWhereobject_type=，INVALID*;3查看數(shù)據(jù)庫(kù)剩余空間.剩余空間不足時(shí)要擴(kuò)展空間，一般的，當(dāng)剩余空間小于10%時(shí)，要進(jìn)行空間擴(kuò)展。對(duì)于ORACLE數(shù)據(jù)庫(kù)，通過(guò)查找TABLESPACES相關(guān)的數(shù)據(jù)字典可以看到有用的信息。.檢查數(shù)據(jù)快速增長(zhǎng)的表，通過(guò)對(duì)于DBA.SEGMENTS數(shù)據(jù)字典的監(jiān)視可以找到，當(dāng)過(guò)快增長(zhǎng)時(shí)，協(xié)調(diào)開發(fā)人員，確定解決方案。.1.4重點(diǎn)表檢查.檢查系統(tǒng)核心業(yè)務(wù)表。因?yàn)檫@些表健康與否與日常業(yè)務(wù)的正常運(yùn)行密切相關(guān)。重點(diǎn)檢查這些表的索引是否失效，表的統(tǒng)W信息是否及時(shí)更新，如：當(dāng)這些表進(jìn)行了大的數(shù)據(jù)裝載或者刪除操作之后。原則上需要檢查所有的表，只是由于上而這些表更關(guān)鍵，建議管理員給以更多的關(guān)注。.重點(diǎn)檢查數(shù)據(jù)量超過(guò)百萬(wàn)行的表，各地的情況可能不一樣，當(dāng)數(shù)據(jù)超過(guò)百萬(wàn)行之后，如果索引失效會(huì)導(dǎo)致表掃描，占用大量系統(tǒng)10,嚴(yán)重影響系統(tǒng)性能。5查看數(shù)據(jù)庫(kù)是否正常包括數(shù)據(jù)庫(kù)實(shí)例是否正常工作、listener是否工作正常，確保數(shù)據(jù)庫(kù)系統(tǒng)環(huán)境正常。數(shù)據(jù)庫(kù)連接是否正常、檢查是否有超出正常水平的連接數(shù)。如：平常500個(gè)，某天下午忽然達(dá)到600個(gè)。應(yīng)記錄這種異常情況。分析產(chǎn)生這種情況的原因，如：在低版本的ORACLE中，很可能是一些其他異常的應(yīng)用出錯(cuò)后產(chǎn)生的死連接。6死鎖檢查監(jiān)控?cái)?shù)據(jù)庫(kù)運(yùn)行過(guò)程中，出現(xiàn)的阻塞，記錄現(xiàn)象，記錄產(chǎn)生阻塞的SQL語(yǔ)句，執(zhí)行的用戶，發(fā)生時(shí)間，頻率，處理（殺掉、等待自然解鎖等）。ORACLE版本中的死鎖會(huì)在alert文件中產(chǎn)生記錄，oracle會(huì)自動(dòng)解鎖（其實(shí)是選擇一個(gè)殺掉）。對(duì)于死鎖的處理過(guò)程要進(jìn)行記錄?？梢允褂肙EM工具或者查找相關(guān)的V3視圖來(lái)確認(rèn)產(chǎn)生阻塞的語(yǔ)句。1.7監(jiān)控SQL語(yǔ)句的執(zhí)行查找效率低下的SQL語(yǔ)句，聯(lián)系協(xié)調(diào)開發(fā)人員，進(jìn)行相關(guān)處理?？墒褂肙RACLE提供的AWR進(jìn)行，也可使用ORACLE提供的OEM工具執(zhí)行，或者自行編制的腳本等等。8操作系統(tǒng)級(jí)檢查運(yùn)行vmstat,sar,topas（AIX系統(tǒng)）,glance（HP系統(tǒng)）等命令檢查CPU、內(nèi)存、虛擬內(nèi)存等的使用情況。運(yùn)行df,du,iostat檢查磁盤使用情況運(yùn)行netstat檢查網(wǎng)絡(luò)情況運(yùn)行手工編制的監(jiān)控腳本檢查。針對(duì)于操作系統(tǒng)的不同，使用的命令也會(huì)有不同，請(qǐng)參考相應(yīng)的操作系統(tǒng)文檔。建議使用man命令察看相應(yīng)的幫助信息。9其他每天查看晚間定時(shí)執(zhí)行的數(shù)據(jù)庫(kù)信息收集作業(yè)和備份作業(yè)的日志輸出，確認(rèn)都已正常完成。往往不能正常完成是由于如下的原因：請(qǐng)確認(rèn)腳本是否變動(dòng)（錯(cuò)誤的修改造成等等），設(shè)備（主機(jī)，磁盤陣列，磁帶庫(kù)，網(wǎng)絡(luò)等等）是否正常，空間是否足夠等等。建議每天按業(yè)務(wù)峰值情況，對(duì)數(shù)據(jù)庫(kù)性能數(shù)據(jù)進(jìn)行定時(shí)采集及分析。3數(shù)據(jù)庫(kù)維護(hù)數(shù)據(jù)庫(kù)維護(hù)工作內(nèi)容包括維護(hù)、故障診斷、錯(cuò)誤修復(fù)、備份恢復(fù)、歷史數(shù)據(jù)遷移等過(guò)程。數(shù)據(jù)庫(kù)維護(hù)工作事項(xiàng)3.1.1頁(yè)面修復(fù)根據(jù)日常監(jiān)控的結(jié)果，進(jìn)行頁(yè)而（或者數(shù)據(jù)庫(kù)壞塊）修復(fù)，如將表數(shù)據(jù)導(dǎo)出后重建表，然后導(dǎo)入數(shù)據(jù)。提交修復(fù)記錄。1.2數(shù)據(jù)庫(kù)對(duì)象重建根據(jù)數(shù)據(jù)庫(kù)監(jiān)控的結(jié)果，重建失效的對(duì)象。如：索引、存儲(chǔ)過(guò)程、函數(shù)、視圖、觸發(fā)器等等。實(shí)用命令：Alterindex〈索弓I名〉rebuild[online];1.3碎片回收（數(shù)據(jù)重組）當(dāng)某些數(shù)據(jù)庫(kù)運(yùn)行一段時(shí)間后，表會(huì)產(chǎn)生碎片，影響數(shù)據(jù)庫(kù)的性能?？筛鶕?jù)日常檢查的結(jié)果，運(yùn)用工具或腳本對(duì)于數(shù)據(jù)庫(kù)空間進(jìn)行重組或回收。由于ORACLE數(shù)據(jù)庫(kù)本身的原因，在進(jìn)行了DELETE操作之后也不會(huì)使HWM（HighWaterMark高水位線）降低，因此不會(huì)釋放所占用的空間，所以建議在進(jìn)行了數(shù)據(jù)遷移之后將全庫(kù)進(jìn)行EXP,然后進(jìn)行IMP操作，以釋放占用的空間。1.4刪除不用的數(shù)據(jù)此項(xiàng)工作要得到開發(fā)方、設(shè)計(jì)人員、以及相關(guān)人員的確認(rèn)后，方可執(zhí)行。1.5備份恢復(fù)需要定期對(duì)于數(shù)據(jù)庫(kù)備份進(jìn)行有效性檢測(cè)，定期進(jìn)行數(shù)據(jù)恢復(fù)的演練操作。以防止萬(wàn)一的數(shù)據(jù)庫(kù)事故時(shí)準(zhǔn)備不足。數(shù)據(jù)庫(kù)需要采用在線的熱備份，不需要關(guān)閉數(shù)據(jù)庫(kù)進(jìn)行，在備份的同時(shí)可以進(jìn)行正常的數(shù)據(jù)庫(kù)的各種操作，滿足了7*24的系統(tǒng)的需要。數(shù)據(jù)庫(kù)的備份不能影響用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)。目標(biāo)需要在線熱備份多級(jí)增量備份并行備份，恢復(fù)減小所需要備份量備份，恢復(fù)使用簡(jiǎn)單可參考如下的方案：.每月做一個(gè)數(shù)據(jù)庫(kù)的全備份（包含只讀表空間）.每星期做一次零級(jí)備份（不包含只讀表空間）.每個(gè)星期三做一次一級(jí)備份.每天做一個(gè)二級(jí)備份.任何表空間改成只讀狀態(tài)后做一個(gè)該表空間的備份。.當(dāng)需要時(shí)(如四個(gè)小時(shí)歸檔文件系統(tǒng)就要接近滿了)備份歸檔文件。1.6歷史數(shù)據(jù)遷移定期進(jìn)行歷史數(shù)據(jù)遷移，減少生產(chǎn)數(shù)據(jù)庫(kù)的壓力。1.7定期修改密碼包括SYS,SYSTEM等用戶。1.8刪除掉不必要的用戶對(duì)于系統(tǒng)安裝時(shí)的演示用戶，如：hr,scott等。建議每周定期清理和備份一周所產(chǎn)生的Alert日志、跟蹤文件和dump文件。分別位于$ORACLE_BASE/admin/$ORACLE_SID/bdump,$ORACLE_BASE/admin/$ORACLE_SID/udump,$ORACLE_BASE/admin/$ORACLE_SID/cdump,等目錄下。定期對(duì)表進(jìn)行統(tǒng)計(jì)分析，(如可使用analyze等命令，8i以上有dbms_stats包來(lái)實(shí)現(xiàn)，使SQL優(yōu)化器總是能找到最好的查詢策略。制定和執(zhí)行紀(jì)錄保證生產(chǎn)庫(kù)的安全：應(yīng)絕對(duì)禁止在生產(chǎn)庫(kù)上進(jìn)行開發(fā)、測(cè)試。1.9其他針對(duì)不同的數(shù)據(jù)庫(kù)版本的不同特點(diǎn)進(jìn)行相應(yīng)的維護(hù)操作。具體情況請(qǐng)參見(jiàn)ORACLE文檔或者訪問(wèn)metalinko4數(shù)據(jù)庫(kù)管理常用SQL腳本常用的SQL腳本，在實(shí)施時(shí)可供數(shù)據(jù)庫(kù)管理員參考，在執(zhí)行時(shí)，需要進(jìn)行相應(yīng)的修改。.剩余空間檢查SELECTtablespace_name,sum(blocks)asfree_blk,trunc(sum(bytes)/(1024*1024))asfree_m,max(bytes)/(1024)asbig_chunk_k,count(*)asnum_chunksFROMdba_free_spaceGROUPBYtablespace_name.表空間數(shù)據(jù)量情況顯示SELECTtablespace_name,max_blocks,count_blocks,sum_free_blocks,to_char(100*sum_free_blocks/sum_alloc_blocks,'99.99')I!ASpct_freeFROM(SELECTtablespace_name,sum(blocks)ASsum_alloc_blocksFROMdba_data_.filesGROUPBYtablespace_name),(SELECTtablespace_nameASfs_ts_name,max(blocks)ASmax_blocks,count(blocks)AScount_blocks,sum(blocks)ASsum_free_blocksFROMdba_free_spaceGROUPBYtablespace_name)WHEREtablespace_name=fs_ts_name.表和索引分析BEGINdbms_utility.analyze.schema('&0WNER','ESTIMATE',NULL,5);END;.檢查空間情況SELECTa.table_name,a.next_extent,a.tablespace_nameFROMall_tablesa,(SELECTtablespace_name,max(bytes)asbig_chunkFROMdba_free_spaceGROUPBYtablespace_name)fWHEREf.tablespace_name=a.tablespace_nameANDa.next_extent>f.big_chunk.檢查已艮存在的空間擴(kuò)展SELECTcount(*),segment_name,segment_type,dt.tablespace_nameFROMdba_tablespacesdt,dba_extentsdxWHEREdt.tablespace_name=dx.tablespace_nameANDdt.next-extent!=dx.bytesANDdx.owner='&OWNER'GROUPBYsegment_name,segment_type,dt.tablespace_name.檢查沒(méi)有主鍵M表SELECTtable_nameFROMall.tablesWHEREowner='&OWNER'MINUSSELECTtable_nameFROMall_constraintsWHEREowner='&&OWNER'ANDconstraint_type='P'.檢查失效的主鍵SELECTowner,constraint_name,table_name,statusFROMall_constraintsWHEREowner='&OWNER'ANDstatus='DISABLED'ANDconstraint_type=,p,.重建索引，具體參數(shù)請(qǐng)根據(jù)實(shí)際情況進(jìn)行修改SELECT'alterindex'index_name'rebuild','tablespaceINDEXESstorage(initial256Knext256K);'FROMall-indexesWHERE(tablespace_name!=’INDEXES'ORnext_extent!=(256*1024))ANDowner='&OWNER'.對(duì)比兩個(gè)實(shí)例的不同SELECTobject_name,object_typeFROMuser_objectsMINUSSELECTobject_name,object_typeFROMuser_objects@&my_db_link.查看動(dòng)態(tài)性能視圖Select*fromV$FIXED_TABLE.查看約束selecta.constraint_name,a.constraint_type,a.*fromuser_constraintsawheretablename='tablename’;selectconstraint_name,column_namefromuser_cons_columnswheretablename='tablename";.查看索引user_indexes包含索引的名字,user_ind_columns包含索引的列..查看數(shù)據(jù)庫(kù)啟動(dòng)參數(shù)：showparameterpara,vSparameter提供當(dāng)前會(huì)話信息,v$system_parameter提供當(dāng)前系統(tǒng)信息。其中isses_modifiable,issys_modifiable表示是否允許動(dòng)態(tài)修改。.查看近程號(hào)：selectp.spid,s.usernamefromv$processp,v$sessionswherep.addr=s.paddr;.查看數(shù)據(jù)文件：selectname,statusfromv$datafile;select*fromdba_data_files;.查看數(shù)據(jù)文祥狀態(tài)selectd.file#fit,d.name,d.status,h.statusfromv$datafiled,v$datafile_headerhwhered.file#=h.file#;.查看控制文件selectnamefromv$controlfile;selecttype,record_size,records_total,records_usedfromv$controlfile_record_sectionwheretype='DATAFILE';.查看是否歸檔模式：archiveloglistselectname,log_modefromv$database;selectarchiverfromv$instance;.查看日志組：selectgroups,current_group#,sequence#fromv$thread;selectgroup#,sequence#,bytes,members,statusfromv$log;select*fromv$logfile;其中status為空表示正常。.查看largepoolselect*fromv$sgastatwherepool='largepool';.查看歸檔位置showparameterarchiveselectdestination,binding,target,statusfromv$archive_dest;.查看歸檔進(jìn)程select*fromv$archive_processes;.查看正在備份的數(shù)據(jù)文件select*fromv$backup;.查看需要恢復(fù)的文件select*fromv$recover_file;.查看所有歸檔E志文件select*fromv$archived_log;.查看恢復(fù)時(shí)要用到的日志文件select*fromv$recovery_log;.查看SGA的結(jié)構(gòu)Showsga;select*fromv$sgastat;.提取librarycache的命中率selectgethitratiowheredb_block_gets+consistent_gets>0;.查看freelist的競(jìng)爭(zhēng)selectclass,count,timefromv$waitstatwhereclass='segmentheader*;selectevent,total_waitsfromv$system_eventwhereevent=’bufferbusywaitsf;bufferbusywaits可在兩種情況發(fā)生:ldirtyqueue已滿,2freelist競(jìng)爭(zhēng)。.查看freelist競(jìng)爭(zhēng)發(fā)生在哪個(gè)segment上selects.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.statefromdba_segmentss,v$session_waitwwherew.event='bufferbusywaits'andw.pl=s.header_fileandw.p2=s.header_block;.查看全表掃描發(fā)生的次數(shù)selectname,valuefromv$sysstatwherenamelike飛tablescan%*;.查看大操作的執(zhí)行情況selectsid,serial#,opname,to-char(start_time, 'HH24:MISS')asstart_t,(sofar/totalwork)*100aspercent_completefromv$session_longops;.查看數(shù)據(jù)文件的I/Oselectphyrds,phywrts,d.namefromv$datafiled,v$filestatfwhered.file#=f.file#orderbyd.name;.查看空閑塊數(shù)少于10%的segment(blocks在high-watermark以下，empty_blocks其上)selectowner,table_name,blocks,empty_blocksfromdba_tableswhereempty_blocks/(blocks+empty_blocks)<0.1andblocks+empty_blocks!=0;.查看migration和chaininganalyzetabletable_namecomputestatistics;selectnum_rows,chain_cntfromdba_tableswheretable_name="table-name*;.查看表的余計(jì)信息analyzetabletable_namecomputestatistics;selectnum_rows,blocks,empty_blocksasempty,avg_space,chain_cnt,avg_row_lenfromdba_tableswhereowner=’HR'andtabie_name='tabie_name';.查看索引的統(tǒng)計(jì)信息analyzeindexindex_namevalidatestructure;select(de1_1f_rows_len/1f_rows_len)*100asindex_usagefromindex_stats;5日常維護(hù)和問(wèn)題管理目的作為一名數(shù)據(jù)庫(kù)管理員，數(shù)據(jù)庫(kù)應(yīng)用的正常穩(wěn)定運(yùn)行是保障我們應(yīng)用系統(tǒng)的重要保證，而數(shù)據(jù)庫(kù)的日常監(jiān)控和維護(hù)工作至關(guān)重要。做好它的檢查工作的文檔管理也是數(shù)據(jù)庫(kù)管理的必不可少的工作之一，這有利于我們追蹤問(wèn)題和對(duì)數(shù)據(jù)庫(kù)的版本控制非常重要。例行工作建議每天例行工作：.確定數(shù)據(jù)庫(kù)