DBA职责及健康检查方法.PPT

1、中国工商银行软件开发中心,DBA 职责及健康检查方法,2018/10/15,中国工商银行软件开发中心,目 的,本课程将介绍据库管理员（DBA）日常维护工作的内容和具体操作方法。各级数据库管理员应遵照本课程的内容，对生产系统数据库进行日常的维护与管理，以确保生产系统安全、稳定运行。 对于生产系统中遇到的非正常状况，不在本课程涵盖的范围内。,2018/10/15,中国工商银行软件开发中心,目 录,DBA职责一、日维护过程二、周维护过程三、月维护过程健康检查,2018/10/15,中国工商银行软件开发中心,日维护过程,方法一： 使用非SYS（如system）用户登陆到所有数据库或例程，如果成功则表示

2、实例已启动。 方法二： $ps ef | grep ora oracle 999 1 0 10:54:35 ? 0:00 ora_reco_testA oracle 1001 1 0 10:54:35 ? 0:00 ora_arc0_testA oracle 989 1 0 10:54:34 ? 0:00 ora_pmon_testA oracle 997 1 0 10:54:35 ? 0:00 ora_smon_testA oracle 995 1 0 10:54:35 ? 0:00 ora_ckpt_testA oracle 993 1 0 10:54:35 ? 0:00 ora_lgwr

3、_testA oracle 991 1 0 10:54:35 ? 0:00 ora_dbw0_testA 如有返回红字的进程则表示实例已启动（“testA”为实例名）。,一、检查所有的实例是否已启动,2018/10/15,中国工商银行软件开发中心,日维护过程,登录服务器所在的操作系统；确定警告日志文件的所在目录路径； SQLshow parameter background_dump_dest 使用UNIX 中的TAIL或其它命令查看alert_.log文件中最近时期的警告日志； 把出现ORA错误的信息记录下来，并立刻定位错误原因。,二、检查警告日志文件里的新增信息,2018/10/15,中国

4、工商银行软件开发中心,日维护过程,无论采取哪种备份方式，都应在第一次备份时做一次恢复测试，从而验证备份方式的可用性。在以后的备份当中，这种恢复测试应定期进行，并且根据系统的数据重要性不同来确定测试频度。每天的备份工作完成后，需要检查备份的日志以确定备份操作是否成功。如果使用了第三方备份工具，除了检查数据库本身的备份日志外，还须检查第三方备份工具的备份日志，最终确定备份是否成功。 如果出现备份失败，应马上对失败原因进行定位，在解决问题后重新备份应用系统。,三、检查数据库备份的有效性,2018/10/15,中国工商银行软件开发中心,日维护过程,如利用第三方备份工具对数据库进行备份，还需检查备份的磁

5、带信息（包含磁带数量，编码等）与备份系统中记录的信息是否一致。,四、检查备份的磁带文档是否完整,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对合理的性能来说是否有足够的资源,1 . 检查表空间的使用情况（剩余空间）,对每一个数据库而言，应关注表空间中是否存在剩余的容量来满足应用的需要。最小的剩余空间至少要能满足每天数据的增长。如果表空间的剩余空间不够，应考虑马上对表空间进行扩展。 可运行脚本free.sql来检查表空间的剩余空间。,2018/10/15,中国工商银行软件开发中心,如果文件系统的剩余空间小于20%，则需删除不用的文件以释放空间或增加磁盘空间。 Unix平台

6、 $ df k Window平台 使用资源浏览器,日维护过程,五、检查对合理的性能来说是否有足够的资源,2 . 检查文件系统的使用情况（剩余空间）,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对合理的性能来说是否有足够的资源,3 . 检查回滚段,对于9i数据库，如果使用自动管理的回滚段，可跳过此步骤。 SQLshow parameter undo_management 返回值为AUTO ，则为自动管理的回滚段对于9i中未使用自动管理的回滚段，及9i以下版本的数据库，可运行脚本seg_wait_ratio.sql来查询回滚段争用的情况。如果大部分的wait_ratio都超

7、过10，并且这种现象保持了较长时间（3天以上），请与开发中心技术部联系。,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对合理的性能来说是否有足够的资源,4 . 检查状态不是“online”的数据文件,通过以下命令进行检查。 SQL select name from v$datafile where status=OFFLINE; 如果有非正常的数据文件 OFFLINE ，可以对此文件进行恢复。,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对合理的性能来说是否有足够的资源,5 . 确定可分配的连续空间范围,对于Local Managed表空间， 可

8、跳过此步骤。 SQLselect tablespace_name,segment_space_management from dba_tablespaces; 返回值为AUTO ，则为Local Managed表空间对于非Local Managed表空间，可运行脚本spacebound.sql来检查。 如果对象的NEXT_EXTENT 比表空间所能提供的最大连续空间还要大，将影响数据库的运行。如果我们查找到这个目标，可以用ALTER TABLESPACE COALESCE来调整表空间所能提供的最大空间范围，或另加数据文件。,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对

9、合理的性能来说是否有足够的资源,6 . 监控数据库服务器CPU、内存、网络等硬件资 源的使用情况,使用vmstat、iostat、glance、top等命令进行监控，如果发现资源使用不正常，如： CPU使用经常在90以上； 磁盘IO的busy长时间在90以上； 进程长时间耗用大量的cpu资源出现以上情况，如果无法定位问题所在，请把当时的现场信息详细记录，并与开发中心技术部联系。,2018/10/15,中国工商银行软件开发中心,日维护过程,五、检查对合理的性能来说是否有足够的资源,7 . 监控数据库性能,使用statspack收集统计数据，statspack可以对所收集的信息生成报告，这份报告对

10、系统性能的分析与优化有很大的作用，如果要对某段时间内的数据库系统性能有一个清楚的分析，可以使用statspack进行监控，并生成报告。关于statspack具体的使用方法，将在本课程第二部分“健康检查方法”中介绍。,2018/10/15,中国工商银行软件开发中心,周维护过程,一、查找异常的目标,1 . 所有的表都应该有唯一的主键,运行脚本no_pk.sql，查看没有主键的表。运行脚本dis_pk.sql，查找没有发挥作用的主键。 请应用管理人员和开发人员对这些没有主键的表及发挥作用的主键进行检查，确认其是否正常。如为不正常，请对此问题进行关注。,2018/10/15,中国工商银行软件开发中心,

11、周维护过程,一、查找异常的目标,2 . 所有的索引都要放到索引表空间中,运行脚本mkrebuild_idx.sql，查看不在索引表空间的索引。 如果经应用管理员和开发人员确认，没有其他的因素导致索引数据存放在非索引表空间，则建议在系统空闲时，把这些索引重建或重编译，将其迁移至索引表空间里。,2018/10/15,中国工商银行软件开发中心,周维护过程,一、查找异常的目标,3 . 查找测试环境和生产环境之间的差异,如果对应生产环境有一套测试环境，应尽量使两者在数据库层上保持相同。如出现不一致的数据类型、数据库对象及字符集，请应用管理员和开发人员确认其原因，如为非正常原因，应对其进行调整，使其保持一

12、致。 运行脚本datatype.sql，检查两个环境的数据类型是否一致。运行脚本obj_coord.sql，查找两个环境的数据库对象的不同点。 运行脚本character.sql，检查两个环境中的数据库字符集是否一致。,2018/10/15,中国工商银行软件开发中心,周维护过程,二、将警告日志存档,应定期对警告日志存档，并对清空当前警告日志，使其大小保持在一定的范围内。这样做的原因是： 警告日志文件过大，会给对警告日志的监控带来诸 多不便； 对警告日志定期存档，能更好的对警告日志的历史 信息进行检索。存档警告日志的具体做法是： 定位警告日志位置的方法参见 。 把当前的警告日志移到指定目录，并对

13、其重新命名 （增加时间标记）。例如： mv alert_SID.log 归档目录/ alert_SID_20031224.log,2018/10/15,中国工商银行软件开发中心,周维护过程,三、对应用表作统计分析,在使用成本优化器(CBO)生成执行计划时，对象的统计信息是否最新，会大大影响执行计划在最优路径中的选择。因此，我们要定期对应用对象（主要是表和索引）作分析，使其统计信息保持最新。运行脚本analyze_comp.sql，对全部应用对象进行分析，一般一星期一次就足够了。如果某些表和索引每天都有大量的数据更新，建议对这些特殊的对象每天进行单独的统计分析，可使用analyze_table.

14、sql对表分析，使用analyze_index.sql对索引分析。,2018/10/15,中国工商银行软件开发中心,周维护过程,四、对应用表和索引进行重组,在对应用表做完分析后，可运行脚本chain_table.sql，找出行链接较多的表，如果其行链接数与行总数的比值大于0.1，建议对此表进行重组。对表重组的方法有： 对此表进行导出导入操作； 使用create table as select方法，需要对重组后的表进行index和constraint的重建。执行步骤如下（重组表a）： create table b from as select a; drop table a; alter tab

15、le b rename to a; 重建表a的index和constraint。 使用alter table move tablespace 语句进行重组。如： alter table a move tablespace tbs1;,2018/10/15,中国工商银行软件开发中心,周维护过程,四、对应用表和索引进行重组,对所有应用索引进行定期重组，或者对所有应用索引做分析并筛选后再决定对哪些索引进行重组。对全部索引重组相对比较耗时，没有针对性，但比较全面，适用于数据量较少的数据库。可运行脚本rebuild_all_index.sql进行。对部分索引重组虽不够全面，但耗时相对较短，并且针对性较强

16、，适用于数据量较大的数据库。例如：对某用户下的树高大于3（此树指索引的二叉树的树结构）或删除的叶节点数量大于索引节点树的20的索引进行重组，可运行脚本rebuild_index.sql进行。注意：以上操作比较耗资源，建议在系统空闲时进行！,2018/10/15,中国工商银行软件开发中心,月维护过程,在月末，对本月内每日及每周的的记录或报告进行分析，从而预估当前数据库服务器在未来的一段时间里CPU，内存，网络，及硬盘等资源的耗用趋势，并计划数据库将来的性能，为日后的管理作好充分的准备。,2018/10/15,中国工商银行软件开发中心,健康检查概述,在一次健康检查当中会检查数据库的以下几个方面：系

17、统和数据库的配置系统和数据库的可用性数据库的状态系统和数据库的性能应用的相关信息,2018/10/15,中国工商银行软件开发中心,健康检查的信息收集,系统空闲时的信息收集：由于这部分收集的信息是相对静态，并且会耗用一定的系统资源，因此建议在系统的空闲时操作系统繁忙时的信息收集：由于这部分收集的信息是为动态的，主要是使用Statspack有针对性对某个时间段内采集ORACLE数据库的运行状态信息，因此必须在系统的运行最有代表性的繁忙时间段内操作,2018/10/15,中国工商银行软件开发中心,系统空闲时的信息收集,运行run_OS.sh(注意，斜体OS为代表的服务器的操作系统，例如为AIX的系统

18、，就运行run_aix.sh)，这时会在当前目录生成一个info的文件，此文件里包含收集的信息。 run_aix.sh run_sun.sh run_hp.sh运行RDA。RDA用于收集Oracle环境及操作系统的配置信息。 使用Object Access收集信息。提供当前存在于程序库缓存中的所有SQL语句的不同行为的总结，以及涉及到的数据对象的信息。,2018/10/15,中国工商银行软件开发中心,运行RDA的步骤,在需要进行信息收集的UNIX服务器上创建一个新目录 ，把安装包rda.tar放置到该目录中 解压rda.tar。解压后将生成所有的子目录运行setup.sh进行RDA选项配置。对

19、于各个输入或选择提示，建议使用缺省项 运行rda.sh。按照输入提示进行操作查看输出结果。输出结果位于RDA_Output目录下，其中有一个压缩文件名RDA.rda.tar.Z，该压缩文件包含所有信息。另外，RDA_Output目录下也包含可直接浏览的RDA_START.htm系列文件。,2018/10/15,中国工商银行软件开发中心,使用Object Access收集信息,以Schema所有者身份连接SQL*Plus确认用户是否拥有v_$sqltext、v_$sqlarea、v_$session、v_$mystat和dba_tables的访问权限。若没有，则进行授权 运行access.sql

20、运行access_report.sql 若前面步骤进行了授权，回收相应权限阅读统计报告： access.lst,2018/10/15,中国工商银行软件开发中心,系统繁忙时的信息收集,获取操作系统的基本状态信息：包括CPU、IO和内存。运行STATSPACK并生成报告。,2018/10/15,中国工商银行软件开发中心,获取操作系统的基本状态信息,AIX的监控命令： vmstat n N iostat n NSolaris的监控命令： vmstat n N iostat Dxt n NHP Unix的监控命令： vmstat n N sar d n N,下面的第一个命令为观察CPU和内存使用状态，

21、第二个命令为观察IO使用状态。,2018/10/15,中国工商银行软件开发中心,运行STATSPACK的步骤,建立Statspack所需表空间（出于一致性的考虑，建议将这个表空间命名为perfstat）以具有sysdba权限的用户连接SQL*Plus，安装Statspack。 SQL ?/rdbms/admin/spcreate （8.1.6版本中用statscre替换spcreate） 设置初始化参数：timed_statistics 收集信息。该操作可选择在系统高峰期重复执行多次，每个采样点都代表着一次对当前系统的快照 SQL execute Statspack.snap; 产生分析报告。

22、该报告将反映两次采样点之间系统的平均状态信息。 SQL ?/rdbms/admin/spreport (在ORACLE8.1.6中使用statsrep替换spreport) 卸载Statspack。如果确认已经不需要Statspack，考虑执行卸载脚本并删除perfstat表空间。 SQL ?/rdbms/admin/spdrop （在8.1.6中使用statsdrp.sql代替spdrop）,2018/10/15,中国工商银行软件开发中心,free.sql,REM free.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 检查表空间的剩余空

23、间REM - REM 结果说明：REM free_blocks 空闲块数目；FREE(M) 空闲空间大小(M)；REM free_percent 空闲空间占比；big_chunk_k 最大连续空闲空间的大小(K)；REM num_chunks 连续空闲空间数目。REM - REM 脚本：SELECT s.tablespace_name, sum ( s.blocks ) as free_blocks,trunc ( sum ( s.bytes ) / (1024*1024) ) as FREE(M),to_char(sum(nvl(s.bytes,0)/(f.bytes)*100,09.99)

24、 free_percent,max ( s.bytes ) / (1024) as big_chunk_k, count (*) as num_chunksFROM dba_free_space s,dba_data_files fwhere s.file_id=f.file_idGROUP BY s.tablespace_name,f.bytes ;,2018/10/15,中国工商银行软件开发中心,seg_wait_ratio.sql,REM seg_wait_ratio.sqlREM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 观察回滚段争用的情况。

25、REM - REM 结果说明：REM 我们只要关注“RATIO”值的返回，如果此值大于10，REM 说明回滚段的争用为不正常，需要调整回滚段的数量和大小。REM - REM 脚本：SELECT name, waits, gets, waits/gets RatioFROM v$rollstat a, v$rollname bWHERE a.usn = b.usn;,2018/10/15,中国工商银行软件开发中心,spacebound.sql,REM spacebound.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 查找NEXT_EXTEN

26、T比表空间所能提供的最大范围还要大的对象。REM - REM 结果说明：REM 如果没有这类对象，返回的结果为空。REM - REM 脚本：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_exte

27、nt f.big_chunk;,2018/10/15,中国工商银行软件开发中心,no_pk.sql,REM no_pk.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 检查没有主键的表。REM - REM 脚本：SELECT table_name FROM all_tables WHERE owner = upper(,2018/10/15,中国工商银行软件开发中心,dis_PK.sql,REM dis_PK.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 查找没有发挥作用的主键。REM -

28、 REM 结果说明：REM 如果没有符合条件的对象，则返回结果为空。REM - REM 脚本：SELECT owner, constraint_name, table_name, statusFROM all_constraintsWHERE owner = upper(,2018/10/15,中国工商银行软件开发中心,mkrebuild_idx.sql,REM mkrebuild_idx.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 检查是否有索引没有放在用户的默认索引表空间中。REM - REM 脚本：SELECT index_name

29、,table_name,tablespace_nameFROM all_indexesWHERE tablespace_name != upper(,2018/10/15,中国工商银行软件开发中心,datatype.sql,REM datatype.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 检查不同的两个运行环境中的数据类型是否一致。REM - REM 脚本：SELECT table_name, column_name, data_type, data_length, data_precision,data_scale, nullabl

30、eFROM all_tab_columns - first environmentWHERE owner = upper(,2018/10/15,中国工商银行软件开发中心,obj_coord.sql,REM obj_coord.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 检查两个实例中不同的对象。REM - REM 脚本：SELECT object_name, object_type FROM user_objects MINUS SELECT object_name, object_type FROM user_objects&db_l

31、ink,2018/10/15,中国工商银行软件开发中心,character.sql,REM character.sqlREM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 查看数据库字符集。REM - REM 脚本：col name format a25col value$ format a15SELECT name,value$ FROM props$ WHERE name in (NLS_LANGUAGE,NLS_TERRITORY, NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET);,2018/10/15,中国工商银行

32、软件开发中心,analyze_comp.sql,REM analyze_comp.sql REM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 在晚间收集数据的信息。REM - REM 脚本：BEGINsys.dbms_utility.analyze_schema (upper(/,2018/10/15,中国工商银行软件开发中心,analyze_table.sql,REM analyze_table.sqlREM - REM 要求：REM 有ANALYZE权限REM - REM 目的：REM 收集某个表的统计信息。REM 需要输入用户名和表名。REM -

33、REM 脚本：BEGINdbms_stats.gather_table_stats(ownname= upper(/,2018/10/15,中国工商银行软件开发中心,analyze_index.sql,REM analyze_index.sqlREM - REM 要求：REM 有ANALYZE权限REM - REM 目的：REM 收集某个索引的统计信息。REM 需要输入用户名和表名。REM - REM 脚本：BEGIN dbms_stats.gather_index_stats(ownname= upper(/,2018/10/15,中国工商银行软件开发中心,chain_table.sql,R

34、EM chain_table.sqlREM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 发现行链接比较大的表REM - REM 结果说明：REM 如果ratio0.1，则说明该表的行链接数目大于正常标准， 需要对该表进行重组。REM - REM 脚本：SELECT table_name,chain_cnt/num_rows ratioFROM all_tables WHERE owner not in (SYS,SYSTEM)AND table_name not in (select table_name from all_tab_columns whe

35、re data_type in(RAW,LONG RAW)AND chain_cnt0 order by chain_cnt desc;,2018/10/15,中国工商银行软件开发中心,rebuild_all_index.sql,REM rebuild_all_index.sqlREM - REM 要求：REM DBA权限或OPER权限REM - REM 目的：REM 对某用户下的所有索引进行重组。REM - REM 结果说明：REM 生成对某用户下的所有索引进行重组的sql脚步，并运行它。 需要输入 用户名。REM - REM 脚本：set heading off;set pages 9999;spool run_rebuild.sql;select alter index |owner|.|index_name| rebuild tablespace | tablespace_name|;from dba_indexeswhere owner=upper(run_rebuild.sql,