数据库维护工作手册概述.docx

数据库维护工作手册概述.docx

《数据库维护工作手册概述.docx》由会员分享，可在线阅读，更多相关《数据库维护工作手册概述.docx（23页珍藏版）》请在冰点文库上搜索。

数据库维护工作手册概述

数据库维护工作手册

文档编号：

文档名称：

编写：

审核：

批准:

批准日期：

目录

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

1

概述

数据库的日常监控是使管理员及时了解系统异常的手段。

大部分情况下，系统总是正常运行的。

只有对正常情况的充分了解，才能通过对比正常情况发现异常情况。

对于数据库的日常监控要有记录，文字记录或者电子文档保存。

对于数据库异常进行分析，提出解决方案。

日常工作包括监控和维护两个部分。

此文档中关于数据库的运行命令示例主要针对于ORACLE数据库，但对于SYBASE数据库同样有参考价值，只要换用相对应的语句即可。

数据库监控

2数据库监控

数据库监控工作内容

制定和改进监控方案，编写监控脚本。

对于数据库进行日常监测，提交记录。

根据监测结果进行分析、预测，提交相应的系统改进建议方案。

数据库监控工作步骤

2.1.1查看数据库日志

数据库的日志上会有大量对于管理员有用的信息。

ORACLE的Alert日志纪录了数据库系统所报的系统级错误信息，以及数据块失效等严重错误信息。

错误信息的产生，会产生相应的跟踪文件，通过查看警告日志和跟踪文件可查找错误原因，对于发现的问题应及时解决和汇报。

如：

1.表空间是否满，是否需要进行添加或者扩展。

Alert文件中会显示有表块无法扩展的提示。

2.表的块或者页面是否损坏。

（往往这时alert文件中会显示ora-600的错误。

）

3.数据库是否进行了异常操作。

（如：

droptablespace等等）。

实用命令：

·报警日志文件（alert.log或alrt.ora）

记录数据库启动，关闭和一些重要的出错信息。

数据库管理员应该经常检查这个文件，并对出现的问题作出即使的反应。

可以通过以下SQL找到他的路径selectvaluefromv$parameterwhereupper（name）='BACKGROUND_DUMP_DEST'，或通过参数文件获得其路径，或者showparameterBACKGROUND_DUMP_DEST。

·后台跟踪文件

路径与报警文件路径一致，记载了系统后台进程出错时写入的信息。

·用户跟踪文件

记载了用户进程出错时写入的信息，一般不可能读懂，可以通过ORACLE的TKPROF工具转化为可以读懂的格式。

用户跟踪文件的路径，你可以通过以下SQL找到他的路径selectvaluefromv$parameterwhereupper（name）='USER_DUMP_DEST'，或通过参数文件获得其路径，或者showparameterUSER_DUMP_DEST。

可以通过设置用户跟踪或dump命令来产生用户跟踪文件，一般在调试、优化、系统分析中有很大的作用。

可在参数文件种用SQL_TRACE=TRUE打开该文件（对所有用户），也可用altersessionsetsql_trace=true打开当前会话，也可用executedbms_system.set_sql_trace_in_session（sid,serial#,true）打开指定会话。

2.1.2检查是否有失效的数据库对象

主要关注索引，触发器，存储过程，函数等等。

如：

查找user_objects数据字典，看其中是否有状态为invalid的对象。

判断失效原因（如：

视图失效的原因有可能是由于创建视图的基表被删除等等），找出原因可进行对象重建或修复。

实用命令：

Selectobject_name,object_type

Fromuser_objects

Whereobject_type=’INVALID’;

2.1.3查看数据库剩余空间

1.剩余空间不足时要扩展空间，一般的，当剩余空间小于10％时，要进行空间扩展。

对于ORACLE数据库，通过查找tablespaces相关的数据字典可以看到有用的信息。

2.检查数据快速增长的表，通过对于dba_segments数据字典的监视可以找到，当过快增长时，协调开发人员，确定解决方案。

2.1.4重点表检查

1.检查系统核心业务表。

因为这些表健康与否与日常业务的正常运行密切相关。

重点检查这些表的索引是否失效，表的统计信息是否及时更新，如：

当这些表进行了大的数据装载或者删除操作之后。

原则上需要检查所有的表，只是由于上面这些表更关键，建议管理员给以更多的关注。

2.重点检查数据量超过百万行的表，各地的情况可能不一样，当数据超过百万行之后，如果索引失效会导致表扫描，占用大量系统IO，严重影响系统性能。

2.1.5查看数据库是否正常

包括数据库实例是否正常工作、listener是否工作正常，确保数据库系统环境正常。

数据库连接是否正常、检查是否有超出正常水平的连接数。

如：

平常500个，某天下午忽然达到600个。

应记录这种异常情况。

分析产生这种情况的原因，如：

在低版本的ORACLE中，很可能是一些其他异常的应用出错后产生的死连接。

2.1.6死锁检查

监控数据库运行过程中，出现的阻塞，记录现象，记录产生阻塞的SQL语句，执行的用户，发生时间，频率，处理（杀掉、等待自然解锁等）。

ORACLE版本中的死锁会在alert文件中产生记录，oracle会自动解锁（其实是选择一个杀掉）。

对于死锁的处理过程要进行记录。

可以使用OEM工具或者查找相关的V$视图来确认产生阻塞的语句。

2.1.7监控SQL语句的执行

查找效率低下的SQL语句，联系协调开发人员，进行相关处理。

可使用ORACLE提供的AWR进行，也可使用ORACLE提供的OEM工具执行，或者自行编制的脚本等等。

2.1.8操作系统级检查

运行vmstat，sar,topas（AIX系统），glance（HP系统）等命令检查CPU、内存、虚拟内存等的使用情况。

运行df,du,iostat检查磁盘使用情况

运行netstat检查网络情况

运行手工编制的监控脚本检查。

针对于操作系统的不同，使用的命令也会有不同，请参考相应的操作系统文档。

建议使用man命令察看相应的帮助信息。

2.1.9其他

每天查看晚间定时执行的数据库信息收集作业和备份作业的日志输出，确认都已正常完成。

往往不能正常完成是由于如下的原因：

请确认脚本是否变动（错误的修改造成等等），设备（主机，磁盘阵列，磁带库，网络等等）是否正常，空间是否足够等等。

建议每天按业务峰值情况，对数据库性能数据进行定时采集及分析。

3数据库维护

数据库维护工作内容

包括维护、故障诊断、错误修复、备份恢复、历史数据迁移等过程。

数据库维护工作事项

3.1.1页面修复

根据日常监控的结果，进行页面（或者数据库坏块）修复，如将表数据导出后重建表，然后导入数据。

提交修复记录。

3.1.2数据库对象重建

根据数据库监控的结果，重建失效的对象。

如：

索引、存储过程、函数、视图、触发器等等。

实用命令：

Alterindex<索引名>rebuild[online];

3.1.3碎片回收（数据重组）

当某些数据库运行一段时间后，表会产生碎片，影响数据库的性能。

可根据日常检查的结果，运用工具或脚本对于数据库空间进行重组或回收。

由于ORACLE数据库本身的原因，在进行了DELETE操作之后也不会使HWM（HighWaterMark　高水位线）降低，因此不会释放所占用的空间，所以建议在进行了数据迁移之后将全库进行EXP，然后进行IMP操作，以释放占用的空间。

3.1.4删除不用的数据

此项工作要得到开发方、设计人员、以及相关人员的确认后，方可执行。

3.1.5备份恢复

需要定期对于数据库备份进行有效性检测，定期进行数据恢复的演练操作。

以防止万一的数据库事故时准备不足。

数据库需要采用在线的热备份，不需要关闭数据库进行，在备份的同时可以进行正常的数据库的各种操作，满足了7*24的系统的需要。

数据库的备份不能影响用户对数据库的访问。

目标

需要在线热备份

多级增量备份

并行备份，恢复

减小所需要备份量

备份，恢复使用简单

可参考如下的方案:

1.每月做一个数据库的全备份（包含只读表空间）

2.每星期做一次零级备份（不包含只读表空间）

3.每个星期三做一次一级备份

4.每天做一个二级备份

5.任何表空间改成只读状态后做一个该表空间的备份。

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.剩余空间检查

SELECTtablespace_name,sum（blocks）asfree_blk,trunc（sum（bytes）/

（1024*1024））asfree_m

max（bytes）/（1024）asbig_chunk_k,count（*）asnum_chunks

FROMdba_free_space

GROUPBYtablespace_name

2.表空间数据量情况显示

SELECTtablespace_name,max_blocks,count_blocks,sum_free_blocks

to_char（100*sum_free_blocks/sum_alloc_blocks,'99.99'）||'%'

ASpct_free

FROM（SELECTtablespace_name

sum（blocks）ASsum_alloc_blocks

FROMdba_data_files

GROUPBYtablespace_name

）

（SELECTtablespace_nameASfs_ts_name

max（blocks）ASmax_blocks

count（blocks）AScount_blocks

sum（blocks）ASsum_free_blocks

FROMdba_free_space

GROUPBYtablespace_name）

WHEREtablespace_name=fs_ts_name

3.表和索引分析

BEGIN

dbms_utility.analyze_schema（'&OWNER','ESTIMATE',NULL,5）;

END;

4.检查空间情况

SELECTa.table_name,a.next_extent,a.tablespace_name

FROMall_tablesa,

（SELECTtablespace_name,max（bytes）asbig_chunk

FROMdba_free_space

GROUPBYtablespace_name）f

WHEREf.tablespace_name=a.tablespace_name

ANDa.next_extent>f.big_chunk

5.检查已经存在的空间扩展

SELECTcount（*）,segment_name,segment_type,dt.tablespace_name

FROMdba_tablespacesdt,dba_extentsdx

WHEREdt.tablespace_name=dx.tablespace_name

ANDdt.next_extent!

=dx.bytesANDdx.owner='&OWNER'

GROUPBYsegment_name,segment_type,dt.tablespace_name

6.检查没有主键的表

SELECTtable_name

FROMall_tables

WHEREowner='&OWNER'

MINUS

SELECTtable_name

FROMall_constraints

WHEREowner='&&OWNER'

ANDconstraint_type='P'

7.检查失效的主键

SELECTowner,constraint_name,table_name,status

FROMall_constraints

WHEREowner='&OWNER'ANDstatus='DISABLED’ANDconstraint_type='P'

8.重建索引，具体参数请根据实际情况进行修改

SELECT'alterindex'||index_name||'rebuild'

'tablespaceINDEXESstorage（initial256Knext256K）;'

FROMall_indexes

WHERE（tablespace_name!

='INDEXES'

ORnext_extent!

=（256*1024）

）

ANDowner='&OWNER'

9.对比两个实例的不同

SELECTobject_name,object_type

FROMuser_objects

MINUS

SELECTobject_name,object_type

FROMuser_objects@&my_db_link

10.查看动态性能视图

Select*fromV$FIXED_TABLE

11.查看约束

selecta.constraint_name,a.constraint_type,a.*

fromuser_constraintsa

wheretable_name='table_name';

selectconstraint_name,column_name

fromuser_cons_columns

wheretable_name='table_name';

12.查看索引

user_indexes包含索引的名字,user_ind_columns包含索引的列.

13.查看数据库启动参数：

showparameterpara，v$parameter提供当前会话信息，v$system_parameter提供当前系统信息。

其中isses_modifiable，issys_modifiable表示是否允许动态修改。

14.查看进程号：

selectp.spid,s.username

fromv$processp,v$sessions

wherep.addr=s.paddr;

15.查看数据文件：

selectname,status

fromv$datafile;

select*

fromdba_data_files;

16.查看数据文件状态

selectd.file#f#,d.name,d.status,h.status

fromv$datafiled,v$datafile_headerh

whered.file#=h.file#;

17.查看控制文件

selectname

fromv$controlfile;

selecttype,record_size,records_total,records_used

fromv$controlfile_record_section

wheretype=’DATAFILE’;

18.查看是否归档模式：

archiveloglist

selectname,log_mode

fromv$database;

selectarchiver

fromv$instance;

19.查看日志组：

selectgroups,current_group#,sequence#

fromv$thread;

selectgroup#,sequence#,bytes,members,status

fromv$log;

select*

fromv$logfile;

其中status为空表示正常。

20.查看largepool

select*

fromv$sgastat

wherepool=’largepool’;

21.查看归档位置

showparameterarchive

selectdestination,binding,target,status

fromv$archive_dest;

22.查看归档进程

select*

fromv$archive_processes;

23.查看正在备份的数据文件

select*

fromv$backup;

24.查看需要恢复的文件

select*

fromv$recover_file;

25.查看所有归档日志文件

select*

fromv$archived_log;

26.查看恢复时要用到的日志文件

select*

fromv$recovery_log;

27.查看SGA的结构

Showsga;

select*

fromv$sgastat;

28.提取librarycache的命中率

selectgethitratio

fromv$librarycache

wherenamespace=’…’;

29.查看正在运行的SQL语句

selectsql_text,users_executing,executions,loads

fromv$sqlarea;

select*

fromv$sqltext

wheresql_text=’select*fromemp%’;

30.查看librarycachereload情况：

selectsum（pins）“Executions”,sum（reloads）“cacheMisses”,sum（reloads）/sum（pins）

fromv$librarycache;

31.查看大匿名块

selectsql_textfromv$sqlarea

wherecommand_type=47

andlength（sql_text）>500;

32.查看当前会话的UGA区

selectsum（value）||’bytes’“Totalsessionmemory”

fromv$mystat,v$statname

wherename=’sessionugamemory’

andv$mystat.statistic#=v$statname.statistic#;

33.查看所有MTS用户的UGA区：

selectsum（value）||’bytes’“Totalsessionmemory”

fromv$sesstat,v$statname

wherename=’sessionugamemory’

andv$sesstat.statistic#=v$statname.statistic#;

34.查看所有用户使用的最大的UGA区：

selectsum（value）||’bytes’“Totalsessionmemory”

fromv$sesstat,v$statname

wherename=’sessionugamemorymax’

andv$sesstat.statistic#=v$statname.statistic#;

35.查看high-watermark以下的块数

selecttable_name,blocks

fromdba_tables

wheretable_name=’table_name’;

36.查看会话的I/O：

selectio.block_gets,io.consistent_gets,io.physical_reads

fromv$sess_ioio,v$sessions

wheres.audsid=USERENV（‘SESSIONID’）

andio.sid=s.sid;

37.查看Bufferpool的命中率

selectname,1-（physical_reads/（db_block_gets+consistent_gets））“HIT_RATIO”

fromsys.v$buffer_pool_statistics

wheredb_block_gets+consistent_gets>0;

38.查看freelist的竞争

selectclass,count,time

fromv$waitstat

whereclass=’segmentheader’;

selectevent,total_waits

fromv$system_event

whereev