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

1、例行工作手冊數(shù)據(jù)庫維護工作手冊文檔編號：文檔名稱:編 寫：審 核:批 準：批準日期:22/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 碎片回收（

2、數(shù)據(jù)重組） 73.2.4 刪除不用的數(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ù)庫的運

3、行命令示例主要針對于ORACL散據(jù)庫，但對于SYBAS嗷據(jù)庫同樣有參考價值，只要換用相對應(yīng)的語句即可。數(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é)果進行分析、預測，提交相應(yīng)的系統(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)生相應(yīng)的跟蹤文件，通過查看警告日志和跟蹤文件可查找錯誤原因，對于發(fā)現(xiàn)的問題應(yīng)及時解決和匯報。如:1 .表空間是否滿，是否需要進行添

4、加或者擴展。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)?？梢酝ㄟ^以下SQL找到他的路徑select value fromv$parameter where upper(name) =BACKGROUND_DUMP_DEST,或通過參數(shù)文件獲得其路 徑，或者 s

5、how parameter BACKGROUND_DUMP_DEST 。 后臺跟蹤文件路徑與報警文件路徑一致，記載了系統(tǒng)后臺進程出錯時寫入的信息。 用戶跟蹤文件記載了用戶進程出錯時寫入的信息，一般不可能讀懂，可以通過ORACLE的TKPROF工具轉(zhuǎn)化為可以讀懂的格式。用戶跟蹤文件的路徑，你可以通過以下 SQL找到他的路徑selectvalue from v$parameter where upper(name) =USER_DUMP_DEST,或通過參數(shù)文件獲得其路 徑，或者 show parameter USER_DUMP_DEST 。可以通過設(shè)置用戶跟蹤或 dump命令來產(chǎn)生用戶跟蹤文件，

6、一般在調(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 檢查是否有失效的數(shù)據(jù)庫對象主要關(guān)注索引，觸發(fā)器，存儲過程，函數(shù)等等。如：查找 user_objects 數(shù)據(jù)字典，看 其中是否有狀態(tài)為invalid 的對象。判斷失效原因(如：視圖失效的原因有可能是由于創(chuàng)建 視圖的基表被刪

7、除等等)，找出原因可進行對象重建或修復。實用命令：Select object_name,object_typeFrom user_objectsINVALID;Where object_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è)務(wù)表。因為這些表健康與否與日常業(yè)

8、務(wù)的正常運行密切相關(guān)。重點檢查這些表的索引是否失效，表的統(tǒng)計信息是否及時更新，如：當這些表進行了大的數(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個。應(yīng)記錄這種異常情況。分析產(chǎn)生這種情況的原因，

9、如：在低版本的 ORACLE很可能是一些其他異常的應(yīng)用出錯后產(chǎn)生的死連接。2.1.6 死鎖檢查監(jiān)控數(shù)據(jù)庫運行過程中，出現(xiàn)的阻塞，記錄現(xiàn)象，記錄產(chǎn)生阻塞的 SQL語句，執(zhí)行的用戶，發(fā)生時間，頻率，處理（殺掉、等待自然解鎖等）。ORACL版本中的死鎖會在 alert文件中產(chǎn)生記錄，oracle會自動解鎖（其實是選擇一個殺掉）。對于死鎖的處理過程要進行記錄?？梢允褂?OEMLL具或者查找相關(guān)的 V雌圖來確認產(chǎn)生阻塞的語句。2.1.7 監(jiān)控SQL語句的執(zhí)行查找效率低下的 SQL語句，聯(lián)系協(xié)調(diào)開發(fā)人員，進行相關(guān)處理?？墒褂肙RACL睫供的AWRS行，也可使用 ORACL提供的OEME具執(zhí)行，或者自行編制

10、的腳本等等。2.1.8 操作系統(tǒng)級檢查運行vmstat , sar,topas（AIX 系統(tǒng)），glance（HP系統(tǒng)）等命令檢查 CPU內(nèi)存、虛擬內(nèi)存等 的使用情況。運行df,du,iostat檢查磁盤使用情況運行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ò)等等）是否正

11、常，空間是否足夠等等。建議每天按業(yè)務(wù)峰值情況，對數(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ù)。提交修復記錄。3.1.2 數(shù)據(jù)庫對象重建根據(jù)數(shù)據(jù)庫監(jiān)控的結(jié)果，重建失效的對象。如：索引、存儲過程、函數(shù)、視圖、觸發(fā)器等等。實用命令：Alter index rebuild online;3.1.3 碎片回收（數(shù)據(jù)重組）當某些數(shù)據(jù)庫運行一段時間后，表會產(chǎn)生碎片，影響數(shù)據(jù)庫的性能。 可根據(jù)日常檢查的OR

12、ACL散據(jù)庫本身的原因,高水位線）降低，因此不會釋EXP,然后進行IMP操作，以結(jié)果，運用工具或腳本對于數(shù)據(jù)庫空間進行重組或回收。由于 在進彳T了 DELETEB作之后也不會使 HWM High Water Mark 放所占用的空間，所以建議在進行了數(shù)據(jù)遷移之后將全庫進行 釋放占用的空間。3.1.4 刪除不用的數(shù)據(jù)此項工作要得到開發(fā)方、設(shè)計人員、以及相關(guān)人員的確認后，方可執(zhí)行。3.1.5 備份恢復需要定期對于數(shù)據(jù)庫備份進行有效性檢測，定期進行數(shù)據(jù)恢復的演練操作。以防止萬一的數(shù)據(jù)庫事故時準備不足。數(shù)據(jù)庫需要采用在線的熱備份，不需要關(guān)閉數(shù)據(jù)庫進行，在備份的同時可以進行正常的 數(shù)據(jù)庫的各種操作，滿足

13、了7*24的系統(tǒng)的需要。數(shù)據(jù)庫的備份不能影響用戶對數(shù)據(jù)庫的訪問。目標需要在線熱備份多級增量備份并行備份，恢復減小所需要備份量備份，恢復使用簡單可參考如下的方案：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)安裝時的

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

15、不同特點進行相應(yīng)的維護操作。具體情況請參見ORACL改檔或者訪問 metalink 。4數(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_spaceGROUP BY table

16、space_name2 .表空間數(shù)據(jù)量情況顯示SELECT tablespace_name, max_blocks, 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_blocks FROM dba_data_filesGROUP BY tablespace_name),(SELECT tablespace_name AS fs_ts_n

17、ame,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, a.next_extent, a.table

18、space_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_name FROM dba_tablespaces dt, d

19、ba_extents dxWHERE dt.tablespace_name = dx.tablespace_name AND dt.next_extent != dx.bytes AND dx.owner = &OWNER GROUP BY segment_name, segment_type, dt.tablespace_name6 .檢查沒有主鍵的表SELECT table_nameFROM all_tablesWHERE owner = &OWNERMINUSSELECT table_nameFROM all_constraintsWHERE owner = &OWNERAND cons

20、traint_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 next 256 K );FROM all_indexesWH

21、ERE ( 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.constraint_name, a.constraint_type,a.*

22、from user_constraints awhere table_name=table_name;select constraint_name, column_namefrom user_cons_columns where table_name=table_name;12 .查看索引user_indexes包含索引的名字，user_ind_columns包含索引的列.13 .查看數(shù)據(jù)庫啟動參數(shù)：show parameter para , v$parameter 提供當前會話信息，v$system_parameter 提供當前系統(tǒng)信,官。其中 isses_modifiable , issy

23、s_modifiable 表示是否允許動態(tài)修改。14 .查看進程號 select p.spid, s.usernamefrom v$process p , v$session s where p.addr=s.paddr;15 .查看數(shù)據(jù)文件：select name, statusfrom v$datafile;select *from dba_data_files;16 .查看數(shù)據(jù)文件狀態(tài)select d.file# f#, , d.status, h.status from v$datafile d, v$datafile_header hwhere d.file#=h.fil

24、e#;17 .查看控制文件select namefrom v$controlfile;select type, record_size, records_total, records_used from v$controlfile_record_section where type= DATAFILE ；18 .查看是否歸檔模式：archive log listselect name, log_modefrom v$database;select archiverfrom v$instance;19 .查看日志組：select groups, current_group#, sequence#

25、from v$thread;select group#, sequence#, bytes , members, statusfrom v$log;select *from v$logfile;其中status為空表示正常。20 .查看 large poolselect *from v$sgastatwhere pool= large pool ;21 .查看歸檔位置show parameter archiveselect destination, binding, target, status from v$archive_dest;22 .查看歸檔進程select *from v$arch

26、ive_processes;23 .查看正在備份的數(shù)據(jù)文件select *from v$backup;24 .查看需要恢復的文件select *from v$recover_file;25 .查看所有歸檔日志文件select *from v$archived_log;26 .查看恢復時要用到的日志文件select *from v$recovery_log;27 .查看SGA勺結(jié)構(gòu)Show sga;select * from v$sgastat;28 .提取library cache的命中率select gethitratio from v$librarycache where namespac

27、e=;29 .查看正在運行的SQL語句select sql_text, users_executing, executions, loads from v$sqlarea;select *from v$sqltext where sql_text= select * from emp% ;Misses30 . 查看 library cache reload 情況：select sum(pins) aExecutions ” , sum(reloads) “cache sum(reloads)/sum(pins) from v$librarycache;31 .查看大匿名塊select sql_

28、text from v$sqlarea where command_type=47and length(sql_text)500;32 .查看當前會話的 UGAJxselect sum(value)| bytesTotal session memory ”from v$mystat, v$statname where name- session uga memory and v$mystat.statistic#=v$statname.statistic#;33 .查看所有MTS用戶的UGALselect sum(value)| bytesTotal session memory ”from

29、v$sesstat, v$statname where name= session uga memory and v$sesstat.statistic#=v$statname.statistic#;34 .查看所有用戶使用的最大的UGAX:select sum(value)| bytesTotal session memory ”from v$sesstat, v$statname where name= session uga memory max and v$sesstat.statistic#=v$statname.statistic#;35 .查看high-water mark 以下

30、的塊數(shù)select table_name, blocksfrom dba_tableswhere table_name= table_name；36 .查看會話的I/O :select io.block_gets, io.consistent_gets, io.physical_reads from v$sess_io io, v$session swhere s.aud sid=USERENV(SESSIONID ) and io.sid=s.sid;37 .查看Buffer pool 的命中率select name, 1- (physical_reads/(db_block_gets+co

31、nsistent_gets)“HIT_RATIOfrom sys.v$buffer_pool_statisticswhere db_block_gets+consistent_gets0;38 .查看free list 的競爭select class, count, timefrom v$waitstatwhere class= segment header；select event, total_waitsfrom v$system_eventwhere event= buffer busy waits ；buffer busy waits 可在兩種情況發(fā)生：1dirty queue 已滿，

32、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 w.event= buffer busy waits and w.p1=s.header_fileand w.p2=s.header_block;40 .查看全表掃描發(fā)生的次數(shù)select name, valuefrom v$sysstatw

33、here 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_completefrom v$session_longops;42 .查看數(shù)據(jù)文件的I/Oempty_blocks 其select phyrds, phywrts, from v$datafile d, v$filestat f where d.file#=f.file# order by

34、 ;43 .查看空閑塊數(shù)少于10%勺segment(blocks 在high-water mark 以下,上)select owner, table_name, blocks, empty_blocks from dba_tableswhere empty_blocks/(blocks+empty_blocks)0.1 and blocks+empty_blocks!=0;44 .查看 migration 和 chaininganalyze table table_name compute statistics;select num_rows, chain_cntfrom dba_

35、tableswhere 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_usa