数据库维护工作手册

1、例行工作手册1/20数据库维护工作手册数据库维护工作手册文档编号：文档名称：编 写：审 核：批 准:批准日期：例行工作手册2/20目目 录录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页

2、面修复.73.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/201概述概述数据库的日常监控是使管理员及时了解系统异常的手段。大部分情况下，系统总是正常运行的。只有对正常情况的充分了解，才能通过对比正常情况发现异常情况。对于数据库的日常监控要有记录，文字记

3、录或者电子文档保存。对于数据库异常进行分析，提出解决方案。日常工作包括监控和维护两个部分。此文档中关于数据库的运行命令示例主要针对于 ORACLE 数据库，但对于 SYBASE 数据库同样有参考价值，只要换用相对应的语句即可。数据库监控2数据库监控数据库监控数据库监控工作内容数据库监控工作内容制定和改进监控方案，编写监控脚本。对于数据库进行日常监测，提交记录。根据监测结果进行分析、预测，提交相应的系统改进建议方案。数据库监控工作步骤数据库监控工作步骤2.1.1查看数据库日志查看数据库日志数据库的日志上会有大量对于管理员有用的信息。ORACLE 的 Alert 日志纪录了数据库系统所报的系统级错

4、误信息，以及数据块失效等严重错误信息。错误信息的产生，会产生相应的跟踪文件，通过查看警告日志和跟踪文件可查找错误原因，对于发现的问题应及时解决和汇报。如：1.表空间是否满，是否需要进行添加或者扩展。Alert 文件中会显示有表块无法扩展的提示。2.表的块或者页面是否损坏。 （往往这时 alert 文件中会显示 ora-600 的错误。 ）3.数据库是否进行了异常操作。 （如：drop tablespace 等等） 。实用命令：报警日志文件（alert.log 或 alrt.ora）记录数据库启动，关闭和一些重要的出错信息。数据库管理员应该经常检查这个文件，并对出现的问题作出即使的反应。可以通过

5、以下 SQL 找到他的路径 select value from v$parameter where upper(name) =BACKGROUND_DUMP_DEST，或通过参数文件获得其路径，或者 show parameter BACKGROUND_DUMP_DEST。后台跟踪文件路径与报警文件路径一致，记载了系统后台进程出错时写入的信息。例行工作手册4/20用户跟踪文件记载了用户进程出错时写入的信息，一般不可能读懂，可以通过 ORACLE 的TKPROF 工具转化为可以读懂的格式。用户跟踪文件的路径，你可以通过以下 SQL 找到他的路径 select value from v$parame

6、ter where upper(name) =USER_DUMP_DEST，或通过参数文件获得其路径，或者 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)打开指定会话。2.1.2检查是否有失效

7、的数据库对象检查是否有失效的数据库对象主要关注索引，触发器，存储过程，函数等等。如：查找 user_objects 数据字典，看其中是否有状态为 invalid 的对象。判断失效原因（如：视图失效的原因有可能是由于创建视图的基表被删除等等） ，找出原因可进行对象重建或修复。实用命令：Select object_name,object_type From user_objects Where object_type=INVALID;.3 查看数据库剩余空间查看数据库剩余空间1.剩余空间不足时要扩展空间，一般的，当剩余空间小于 10时，要进行空间扩展。对于 ORACLE 数据库，通

8、过查找TABLESPACES相关的数据字典可以看到有用的信息。2.检查数据快速增长的表，通过对于DBA_SEGMENTS数据字典的监视可以找到，当过快增长时，协调开发人员，确定解决方案。2.1.4重点表检查重点表检查1.检查系统核心业务表。因为这些表健康与否与日常业务的正常运行密切相关。重点检查这些表的索引是否失效，表的统计信息是否及时更新，如：当这些表进行了大的数据装载或者删除操作之后。原则上需要检查所有的表，只是由于上面这些表更关键，建议管理员给以更多的关注。2.重点检查数据量超过百万行的表，各地的情况可能不一样，当数据超过百万行之后，如果索引失效会导致表扫描，占用大量系统 IO，严重影响

9、系统性能。2.1.5查看数据库是否正常查看数据库是否正常包括数据库实例是否正常工作、listener 是否工作正常，确保数据库系统环境正常。数据库连接是否正常、检查是否有超出正常水平的连接数。例行工作手册5/20如：平常 500 个，某天下午忽然达到 600 个。应记录这种异常情况。分析产生这种情况的原因，如：在低版本的 ORACLE 中，很可能是一些其他异常的应用出错后产生的死连接。.6 死锁检查死锁检查监控数据库运行过程中，出现的阻塞，记录现象，记录产生阻塞的 SQL 语句，执行的用户，发生时间，频率，处理（杀掉、等待自然解锁等） 。ORACLE 版本中的死锁会在 aler

10、t文件中产生记录，oracle 会自动解锁（其实是选择一个杀掉） 。对于死锁的处理过程要进行记录。可以使用 OEM 工具或者查找相关的 V$视图来确认产生阻塞的语句。2.1.7监控监控 SQL 语句的执行语句的执行查找效率低下的 SQL 语句，联系协调开发人员，进行相关处理。可使用 ORACLE 提供的AWR 进行，也可使用 ORACLE 提供的 OEM 工具执行，或者自行编制的脚本等等。.8 操作系统级检查操作系统级检查运行 vmstat，sar,topas(AIX 系统)，glance(HP 系统)等命令检查 CPU、内存、虚拟内存等的使用情况。运行 df,du,iosta

11、t 检查磁盘使用情况运行 netstat 检查网络情况运行手工编制的监控脚本检查。针对于操作系统的不同，使用的命令也会有不同，请参考相应的操作系统文档。建议使用man 命令察看相应的帮助信息。2.1.9其他其他每天查看晚间定时执行的数据库信息收集作业和备份作业的日志输出，确认都已正常完成。往往不能正常完成是由于如下的原因：请确认脚本是否变动（错误的修改造成等等） ，设备（主机，磁盘阵列，磁带库，网络等等）是否正常，空间是否足够等等。建议每天按业务峰值情况，对数据库性能数据进行定时采集及分析。3数据库维护数据库维护数据库维护工作内容数据库维护工作内容包括维护、故障诊断、错误修复、备份恢复、历史数

12、据迁移等过程。例行工作手册6/20数据库维护工作事项数据库维护工作事项3.1.1页面修复页面修复根据日常监控的结果，进行页面（或者数据库坏块）修复，如将表数据导出后重建表，然后导入数据。提交修复记录。3.1.2数据库对象重建数据库对象重建根据数据库监控的结果，重建失效的对象。如：索引、存储过程、函数、视图、触发器等等。实用命令：Alter index rebuild online;3.1.3碎片回收（数据重组）碎片回收（数据重组）当某些数据库运行一段时间后，表会产生碎片，影响数据库的性能。可根据日常检查的结果，运用工具或脚本对于数据库空间进行重组或回收。由于 ORACLE 数据库本身的原因，在

13、进行了 DELETE 操作之后也不会使 HWM（High Water Mark高水位线）降低，因此不会释放所占用的空间，所以建议在进行了数据迁移之后将全库进行 EXP，然后进行 IMP 操作，以释放占用的空间。3.1.4删除不用的数据删除不用的数据此项工作要得到开发方、设计人员、以及相关人员的确认后，方可执行。3.1.5备份恢复备份恢复需要定期对于数据库备份进行有效性检测，定期进行数据恢复的演练操作。以防止万一的数据库事故时准备不足。数据库需要采用在线的热备份，不需要关闭数据库进行，在备份的同时可以进行正常的数据库的各种操作，满足了 7*24 的系统的需要。数据库的备份不能影响用户对数据库的访

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

15、r，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其他其他针对不同的数据库版本

16、的不同特点进行相应的维护操作。具体情况请参见 ORACLE 文档或者访问 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_spac

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

24、meter 提供当前系统信息。其中 isses_modifiable，issys_modifiable表示是否允许动态修改。14. 查看进程号：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

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. 查看正在备份的数据文件select * from v$backup;24. 查看需要恢复的文件select * from v$recover_file;25. 查看所有归档日志文件例行工作手册12/20select * from v$archived_log;26. 查看恢复时要用到的日志文件select * from v$recovery_log;27. 查看 SGA 的结构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 区例行工作手册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 区：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 区：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 以下的块数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 可在两种情况发生：1dirty queue 已满，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

34、w.event=buffer busy waits and w.p1=s.header_file and w.p2=s.header_block;40. 查看全表扫描发生的次数select name, value from v$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_longo

35、ps;42. 查看数据文件的 I/Oselect phyrds, phywrts, from v$datafile d, v$filestat f where d.file#=f.file# order by ;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

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. 查看表的统计信息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;例行工作手册16/205日常维护和问题管理日常维护和问题管理目的目的作为一名数据库管理员，数据库应用的正常稳定运行