1、1 / 21數據庫維護工作手冊數據庫維護工作手冊文檔編號：文檔名稱：編 寫：審 核：批 準:批準日期：目目 錄錄1概述概述.42數據庫監控數據庫監控.42.1 數據庫監控工作內容.42.2 數據庫監控工作步驟.42.2.1查看數據庫日志.42.2.2檢查是否有失效的數據庫對象.52.2.3查看數據庫剩余空間.52.2.4重點表檢查.52.2.5查看數據庫是否正常.62.2.6死鎖檢查.62.2.7監控SQL語句的執行.62.2.8操作系統級檢查.62.2.9其他.63數據庫維護數據庫維護.73.1 數據庫維護工作內容.73.2 數據庫維護工作事項.73.2.1頁面修復.73.2.2數據庫對象重

2、建.73.2.3碎片回收（數據重組）.73.2.4刪除不用的數據.73.2.5備份恢復.73.2.6歷史數據遷移.83.2.7定期修改密碼.83.2.8刪除掉不必要的用戶.83.2.9其他.84數據庫管理常用數據庫管理常用 SQL 腳本腳本.95日常維護和問題管理日常維護和問題管理.175.1 目的.175.2 例行工作建議.175.3 相關填表說明.17概述概述數據庫的日常監控是使管理員及時了解系統異常的手段。大部分情況下，系統總是正常運行的。只有對正常情況的充分了解，才能通過對比正常情況發現異常情況。對于數據庫的日常監控要有記錄，文字記錄或者電子文檔保存。對于數據庫異常進行分析，提出解決方

3、案。日常工作包括監控和維護兩個部分。此文檔中關于數據庫的運行命令示例主要針對于 ORACLE 數據庫，但對于 SYBASE 數據庫同樣有參考價值，只要換用相對應的語句即可。數據庫監控1數據庫監控數據庫監控數據庫監控工作內容數據庫監控工作內容制定和改進監控方案，編寫監控腳本。對于數據庫進行日常監測，提交記錄。根據監測結果進行分析、預測，提交相應的系統改進建議方案。數據庫監控工作步驟數據庫監控工作步驟1.1.1查看數據庫日志查看數據庫日志數據庫的日志上會有大量對于管理員有用的信息。ORACLE 的 Alert 日志紀錄了數據庫系統所報的系統級錯誤信息，以及數據塊失效等嚴重錯誤信息。錯誤信息的產生，

4、會產生相應的跟蹤文件，通過查看警告日志和跟蹤文件可查找錯誤原因，對于發現的問題應及時解決和匯報。如：1.表空間是否滿，是否需要進行添加或者擴展。Alert 文件中會顯示有表塊無法擴展的提示。2.表的塊或者頁面是否損壞。 （往往這時 alert 文件中會顯示 ora-600 的錯誤。 ）3.數據庫是否進行了異常操作。 （如：drop tablespace 等等） 。實用命令：報警日志文件（alert.log 或 alrt.ora）記錄數據庫啟動，關閉和一些重要的出錯信息。數據庫管理員應該經常檢查這個文件，并對出現的問題作出即使的反應。可以通過以下 SQL 找到他的路徑 select value

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

6、，或通過參數文件獲得其路徑，或者 show parameter USER_DUMP_DEST。可以通過設置用戶跟蹤或 dump 命令來產生用戶跟蹤文件，一般在調試、優化、系統分析中有很大的作用。可在參數文件種用 SQL_TRACE=TRUE 打開該文件(對所有用戶)，也可用 alter session set sql_trace=true 打開當前會話，也可用 execute dbms_system.set_sql_trace_in_session(sid,serial#,true)打開指定會話。1.1.2檢查是否有失效的數據庫對象檢查是否有失效的數據庫對象主要關注索引，觸發器，存儲過程，函數

7、等等。如：查找 user_objects 數據字典，看其中是否有狀態為 invalid 的對象。判斷失效原因（如：視圖失效的原因有可能是由于創建視圖的基表被刪除等等） ，找出原因可進行對象重建或修復。實用命令：Select object_name,object_type From user_objects Where object_type=INVALID;.3 查看數據庫剩余空間查看數據庫剩余空間1.剩余空間不足時要擴展空間，一般的，當剩余空間小于 10時，要進行空間擴展。對于 ORACLE 數據庫，通過查找TABLESPACES相關的數據字典可以看到有用的信息。2.檢查數據

8、快速增長的表，通過對于DBA_SEGMENTS數據字典的監視可以找到，當過快增長時，協調開發人員，確定解決方案。1.1.4重點表檢查重點表檢查1.檢查系統核心業務表。因為這些表健康與否與日常業務的正常運行密切相關。重點檢查這些表的索引是否失效，表的統計信息是否及時更新，如：當這些表進行了大的數據裝載或者刪除操作之后。原則上需要檢查所有的表，只是由于上面這些表更關鍵，建議管理員給以更多的關注。2.重點檢查數據量超過百萬行的表，各地的情況可能不一樣，當數據超過百萬行之后，如果索引失效會導致表掃描，占用大量系統 IO，嚴重影響系統性能。1.1.5查看數據庫是否正常查看數據庫是否正常包括數據庫實例是否

9、正常工作、listener 是否工作正常，確保數據庫系統環境正常。數據庫連接是否正常、檢查是否有超出正常水平的連接數。如：平常 500 個，某天下午忽然達到 600 個。應記錄這種異常情況。分析產生這種情況的原因，如：在低版本的 ORACLE 中，很可能是一些其他異常的應用出錯后產生的死連接。.6 死鎖檢查死鎖檢查監控數據庫運行過程中，出現的阻塞，記錄現象，記錄產生阻塞的 SQL 語句，執行的用戶，發生時間，頻率，處理（殺掉、等待自然解鎖等） 。ORACLE 版本中的死鎖會在 alert文件中產生記錄，oracle 會自動解鎖（其實是選擇一個殺掉） 。對于死鎖的處理過程要進行記

10、錄。可以使用 OEM 工具或者查找相關的 V$視圖來確認產生阻塞的語句。1.1.7監控監控 SQL 語句的執行語句的執行查找效率低下的 SQL 語句，聯系協調開發人員，進行相關處理。可使用 ORACLE 提供的AWR 進行，也可使用 ORACLE 提供的 OEM 工具執行，或者自行編制的腳本等等。.8 操作系統級檢查操作系統級檢查運行 vmstat，sar,topas(AIX 系統)，glance(HP 系統)等命令檢查 CPU、內存、虛擬內存等的使用情況。運行 df,du,iostat 檢查磁盤使用情況運行 netstat 檢查網絡情況運行手工編制的監控腳本檢查。針對于操作系

11、統的不同，使用的命令也會有不同，請參考相應的操作系統文檔。建議使用man 命令察看相應的幫助信息。1.1.9其他其他每天查看晚間定時執行的數據庫信息收集作業和備份作業的日志輸出，確認都已正常完成。往往不能正常完成是由于如下的原因：請確認腳本是否變動（錯誤的修改造成等等） ，設備（主機，磁盤陣列，磁帶庫，網絡等等）是否正常，空間是否足夠等等。建議每天按業務峰值情況，對數據庫性能數據進行定時采集及分析。2數據庫維護數據庫維護數據庫維護工作內容數據庫維護工作內容包括維護、故障診斷、錯誤修復、備份恢復、歷史數據遷移等過程。數據庫維護工作事項數據庫維護工作事項2.1.1頁面修復頁面修復根據日常監控的結果

12、，進行頁面（或者數據庫壞塊）修復，如將表數據導出后重建表，然后導入數據。提交修復記錄。2.1.2數據庫對象重建數據庫對象重建根據數據庫監控的結果，重建失效的對象。如：索引、存儲過程、函數、視圖、觸發器等等。實用命令：Alter index rebuild online;2.1.3碎片回收（數據重組）碎片回收（數據重組）當某些數據庫運行一段時間后，表會產生碎片，影響數據庫的性能。可根據日常檢查的結果，運用工具或腳本對于數據庫空間進行重組或回收。由于 ORACLE 數據庫本身的原因，在進行了 DELETE 操作之后也不會使 HWM（High Water Mark高水位線）降低，因此不會釋放所占用的

13、空間，所以建議在進行了數據遷移之后將全庫進行 EXP，然后進行 IMP 操作，以釋放占用的空間。2.1.4刪除不用的數據刪除不用的數據此項工作要得到開發方、設計人員、以及相關人員的確認后，方可執行。2.1.5備份恢復備份恢復需要定期對于數據庫備份進行有效性檢測，定期進行數據恢復的演練操作。以防止萬一的數據庫事故時準備不足。數據庫需要采用在線的熱備份，不需要關閉數據庫進行，在備份的同時可以進行正常的數據庫的各種操作，滿足了 7*24 的系統的需要。數據庫的備份不能影響用戶對數據庫的訪問。目標需要在線熱備份多級增量備份并行備份，恢復減小所需要備份量備份，恢復使用簡單可參考如下的方案:1.每月做一個

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

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

16、本腳本常用的 SQL 腳本，在實施時可供數據庫管理員參考，在執行時，需要進行相應的修改。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_spaceGROUP BY tablespace_name2.表空間數據量情況顯示SELECT tablespace_name, max_bl

17、ocks, count_blocks, sum_free_blocks, 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 tablespace_name AS fs_ts_name, max(blocks) AS max_blocks, count(blocks) AS coun

18、t_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, a.next_extent, a.tablespace_nameFROM all_tables a,( SELECT tablespace_n

19、ame, max(bytes) as big_chunkFROM dba_free_spaceGROUP BY tablespace_name ) fWHERE f.tablespace_name = a.tablespace_nameAND a.next_extent f.big_chunk5.檢查已經存在的空間擴展SELECT count(*), segment_name, segment_type, dt.tablespace_nameFROM dba_tablespaces dt, dba_extents dxWHERE dt.tablespace_name = dx.tablespa

20、ce_nameAND dt.next_extent != dx.bytes AND dx.owner = &OWNERGROUP BY segment_name, segment_type, dt.tablespace_name6.檢查沒有主鍵的表SELECT table_nameFROM all_tablesWHERE owner = &OWNERMINUSSELECT table_nameFROM all_constraintsWHERE owner = &OWNERAND constraint_type = P7.檢查失效的主鍵SELECT owner, cons

21、traint_name, table_name, statusFROM all_constraintsWHERE owner = &OWNER AND status = DISABLED AND constraint_type = P8.重建索引，具體參數請根據實際情況進行修改SELECT alter index | index_name | rebuild , tablespace INDEXES storage ( initial 256 K next 256 K ) ; FROM all_indexesWHERE ( tablespace_name != INDEXESOR ne

22、xt_extent != ( 256 * 1024 )AND owner = &OWNER9.對比兩個實例的不同SELECT object_name, object_typeFROM user_objectsMINUSSELECT object_name, object_typeFROM user_objects&my_db_link10. 查看動態性能視圖Select * from V$FIXED_TABLE11. 查看約束select a.constraint_name, a.constraint_type,a.*from user_constraints awhere t

23、able_name=table_name;select constraint_name, column_name from user_cons_columns where table_name=table_name;12. 查看索引 user_indexes 包含索引的名字,user_ind_columns 包含索引的列.13. 查看數據庫啟動參數：show parameter para，v$parameter 提供當前會話信息，v$system_parameter 提供當前系統信息。其中 isses_modifiable，issys_modifiable表示是否允許動態修改。14. 查看進程

24、號：select p.spid, s.username from v$process p , v$session s where p.addr=s.paddr;15. 查看數據文件：select name, status from v$datafile;select * from dba_data_files;16. 查看數據文件狀態select d.file# f#, , d.status, h.status from v$datafile d, v$datafile_header h where d.file#=h.file#;17. 查看控制文件select name fro

25、m 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 from v$instance;19. 查看日志組：select groups, current_group#, sequence# from v$thread;select group

26、#, 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, target, status from v$archive_dest;22. 查看歸檔進程select * from v$archive_processes;23. 查

27、看正在備份的數據文件select * from v$backup;24. 查看需要恢復的文件select * from v$recover_file;25. 查看所有歸檔日志文件select * from v$archived_log;26. 查看恢復時要用到的日志文件select * from v$recovery_log;27. 查看 SGA 的結構Show sga;select * from v$sgastat;28. 提取 library cache 的命中率select gethitratio from v$librarycache where namespace=;29. 查看正在

28、運行的 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) “cache Misses”, sum(reloads)/sum(pins)from v$librarycache;31. 查看大匿名塊select sql_text from v

29、$sqlarea where command_type=47 and length(sql_text)500;32. 查看當前會話的 UGA 區select sum(value)|bytes “Total session memory” from v$mystat, v$statname where name=session uga memory and v$mystat.statistic#=v$statname.statistic#;33. 查看所有 MTS 用戶的 UGA 區：select sum(value)|bytes “Total session memory” from v$se

30、sstat, v$statname where name=session uga memory and v$sesstat.statistic#=v$statname.statistic#;34. 查看所有用戶使用的最大的 UGA 區：select sum(value)|bytes “Total session memory” from v$sesstat, v$statname where name=session uga memory max and v$sesstat.statistic#=v$statname.statistic#;35. 查看 high-water mark 以下的塊

31、數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=USERENV(SESSIONID) and io.sid=s.sid;37. 查看 Buffer pool 的命中率select name, 1-(physical_reads/(db_block_gets+cons

32、istent_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;select event, total_waits from v$system_event where event=buffer busy waits;buffer busy waits 可在兩種情況發生：1dirty queue

33、 已滿，2free list 競爭。39. 查看 free list 競爭發生在哪個 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 w.event=buffer busy waits and w.p1=s.header_file and w.p2=s.header_block;40. 查看全表掃描發生的次數select name, value from v$

34、sysstat where name like %table scan%;41. 查看大操作的執行情況select sid, serial#, opname, to_char(start_time, HH24:MI:SS) as start_t, (sofar/totalwork)*100 as percent_complete from v$session_longops;42. 查看數據文件的 I/Oselect phyrds, phywrts, from v$datafile d, v$filestat f where d.file#=f.file# order by d.

35、name;43. 查看空閑塊數少于 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 blocks+empty_blocks!=0;44. 查看 migration 和 chaininganalyze table table_name compute statistics;select num_rows, cha

36、in_cntfrom dba_tables where table_name=table_name;45. 查看表的統計信息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. 查看索引的統計信息analyze index index_name validate structure;select (del_lf_rows_len/lf_rows_len)*100 as index_usage from index_stats;4日常維護和問題管理日常維護和問題管理目