1、例行工作手冊1/20數(shù)據(jù)庫維護工作手冊數(shù)據(jù)庫維護工作手冊文檔編號：文檔名稱：編 寫：審 核：批 準:批準日期：例行工作手冊2/20目目 錄錄1概述概述.42數(shù)據(jù)庫監(jiān)控數(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ù)庫是否正常.62.2.6死鎖檢查.62.2.7監(jiān)控 SQL 語句的執(zhí)行.62.2.8操作系統(tǒng)級檢查.62.2.9其他.63數(shù)據(jù)庫維護數(shù)據(jù)庫維護.73.1 數(shù)據(jù)庫維護工作內(nèi)容.73.2 數(shù)據(jù)庫維護工作事項.73.2.1頁

2、面修復(fù).73.2.2數(shù)據(jù)庫對象重建.73.2.3碎片回收（數(shù)據(jù)重組）.73.2.4刪除不用的數(shù)據(jù).73.2.5備份恢復(fù).73.2.6歷史數(shù)據(jù)遷移.83.2.7定期修改密碼.83.2.8刪除掉不必要的用戶.83.2.9其他.84數(shù)據(jù)庫管理常用數(shù)據(jù)庫管理常用 SQL 腳本腳本.95日常維護和問題管理日常維護和問題管理.175.1 目的.175.2 例行工作建議.175.3 相關(guān)填表說明.17例行工作手冊3/201概述概述數(shù)據(jù)庫的日常監(jiān)控是使管理員及時了解系統(tǒng)異常的手段。大部分情況下，系統(tǒng)總是正常運行的。只有對正常情況的充分了解，才能通過對比正常情況發(fā)現(xiàn)異常情況。對于數(shù)據(jù)庫的日常監(jiān)控要有記錄，文字記

3、錄或者電子文檔保存。對于數(shù)據(jù)庫異常進行分析，提出解決方案。日常工作包括監(jiān)控和維護兩個部分。此文檔中關(guān)于數(shù)據(jù)庫的運行命令示例主要針對于 ORACLE 數(shù)據(jù)庫，但對于 SYBASE 數(shù)據(jù)庫同樣有參考價值，只要換用相對應(yīng)的語句即可。數(shù)據(jù)庫監(jiān)控2數(shù)據(jù)庫監(jiān)控數(shù)據(jù)庫監(jiān)控數(shù)據(jù)庫監(jiān)控工作內(nèi)容數(shù)據(jù)庫監(jiān)控工作內(nèi)容制定和改進監(jiān)控方案，編寫監(jiān)控腳本。對于數(shù)據(jù)庫進行日常監(jiān)測，提交記錄。根據(jù)監(jiān)測結(jié)果進行分析、預(yù)測，提交相應(yīng)的系統(tǒng)改進建議方案。數(shù)據(jù)庫監(jiān)控工作步驟數(shù)據(jù)庫監(jiān)控工作步驟2.1.1查看數(shù)據(jù)庫日志查看數(shù)據(jù)庫日志數(shù)據(jù)庫的日志上會有大量對于管理員有用的信息。ORACLE 的 Alert 日志紀錄了數(shù)據(jù)庫系統(tǒng)所報的系統(tǒng)級錯

4、誤信息，以及數(shù)據(jù)塊失效等嚴重錯誤信息。錯誤信息的產(chǎn)生，會產(chǎn)生相應(yīng)的跟蹤文件，通過查看警告日志和跟蹤文件可查找錯誤原因，對于發(fā)現(xiàn)的問題應(yīng)及時解決和匯報。如：1.表空間是否滿，是否需要進行添加或者擴展。Alert 文件中會顯示有表塊無法擴展的提示。2.表的塊或者頁面是否損壞。 （往往這時 alert 文件中會顯示 ora-600 的錯誤。 ）3.數(shù)據(jù)庫是否進行了異常操作。 （如：drop tablespace 等等） 。實用命令：報警日志文件（alert.log 或 alrt.ora）記錄數(shù)據(jù)庫啟動，關(guān)閉和一些重要的出錯信息。數(shù)據(jù)庫管理員應(yīng)該經(jīng)常檢查這個文件，并對出現(xiàn)的問題作出即使的反應(yīng)?？梢酝ㄟ^

5、以下 SQL 找到他的路徑 select value from v$parameter where upper(name) =BACKGROUND_DUMP_DEST，或通過參數(shù)文件獲得其路徑，或者 show parameter BACKGROUND_DUMP_DEST。后臺跟蹤文件路徑與報警文件路徑一致，記載了系統(tǒng)后臺進程出錯時寫入的信息。例行工作手冊4/20用戶跟蹤文件記載了用戶進程出錯時寫入的信息，一般不可能讀懂，可以通過 ORACLE 的TKPROF 工具轉(zhuǎn)化為可以讀懂的格式。用戶跟蹤文件的路徑，你可以通過以下 SQL 找到他的路徑 select value from v$parame

6、ter where upper(name) =USER_DUMP_DEST，或通過參數(shù)文件獲得其路徑，或者 show parameter USER_DUMP_DEST。可以通過設(shè)置用戶跟蹤或 dump 命令來產(chǎn)生用戶跟蹤文件，一般在調(diào)試、優(yōu)化、系統(tǒng)分析中有很大的作用?？稍趨?shù)文件種用 SQL_TRACE=TRUE 打開該文件(對所有用戶)，也可用 alter session set sql_trace=true 打開當前會話，也可用 execute dbms_system.set_sql_trace_in_session(sid,serial#,true)打開指定會話。2.1.2檢查是否有失效

7、的數(shù)據(jù)庫對象檢查是否有失效的數(shù)據(jù)庫對象主要關(guān)注索引，觸發(fā)器，存儲過程，函數(shù)等等。如：查找 user_objects 數(shù)據(jù)字典，看其中是否有狀態(tài)為 invalid 的對象。判斷失效原因（如：視圖失效的原因有可能是由于創(chuàng)建視圖的基表被刪除等等） ，找出原因可進行對象重建或修復(fù)。實用命令：Select object_name,object_type From user_objects Where object_type=INVALID;.3 查看數(shù)據(jù)庫剩余空間查看數(shù)據(jù)庫剩余空間1.剩余空間不足時要擴展空間，一般的，當剩余空間小于 10時，要進行空間擴展。對于 ORACLE 數(shù)據(jù)庫，通

8、過查找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è)務(wù)表。因為這些表健康與否與日常業(yè)務(wù)的正常運行密切相關(guān)。重點檢查這些表的索引是否失效，表的統(tǒng)計信息是否及時更新，如：當這些表進行了大的數(shù)據(jù)裝載或者刪除操作之后。原則上需要檢查所有的表，只是由于上面這些表更關(guān)鍵，建議管理員給以更多的關(guān)注。2.重點檢查數(shù)據(jù)量超過百萬行的表，各地的情況可能不一樣，當數(shù)據(jù)超過百萬行之后，如果索引失效會導(dǎo)致表掃描，占用大量系統(tǒng) IO，嚴重影響

9、系統(tǒng)性能。2.1.5查看數(shù)據(jù)庫是否正常查看數(shù)據(jù)庫是否正常包括數(shù)據(jù)庫實例是否正常工作、listener 是否工作正常，確保數(shù)據(jù)庫系統(tǒng)環(huán)境正常。數(shù)據(jù)庫連接是否正常、檢查是否有超出正常水平的連接數(shù)。例行工作手冊5/20如：平常 500 個，某天下午忽然達到 600 個。應(yīng)記錄這種異常情況。分析產(chǎn)生這種情況的原因，如：在低版本的 ORACLE 中，很可能是一些其他異常的應(yīng)用出錯后產(chǎn)生的死連接。.6 死鎖檢查死鎖檢查監(jiān)控數(shù)據(jù)庫運行過程中，出現(xiàn)的阻塞，記錄現(xiàn)象，記錄產(chǎn)生阻塞的 SQL 語句，執(zhí)行的用戶，發(fā)生時間，頻率，處理（殺掉、等待自然解鎖等） 。ORACLE 版本中的死鎖會在 aler

10、t文件中產(chǎn)生記錄，oracle 會自動解鎖（其實是選擇一個殺掉） 。對于死鎖的處理過程要進行記錄。可以使用 OEM 工具或者查找相關(guān)的 V$視圖來確認產(chǎn)生阻塞的語句。2.1.7監(jiān)控監(jiān)控 SQL 語句的執(zhí)行語句的執(zhí)行查找效率低下的 SQL 語句，聯(lián)系協(xié)調(diào)開發(fā)人員，進行相關(guān)處理?？墒褂?ORACLE 提供的AWR 進行，也可使用 ORACLE 提供的 OEM 工具執(zhí)行，或者自行編制的腳本等等。.8 操作系統(tǒng)級檢查操作系統(tǒng)級檢查運行 vmstat，sar,topas(AIX 系統(tǒng))，glance(HP 系統(tǒng))等命令檢查 CPU、內(nèi)存、虛擬內(nèi)存等的使用情況。運行 df,du,iosta

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

12、據(jù)遷移等過程。例行工作手冊6/20數(shù)據(jù)庫維護工作事項數(shù)據(jù)庫維護工作事項3.1.1頁面修復(fù)頁面修復(fù)根據(jù)日常監(jiān)控的結(jié)果，進行頁面（或者數(shù)據(jù)庫壞塊）修復(fù)，如將表數(shù)據(jù)導(dǎo)出后重建表，然后導(dǎo)入數(shù)據(jù)。提交修復(fù)記錄。3.1.2數(shù)據(jù)庫對象重建數(shù)據(jù)庫對象重建根據(jù)數(shù)據(jù)庫監(jiān)控的結(jié)果，重建失效的對象。如：索引、存儲過程、函數(shù)、視圖、觸發(fā)器等等。實用命令：Alter index rebuild online;3.1.3碎片回收（數(shù)據(jù)重組）碎片回收（數(shù)據(jù)重組）當某些數(shù)據(jù)庫運行一段時間后，表會產(chǎn)生碎片，影響數(shù)據(jù)庫的性能?？筛鶕?jù)日常檢查的結(jié)果，運用工具或腳本對于數(shù)據(jù)庫空間進行重組或回收。由于 ORACLE 數(shù)據(jù)庫本身的原因，在

13、進行了 DELETE 操作之后也不會使 HWM（High Water Mark高水位線）降低，因此不會釋放所占用的空間，所以建議在進行了數(shù)據(jù)遷移之后將全庫進行 EXP，然后進行 IMP 操作，以釋放占用的空間。3.1.4刪除不用的數(shù)據(jù)刪除不用的數(shù)據(jù)此項工作要得到開發(fā)方、設(shè)計人員、以及相關(guān)人員的確認后，方可執(zhí)行。3.1.5備份恢復(fù)備份恢復(fù)需要定期對于數(shù)據(jù)庫備份進行有效性檢測，定期進行數(shù)據(jù)恢復(fù)的演練操作。以防止萬一的數(shù)據(jù)庫事故時準備不足。數(shù)據(jù)庫需要采用在線的熱備份，不需要關(guān)閉數(shù)據(jù)庫進行，在備份的同時可以進行正常的數(shù)據(jù)庫的各種操作，滿足了 7*24 的系統(tǒng)的需要。數(shù)據(jù)庫的備份不能影響用戶對數(shù)據(jù)庫的訪

14、問。目標需要在線熱備份多級增量備份并行備份，恢復(fù)減小所需要備份量備份，恢復(fù)使用簡單可參考如下的方案:1.每月做一個數(shù)據(jù)庫的全備份（包含只讀表空間）2.每星期做一次零級備份（不包含只讀表空間）3.每個星期三做一次一級備份4.每天做一個二級備份5.任何表空間改成只讀狀態(tài)后做一個該表空間的備份。例行工作手冊7/206.當需要時（如四個小時歸檔文件系統(tǒng)就要接近滿了）備份歸檔文件。3.1.6歷史數(shù)據(jù)遷移歷史數(shù)據(jù)遷移定期進行歷史數(shù)據(jù)遷移，減少生產(chǎn)數(shù)據(jù)庫的壓力。3.1.7定期修改密碼定期修改密碼包括 SYS，SYSTEM 等用戶。3.1.8刪除掉不必要的用戶刪除掉不必要的用戶對于系統(tǒng)安裝時的演示用戶，如：h

15、r，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)庫的安全：應(yīng)絕對禁止在生產(chǎn)庫上進行開發(fā)、測試。3.1.9其他其他針對不同的數(shù)據(jù)庫版本

16、的不同特點進行相應(yīng)的維護操作。具體情況請參見 ORACLE 文檔或者訪問 metalink。4數(shù)據(jù)庫管理常用數(shù)據(jù)庫管理常用 SQL 腳本腳本常用的 SQL 腳本，在實施時可供數(shù)據(jù)庫管理員參考，在執(zhí)行時，需要進行相應(yīng)的修改。1.剩余空間檢查SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunksFROM dba_free_spac

17、eGROUP BY tablespace_name2.表空間數(shù)據(jù)量情況顯示SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks例行工作手冊8/20, to_char(100*sum_free_blocks/sum_alloc_blocks, 99.99) | %AS pct_freeFROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocksFROM dba_data_filesGROUP BY tablespace_name), ( SELECT ta

18、blespace_name AS fs_ts_name, max(blocks) AS max_blocks, count(blocks) AS count_blocks, sum(blocks) AS sum_free_blocksFROM dba_free_spaceGROUP BY tablespace_name )WHERE tablespace_name = fs_ts_name3.表和索引分析BEGINdbms_utility.analyze_schema ( &OWNER, ESTIMATE, NULL, 5 ) ;END ;4.檢查空間情況SELECT a.table_name

19、, a.next_extent, a.tablespace_nameFROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunkFROM dba_free_spaceGROUP BY tablespace_name ) fWHERE f.tablespace_name = a.tablespace_nameAND a.next_extent f.big_chunk5.檢查已經(jīng)存在的空間擴展SELECT count(*), segment_name, segment_type, dt.tablespace_nameFROM

20、 dba_tablespaces dt, dba_extents dxWHERE dt.tablespace_name = dx.tablespace_nameAND dt.next_extent != dx.bytes AND dx.owner = &OWNERGROUP BY segment_name, segment_type, dt.tablespace_name6.檢查沒有主鍵的表SELECT table_nameFROM all_tablesWHERE owner = &OWNER例行工作手冊9/20MINUSSELECT table_nameFROM all_constraint

21、sWHERE owner = &OWNERAND constraint_type = P7.檢查失效的主鍵SELECT owner, constraint_name, table_name, statusFROM all_constraintsWHERE owner = &OWNER AND status = DISABLED AND constraint_type = P8.重建索引，具體參數(shù)請根據(jù)實際情況進行修改SELECT alter index | index_name | rebuild , tablespace INDEXES storage ( initial 256 K nex

22、t 256 K ) ; FROM all_indexesWHERE ( tablespace_name != INDEXESOR next_extent != ( 256 * 1024 )AND owner = &OWNER9.對比兩個實例的不同SELECT object_name, object_typeFROM user_objectsMINUSSELECT object_name, object_typeFROM user_objects&my_db_link10. 查看動態(tài)性能視圖Select * from V$FIXED_TABLE11. 查看約束select a.constrain

23、t_name, a.constraint_type,a.*from user_constraints awhere table_name=table_name;select constraint_name, column_name from user_cons_columns where table_name=table_name;例行工作手冊10/2012. 查看索引 user_indexes 包含索引的名字,user_ind_columns 包含索引的列.13. 查看數(shù)據(jù)庫啟動參數(shù)：show parameter para，v$parameter 提供當前會話信息，v$system_para

24、meter 提供當前系統(tǒng)信息。其中 isses_modifiable，issys_modifiable表示是否允許動態(tài)修改。14. 查看進程號：select p.spid, s.username from v$process p , v$session s where p.addr=s.paddr;15. 查看數(shù)據(jù)文件：select name, status from v$datafile;select * from dba_data_files;16. 查看數(shù)據(jù)文件狀態(tài)select d.file# f#, , d.status, h.status from v$datafile

25、d, v$datafile_header h where d.file#=h.file#;17. 查看控制文件select name from v$controlfile;select type, record_size, records_total, records_used from v$controlfile_record_section where type=DATAFILE;18. 查看是否歸檔模式：archive log listselect name, log_mode from v$database;select archiver 例行工作手冊11/20from v$insta

26、nce;19. 查看日志組：select groups, current_group#, sequence# from v$thread;select group#, sequence#, bytes , members, status from v$log;select * from v$logfile; 其中 status 為空表示正常。20. 查看 large poolselect * from v$sgastat where pool=large pool;21. 查看歸檔位置show parameter archive select destination, binding, tar

27、get, status from v$archive_dest;22. 查看歸檔進程select * from v$archive_processes;23. 查看正在備份的數(shù)據(jù)文件select * from v$backup;24. 查看需要恢復(fù)的文件select * from v$recover_file;25. 查看所有歸檔日志文件例行工作手冊12/20select * from v$archived_log;26. 查看恢復(fù)時要用到的日志文件select * from v$recovery_log;27. 查看 SGA 的結(jié)構(gòu)Show sga;select * from v$sgast

28、at;28. 提取 library cache 的命中率select gethitratio from v$librarycache where namespace=;29. 查看正在運行的 SQL 語句select sql_text, users_executing, executions, loads from v$sqlarea;select * from v$sqltext where sql_text=select * from emp%;30. 查看 library cache reload 情況：select sum(pins) “Executions”, sum(reloads

29、) “cache Misses”, sum(reloads)/sum(pins)from v$librarycache;31. 查看大匿名塊select sql_text from v$sqlarea where command_type=47 and length(sql_text)500;32. 查看當前會話的 UGA 區(qū)例行工作手冊13/20select sum(value)|bytes “Total session memory” from v$mystat, v$statname where name=session uga memory and v$mystat.statistic

30、#=v$statname.statistic#;33. 查看所有 MTS 用戶的 UGA 區(qū)：select sum(value)|bytes “Total session memory” from v$sesstat, v$statname where name=session uga memory and v$sesstat.statistic#=v$statname.statistic#;34. 查看所有用戶使用的最大的 UGA 區(qū)：select sum(value)|bytes “Total session memory” from v$sesstat, v$statname where

31、 name=session uga memory max and v$sesstat.statistic#=v$statname.statistic#;35. 查看 high-water mark 以下的塊數(shù)select table_name, blocks from dba_tables where table_name=table_name;36. 查看會話的 I/O：select io.block_gets, io.consistent_gets, io.physical_reads from v$sess_io io, v$session s where s.audsid=USEREN

32、V(SESSIONID) and io.sid=s.sid;37. 查看 Buffer pool 的命中率select name, 1-(physical_reads/(db_block_gets+consistent_gets) “HIT_RATIO” from sys.v$buffer_pool_statistics where db_block_gets+consistent_gets0;38. 查看 free list 的競爭select class, count, time from v$waitstat where class=segment header;例行工作手冊14/20s

33、elect event, total_waits from v$system_event where event=buffer busy waits;buffer busy waits 可在兩種情況發(fā)生：1dirty queue 已滿，2free list 競爭。39. 查看 free list 競爭發(fā)生在哪個 segment 上select s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.statefrom dba_segments s, v$session_wait wwhere

34、w.event=buffer busy waits and w.p1=s.header_file and w.p2=s.header_block;40. 查看全表掃描發(fā)生的次數(shù)select name, value from v$sysstat where name like %table scan%;41. 查看大操作的執(zhí)行情況select sid, serial#, opname, to_char(start_time, HH24:MI:SS) as start_t, (sofar/totalwork)*100 as percent_complete from v$session_longo

35、ps;42. 查看數(shù)據(jù)文件的 I/Oselect phyrds, phywrts, from v$datafile d, v$filestat f where d.file#=f.file# order by ;43. 查看空閑塊數(shù)少于 10%的 segment(blocks 在 high-water mark 以下，empty_blocks 其上)select owner, table_name, blocks, empty_blocks from dba_tables where empty_blocks/(blocks+empty_blocks)0.1 and

36、blocks+empty_blocks!=0;44. 查看 migration 和 chaininganalyze table table_name compute statistics;例行工作手冊15/20select num_rows, chain_cntfrom dba_tables where table_name=table_name;45. 查看表的統(tǒng)計信息analyze table table_name compute statistics;select num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len from dba_tableswhere owner=HR and table_name=table_name;46. 查看索引的統(tǒng)計信息analyze index index_name validate structure;select (del_lf_rows_len/lf_rows_len)*100 as index_usage from index_stats;例行工作手冊16/205日常維護和問題管理日常維護和問題管理目的目的作為一名數(shù)據(jù)庫管理員，數(shù)據(jù)庫應(yīng)用的正常穩(wěn)定運行