数据库维护工作手册.docVIP

word 编辑文档 数据库维护工作手册数据库维护工作手册 文档编号 文档名称 编 写 审 核 批 准 批准日期 word 编辑文档 目目 录录 1概述概述 4 2数据库监控数据库监控 4 2 1 数据库监控工作内容 4 2 2 数据库监控工作步骤 4 2 2 1查看数据库日志 4 2 2 2检查是否有失效的数据库对象 5 2 2 3查看数据库剩余空间 5 2 2 4重点表检查 5 2 2 5查看数据库是否正常 6 2 2 6死锁检查 6 2 2 7监控 SQL 语句的执行 6 2 2 8操作系统级检查 6 2 2 9其他 6 3数据库维护数据库维护 7 3 1 数据库维护工作内容 7 3 2 数据库维护工作事项 7 3 2 1页面修复 7 3 2 2数据库对象重建 7 3 2 3碎片回收 数据重组 7 3 2 4删除不用的数据 7 3 2 5备份恢复 7 3 2 6历史数据迁移 8 3 2 7定期修改密码 8 3 2 8删除掉不必要的用户 8 3 2 9其他 8 4数据库管理常用数据库管理常用 SQL 脚本脚本 9 5日常维护和问题管理日常维护和问题管理 17 5 1 目的 17 5 2 例行工作建议 17 5 3 相关填表说明 17 word 编辑文档 1概述概述 数据库的日常监控是使管理员及时了解系统异常的手段 大部分情况下 系统总是正 常运行的 只有对正常情况的充分了解 才能通过对比正常情况发现异常情况 对于数据 库的日常监控要有记录 文字记录或者电子文档保存 对于数据库异常进行分析 提出解 决方案 日常工作包括监控和维护两个部分 此文档中关于数据库的运行命令示例主要针对于 ORACLE 数据库 但对于 SYBASE 数据 库同样有参考价值 只要换用相对应的语句即可 数据库监控 2数据库监控数据库监控 数据库监控工作内容数据库监控工作内容 制定和改进监控方案 编写监控脚本 对于数据库进行日常监测 提交记录 根据监测结果进行分析 预测 提交相应的系统改进建议方案 数据库监控工作步骤数据库监控工作步骤 2 1 1查看数据库日志查看数据库日志 数据库的日志上会有大量对于管理员有用的信息 ORACLE 的 Alert 日志纪录了数据库 系统所报的系统级错误信息 以及数据块失效等严重错误信息 错误信息的产生 会产生 相应的跟踪文件 通过查看警告日志和跟踪文件可查找错误原因 对于发现的问题应及时 解决和汇报 如 1 表空间是否满 是否需要进行添加或者扩展 Alert 文件中会显示有表块无法扩 展的提示 2 表的块或者页面是否损坏 往往这时 alert 文件中会显示 ora 600 的错误 3 数据库是否进行了异常操作 如 drop tablespace 等等 实用命令 报警日志文件 alert log 或 alrt ora 记录数据库启动 关闭和一些重要的出错信息 数据库管理员应该经常检查这个文件 并对出现的问题作出即使的反应 可以通过以下 SQL 找到他的路径 select value from v parameter where upper name BACKGROUND DUMP DEST 或通过参数文件获得其 路径 或者 show parameter BACKGROUND DUMP DEST 后台跟踪文件 路径与报警文件路径一致 记载了系统后台进程出错时写入的信息 word 编辑文档 用户跟踪文件 记载了用户进程出错时写入的信息 一般不可能读懂 可以通过 ORACLE 的 TKPROF 工具转化为可以读懂的格式 用户跟踪文件的路径 你可以通过以下 SQL 找到他 的路径 select value from v parameter 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检查是否有失效的数据库对象检查是否有失效的数据库对象 主要关注索引 触发器 存储过程 函数等等 如 查找 user objects 数据字典 看 其中是否有状态为 invalid 的对象 判断失效原因 如 视图失效的原因有可能是由于创 建视图的基表被删除等等 找出原因可进行对象重建或修复 实用命令 Select object name object type From user objects Where object type INVALID 2 1 32 1 3 查看数据库剩余空间查看数据库剩余空间 1 剩余空间不足时要扩展空间 一般的 当剩余空间小于 10 时 要进行空间扩展 对于 ORACLE 数据库 通过查找TABLESPACES相关的数据字典可以看到有用的信 息 2 检查数据快速增长的表 通过对于DBA SEGMENTS数据字典的监视可以找到 当 过快增长时 协调开发人员 确定解决方案 2 1 4重点表检查重点表检查 1 检查系统核心业务表 因为这些表健康与否与日常业务的正常运行密切相关 重点检查这些表的索 引是否失效 表的统计信息是否及时更新 如 当这些表进行了大的数据装载或 者删除操作之后 原则上需要检查所有的表 只是由于上面这些表更关键 建议 管理员给以更多的关注 2 重点检查数据量超过百万行的表 各地的情况可能不一样 当数据超过百万行之 后 如果索引失效会导致表扫描 占用大量系统 IO 严重影响系统性能 2 1 5查看数据库是否正常查看数据库是否正常 包括数据库实例是否正常工作 listener 是否工作正常 确保数据库系统环境正常 数 据库连接是否正常 检查是否有超出正常水平的连接数 word 编辑文档 如 平常 500 个 某天下午忽然达到 600 个 应记录这种异常情况 分析产生这种情况的 原因 如 在低版本的 ORACLE 中 很可能是一些其他异常的应用出错后产生的死连接 2 1 62 1 6 死锁检查死锁检查 监控数据库运行过程中 出现的阻塞 记录现象 记录产生阻塞的 SQL 语句 执行的 用户 发生时间 频率 处理 杀掉 等待自然解锁等 ORACLE 版本中的死锁会在 alert 文件中产生记录 oracle 会自动解锁 其实是选择一个杀掉 对于死锁的处理过程要进 行记录 可以使用 OEM 工具或者查找相关的 V 视图来确认产生阻塞的语句 2 1 7监控监控 SQL 语句的执行语句的执行 查找效率低下的 SQL 语句 联系协调开发人员 进行相关处理 可使用 ORACLE 提供的 AWR 进行 也可使用 ORACLE 提供的 OEM 工具执行 或者自行编制的脚本等等 2 1 82 1 8 操作系统级检查操作系统级检查 运行 vmstat sar topas AIX 系统 glance HP 系统 等命令检查 CPU 内存 虚拟内存等 的使用情况 运行 df du iostat 检查磁盘使用情况 运行 netstat 检查网络情况 运行手工编制的监控脚本检查 针对于操作系统的不同 使用的命令也会有不同 请参考相应的操作系统文档 建议使用 man 命令察看相应的帮助信息 2 1 9其他其他 每天查看晚间定时执行的数据库信息收集作业和备份作业的日志输出 确认都已正常 完成 往往不能正常完成是由于如下的原因 请确认脚本是否变动 错误的修改造成等等 设备 主机 磁盘阵列 磁带库 网络等等 是否正常 空间是否足够等等 建议每天按业务峰值情况 对数据库性能数据进行定时采集及分析 3数据库维护数据库维护 数据库维护工作内容数据库维护工作内容 包括维护 故障诊断 错误修复 备份恢复 历史数据迁移等过程 word 编辑文档 数据库维护工作事项数据库维护工作事项 3 1 1页面修复页面修复 根据日常监控的结果 进行页面 或者数据库坏块 修复 如将表数据导出后重建表 然后导入数据 提交修复记录 3 1 2数据库对象重建数据库对象重建 根据数据库监控的结果 重建失效的对象 如 索引 存储过程 函数 视图 触发器等 等 实用命令 Alter index rebuild online 3 1 3碎片回收 数据重组 碎片回收 数据重组 当某些数据库运行一段时间后 表会产生碎片 影响数据库的性能 可根据日常检查 的结果 运用工具或脚本对于数据库空间进行重组或回收 由于 ORACLE 数据库本身的原因 在进行了 DELETE 操作之后也不会使 HWM High Water Mark 高水位线 降低 因此不会 释放所占用的空间 所以建议在进行了数据迁移之后将全库进行 EXP 然后进行 IMP 操作 以释放占用的空间 3 1 4删除不用的数据删除不用的数据 此项工作要得到开发方 设计人员 以及相关人员的确认后 方可执行 3 1 5备份恢复备份恢复 需要定期对于数据库备份进行有效性检测 定期进行数据恢复的演练操作 以防止万 一的数据库事故时准备不足 数据库需要采用在线的热备份 不需要关闭数据库进行 在备份的同时可以进行正常 的数据库的各种操作 满足了 7 24 的系统的需要 数据库的备份不能影响用户对数据库的 访问 目标 需要在线热备份 多级增量备份 并行备份 恢复 减小所需要备份量 备份 恢复使用简单 可参考如下的方案 1 每月做一个数据库的全备份 包含只读表空间 2 每星期做一次零级备份 不包含只读表空间 3 每个星期三做一次一级备份 4 每天做一个二级备份 5 任何表空间改成只读状态后做一个该表空间的备份 word 编辑文档 6 当需要时 如四个小时归档文件系统就要接近满了 备份归档文件 3 1 6历史数据迁移历史数据迁移 定期进行历史数据迁移 减少生产数据库的压力 3 1 7定期修改密码定期修改密码 包括 SYS SYSTEM 等用户 3 1 8删除掉不必要的用户删除掉不必要的用户 对于系统安装时的演示用户 如 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其他其他 针对不同的数据库版本的不同特点进行相应的维护操作 具体情况请参见 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 chunks FROM dba free space GROUP BY tablespace name 2 表空间数据量情况显示 SELECT tablespace name max blocks count blocks sum free blocks word 编辑文档 to char 100 sum free blocks sum alloc blocks 99 99 AS pct free FROM SELECT tablespace name sum blocks AS sum alloc blocks FROM dba data files GROUP BY tablespace name SELECT tablespace name AS fs ts name max blocks AS max blocks count blocks AS count blocks sum blocks AS sum free blocks FROM dba free space GROUP BY tablespace name WHERE tablespace name fs ts name 3 表和索引分析 BEGIN dbms utility analyze schema END 4 检查空间情况 SELECT a table name a next extent a tablespace name FROM all tables a SELECT tablespace name max bytes as big chunk FROM dba free space GROUP BY tablespace name f WHERE f tablespace name a tablespace name AND a next extent f big chunk 5 检查已经存在的空间扩展 SELECT count segment name segment type dt tablespace name FROM dba tablespaces dt dba extents dx WHERE dt tablespace name dx tablespace name AND dt next extent dx bytes AND dx owner FROM all indexes WHERE tablespace name INDEXES OR next extent 256 1024 AND owner select constraint name column name from user cons columns where table name table name word 编辑文档 12 查看索引 user indexes 包含索引的名字 user ind columns 包含索引的列 13 查看数据库启动参数 show parameter para v parameter 提供当前会话信息 v system parameter 提供当前系统信息 其中 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 name d status h status from v datafile 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 list select name log mode from v database select archiver word 编辑文档 from v instance 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 pool select 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 查看正在备份的数据文件 select from v backup 24 查看需要恢复的文件 select from v recover file 25 查看所有归档日志文件 word 编辑文档 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 查看正在运行的 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 sqlarea where command type 47 and length sql text 500 32 查看当前会话的 UGA 区 word 编辑文档 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 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 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 USERENV 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 gets 0 38 查看 free list 的竞争 select class count time from v waitstat where class segment header word 编辑文档 select 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 state from dba segments s v session wait w where 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 longops 42 查看数据文件的 I O select phyrds phywrts d name from v datafile d v filestat f where d file f file order by d 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 和 chaining analyze table table name compute statistics word 编辑文档 select num rows chain cnt from 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 tables where 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 word 编辑文档 5日常维护和问题管理日常维护和问题管理 目的目的 作为一名数据库管理员 数据库应用的正常稳定运行是保障我们应用系统的 重要保证 而数据库的日常监控和维护工作至关重要 做好它的检查工作的文档管理也是 数据库管理的必不可少的工作之一 这有利于我们追踪问题和对数据库的版本控制非常重 要 例行工作建议例行工作建议 每天例行工作 1 确定数据库实例处于运行状态 2 检查 ALERT 日志 3 确定数据库成功备份 4 确定数据库归档到磁带 5 确定