數(shù)據(jù)庫維護工作手冊

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

數(shù)據(jù)庫維護工作手冊文檔編號：文檔名稱：編寫：審核：批準：批準日期：1概述數(shù)據(jù)庫的日常監(jiān)控是使管理員及時了解系統(tǒng)異常的手段。大部分情況下，系統(tǒng)總是正常運行的。只有對正常情況的充分了解，才能通過對比正常情況發(fā)現(xiàn)異常情況。對于數(shù)據(jù)庫的日常監(jiān)控要有記錄，文字記錄或者電子文檔保存。對于數(shù)據(jù)庫異常進行分析，提出解決方案。日常工作包括監(jiān)控和維護兩個部分。此文檔中關(guān)于數(shù)據(jù)庫的運行命令示例主要針對于ORACLE數(shù)據(jù)庫，但對于SYBASE數(shù)據(jù)庫同樣有參考價值，只要換用相對應的語句即可。數(shù)據(jù)庫監(jiān)控2數(shù)據(jù)庫監(jiān)控數(shù)據(jù)庫監(jiān)控工作內(nèi)容制定和改進監(jiān)控方案，編寫監(jiān)控腳本。對于數(shù)據(jù)庫進行日常監(jiān)測，提交記錄，根據(jù)監(jiān)測結(jié)果進行分析、預測，提交相應的系統(tǒng)改進建議方案。數(shù)據(jù)庫監(jiān)控工作步驟2.1.1查看數(shù)據(jù)庫日志數(shù)據(jù)庫的日志上會有大量對于管理員有用的信息。ORACLE的Alert日志紀錄了數(shù)據(jù)庫系統(tǒng)所報的系統(tǒng)級錯誤信息，以及數(shù)據(jù)塊失效等嚴重錯誤信息。錯誤信息的產(chǎn)生，會產(chǎn)生相應的跟蹤文件，通過查看警告日志和跟蹤文件可查找錯誤原因，對于發(fā)現(xiàn)的問題應及時解決和匯報。如：.表空間是否滿，是否需要進行添加或者擴展。Alert文件中會顯示有表塊無法擴展的提示。.表的塊或者頁面是否損壞。(往往這時alert文件中會顯示ora-600的錯誤。).數(shù)據(jù)庫是否進行了異常操作。(如：droptablespace等等)。實用命令：報警日志文件(alert,log或alrt<SID>.ora)記錄數(shù)據(jù)庫啟動，關(guān)閉和一些重要的出錯信息。數(shù)據(jù)庫管理員應該經(jīng)常檢查這個文件，并對出現(xiàn)的問題作出即使的反應?？梢酝ㄟ^以下SQL找到他的路徑selectvaluefromvSparameterwhereupper(name)BACKGROUND_DUMP_DEST,,或通過參數(shù)文件獲得其路徑，或者showparameterBACKGROUND_DUMP_DEST。后臺跟蹤文件路徑與報警文件路徑一致，記載了系統(tǒng)后臺進程出錯時寫入的信息。用戶跟蹤文件記載了用戶進程出錯時寫入的信息，一般不可能讀懂，可以通過ORACLE的TKPROF工具轉(zhuǎn)化為可以讀懂的格式。用戶跟蹤文件的路徑，你可以通過以下SQL找到他的路徑selectvaluefromv$parameterwhereupper(name)二'USER_DUMP_DEST,或通過參數(shù)文件獲得其路徑，或者showparameterUSER_DUMP_DESTO京以插過設置用戶跟蹤或dump命令來產(chǎn)生用戶跟蹤文件，一般在調(diào)試、優(yōu)化、系統(tǒng)分析中有很大的作用。可在參數(shù)文件種用SQL_TRACE二TRUE打開該文件(對所有用戶)，也可用altersessionsetsql_trace二true打開當前會話，也可用executedbms_system.set_sql_trace_in_session(sid,serial#,true)打開指定會話。2.1.2檢查是否有失效的數(shù)據(jù)庫對象主要關(guān)注索引，觸發(fā)器，存儲過程，函數(shù)等等。如：查找user_objects數(shù)據(jù)字典，看其中是否有狀態(tài)為invalid的對象。判斷失效原因(如：視圖失效的原因有可能是由于創(chuàng)建視圖的基表被刪除等等)，找出原因可進行對象重建或修復。實用命令：Selectobject_name,object_typeFromuser_objectsWhereobject_type=，INVALID*;3查看數(shù)據(jù)庫剩余空間.剩余空間不足時要擴展空間，一般的，當剩余空間小于10%時，要進行空間擴展。對于ORACLE數(shù)據(jù)庫，通過查找TABLESPACES相關(guān)的數(shù)據(jù)字典可以看到有用的信息。.檢查數(shù)據(jù)快速增長的表，通過對于DBA.SEGMENTS數(shù)據(jù)字典的監(jiān)視可以找到，當過快增長時，協(xié)調(diào)開發(fā)人員，確定解決方案。.1.4重點表檢查.檢查系統(tǒng)核心業(yè)務表。因為這些表健康與否與日常業(yè)務的正常運行密切相關(guān)。重點檢查這些表的索引是否失效，表的統(tǒng)W信息是否及時更新，如：當這些表進行了大的數(shù)據(jù)裝載或者刪除操作之后。原則上需要檢查所有的表，只是由于上而這些表更關(guān)鍵，建議管理員給以更多的關(guān)注。.重點檢查數(shù)據(jù)量超過百萬行的表，各地的情況可能不一樣，當數(shù)據(jù)超過百萬行之后，如果索引失效會導致表掃描，占用大量系統(tǒng)10,嚴重影響系統(tǒng)性能。5查看數(shù)據(jù)庫是否正常包括數(shù)據(jù)庫實例是否正常工作、listener是否工作正常，確保數(shù)據(jù)庫系統(tǒng)環(huán)境正常。數(shù)據(jù)庫連接是否正常、檢查是否有超出正常水平的連接數(shù)。如：平常500個，某天下午忽然達到600個。應記錄這種異常情況。分析產(chǎn)生這種情況的原因，如：在低版本的ORACLE中，很可能是一些其他異常的應用出錯后產(chǎn)生的死連接。6死鎖檢查監(jiān)控數(shù)據(jù)庫運行過程中，出現(xiàn)的阻塞，記錄現(xiàn)象，記錄產(chǎn)生阻塞的SQL語句，執(zhí)行的用戶，發(fā)生時間，頻率，處理（殺掉、等待自然解鎖等）。ORACLE版本中的死鎖會在alert文件中產(chǎn)生記錄，oracle會自動解鎖（其實是選擇一個殺掉）。對于死鎖的處理過程要進行記錄。可以使用OEM工具或者查找相關(guān)的V3視圖來確認產(chǎn)生阻塞的語句。1.7監(jiān)控SQL語句的執(zhí)行查找效率低下的SQL語句，聯(lián)系協(xié)調(diào)開發(fā)人員，進行相關(guān)處理?？墒褂肙RACLE提供的AWR進行，也可使用ORACLE提供的OEM工具執(zhí)行，或者自行編制的腳本等等。8操作系統(tǒng)級檢查運行vmstat,sar,topas（AIX系統(tǒng)）,glance（HP系統(tǒng)）等命令檢查CPU、內(nèi)存、虛擬內(nèi)存等的使用情況。運行df,du,iostat檢查磁盤使用情況運行netstat檢查網(wǎng)絡情況運行手工編制的監(jiān)控腳本檢查。針對于操作系統(tǒng)的不同，使用的命令也會有不同，請參考相應的操作系統(tǒng)文檔。建議使用man命令察看相應的幫助信息。9其他每天查看晚間定時執(zhí)行的數(shù)據(jù)庫信息收集作業(yè)和備份作業(yè)的日志輸出，確認都已正常完成。往往不能正常完成是由于如下的原因：請確認腳本是否變動（錯誤的修改造成等等），設備（主機，磁盤陣列，磁帶庫，網(wǎng)絡等等）是否正常，空間是否足夠等等。建議每天按業(yè)務峰值情況，對數(shù)據(jù)庫性能數(shù)據(jù)進行定時采集及分析。3數(shù)據(jù)庫維護數(shù)據(jù)庫維護工作內(nèi)容包括維護、故障診斷、錯誤修復、備份恢復、歷史數(shù)據(jù)遷移等過程。數(shù)據(jù)庫維護工作事項3.1.1頁面修復根據(jù)日常監(jiān)控的結(jié)果，進行頁而（或者數(shù)據(jù)庫壞塊）修復，如將表數(shù)據(jù)導出后重建表，然后導入數(shù)據(jù)。提交修復記錄。1.2數(shù)據(jù)庫對象重建根據(jù)數(shù)據(jù)庫監(jiān)控的結(jié)果，重建失效的對象。如：索引、存儲過程、函數(shù)、視圖、觸發(fā)器等等。實用命令：Alterindex〈索弓I名〉rebuild[online];1.3碎片回收（數(shù)據(jù)重組）當某些數(shù)據(jù)庫運行一段時間后，表會產(chǎn)生碎片，影響數(shù)據(jù)庫的性能。可根據(jù)日常檢查的結(jié)果，運用工具或腳本對于數(shù)據(jù)庫空間進行重組或回收。由于ORACLE數(shù)據(jù)庫本身的原因，在進行了DELETE操作之后也不會使HWM（HighWaterMark高水位線）降低，因此不會釋放所占用的空間，所以建議在進行了數(shù)據(jù)遷移之后將全庫進行EXP,然后進行IMP操作，以釋放占用的空間。1.4刪除不用的數(shù)據(jù)此項工作要得到開發(fā)方、設計人員、以及相關(guān)人員的確認后，方可執(zhí)行。1.5備份恢復需要定期對于數(shù)據(jù)庫備份進行有效性檢測，定期進行數(shù)據(jù)恢復的演練操作。以防止萬一的數(shù)據(jù)庫事故時準備不足。數(shù)據(jù)庫需要采用在線的熱備份，不需要關(guān)閉數(shù)據(jù)庫進行，在備份的同時可以進行正常的數(shù)據(jù)庫的各種操作，滿足了7*24的系統(tǒng)的需要。數(shù)據(jù)庫的備份不能影響用戶對數(shù)據(jù)庫的訪問。目標需要在線熱備份多級增量備份并行備份，恢復減小所需要備份量備份，恢復使用簡單可參考如下的方案：.每月做一個數(shù)據(jù)庫的全備份（包含只讀表空間）.每星期做一次零級備份（不包含只讀表空間）.每個星期三做一次一級備份.每天做一個二級備份.任何表空間改成只讀狀態(tài)后做一個該表空間的備份。.當需要時(如四個小時歸檔文件系統(tǒng)就要接近滿了)備份歸檔文件。1.6歷史數(shù)據(jù)遷移定期進行歷史數(shù)據(jù)遷移，減少生產(chǎn)數(shù)據(jù)庫的壓力。1.7定期修改密碼包括SYS,SYSTEM等用戶。1.8刪除掉不必要的用戶對于系統(tǒng)安裝時的演示用戶，如：hr,scott等。建議每周定期清理和備份一周所產(chǎn)生的Alert日志、跟蹤文件和dump文件。分別位于$ORACLE_BASE/admin/$ORACLE_SID/bdump,$ORACLE_BASE/admin/$ORACLE_SID/udump,$ORACLE_BASE/admin/$ORACLE_SID/cdump,等目錄下。定期對表進行統(tǒng)計分析，(如可使用analyze等命令，8i以上有dbms_stats包來實現(xiàn)，使SQL優(yōu)化器總是能找到最好的查詢策略。制定和執(zhí)行紀錄保證生產(chǎn)庫的安全：應絕對禁止在生產(chǎn)庫上進行開發(fā)、測試。1.9其他針對不同的數(shù)據(jù)庫版本的不同特點進行相應的維護操作。具體情況請參見ORACLE文檔或者訪問metalinko4數(shù)據(jù)庫管理常用SQL腳本常用的SQL腳本，在實施時可供數(shù)據(jù)庫管理員參考，在執(zhí)行時，需要進行相應的修改。.剩余空間檢查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.檢查已艮存在的空間擴展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表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ù)請根據(jù)實際情況進行修改SELECT'alterindex'index_name'rebuild','tablespaceINDEXESstorage(initial256Knext256K);'FROMall-indexesWHERE(tablespace_name!=’INDEXES'ORnext_extent!=(256*1024))ANDowner='&OWNER'.對比兩個實例的不同SELECTobject_name,object_typeFROMuser_objectsMINUSSELECTobject_name,object_typeFROMuser_objects@&my_db_link.查看動態(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ù)庫啟動參數(shù)：showparameterpara,vSparameter提供當前會話信息,v$system_parameter提供當前系統(tǒng)信息。其中isses_modifiable,issys_modifiable表示是否允許動態(tài)修改。.查看近程號：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;.查看歸檔進程select*fromv$archive_processes;.查看正在備份的數(shù)據(jù)文件select*fromv$backup;.查看需要恢復的文件select*fromv$recover_file;.查看所有歸檔E志文件select*fromv$archived_log;.查看恢復時要用到的日志文件select*fromv$recovery_log;.查看SGA的結(jié)構(gòu)Showsga;select*fromv$sgastat;.提取librarycache的命中率selectgethitratiowheredb_block_gets+consistent_gets>0;.查看freelist的競爭selectclass,count,timefromv$waitstatwhereclass='segmentheader*;selectevent,total_waitsfromv$system_eventwhereevent=’bufferbusywaitsf;bufferbusywaits可在兩種情況發(fā)生:ldirtyqueue已滿,2freelist競爭。.查看freelist競爭發(fā)生在哪個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*;.查看表的余計信息analyzetabletable_namecomputestatistics;selectnum_rows,blocks,empty_blocksasempty,avg_space,chain_cnt,avg_row_lenfromdba_tableswhereowner=’HR'andtabie_name='tabie_name';.查看索引的統(tǒng)計信息analyzeindexindex_namevalidatestructure;select(de1_1f_rows_len/1f_rows_len)*100asindex_usagefromindex_stats;5日常維護和問題管理目的作為一名數(shù)據(jù)庫管理員，數(shù)據(jù)庫應用的正常穩(wěn)定運行是保障我們應用系統(tǒng)的重要保證，而數(shù)據(jù)庫的日常監(jiān)控和維護工作至關(guān)重要。做好它的檢查工作的文檔管理也是數(shù)據(jù)庫管理的必不可少的工作之一，這有利于我們追蹤問題和對數(shù)據(jù)庫的版本控制非常重要。例行工作建議每天例行工作：.確定數(shù)據(jù)庫