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

1、例行工作手冊數(shù)據(jù)庫維護工作手冊文檔編號：文檔名稱:編寫：審核:批準：批準日期:21/21目錄1 概述42 數(shù)據(jù)庫監(jiān)控42.1 數(shù)據(jù)庫監(jiān)控工作內(nèi)容42.2 數(shù)據(jù)庫監(jiān)控工作步驟42.2.1 查看數(shù)據(jù)庫日志42.2.2 檢查是否有失效白數(shù)據(jù)庫對象52.2.3 查看數(shù)據(jù)庫剩余空間52.2.4 重點表檢查52.2.5 查看數(shù)據(jù)庫是否正常52.2.6 死鎖檢查62.2.7 監(jiān)控SQL語句的執(zhí)行62.2.8 操作系統(tǒng)級檢查62.2.9 其他63 數(shù)據(jù)庫維護63.1 數(shù)據(jù)庫維護工作內(nèi)容63.2 數(shù)據(jù)庫維護工作事項63.2.1 頁面修復63.2.2 數(shù)據(jù)庫對象重建73.2.3 碎片回收（數(shù)據(jù)重組）73.2.4

2、刪除不用的數(shù)據(jù)73.2.5 備份恢復73.2.6 歷史數(shù)據(jù)遷移73.2.7 定期修改密碼83.2.8 刪除掉不必要的用戶83.2.9 其他84 數(shù)據(jù)庫管理常用SQL腳本85 日常維護和問題管理175.1 目的175.2 例行工作建議175.3 相關(guān)填表說明171概述數(shù)據(jù)庫的日常監(jiān)控是使管理員及時了解系統(tǒng)異常的手段。大部分情況下，系統(tǒng)總是正常運行的。只有對正常情況的充分了解，才能通過對比正常情況發(fā)現(xiàn)異常情況。對于數(shù)據(jù)庫的日常監(jiān)控要有記錄，文字記錄或者電子文檔保存。對于數(shù)據(jù)庫異常進行分析，提出解決方案。日常工作包括監(jiān)控和維護兩個部分。此文檔中關(guān)于數(shù)據(jù)庫的運行命令示例主要針對于ORACL散據(jù)庫，但對

3、于SYBAS嗷據(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)的問題應及時解決和匯報。如:1 .表空間是否滿，是否需要進行添加或者擴展。Alert文件中會顯示有表塊無

4、法擴展的提示。2 .表的塊或者頁面是否損壞。(往往這時alert文件中會顯示ora-600的錯誤。)3 .數(shù)據(jù)庫是否進行了異常操作。(如：droptablespace等等)。實用命令： 報警日志文件(alert.log或alrt<SID>.ora)記錄數(shù)據(jù)庫啟動，關(guān)閉和一些重要的出錯信息。數(shù)據(jù)庫管理員應該經(jīng)常檢查這個文件，并對出現(xiàn)的問題作出即使的反應?？梢酝ㄟ^以下SQL找到他的路徑selectvaluefromv$parameterwhereupper(name)='BACKGROUND_DUMP_DEST',或通過參數(shù)文件獲得其路徑，或者showparameter

5、BACKGROUND_DUMP_DEST。 后臺跟蹤文件路徑與報警文件路徑一致，記載了系統(tǒng)后臺進程出錯時寫入的信息。 用戶跟蹤文件記載了用戶進程出錯時寫入的信息，一般不可能讀懂，可以通過ORACLE的TKPROF工具轉(zhuǎn)化為可以讀懂的格式。用戶跟蹤文件的路徑，你可以通過以下SQL找到他的路徑selectvaluefromv$parameterwhereupper(name)='USER_DUMP_DEST',或通過參數(shù)文件獲得其路徑，或者showparameterUSER_DUMP_DEST。可以通過設置用戶跟蹤或dump命令來產(chǎn)生用戶跟蹤文件，一般在調(diào)試、優(yōu)化、系統(tǒng)分析中有很大

6、的作用?？稍趨?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_n

7、ame,object_typeFromuser_objects'INVALID'Whereobject_type=2.1.3 查看數(shù)據(jù)庫剩余空間1 .剩余空間不足時要擴展空間，一般的，當剩余空間小于10%時，要進行空間擴展。對于ORACLE數(shù)據(jù)庫，通過查找TABLESPACES相關(guān)的數(shù)據(jù)字典可以看到有用的信息。2 .檢查數(shù)據(jù)快速增長的表，通過對于DBA_SEGMENTS數(shù)據(jù)字典的監(jiān)視可以找到，當過快增長時，協(xié)調(diào)開發(fā)人員，確定解決方案。2.1.4重點表檢查1.檢查系統(tǒng)核心業(yè)務表。因為這些表健康與否與日常業(yè)務的正常運行密切相關(guān)。重點檢查這些表的索引是否失效，表的統(tǒng)計信息是否及時更新

8、，如：當這些表進行了大的數(shù)據(jù)裝載或者刪除操作之后。原則上需要檢查所有的表，只是由于上面這些表更關(guān)鍵，建議管理員給以更多的關(guān)注。2.重點檢查數(shù)據(jù)量超過百萬行的表，各地的情況可能不一樣，當數(shù)據(jù)超過百萬行之后，如果索引失效會導致表掃描，占用大量系統(tǒng)IO,嚴重影響系統(tǒng)性能。2.1.5 查看數(shù)據(jù)庫是否正常包括數(shù)據(jù)庫實例是否正常工作、listener是否工作正常，確保數(shù)據(jù)庫系統(tǒng)環(huán)境正常。數(shù)據(jù)庫連接是否正常、檢查是否有超出正常水平的連接數(shù)。如：平常500個，某天下午忽然達到600個。應記錄這種異常情況。分析產(chǎn)生這種情況的原因，如：在低版本的ORACLE很可能是一些其他異常的應用出錯后產(chǎn)生的死連接。2.1.6

9、 死鎖檢查監(jiān)控數(shù)據(jù)庫運行過程中，出現(xiàn)的阻塞，記錄現(xiàn)象，記錄產(chǎn)生阻塞的SQL語句，執(zhí)行的用戶，發(fā)生時間，頻率，處理（殺掉、等待自然解鎖等）。ORACL版本中的死鎖會在alert文件中產(chǎn)生記錄，oracle會自動解鎖（其實是選擇一個殺掉）。對于死鎖的處理過程要進行記錄?？梢允褂肙EMLL具或者查找相關(guān)的V雌圖來確認產(chǎn)生阻塞的語句。2.1.7 監(jiān)控SQL語句的執(zhí)行查找效率低下的SQL語句，聯(lián)系協(xié)調(diào)開發(fā)人員，進行相關(guān)處理。可使用ORACL睫供的AWRS行，也可使用ORACL提供的OEME具執(zhí)行，或者自行編制的腳本等等。2.1.8 操作系統(tǒng)級檢查運行vmstat,sar,topas（AIX系統(tǒng)），gla

10、nce（HP系統(tǒng)）等命令檢查CPU內(nèi)存、虛擬內(nèi)存等的使用情況。運行df,du,iostat檢查磁盤使用情況運行netstat檢查網(wǎng)絡情況運行手工編制的監(jiān)控腳本檢查。針對于操作系統(tǒng)的不同，使用的命令也會有不同，請參考相應的操作系統(tǒng)文檔。建議使用man命令察看相應的幫助信息。2.1.9 其他每天查看晚間定時執(zhí)行的數(shù)據(jù)庫信息收集作業(yè)和備份作業(yè)的日志輸出，確認都已正常完成。往往不能正常完成是由于如下的原因：請確認腳本是否變動（錯誤的修改造成等等），設備（主機，磁盤陣列，磁帶庫，網(wǎng)絡等等）是否正常，空間是否足夠等等。建議每天按業(yè)務峰值情況，對數(shù)據(jù)庫性能數(shù)據(jù)進行定時采集及分析。3數(shù)據(jù)庫維護數(shù)據(jù)庫維護工作內(nèi)

11、容包括維護、故障診斷、錯誤修復、備份恢復、歷史數(shù)據(jù)遷移等過程。數(shù)據(jù)庫維護工作事項3.1.1 頁面修復根據(jù)日常監(jiān)控的結(jié)果，進行頁面（或者數(shù)據(jù)庫壞塊）修復，如將表數(shù)據(jù)導出后重建表,然后導入數(shù)據(jù)。提交修復記錄。3.1.2 數(shù)據(jù)庫對象重建根據(jù)數(shù)據(jù)庫監(jiān)控的結(jié)果，重建失效的對象。如：索引、存儲過程、函數(shù)、視圖、觸發(fā)器等等。實用命令：Alterindex<索弓I名>rebuildonline;3.1.3 碎片回收（數(shù)據(jù)重組）當某些數(shù)據(jù)庫運行一段時間后，表會產(chǎn)生碎片，影響數(shù)據(jù)庫的性能。可根據(jù)日常檢查的ORACL散據(jù)庫本身的原因,高水位線）降低，因此不會釋EXP,然后進行IMP操作，以結(jié)果，運用工具

12、或腳本對于數(shù)據(jù)庫空間進行重組或回收。由于在進彳T了DELETEB作之后也不會使HWMHighWaterMark放所占用的空間，所以建議在進行了數(shù)據(jù)遷移之后將全庫進行釋放占用的空間。3.1.4 刪除不用的數(shù)據(jù)此項工作要得到開發(fā)方、設計人員、以及相關(guān)人員的確認后，方可執(zhí)行。3.1.5 備份恢復需要定期對于數(shù)據(jù)庫備份進行有效性檢測，定期進行數(shù)據(jù)恢復的演練操作。以防止萬一的數(shù)據(jù)庫事故時準備不足。數(shù)據(jù)庫需要采用在線的熱備份，不需要關(guān)閉數(shù)據(jù)庫進行，在備份的同時可以進行正常的數(shù)據(jù)庫的各種操作，滿足了7*24的系統(tǒng)的需要。數(shù)據(jù)庫的備份不能影響用戶對數(shù)據(jù)庫的訪問。目標需要在線熱備份多級增量備份并行備份，恢復減小

13、所需要備份量備份，恢復使用簡單可參考如下的方案：1 .每月做一個數(shù)據(jù)庫的全備份（包含只讀表空間）2 .每星期做一次零級備份（不包含只讀表空間）3 .每個星期三做一次一級備份4 .每天做一個二級備份5 .任何表空間改成只讀狀態(tài)后做一個該表空間的備份。6 .當需要時（如四個小時歸檔文件系統(tǒng)就要接近滿了）備份歸檔文件。3.1.6 歷史數(shù)據(jù)遷移定期進行歷史數(shù)據(jù)遷移，減少生產(chǎn)數(shù)據(jù)庫的壓力。3.1.7 定期修改密碼包括SYS,SYSTEM等用戶。3.1.8 刪除掉不必要的用戶對于系統(tǒng)安裝時的，M示用戶，如：hr,scott等。建議每周定期清理和備份一周所產(chǎn)生的Alert日志、跟蹤文件和dump文件。分別位

14、于$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ā)、測試。3.1.9 其他針對不同的數(shù)據(jù)庫版本的不同特點進行相應的維護操作。具體情況請參見ORACL改檔或者訪問metalink。4數(shù)據(jù)庫管理常用SQL腳本常用的SQL腳本，

15、在實施時可供數(shù)據(jù)庫管理員參考，在執(zhí)行時，需要進行相應的修改。1 .剩余空間檢查SELECTtablespace_name,sum(blocks)asfree_blk,trunc(sum(bytes)/(1024*1024)asfree_m,max(bytes)/(1024)asbig_chunk_k,count(*)asnum_chunksFROMdba_free_spaceGROUPBYtablespace_name2 .表空間數(shù)據(jù)量情況顯示SELECTtablespace_name,max_blocks,count_blocks,sum_free_blocks,to_char(100*su

16、m_free_blocks/sum_alloc_blocks,'99.99')|'%'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_space

17、GROUPBYtablespace_name)WHEREtablespace_name=fs_ts_name3 .表和索引分析BEGINdbms_utility.analyze_schema('&OWNER','ESTIMATE',NULL,5);END;4 .檢查空間情況SELECTa.table_name,a.next_extent,a.tablespace_nameFROMall_tablesa,(SELECTtablespace_name,max(bytes)asbig_chunkFROMdba_free_spaceGROUPBYtablespa

18、ce_name)fWHEREf.tablespace_name=a.tablespace_nameANDa.next_extent>f.big_chunk5 .檢查已經(jīng)存在的空間擴展SELECTcount(*),segment_name,segment_type,dt.tablespace_nameFROMdba_tablespacesdt,dba_extentsdxWHEREdt.tablespace_name=dx.tablespace_nameANDdt.next_extent!=dx.bytesANDdx.owner='&OWNER'GROUPBYsegm

19、ent_name,segment_type,dt.tablespace_name6 .檢查沒有主鍵的表SELECTtable_nameFROMall_tablesWHEREowner='&OWNER'MINUSSELECTtable_nameFROMall_constraintsWHEREowner='&&OWNER'ANDconstraint_type='P'7 .檢查失效的主鍵SELECTowner,constraint_name,table_name,statusFROMall_constraintsWHEREown

20、er='&OWNER'ANDstatus='DISABLEDANDconstraint_type='P'8 .重建索引，具體參數(shù)請根據(jù)實際情況進行修改SELECT'alterindex'|index_name|'rebuild','tablespaceINDEXESstorage(initial256Knext256K);'FROMall_indexesWHERE(tablespace_name!='INDEXES'ORnext_extent!=(256*1024)ANDowner=

21、'&OWNER'9 .對比兩個實例的不同SELECTobject_name,object_typeFROMuser_objectsMINUSSELECTobject_name,object_typeFROMuser_objects&my_db_link10 .查看動態(tài)性能視圖Select*fromV$FIXED_TABLE11 .查看約束selecta.constraint_name,a.constraint_type,a.*fromuser_constraintsawheretable_name='table_name'selectconstr

22、aint_name,column_namefromuser_cons_columnswheretable_name='table_name'12 .查看索引user_indexes包含索引的名字，user_ind_columns包含索引的列.13 .查看數(shù)據(jù)庫啟動參數(shù)：showparameterpara,v$parameter提供當前會話信息，v$system_parameter提供當前系統(tǒng)信,官。其中isses_modifiable,issys_modifiable表示是否允許動態(tài)修改。14 .查看進程號selectp.spid,s.usernamefromv$process

23、p,v$sessionswherep.addr=s.paddr;15 .查看數(shù)據(jù)文件：selectname,statusfromv$datafile;select*fromdba_data_files;16 .查看數(shù)據(jù)文件狀態(tài)selectd.file#f#,,d.status,h.statusfromv$datafiled,v$datafile_headerhwhered.file#=h.file#;17 .查看控制文件selectnamefromv$controlfile;selecttype,record_size,records_total,records_usedfromv

24、$controlfile_record_sectionwheretype='DATAFILE；18 .查看是否歸檔模式：archiveloglistselectname,log_modefromv$database;selectarchiverfromv$instance;19 .查看日志組：selectgroups,current_group#,sequence#fromv$thread;selectgroup#,sequence#,bytes,members,statusfromv$log;select*fromv$logfile;其中status為空表示正常。20 .查看larg

25、epoolselect*fromv$sgastatwherepool='largepool'21 .查看歸檔位置showparameterarchiveselectdestination,binding,target,statusfromv$archive_dest;22 .查看歸檔進程select*fromv$archive_processes;23 .查看正在備份的數(shù)據(jù)文件select*fromv$backup;24 .查看需要恢復的文件select*fromv$recover_file;25 .查看所有歸檔日志文件select*fromv$archived_log;26

26、.查看恢復時要用到的日志文件select*fromv$recovery_log;27 .查看SGA勺結(jié)構(gòu)Showsga;select*fromv$sgastat;28 .提取librarycache的命中率selectgethitratiofromv$librarycachewherenamespace=''29 .查看正在運行的SQL語句selectsql_text,users_executing,executions,loadsfromv$sqlarea;select*fromv$sqltextwheresql_text='select*fromemp%'M

27、isses30 .查看librarycachereload情況：selectsum(pins)aExecutions”,sum(reloads)“cachesum(reloads)/sum(pins)fromv$librarycache;31 .查看大匿名塊selectsql_textfromv$sqlareawherecommand_type=47andlength(sql_text)>500;32 .查看當前會話的UGAJxselectsum(value)|'bytes'"Totalsessionmemory”fromv$mystat,v$statnamew

28、herename-sessionugamemory'andv$mystat.statistic#=v$statname.statistic#;33 .查看所有MTS用戶的UGALselectsum(value)|'bytes'"Totalsessionmemory”fromv$sesstat,v$statnamewherename='sessionugamemory'andv$sesstat.statistic#=v$statname.statistic#;34 .查看所有用戶使用的最大的UGAX:selectsum(value)|'b

29、ytes'"Totalsessionmemory”fromv$sesstat,v$statnamewherename='sessionugamemorymax'andv$sesstat.statistic#=v$statname.statistic#;35 .查看high-watermark以下的塊數(shù)selecttable_name,blocksfromdba_tableswheretable_name='table_name'；36 .查看會話的I/O:selectio.block_gets,io.consistent_gets,io.phy

30、sical_readsfromv$sess_ioio,v$sessionswheres.audsid=USERENV('SESSIONID)andio.sid=s.sid;37 .查看Bufferpool的命中率selectname,1-(physical_reads/(db_block_gets+consistent_gets)“HIT_RATIOfromsys.v$buffer_pool_statisticswheredb_block_gets+consistent_gets>0;38 .查看freelist的競爭selectclass,count,timefromv$wai

31、tstatwhereclass='segmentheader'；selectevent,total_waitsfromv$system_eventwhereevent='bufferbusywaits'；bufferbusywaits可在兩種情況發(fā)生：1dirtyqueue已滿，2freelist競爭。39 .查看freelist競爭發(fā)生在哪個segment上selects.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.statefromdba_segmentss,

32、v$session_waitwwherew.event='bufferbusywaits'andw.p1=s.header_fileandw.p2=s.header_block;40 .查看全表掃描發(fā)生的次數(shù)selectname,valuefromv$sysstatwherenamelike'%tablescan%'；41 .查看大操作的執(zhí)行情況selectsid,serial#,opname,to_char(start_time,'HH24:MI:SS)asstart_t,(sofar/totalwork)*100aspercent_completef

33、romv$session_longops;42 .查看數(shù)據(jù)文件的I/Oempty_blocks 其selectphyrds,phywrts,fromv$datafiled,v$filestatfwhered.file#=f.file#;43 .查看空閑塊數(shù)少于10%勺segment(blocks在high-watermark以下,上)selectowner,table_name,blocks,empty_blocksfromdba_tableswhereempty_blocks/(blocks+empty_blocks)<0.1andblocks+e

34、mpty_blocks!=0;44 .查看migration和chaininganalyzetabletable_namecomputestatistics;selectnum_rows,chain_cntfromdba_tableswheretable_name='table_name'；45 .查看表的統(tǒng)計信息analyzetabletable_namecomputestatistics;selectnum_rows,blocks,empty_blocksasempty,avg_space,chain_cnt,avg_row_lenfromdba_tableswhereowner='HRandtable_name='table_name'；46 .查看索引的統(tǒng)計信息analyzeindexindex_namevalidatestructure;select(del_lf_rows_len/lf_rows_len)*100asindex_usagefromindex_s