5.2 信息系统数据资源例行管理

数据资源例行管理是 一种预防性的维护工作，它是在系统正常运行过程中，定期采取一系 列的监控、检测与保养工作，及时发现并消除系统运行缺陷或隐患，使系统能够长期安全、稳 定、可靠地运行。

数据资源例行管理计划

例行管理计划中 需列出监控检测的对象、重要性等级以及常规操作方法，监控检测的频次或周期、正常状态值 和报警阈值等

序号	管理任务	操作内容	重要性	检测时间
1	数据库检查	检查并记录数据库增长情况检查数据库是否有死锁现象	重要(H)
2	数据备份	检查备份内容的正确性检查是否会出现数据备份失败的现象检查是否存在大数据量备份记录条数丢失的现象	重要(H)
3	数据恢复	检查是否会出现数据备份恢复失败的现象检查在各个数据库中小数位长度不一致的现象	重要(H)

数据资源载体的管理

存放数据资源的介质必须具有明确的标识；标识必须使用统一的命名规范，注明介质编号、 备份内容、备份日期、备份时间、启用日期和保留期限等重要信息。

存储介质的管理包括：

存储介质借用管理

对存储介质的访问一般设有权限控制，借用人需提出借用申请，填写使用时间、内容、用 途，经信息系统责任人批准后，介质管理员方能借出备份。

存储介质转储管理

对长期保存的存储介质，应按照制造厂商确定的存储有效寿命进行定期转储处理。

存储介质销毁管理

存储介质需要废弃或销毁时，由介质管理员提出申请，由信息系统负责人审批执行。

数据库例行维护

健康检查

数据库日志检查

日志内容包括：事务开始标记、事务的唯一标识、所操作数据项的唯一标识、 数据项的写前值(数据插入操作不包含该项)、数据项的写后值(数据删除操作不包含该项)、 事务提交或终止标记。

数据库一致性检查

对数据库的物理和逻辑一致性进行检查

语句分类	执行
维护语句	对数据库、索引或文件组进行维护的任务
杂项语句	诸如启用行级锁定或从内存中删除动态链接库(DLL)等杂项任务
状态语句	状态检查
验证语句	对数据库、表、索引、目录、文件组、系统表或数据库页的分配进行的验证操作

数据库监测管理

从应用可用性、系统资源占用和数据库性能指标三个方面监测数据库应用相关的服务，确 保数据库运行正常。

• 数据库基本信息监测包括数据库的文件系统、碎片、死锁进程的监测，数据库可设置 死锁检测进程执行的间隔时间，死锁检测进程负责监测、处理数据库系统中出现的死锁。

• 数据库表空间监测。

• 数据库文件I/O 监测。

数据库备份与恢复

数据库备份与恢复

数据库备份就是将数据库中的数据及数据库的物理和逻辑结构等相关数据字典信息，存放 在其他的存储介质中进行保存。

数据库故障及恢复

• 事务故障：事务故障是指由于事务内部的逻辑错误或系统错误引起
• 系统故障：软故障，是指系统停止运转的任何时间
• 介质故障：硬故障，是指外存故障

数据库性能优化

数据库维护人员根据用户需求和监测结果对数据库性能进行调整和优化，如执行空间释 放、表的重构、索引重建、数据分片等操作。

• 空间释放

  事务日志文件记录着用户的各种数据库操作，对于用户操作频繁的数据库，其事务日志空 间膨胀速度非常快，数据库维护人员需定期检查事务日志的大小，减少磁盘空间的耗用。以SQL Server2008 数据库管理系统为例，压缩数据库。

方案一：完全命令模式
USE[master]
GO
ALTERDATABASEDNName SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTERDATABASE DNName SET RECOVERY SIMPLE --简单模式
GO
USE DNName
G0
DBCC SHRINKFILE(N'DNName_Log',11,TRUNCATEONLY)
GO
USE[master]
G0
ALTERDATABASEDNName SET RECOVERYFULLWITH NO_WAIT
GO
ALTERDATABASE DNName SET RECOVERY FULL --还原为完全模式
GO 

优点：清除日志所运行消耗的时间短，90GB 的日志在几分钟左右即可清除完毕，做完之 后做个完全备份在几分钟内即可完成。

缺点：此动作最好不要经常使用，因为它的运行会带来系统碎片。

使用环境：统的日志文件异常增大或者备份LOG 时间太长可能影响生 产的情况下使用。

• 表的重构

  对不断被更新的表，数据库管理员应当定期地或者在大批量的数据处理之后重新收集表的 统计信息，检查表中数据及磁盘空间使用。如果发现大量记录的转移、未回收的空间，就需要 重新构建表。

• 索引重建

  索引是提高数据查询最有效的方法，正确的索引可能使效率提高很多，而无效的索引可能 是浪费了数据库空间，甚至大大降低查询性能。针对有频繁的插入/更新/删除操作的表，表和索引将产生较多的碎片，索引将变得非聚簇，性能也将下降，严重的时候会产生索引阻塞等问 题，为此需要进行索引重建。

• 数据分片

  数据分片是将海量数据根据一定的规则分布在多个存储设备上，这样每个存储设备的数据 量相对就会小很多，由此实现并行的读/写操作，满足读/写量大的系统的性能需求。

Oracle数据库监控

• 通过系统自带的语句
• 监控软件。如Spotlight on Oracle

检查数据库基本状况

包含：检查Oracle 实例状态，检查Oracle 服务进程，检查Oracle 监听进程，共6个部分。 1)检查Oracle实例状态

select instance_name,host_name,startup_time,status,database_status from v$instance;

其中“STATUS” 表示Oracle当前的实例状态，必须为“OPEN”;“DATABASE_STATUS” 表示Oracle当前数据库的状态，必须为 “ACTIVE”。

2)检查Oracle 在线日志状态

select group#,status,type,member from v$logfile;

输出结果应该有3条以上(包含3条)记录， “STATUS” 应该为非“INVALID”, 非

“DELETED”。注 ：“STATUS” 显示为空表示正常。

3)检查Oracle 表空间的状态

select tablespace_name,status from dba_tablespaces;

输出结果中STATUS应该都为ONLINE。

4 ) 检 查Oracle所有数据文件状态

select name,status from v$datafile;

输出结果中“STATUS” 应该都为“ONLINE” 。 或者：

select file_name,status from dba_data_files

输出结果中 “STATUS” 应该都为 “AVAILABLE”。

5)检查无效对象

select owner,object_name,object_type from dba_objects where status!='VALID'and owner!='SYS'and owner!='SYSTEM';

如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成 这个对象，或者：

SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID';

-6)检查所有回滚段状态

select segment_name,status from dba_rollback_segs;

输出结果中所有回滚段的“STATUS” 应该为“ONLINE”。

检查Oracle相关资源的使用情况

1 ) 检 查Oracle 初始化文件中相关参数值

select resource_namemax_utilization,initial_allocation,limit_valuefrom vSresource_limit;

若 LIMIT_VALU-MAX_UTILIZATION<=5, 则表明与RESOURCE_NAME 相关的Oracle 初始化参数需要调整。可以通过修改 Oracle 初 始 化 参 数 文 件 $ORACLE_BASE/admin/ CKDB/pfile/initORCL.ora来修改。

2)检查数据库连接情况

查看当前会话连接数，是否属于正常范围。

select count(*)from vSsession;select sid,serial#,username,program,machine,status from v$session;

SID 是会话(session) 的 ID 号 ；SERIAL# 是会话的序列号，和SID 一起用来唯一 标识一个会话；USERNAME 是建立该会话的用户名；PROGRAM 是这个会话是用什么工具连 接到数据库的；STATUS 是当前这个会话的状态，ACTIVE 表示会话正在执行某些任务， INACTIVE 表示当前会话没有执行任何操作。

如果DBA 要手工断开某个会话，则执行：

alter system kill session 'SID,SERIAL#;

3)检查系统磁盘空间

[oracle@AS14~]$df-hFilesystem Size Used Avail Use%Mounted on/dev/sda59.7G 3.9G 5.4G 42%//dev/sdal 479M 16M 438M 4%/boot/dev/sda249G 19G 28G41%/datanone 1014M 01014M 0%/dev/shm

4)检查表空间使用情况

select f.tablespace_name,a.total,f.free,round((f.free/a.total)* ***\*100)\**** "%Free"from (select tablespace_name,sum(bytes/(***\*1024\*1024\**** ))totalfrom dba_data_filesgroup by tablespace_name)a,(select tablespace_name,round(sum(bytes/(***\*1024\*1024)\****   freefrom dba_free_spacegroup by tablespace_name)fWHERE a.tablespace_name =f.tablespace_name(+)order by "%Free";

如果空闲率%Free 小于10%以上(包含10%),则注意要增加数据文件来扩展表空间而不 要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则 是每个数据文件大小为2G 或 者 4G, 自动扩展的最大限制在8G。

5)检查一些扩展异常的对象

select Segment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100  PercentFrom sys.DBA_SegmentsWhere Max_Extents !=0and(Extents   /Max_extents)*100>=95order By Percent;

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要 修改它的存储结构参数。

6 ) 检 查system 表空间内的内容

select distinct (owner)from dba_tableswhere tablespace_name ='SYSTEM'and owner !='SYS'and owner !='SYSTEM1unionselect distinct (owner)from dba_indexeswhere tablespace_name='SYSTEM'and owner !='SYS'and owner !='SYSTEM';

如果记录返回，则表明system 表空间内存在一些非system 和 sys 用户的对象。应该进一 步检查这些对象是否与应用相关。如果相关请把这些对象移到非 System 表空间，同时应该检查 这些对象属主的缺省表空间值。

7)检查对象的下一扩展与表空间的最大扩展值

select a.table_name,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_chunkunionselect a.index_name,a.next_extent,a.tablespace_namefrom all_indexes 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_chunk;

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需 调整相应表空间的存储参数。

检查Oracle数据库备份结果

1)检查数据库备份日志信息

假设：备份的临时目录为/backup/hotbakup,需要检查2017年7月22日的备份结果，则用 下面的命令来检查：

cat /backup/hotbackup/hotbackup-17-7-22.loglgrep -ierror

备份脚本的日志文件为 hotbackup-月份-日期-年份.log,在备份的临时目录下面。如果文件 中存在“ERROR:”, 则表明备份没有成功，存在问题需要检查。

2)检查backup卷中文件产生的时间

#ls -It /backup/hotbackup

backup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份 脚本产生的。如果时间不对则表明热备份脚本没执行成功。

3)检查Oracle 用户的Email

#tail -n 300/var/mail/oracle

热备份脚本是通过Oracle 用户的cron 去执行的。cron 执行完后操作系统就会发一条 Email 通知 Oracle 用户任务已经完成。查看Oracle Email中今天凌晨部分有无ORA-,Error,Failed

等出错信息，如果有则表明备份不正常。

检查Oracle数据库性能

1)检查数据库的等待事件

set pages 80set lines 120col event for a40select     sid,event,pl,p2,p3,WAIT_TIME,SECONDS_IN_WAITfrom vSsession_waitwhere event not like 'SQL%'and event not like 'rdbms%';

如果数据库长时间持续出现大量像 latch free,enqueue,buffer busy waits,db file sequential read,db file scattered read 等等待事件时，需要对其进行分析，可能存在问题的语句。

2)Disk Read最 高 的SQL 语句的获取

SELECTSQL_TEXTFROM (SELECT*FROM V$SQLAREA ORDER BY DISK_READS)WHERE  ROWNUM<=5;

3)查找前10条性能差的SQL

SELECT*FROM (SELECT PARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROM V$SQLAREAORDER BY DISK_READS DESC)WHERE  ROWNUM<10;

4)等待时间最多的5个系统等待事件的获取

SELECT*FROM(SELECT*FROMVSSYSTEM_EVENTWHERE EVENT NOT LIKE 'SQL%ORDER BY TOTAL_WAITS DESC)WHERE  ROWNUM<=5;

5)检查运行很久的SQL

COLUMNUSERNAME FORMAT A12COLUMN OPNAME FORMAT A16COLUMN PROGRESS FORMAT A8SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)|Ⅱ'%'AS   PROGRESS,TIME_REMAININGSQL_TEXTFROM VSSESSION_LONGOPS,VSSQLWHERE  TIME_REMAINING◇0AND  SQL_ADDRESS=ADDRESSAND  SQL_HASH_VALUE=HASH_VALUE;

6 ) 检 查 消 耗CPU 最高的进程

SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999COLUMN S_#FORMAT 999COLUMN USERNAME FORMAT A9 HEADING "ORAUSER"COLUMN PROGRAM FORMAT A29COLUMN SQLFORMAT A60COLUMNOSNAME FORMAT A9 HEADING "OS USER"SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL#S_#P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROM VSPROCESS P,VSSESSION S,VSSQLAREAA  WHERE  P.ADDR=S.PADDR  AND  S.SQL_ADDRESS=A.ADDRESS(+) AND P.SPID LIKE '%&1%';

7)检查碎片程度高的表

SELECT segment_name table_name,COUNT(*)extentsFROM dba_segmentsWHERE ownerNOT IN(SYS','SYSTEM)GROUP BY segment_nameHAVING COUNT(*)=(SELECT MAX(COUNT(*))FROM dba_segmentsGROUP BY segment_name);

8)检查表空间的I/O 比 例

SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS  PYR,F.PHYBLKRD   PBR,F.PHYWRTS   PYWF.PHYBLKWRT PBWFROM VSFILESTAT F,DBA_DATA_FILES DFWHERE F.FILE#=DF.FILE_IDORDER BY DF.TABLESPACE_NAME;

9)检查文件系统的I/O 比 例

SELECT SUBSTR(A.FILE#,1,2)"#",SUBSTR(A.NAME,1,30)"NAME",A.STATUS,A.BYTESB.PHYRDS,B.PHYWRTSFROMV$DATAFILEA,VSFILESTATBWHERE A.FILE#=B.FILE#;

10)检查死锁及处理

查询目前锁对象信息。

select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,0.object_idfrom dba_objectso,v$locked_object 1,v$session swhere o.object_id=1.object_idand s.sid=L.session_id;

Oracle级 kill 掉该 session:

alter system kill session’&sid,&serial#;

操作系统级kill 掉 session:

#>kill -9 pid

检查数据库CPU、I/O、内存性能

1)CPU 使用情况

注意剩余的CPU 使用量，当其平均值下降至10%以下时视为CPU 使用率异常，需记录下 该数值，并将状态记为异常。

2)内存使用情况

#free -m

total used free shared buffers cached

Mem:20261958670761556

-/+buffers/cache:3261700

Swap:5992925900

如上所示，2026表示系统总内存、1958表示系统使用的内存、67表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。

3)系统I/O情况

4)系统负载情况

5)查看是否有僵死进程

7)定期做统计分析

8)检查缓冲区命中率

9)检查共享池命中率

10)检查排序区

11)检查日志缓冲区

其他检查

1)检查当前 crontab 任务是否正常

[oracle@AS14~]$crontab -1

2)Oracle Job是否有失败

select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE;

3)监控数据量的增长情况

4)检查失效的索引

5)检查不起作用的约束

6)检查无效的 trigger

SQL Server监控

事件探查器的基本用法

(1)启动 SQL 事件探查器，连接到目标数据库实例，创建一个新跟踪，指定一个跟踪模 板(跟踪模板预置了一些事件和用于跟踪的列)

(2)作为可选的一步，还可以选择特定事件和列

(3)另外还可以单击“组织列”按钮，在弹出的窗口中指定列的显示顺序，单击“列过滤器”按钮，在弹出的窗口中设置过滤器，例如，通过设置数据库的名称(在like 文本框中), 只跟踪特定的数据库，如果不设置过滤器，SQL 事件探查器会捕捉所有的事件，跟踪的信息会 非常多

(4)运行事件探查器，等待捕捉事件。

(5)跟踪了足够的信息后，停掉事件探查器，将跟踪信息保存到一个文件中，或者保存到 一个数据表中，如果保存到表中，需要指定表名，SQL Server会自动创建表中的字段。

( 6 ) 执 行 下 面 的SQL 查 询 语 句 找 出 执 行 代 价 较 高 的TSQL,

SELECT    TextData,Duration,...,FROM     Table_Name     ORDER     BY
Duration    DESC

有效利用SQL事件探查器排除与性能相关的问题

1)使用现有的模板，但需要时应创建自己的模板

2)捕捉表扫描 (TableScan) 和死锁(DeadLock) 事件

_3)创建重放跟踪

4)创建优化跟踪

5)捕捉ShowPlan在事件探查器中包括SQL 执行计划