数据库维护工作手册

1、例行工作手册数据库维护工作手册文档编号：文档名称:编 写：审 核:批 准：批准日期:22/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 碎片回收（

2、数据重组） 73.2.4 删除不用的数据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概述数据库的日常监控是使管理员及时了解系统异常的手段。大部分情况下，系统总是正常运行的。只有对正常情况的充分了解， 才能通过对比正常情况发现异常情况。 对于数据库的 日常监控要有记录，文字记录或者电子文档保存。 对于数据库异常进行分析， 提出解决方案。日常工作包括监控和维护两个部分。此文档中关于数据库的运

3、行命令示例主要针对于ORACL散据库，但对于SYBAS嗷据库同样有参考价值，只要换用相对应的语句即可。数据库监控2数据库监控数据库监控工作内容制定和改进监控方案，编写监控脚本。对于数据库进行日常监测，提交记录。根据监测结果进行分析、预测，提交相应的系统改进建议方案。数据库监控工作步骤2.1.1 查看数据库日志数据库的日志上会有大量对于管理员有用的信息。ORACLE勺Alert日志纪录了数据库系统所报的系统级错误信息，以及数据块失效等严重错误信息。错误信息的产生，会产生相应的跟踪文件，通过查看警告日志和跟踪文件可查找错误原因，对于发现的问题应及时解决和汇报。如:1 .表空间是否满，是否需要进行添

4、加或者扩展。Alert文件中会显示有表块无法扩展的提示。2 .表的块或者页面是否损坏。(往往这时alert文件中会显示ora-600的错误。)3 .数据库是否进行了异常操作。(如：drop tablespace等等)。实用命令： 报警日志文件(alert.log 或 alrt.ora )记录数据库启动，关闭和一些重要的出错信息。数据库管理员应该经常检查这个文件， 并对出现的问题作出即使的反应。可以通过以下SQL找到他的路径select value fromv$parameter where upper(name) =BACKGROUND_DUMP_DEST,或通过参数文件获得其路 径，或者 s

5、how parameter BACKGROUND_DUMP_DEST 。 后台跟踪文件路径与报警文件路径一致，记载了系统后台进程出错时写入的信息。 用户跟踪文件记载了用户进程出错时写入的信息，一般不可能读懂，可以通过ORACLE的TKPROF工具转化为可以读懂的格式。用户跟踪文件的路径，你可以通过以下 SQL找到他的路径selectvalue from v$parameter where upper(name) =USER_DUMP_DEST,或通过参数文件获得其路 径，或者 show parameter USER_DUMP_DEST 。可以通过设置用户跟踪或 dump命令来产生用户跟踪文件，

6、一般在调试、优化、系统分 析中有很大的作用。可在参数文件种用 SQL_TRACE=TRUE 打开该文件(对所有用户)，也可用alter session set sql_trace=true 打 开 当 前 会 话， 也 可 用 execute dbms_system.set_sql_trace_in_session(sid,serial#,true)打开指定会话。2.1.2 检查是否有失效的数据库对象主要关注索引，触发器，存储过程，函数等等。如：查找 user_objects 数据字典，看 其中是否有状态为invalid 的对象。判断失效原因(如：视图失效的原因有可能是由于创建 视图的基表被删

7、除等等)，找出原因可进行对象重建或修复。实用命令：Select object_name,object_typeFrom user_objectsINVALID;Where object_type=2.1.3 查看数据库剩余空间1 .剩余空间不足时要扩展空间，一般的，当剩余空间小于10%时，要进行空间扩展。对于ORACLE数据库，通过查找 TABLESPACES相关的数据字典可以看到有用的信 息。2 .检查数据快速增长的表， 通过对于DBA _SEGMENTS数据字典的监视可以找到，当过快增长时，协调开发人员，确定解决方案。2.1.4重点表检查1. 检查系统核心业务表。因为这些表健康与否与日常业

8、务的正常运行密切相关。重点检查这些表的索引是否失效，表的统计信息是否及时更新，如：当这些表进行了大的数据装载或者删 除操作之后。原则上需要检查所有的表，只是由于上面这些表更关键，建议管理员给以更多的关注。2.重点检查数据量超过百万行的表，各地的情况可能不一样，当数据超过百万行之后， 如果索引失效会导致表扫描，占用大量系统IO,严重影响系统性能。2.1.5 查看数据库是否正常包括数据库实例是否正常工作、listener是否工作正常，确保数据库系统环境正常。数据库连接是否正常、检查是否有超出正常水平的连接数。如：平常500个，某天下午忽然达到 600个。应记录这种异常情况。分析产生这种情况的原因，

9、如：在低版本的 ORACLE很可能是一些其他异常的应用出错后产生的死连接。2.1.6 死锁检查监控数据库运行过程中，出现的阻塞，记录现象，记录产生阻塞的 SQL语句，执行的用户，发生时间，频率，处理（杀掉、等待自然解锁等）。ORACL版本中的死锁会在 alert文件中产生记录，oracle会自动解锁（其实是选择一个杀掉）。对于死锁的处理过程要进行记录。可以使用 OEMLL具或者查找相关的 V雌图来确认产生阻塞的语句。2.1.7 监控SQL语句的执行查找效率低下的 SQL语句，联系协调开发人员，进行相关处理。可使用ORACL睫供的AWRS行，也可使用 ORACL提供的OEME具执行，或者自行编制

10、的脚本等等。2.1.8 操作系统级检查运行vmstat , sar,topas（AIX 系统），glance（HP系统）等命令检查 CPU内存、虚拟内存等 的使用情况。运行df,du,iostat检查磁盘使用情况运行netstat 检查网络情况运行手工编制的监控脚本检查。针对于操作系统的不同，使用的命令也会有不同，请参考相应的操作系统文档。建议使用 man命令察看相应的帮助信息。2.1.9 其他每天查看晚间定时执行的数据库信息收集作业和备份作业的日志输出，确认都已正常完成。往往不能正常完成是由于如下的原因：请确认脚本是否变动（错误的修改造成等等），设备（主机，磁盘阵列，磁带库，网络等等）是否正

11、常，空间是否足够等等。建议每天按业务峰值情况，对数据库性能数据进行定时采集及分析。3数据库维护数据库维护工作内容包括维护、故障诊断、错误修复、备份恢复、历史数据迁移等过程。数据库维护工作事项3.1.1 页面修复根据日常监控的结果，进行页面（或者数据库坏块）修复，如将表数据导出后重建表,然后导入数据。提交修复记录。3.1.2 数据库对象重建根据数据库监控的结果，重建失效的对象。如：索引、存储过程、函数、视图、触发器等等。实用命令：Alter index rebuild online;3.1.3 碎片回收（数据重组）当某些数据库运行一段时间后，表会产生碎片，影响数据库的性能。 可根据日常检查的OR

12、ACL散据库本身的原因,高水位线）降低，因此不会释EXP,然后进行IMP操作，以结果，运用工具或脚本对于数据库空间进行重组或回收。由于 在进彳T了 DELETEB作之后也不会使 HWM High Water Mark 放所占用的空间，所以建议在进行了数据迁移之后将全库进行 释放占用的空间。3.1.4 删除不用的数据此项工作要得到开发方、设计人员、以及相关人员的确认后，方可执行。3.1.5 备份恢复需要定期对于数据库备份进行有效性检测，定期进行数据恢复的演练操作。以防止万一的数据库事故时准备不足。数据库需要采用在线的热备份，不需要关闭数据库进行，在备份的同时可以进行正常的 数据库的各种操作，满足

13、了7*24的系统的需要。数据库的备份不能影响用户对数据库的访问。目标需要在线热备份多级增量备份并行备份，恢复减小所需要备份量备份，恢复使用简单可参考如下的方案：1 .每月做一个数据库的全备份（包含只读表空间）2 .每星期做一次零级备份（不包含只读表空间）3 .每个星期三做一次一级备份4 .每天做一个二级备份5 .任何表空间改成只读状态后做一个该表空间的备份。6 .当需要时（如四个小时归档文件系统就要接近满了）备份归档文件。3.1.6 历史数据迁移定期进行历史数据迁移，减少生产数据库的压力。3.1.7 定期修改密码包括SYS, SYSTEM 等用户。3.1.8 删除掉不必要的用户对于系统安装时的

14、，M示用户，如： hr, scott等。建议每周定期清理和备份一周所产生的Alert日志、跟踪文件和 dump文件。分别位于$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 其他针对不同的数据库版本的

15、不同特点进行相应的维护操作。具体情况请参见ORACL改档或者访问 metalink 。4数据库管理常用SQL脚本常用的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 table

16、space_name2 .表空间数据量情况显示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 . 检查已经存在的空间扩展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 .重建索引，具体参数请根据实际情况进行修改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 .查看动态性能视图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 .查看数据库启动参数：show parameter para , v$parameter 提供当前会话信息，v$system_parameter 提供当前系统信,官。其中 isses_modifiable , issy

23、s_modifiable 表示是否允许动态修改。14 .查看进程号 select p.spid, s.usernamefrom v$process p , v$session s where p.addr=s.paddr;15 .查看数据文件：select name, statusfrom v$datafile;select *from dba_data_files;16 .查看数据文件状态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 .查看正在备份的数据文件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 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、的块数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 可在两种情况发生：1dirty queue 已满，

32、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_fileand w.p2=s.header_block;40 .查看全表扫描发生的次数select name, valuefrom v$sysstatw

33、here name like %table scan%；41 .查看大操作的执行情况select sid, serial#, opname, to_char(start_time,HH24:MI:SS ) as start _t,(sofar/totalwork)*100 as percent_completefrom v$session_longops;42 .查看数据文件的I/Oempty_blocks 其select phyrds, phywrts, from v$datafile d, v$filestat f where d.file#=f.file# order by

34、 ;43 .查看空闲块数少于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 .查看表的统计信息 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_usa