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

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

3、于SYBAS嗷據庫同樣有參考價值，只要換用相對應的語句即可。數據庫監控2數據庫監控數據庫監控工作內容制定和改進監控方案，編寫監控腳本。對于數據庫進行日常監測，提交記錄。根據監測結果進行分析、預測，提交相應的系統改進建議方案。數據庫監控工作步驟2.1.1 查看數據庫日志數據庫的日志上會有大量對于管理員有用的信息。ORACLE勺Alert日志紀錄了數據庫系統所報的系統級錯誤信息，以及數據塊失效等嚴重錯誤信息。錯誤信息的產生，會產生相應的跟蹤文件，通過查看警告日志和跟蹤文件可查找錯誤原因，對于發現的問題應及時解決和匯報。如:1 .表空間是否滿，是否需要進行添加或者擴展。Alert文件中會顯示有表塊無

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

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

6、的作用?？稍趨滴募N用SQL_TRACE=TRUE打開該文件(對所有用戶)，也可用altersessionsetsql_trace=true打開當前會話，也可用executedbms_system.set_sql_trace_in_session(sid,serial#,true)打開指定會話。2.1.2 檢查是否有失效的數據庫對象主要關注索引，觸發器，存儲過程，函數等等。如：查找user_objects數據字典，看其中是否有狀態為invalid的對象。判斷失效原因(如：視圖失效的原因有可能是由于創建視圖的基表被刪除等等)，找出原因可進行對象重建或修復。實用命令：Selectobject_n

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

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

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

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

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

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

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

14、于$ORACLE_BASE/admin/$ORACLE_SID/bdump,$ORACLE_BASE/admin/$ORACLE_SID/udump,$ORACLE_BASE/admin/$ORACLE_SID/cdump,等目錄下。定期對表進行統計分析，（如可使用analyze等命令，8i以上有dbms_stats包來實現，使SQL優化器總是能找到最好的查詢策略。制定和執行紀錄保證生產庫的安全：應絕對禁止在生產庫上進行開發、測試。3.1.9 其他針對不同的數據庫版本的不同特點進行相應的維護操作。具體情況請參見ORACL改檔或者訪問metalink。4數據庫管理常用SQL腳本常用的SQL腳本，

15、在實施時可供數據庫管理員參考，在執行時，需要進行相應的修改。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 .表空間數據量情況顯示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 .檢查已經存在的空間擴展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 .重建索引，具體參數請根據實際情況進行修改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 .查看動態性能視圖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 .查看數據庫啟動參數：showparameterpara,v$parameter提供當前會話信息，v$system_parameter提供當前系統信,官。其中isses_modifiable,issys_modifiable表示是否允許動態修改。14 .查看進程號selectp.spid,s.usernamefromv$process

23、p,v$sessionswherep.addr=s.paddr;15 .查看數據文件：selectname,statusfromv$datafile;select*fromdba_data_files;16 .查看數據文件狀態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 .查看正在備份的數據文件select*fromv$backup;24 .查看需要恢復的文件select*fromv$recover_file;25 .查看所有歸檔日志文件select*fromv$archived_log;26

26、.查看恢復時要用到的日志文件select*fromv$recovery_log;27 .查看SGA勺結構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以下的塊數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可在兩種情況發生：1dirtyqueue已滿，2freelist競爭。39 .查看freelist競爭發生在哪個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 .查看全表掃描發生的次數selectname,valuefromv$sysstatwherenamelike'%tablescan%'；41 .查看大操作的執行情況selectsid,serial#,opname,to_char(start_time,'HH24:MI:SS)asstart_t,(sofar/totalwork)*100aspercent_completef

33、romv$session_longops;42 .查看數據文件的I/Oempty_blocks 其selectphyrds,phywrts,fromv$datafiled,v$filestatfwhered.file#=f.file#;43 .查看空閑塊數少于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 .查看表的統計信息analyzetabletable_namecomputestatistics;selectnum_rows,blocks,empty_blocksasempty,avg_space,chain_cnt,avg_row_lenfromdba_tableswhereowner='HRandtable_name='table_name'；46 .查看索引的統計信息analyzeindexindex_namevalidatestructure;select(del_lf_rows_len/lf_rows_len)*100asindex_usagefromindex_s