数据库维护工作手册

数据库维护工作手册Lastrevisiondate:13December2020.

数据库维护工作手册文档编号：文档名称：编写：审核：批准：批准日期：1概述数据库的日常监控是使管理员及时了解系统异常的手段。大部分情况下，系统总是正常运行的。只有对正常情况的充分了解，才能通过对比正常情况发现异常情况。对于数据库的日常监控要有记录，文字记录或者电子文档保存。对于数据库异常进行分析，提出解决方案。日常工作包括监控和维护两个部分。此文档中关于数据库的运行命令示例主要针对于ORACLE数据库，但对于SYBASE数据库同样有参考价值，只要换用相对应的语句即可。数据库监控2数据库监控数据库监控工作内容制定和改进监控方案，编写监控脚本。对于数据库进行日常监测，提交记录，根据监测结果进行分析、预测，提交相应的系统改进建议方案。数据库监控工作步骤2.1.1查看数据库日志数据库的日志上会有大量对于管理员有用的信息。ORACLE的Alert日志纪录了数据库系统所报的系统级错误信息，以及数据块失效等严重错误信息。错误信息的产生，会产生相应的跟踪文件，通过查看警告日志和跟踪文件可查找错误原因，对于发现的问题应及时解决和汇报。如：.表空间是否满，是否需要进行添加或者扩展。Alert文件中会显示有表块无法扩展的提示。.表的块或者页面是否损坏。(往往这时alert文件中会显示ora-600的错误。).数据库是否进行了异常操作。(如：droptablespace等等)。实用命令：报警日志文件(alert,log或alrt<SID>.ora)记录数据库启动，关闭和一些重要的出错信息。数据库管理员应该经常检查这个文件，并对出现的问题作出即使的反应。可以通过以下SQL找到他的路径selectvaluefromvSparameterwhereupper(name)BACKGROUND_DUMP_DEST,,或通过参数文件获得其路径，或者showparameterBACKGROUND_DUMP_DEST。后台跟踪文件路径与报警文件路径一致，记载了系统后台进程出错时写入的信息。用户跟踪文件记载了用户进程出错时写入的信息，一般不可能读懂，可以通过ORACLE的TKPROF工具转化为可以读懂的格式。用户跟踪文件的路径，你可以通过以下SQL找到他的路径selectvaluefromv$parameterwhereupper(name)二'USER_DUMP_DEST,或通过参数文件获得其路径，或者showparameterUSER_DUMP_DESTO京以插过设置用户跟踪或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_typeFromuser_objectsWhereobject_type=，INVALID*;3查看数据库剩余空间.剩余空间不足时要扩展空间，一般的，当剩余空间小于10%时，要进行空间扩展。对于ORACLE数据库，通过查找TABLESPACES相关的数据字典可以看到有用的信息。.检查数据快速增长的表，通过对于DBA.SEGMENTS数据字典的监视可以找到，当过快增长时，协调开发人员，确定解决方案。.1.4重点表检查.检查系统核心业务表。因为这些表健康与否与日常业务的正常运行密切相关。重点检查这些表的索引是否失效，表的统W信息是否及时更新，如：当这些表进行了大的数据装载或者删除操作之后。原则上需要检查所有的表，只是由于上而这些表更关键，建议管理员给以更多的关注。.重点检查数据量超过百万行的表，各地的情况可能不一样，当数据超过百万行之后，如果索引失效会导致表扫描，占用大量系统10,严重影响系统性能。5查看数据库是否正常包括数据库实例是否正常工作、listener是否工作正常，确保数据库系统环境正常。数据库连接是否正常、检查是否有超出正常水平的连接数。如：平常500个，某天下午忽然达到600个。应记录这种异常情况。分析产生这种情况的原因，如：在低版本的ORACLE中，很可能是一些其他异常的应用出错后产生的死连接。6死锁检查监控数据库运行过程中，出现的阻塞，记录现象，记录产生阻塞的SQL语句，执行的用户，发生时间，频率，处理（杀掉、等待自然解锁等）。ORACLE版本中的死锁会在alert文件中产生记录，oracle会自动解锁（其实是选择一个杀掉）。对于死锁的处理过程要进行记录。可以使用OEM工具或者查找相关的V3视图来确认产生阻塞的语句。1.7监控SQL语句的执行查找效率低下的SQL语句，联系协调开发人员，进行相关处理。可使用ORACLE提供的AWR进行，也可使用ORACLE提供的OEM工具执行，或者自行编制的脚本等等。8操作系统级检查运行vmstat,sar,topas（AIX系统）,glance（HP系统）等命令检查CPU、内存、虚拟内存等的使用情况。运行df,du,iostat检查磁盘使用情况运行netstat检查网络情况运行手工编制的监控脚本检查。针对于操作系统的不同，使用的命令也会有不同，请参考相应的操作系统文档。建议使用man命令察看相应的帮助信息。9其他每天查看晚间定时执行的数据库信息收集作业和备份作业的日志输出，确认都已正常完成。往往不能正常完成是由于如下的原因：请确认脚本是否变动（错误的修改造成等等），设备（主机，磁盘阵列，磁带库，网络等等）是否正常，空间是否足够等等。建议每天按业务峰值情况，对数据库性能数据进行定时采集及分析。3数据库维护数据库维护工作内容包括维护、故障诊断、错误修复、备份恢复、历史数据迁移等过程。数据库维护工作事项3.1.1页面修复根据日常监控的结果，进行页而（或者数据库坏块）修复，如将表数据导出后重建表，然后导入数据。提交修复记录。1.2数据库对象重建根据数据库监控的结果，重建失效的对象。如：索引、存储过程、函数、视图、触发器等等。实用命令：Alterindex〈索弓I名〉rebuild[online];1.3碎片回收（数据重组）当某些数据库运行一段时间后，表会产生碎片，影响数据库的性能。可根据日常检查的结果，运用工具或脚本对于数据库空间进行重组或回收。由于ORACLE数据库本身的原因，在进行了DELETE操作之后也不会使HWM（HighWaterMark高水位线）降低，因此不会释放所占用的空间，所以建议在进行了数据迁移之后将全库进行EXP,然后进行IMP操作，以释放占用的空间。1.4删除不用的数据此项工作要得到开发方、设计人员、以及相关人员的确认后，方可执行。1.5备份恢复需要定期对于数据库备份进行有效性检测，定期进行数据恢复的演练操作。以防止万一的数据库事故时准备不足。数据库需要采用在线的热备份，不需要关闭数据库进行，在备份的同时可以进行正常的数据库的各种操作，满足了7*24的系统的需要。数据库的备份不能影响用户对数据库的访问。目标需要在线热备份多级增量备份并行备份，恢复减小所需要备份量备份，恢复使用简单可参考如下的方案：.每月做一个数据库的全备份（包含只读表空间）.每星期做一次零级备份（不包含只读表空间）.每个星期三做一次一级备份.每天做一个二级备份.任何表空间改成只读状态后做一个该表空间的备份。.当需要时(如四个小时归档文件系统就要接近满了)备份归档文件。1.6历史数据迁移定期进行历史数据迁移，减少生产数据库的压力。1.7定期修改密码包括SYS,SYSTEM等用户。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优化器总是能找到最好的查询策略。制定和执行纪录保证生产库的安全：应绝对禁止在生产库上进行开发、测试。1.9其他针对不同的数据库版本的不同特点进行相应的维护操作。具体情况请参见ORACLE文档或者访问metalinko4数据库管理常用SQL脚本常用的SQL脚本，在实施时可供数据库管理员参考，在执行时，需要进行相应的修改。.剩余空间检查SELECTtablespace_name,sum(blocks)asfree_blk,trunc(sum(bytes)/(1024*1024))asfree_m,max(bytes)/(1024)asbig_chunk_k,count(*)asnum_chunksFROMdba_free_spaceGROUPBYtablespace_name.表空间数据量情况显示SELECTtablespace_name,max_blocks,count_blocks,sum_free_blocks,to_char(100*sum_free_blocks/sum_alloc_blocks,'99.99')I!ASpct_freeFROM(SELECTtablespace_name,sum(blocks)ASsum_alloc_blocksFROMdba_data_.filesGROUPBYtablespace_name),(SELECTtablespace_nameASfs_ts_name,max(blocks)ASmax_blocks,count(blocks)AScount_blocks,sum(blocks)ASsum_free_blocksFROMdba_free_spaceGROUPBYtablespace_name)WHEREtablespace_name=fs_ts_name.表和索引分析BEGINdbms_utility.analyze.schema('&0WNER','ESTIMATE',NULL,5);END;.检查空间情况SELECTa.table_name,a.next_extent,a.tablespace_nameFROMall_tablesa,(SELECTtablespace_name,max(bytes)asbig_chunkFROMdba_free_spaceGROUPBYtablespace_name)fWHEREf.tablespace_name=a.tablespace_nameANDa.next_extent>f.big_chunk.检查已艮存在的空间扩展SELECTcount(*),segment_name,segment_type,dt.tablespace_nameFROMdba_tablespacesdt,dba_extentsdxWHEREdt.tablespace_name=dx.tablespace_nameANDdt.next-extent!=dx.bytesANDdx.owner='&OWNER'GROUPBYsegment_name,segment_type,dt.tablespace_name.检查没有主键M表SELECTtable_nameFROMall.tablesWHEREowner='&OWNER'MINUSSELECTtable_nameFROMall_constraintsWHEREowner='&&OWNER'ANDconstraint_type='P'.检查失效的主键SELECTowner,constraint_name,table_name,statusFROMall_constraintsWHEREowner='&OWNER'ANDstatus='DISABLED'ANDconstraint_type=,p,.重建索引，具体参数请根据实际情况进行修改SELECT'alterindex'index_name'rebuild','tablespaceINDEXESstorage(initial256Knext256K);'FROMall-indexesWHERE(tablespace_name!=’INDEXES'ORnext_extent!=(256*1024))ANDowner='&OWNER'.对比两个实例的不同SELECTobject_name,object_typeFROMuser_objectsMINUSSELECTobject_name,object_typeFROMuser_objects@&my_db_link.查看动态性能视图Select*fromV$FIXED_TABLE.查看约束selecta.constraint_name,a.constraint_type,a.*fromuser_constraintsawheretablename='tablename’;selectconstraint_name,column_namefromuser_cons_columnswheretablename='tablename";.查看索引user_indexes包含索引的名字,user_ind_columns包含索引的列..查看数据库启动参数：showparameterpara,vSparameter提供当前会话信息,v$system_parameter提供当前系统信息。其中isses_modifiable,issys_modifiable表示是否允许动态修改。.查看近程号：selectp.spid,s.usernamefromv$processp,v$sessionswherep.addr=s.paddr;.查看数据文件：selectname,statusfromv$datafile;select*fromdba_data_files;.查看数据文祥状态selectd.file#fit,d.name,d.status,h.statusfromv$datafiled,v$datafile_headerhwhered.file#=h.file#;.查看控制文件selectnamefromv$controlfile;selecttype,record_size,records_total,records_usedfromv$controlfile_record_sectionwheretype='DATAFILE';.查看是否归档模式：archiveloglistselectname,log_modefromv$database;selectarchiverfromv$instance;.查看日志组：selectgroups,current_group#,sequence#fromv$thread;selectgroup#,sequence#,bytes,members,statusfromv$log;select*fromv$logfile;其中status为空表示正常。.查看largepoolselect*fromv$sgastatwherepool='largepool';.查看归档位置showparameterarchiveselectdestination,binding,target,statusfromv$archive_dest;.查看归档进程select*fromv$archive_processes;.查看正在备份的数据文件select*fromv$backup;.查看需要恢复的文件select*fromv$recover_file;.查看所有归档E志文件select*fromv$archived_log;.查看恢复时要用到的日志文件select*fromv$recovery_log;.查看SGA的结构Showsga;select*fromv$sgastat;.提取librarycache的命中率selectgethitratiowheredb_block_gets+consistent_gets>0;.查看freelist的竞争selectclass,count,timefromv$waitstatwhereclass='segmentheader*;selectevent,total_waitsfromv$system_eventwhereevent=’bufferbusywaitsf;bufferbusywaits可在两种情况发生:ldirtyqueue已满,2freelist竞争。.查看freelist竞争发生在哪个segment上selects.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.statefromdba_segmentss,v$session_waitwwherew.event='bufferbusywaits'andw.pl=s.header_fileandw.p2=s.header_block;.查看全表扫描发生的次数selectname,valuefromv$sysstatwherenamelike飞tablescan%*;.查看大操作的执行情况selectsid,serial#,opname,to-char(start_time, 'HH24:MISS')asstart_t,(sofar/totalwork)*100aspercent_completefromv$session_longops;.查看数据文件的I/Oselectphyrds,phywrts,d.namefromv$datafiled,v$filestatfwhered.file#=f.file#orderbyd.name;.查看空闲块数少于10%的segment(blocks在high-watermark以下，empty_blocks其上)selectowner,table_name,blocks,empty_blocksfromdba_tableswhereempty_blocks/(blocks+empty_blocks)<0.1andblocks+empty_blocks!=0;.查看migration和chaininganalyzetabletable_namecomputestatistics;selectnum_rows,chain_cntfromdba_tableswheretable_name="table-name*;.查看表的余计信息analyzetabletable_namecomputestatistics;selectnum_rows,blocks,empty_blocksasempty,avg_space,chain_cnt,avg_row_lenfromdba_tableswhereowner=’HR'andtabie_name='tabie_name';.查看索引的统计信息analyzeindexindex_namevalidatestructure;select(de1_1f_rows_len/1f_rows_len)*100asindex_usagefromindex_stats;5日常维护和问题管理目的作为一名数据库管理员，数据库应用的正常稳定运行是保障我们应用系统的重要保证，而数据库的日常监控和维护工作至关重要。做好它的检查工作的文档管理也是数据库管理的必不可少的工作之一，这有利于我们追踪问题和对数据库的版本控制非常重要。例行工作建议每天例行工作：.确定数据库