数据库日常运行维护方案

数据库日常运行维护方案Oracle数据库日常运行维护方案2019年3月1项目背景及目标1.1项目背景XXX信息化建设经过多年的发展和完善，已经建立成熟的网络环境及业务及管理的各类应用系统，目前在线运行的PC近XX台，近年来建设的XX业务管理等若干应用信息系统多数是基于Oracle数据库系统的应用。这些Oracle数据库产品的标准服务都已经过了服务期。而各系统随着数据量的逐年增加，陆续出现了性能问题，有必要进行数据库系统的升级及性能优化，以确保应用系统的正常运行，为XXX提供更好的信息服务。1.2项目目标尽早发现性能瓶颈，及时调整，保障数据库稳定高效工作；对各个系统数据库进行补丁升级服务，安装补丁前需要对补丁的可行性及风险即你想那个分析，并制定升级计划和应急回退计划。同时要做好系统备份准备及详细的测试工作，确保系统的稳定性、安全性，保障系统业务数据的安全；数据库架构的合理化；提升应用系统性能，完成各系统数据库的性能调优工作，包括：外部资源调优、行的重新安排调优、SQL性能调优、表格和索引存储参数设置调优等。各业务持续性得到有效的保证。2需求分析通过对xxx技术要求进行详实的分析以及xxx信息系统建设的了解，各应用系统的Oracle产品日常运行维护项目主要从如下几个方面进行：1、由于xxx有些系统软件建设的较早，目前存在不同版本的数据库共存的现象，包括：Oralce8、Oracle9I、Oracle10g以及Oracle11g等。而Oracle9I版本之前的数据库SQL编程语句还不是业界通用的标准化的语句，它与后面版本的SQL编程语句有很大的差别，所以在这方面的性能优化需要做好充分备份的准备。2、正是由于这些系统建设的较早，基于当时的实际情况，应用系统或数据库都还存在一些不足，针对这些情况软件开发商都开发出相应的补丁提供给用户进行升级以防范风险。所以在对各个系统数据库进行补丁升级服务之前，需要对补丁的可行性、安全性及风险进行充分的测试和分析。并制定相关的应急预案及数据库升级计划和应急回退计划，同时还需要做好系统备份准备和详细的测试工作，以确保系统的稳定性、安全性，从而保证系统业务数据的安全；3、如上所说，这些系统建设的较为长久，由于长时间的运行各个系统存在一些冗余，由于冗余的存在使得这些系统数据库需要进行性能的优化，包括外部资源优化、行的重新安排以及SQL性能优化、表格和索引存储参数等需要重新进行设置优化。4、对于当前的一些应用如：企业资产管理系统(EAM)、基建MIS管理系统、全面预算管理系统、生产综合管理系统、企业门户（EIP/EAI）系统、综合指标统计分析系统、燃料管理信息系统、标准化管理信息系统、档案管理信息系统、安健环管理系统、技术监督管理子系统、IT运维服务系统、SIS系统接口数据库、生产图纸管理系统等等所有这些系统都需要重新进行整理并形成一个完善的文档资料。5、由于这些数据库系统承载着xxx非常重要的业务系统数据，所以在日常维护中需要非常仔细，每周、每月、每季都需要有相应的巡检记录，需要详细记载以下一些内容：监控数据库对象的空间扩展情况监控数据量的增长情况系统健康检查数据库对象有效性检查查看是否有危害到安全策略的问题。查看alert、Sqlnet等日志并归档报错日志分析表和索引查看对数据库会产生危害的增长速度检查表空间碎片数据库性能调整预测数据库将来的性能调整和维护工作后续空间3项目总体方案建立在Oracle数据库上的关键业务系统，是当今企业的核心应用。如何改善其性能和可用性，是包括系统设计、维护和管理人员的最大挑战。为了更好地维护系统和数据库，必须随时了解系统和数据库的运行状况。但由于数据库维护具有一定的复杂性，增加了维护工作的难度。所以数据库维护需要借助一些相关的工具，优秀的数据库管理工具，可以大大简化生产环境下的应用维护和管理，提高IT人员的工作效率。数据库管理人员借助相应的工具可以主动、迅速、方便的监控系统的运行。基于我公司多年在Oracle数据库的使用及研究经验上，对于Oracle数据库的管理，主要包括三方面的内容：系统诊断：了解当前运行的Oracle的状态，发现数据库性能瓶颈；空间管理：即数据库存储结构的调优，包括定期检查数据库的存储结构，发现Oracle数据库存储中的主要问题(如数据库碎片)，进行碎片重组和数据分布以及容量规划等；调优SQL,分析对系统性能影响比较大的SQL语句，调整SQL语句的执行效率。使SQL存取尽可能少的数据块。下面我们将从以下这几个方面详细阐述：3.1数据库性能优化Oracle性能管理既是一种艺术，也是一种科学。从实用角度讲，它可以分为两种类型，主动式和被动式性能管理。主动式性能管理涉及到特定系统实施初期的设计和开发，包括硬件选择、性能及容量规划，海量存储系统的选择，I-O子系统配置及优化，以及如何对不同组件进行定制，以满足Oracle数据库和应用系统的复杂要求。被动式性能管理涉及到现有环境中不同组件的性能评估、故障排除和Oracle环境的优化。本文旨在探讨如何进行被动式性能调优，以便为Oracle性能调优提供必要的指导，从而避免仅仅通过反复尝试的方式进行性能调优，提高Oracle性能管理的效率。所以ORACLE数据库性能恶化表现基本上都是用户响应时间比较长，须要用户长时间的等待。获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。对于以上的两个问题，通常我们采用以下几个方面来进行改善：调整服务器内存分配。例如，可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。调整硬盘I/O问题，达到I/O负载均衡。调整运用程序结构设计。优化调整操作系统参数和使用资源管理器。SQL优化、诊断latch竞争、Rollback(undo)Segment优化、提升block的效率等。3.1.1检查Oracle数据库性能检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志ORACLE产品日常运行维护年度服务项目缓冲区，总共十个部分。检查数据库的等待事件setpages80setlines120coleventfora40selectsid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfromv$session_waitwhereeventnotlike'SQL%'andeventnotlike'rdbms%';如果数据库长时间持续出现大量像latchfree，enqueue，bufferbusywaits，dbfilesequentialread，dbfilescatteredread等等待事件时，需要对其进行分析，可能存在问题的语句。DiskRead最高的SQL语句的获取SELECTSQL_TEXTFROM(SELECT*FROMV$SQLAREAORDERBYDISK_READS)WHEREROWNUM<=5;查找前十条性能差的sqlSELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;等待时间最多的5个系统等待事件的获取SELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSDESC)WHEREROWNUM<=5;检查运行很久的SQLCOLUMNUSERNAMEFORMATA12;COLUMNOPNAMEFORMATA16;COLUMNPROGRESSFORMATA8;SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;检查消耗CPU最高的进程SETLINE240;SETVERIFYOFF;COLUMNSIDFORMAT999;COLUMNPIDFORMAT999;COLUMNS_#FORMAT999;COLUMNUSERNAMEFORMATA9HEADING"ORAUSER";COLUMNPROGRAMFORMATA29;COLUMNSQLFORMATA60;COLUMNOSNAMEFORMATA9HEADING"OSUSER";SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&1%';检查碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);检查表空间的I/O比例SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAME"FILE",F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBYDF.TABLESPACE_NAME;检查文件系统的I/O比例SELECTSUBSTR(A.FILE#,1,2)"#",SUBSTR(A.NAME,1,30)"NAME",A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROMV$DATAFILEA,V$FILESTATBWHEREA.FILE#=B.FILE#;检查死锁及处理colsidfor999999;colusernamefora10;colschemanamefora10;colosuserfora16;colmachinefora16;colterminalfora20;colownerfora10;colobject_namefora30;colobject_typefora10;selectsid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_idfromdba_objectso,v$locked_objectl,v$sessionswhereo.object_id=l.object_idands.sid=l.session_id;检查数据库cpu、I/O、内存性能记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。CPU使用情况注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu，当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。内存使用情况：如上所示，蓝色部分表示系统总内存，红色部分表示系统使用的内存，黄色部分表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。系统I/O情况：如上所示，蓝色字体部分表示磁盘读写情况，红色字体部分为cpuIO等待情况。系统负载情况：如上所示，蓝体字部分表示系统负载，后面的3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常。查看是否有僵死进程selectspidfromv$processwhereaddrnotin(selectpaddrfromv$session);有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。定期做统计分析对于采用OracleCost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explainplan。在以下情况更需要进行统计信息的更新：应用发生变化；大规模数据迁移、历史数据迁出、其他数据的导入等；数据量发生变化。查看表或索引的统计信息是否需更新，如：Selecttable_name,num_rows,last_analyzedFromuser_tableswheretable_name='DJ_NSRXX'；selectcount(*)fromDJ_NSRXX；如num_rows和count(*)如果行数相差很多,则该表需要更新统计信息，建议一周做一次统计信息收集，如：execsys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade=>TRUE,degree=>4);检查缓冲区命中率SELECTa.VALUE+b.VALUElogical_reads,c.VALUEphys_reads,round(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME='dbblockgets'ANDb.NAME='consistentgets'ANDc.NAME='physicalreads';如果命中率低于90%则需加大数据库参数db_cache_size。检查共享池命中率selectsum(pinhits)/sum(pins)*100fromv$librarycache;如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数sharedpool的大小。检查排序区selectname,valuefromv$sysstatwherenamelike'%sort%';如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false或pga_aggregate_target(workarea_size_policy=true)。检查日志缓冲区selectname,valuefromv$sysstatwherenamein('redoentries','redobufferallocationretries');如果redobufferallocationretries/redoentries超过1%，则需要增大log_buffer。3.1.2性能调优及方法性能调优主要有主动调优和被动调优，主动调优在前面我们已经进行了阐述，被动调优主要有以下方法进行。1、确定合理的性能优化目标。2、测试并记录当前的性能指标。3、确定当前存在的Oracle性能瓶颈(Oracle中何处存在等待，哪个SQL语句与此有关)。4、确定当前的操作系统瓶颈。5、优化相关的组件(应用、数据库、I/O、连接OS及其它)。6、跟踪并实施变化管理制度。7、测试并记录目前的性能指标。8、重复第3到第7步直至达到既定的优化目标。不要对并非性能瓶颈的部分进行优化，否则可能引起额外的问题。正如任何聪明的人会告诉你的：“如果还未坏，千万不要修”。更重要的是，一旦既定的优化目标已经达到，就务必停止所有的优化。获取Oracle的性能指标(测试前及测试后)必须在峰值处理时测试并获取系统在优化前和优化后的性能指标。数据采集不应在数据库instance刚刚起动后进行。同时，测试数据应在峰值期间每过15分钟进行一次。初始化参数TIMED_STATISTICS应该被设为TRUE。通过运行以下脚本开始快照：$ORACLE_HOME/rdbms/admin/utlbstat.sql通过运行以下脚本结束快照：$ORACLE_HOME/rdbms/admin/utlestat.sql完成utlestat.sql操作后，会在当前目录中生成名为“report.txt”的文件，包含系统的性能数据。该报告包括每15分钟捕获的所有与Oracle例程相关的参数。寻找问题根源如上所述，通过查看v$system_event事件开始系统事件的问题诊断。下一步是查看v$session_event，找出引起或经历等待事件的进程。最后一步是通过v$session_wait获得事件的细节。同时，应该进一步通过OS进行深入分析，了解核心的CPU、内存和IO状态参数。最后，结合两种不同的诊断的结论，找出系统瓶颈所在。System_Event事件v$system_event可以从全局的角度查看Oracle系统中的所有事件。尽管它并不包括任何进程级的信息(当前或历史)，但却可以显示上次例程弹出后总的等待时间。这种动态性能视图中的数据，会在下次例程起动时清零。出于这种原因，这种视力中的数据应该在不同时段进行抽样。Session_Event事件v$session_event视图在进程级提供与v$system_event相同的信息(即，SID等)。这种视图可以从“system-wideevents”级进一步钻取，到达进程级，以确哪个进程引起或经历了等待事件。Session_Wait事件v$session_wait视图在特定事件的进程级提供低层次的信息挖掘。不同于其它一些视图，这种方式可以“实时”获取进程级的等待信息。这是真正有用的信息。切记，每次查看这一视图得到的结果可能不一样。这可能与数据库中当前的活动有关。应用优化从统计(和现实)的角度看，80%的Oracle系统性能问题可以通过SQL代码优化来解决。任何应用优化的过程，不外乎是索引优化、全表扫描、并行机制改进和选择正确数据组合方法的过程。这正是要达到最佳应用性能所必须考虑的因素。没有SQL的优化，就无法实现高性能的应用。良好的SQL语句可以减少CPU资源的消耗，提高响应速度。同时，优化后的SQL语句还可以提高应用的可扩展性，这是除增加大量内存外，任何其它硬件手段也无法实现的。.1例程调优需要配置的主要初始化参数：以下是一些已知与例程优化关系最密切的一些核心Oracle初始化参数。它们都会影响Oracle及SGA区的活动。任何对这些参数的改动，在实施到生产环境之前，都必须进行测试。一旦改变了生产环境的参数，就必须对相关的Oracle动态性能指标和操作系统的性能进行监测，寻找可能由此产生的异常现象。1）DB_BLOCK_SIZE该参数在数据库建立前设定，决定了数据库中每个数据块的大小。只有重新建立数据库，才有可能改变该参数。db_block_size的配置应遵循以下公式：DB_BLOCK_SIZE=FILESYSTEMBLOCKSIZE>=O-SPAGESIZE这可以确保Oracle获得最佳I/O性能，同时不会由于冗余或不必要的I/O，给I/O子系统带来压力。2）DB_BLOCK_BUFFERS该参数决定了SGA区数据库缓冲区中的块数量。由于这是Oracle读取和写入的区域，它的不正确配置会引起严重的I/O性能问题。尽管缓冲区的大小与应用性质、数据库大小、同步用户数等无关，它的确是SGA区中最大的组件。经常可以看到缓冲区占用75-80%SGA区内存的情况。另外，这一参数设置过大，也会引起整个系统的内存不足，引起操作系统过多的读写操作。该参数及SHARED_POOL_SIZE通常是两个最重要的SGA优化目标。只有当数据库缓冲率长时间低于70%时，才需要增加其大小说。即使在这种情况下，也需要进一步审查应用的性能和整个系统的吞吐性。若存在延迟性的应用设计问题，则无论数据库缓冲区的大小如何，缓冲和读写率都不会有太大改变为。在实调优中，也曾发现由于SQL语句的问题，出现缓冲率很高，但仍存在全系统性能问题的情况。3）SHARED_POOL_SIZE该参数按字节数设定，定义了SGA中共享区的大小。该组件的大小严重依赖于应用的类型(即该应用是重用SQL，还是生成动态SQL，等等)。同时它也取决于同步用户的数量，以及实例是否被配置成支持多线程服务器(MTS)。如果该应用采用了MTS配置，则共享区应该明显增加，因为光标状态和用户进程数据等程序全局区域(PGA)都被置入了共享区。有关多数应用的SHARED_POOL_SIZE大小设置，可以从每10个同步用户16MB共享区开始。这不是一成不变的，因为应用的性质最终会决定该组件的大小。只有当库缓冲和字典缓冲使用率一直低于90%时，才需要关注这一参数。但如果应用并未采用变量合并和/共离图标时，内存的数量并不会使缓冲使用率高于90%。共享区过大会导致处理时间增加，甚至SQL语句的挂起。如果应用不能有效地重用SQL，则无论配置多大的库缓冲或字典缓冲都无济于事，不能改善缓冲使用率。另一个值得考虑的因素是需要随时使用的存储PL/SQL代码数量。应用的核心包可以通过查看DBA_SOURCE、USER_SOURCE得以确认，其大小通过查询DBA_OBJECT_SIZE了解。另外，为了确定存储PL/SQL是否被置于内存，可以查询动态性能视图V$DB_OBJECT_SIZE。内时，包DBMS_SHARED_POOL中的程序大小可被用于确定应用中大包的规模。4）LOG_BUFFER根据字节设定，该参数定义了SGA缓冲区中redolog的大小。缺省值通常是数据库块大小的四倍，这对于多数环境并不是最佳的。对于中型的Oracle环境，其结构应该为512Kb左右。对该存储结构而言，更大并不意味着更好。超过1MB就可能有问题。需要监控V$SESSION_WAIT中logbufferspace的等待事件，以优化该内存结构。需要提醒的是，在线redolog文件的大小设置不当，会引起redo请求的等待。5）DB_WRITERS该参数可以针对所有文件系统支持，且不可使用DirectI-O的Oracle实施设定。这并不需要与rawpartitions一起使用，因为异步I-O更加。建议将该参数设定为(2*独立磁盘驱动器数量/卷)。该参数只有在report.txt中的“averagewritequeuelength”持续高于1时，才需要设定。在Oracle8.0和更高版本中，该参数已不再被支持，而为其它两个名为DB_WRITER_PROCESSES和DBWR_IO_SLAVES的参数取代。若需要设置DB_WRITER_PROCESSES值高于8，则DB_WRITER_PROCESSES可被设为1，且DBWR_IO_SLAVES可被设为“n”，其中n的值必须设置为(2*独立磁盘驱动器数量/卷)。.2I-O优化I-O优化是系统优化中的一个关键步骤，还涉及到其它任务，将文件在不同驱动器/卷中进行分布，采用优化分区技术、确定I-O子系统瓶颈、确定控制器瓶颈并根据应用的类型选择最佳的RAID级。I-O优化应该在全面了解Oracle及OracleRDBMS结构之后进行。应该在进行I-O优化前后实施I-O数据监控，如平均服务时间，IOPS，平均磁盘队列长度等。.3竞争优化多数与Oracle有关的竞争问题可以通过主动配置管理相关的初始化参数进行。不恰当地配置init.ora中的锁参数可能引起竞争。为了不打破其中的平衡，所需的参数可进行配置并主动得以处理。包括表在内的数据库对象可能存在两个竞争点。第一个是所配置的“freelists”的数量(缺省值为1)。freelist结构维护着表中可用于插入的块。对于存在大量同步插入的表，有必要配置该结构。为了以主动方式处理freelist竞争，必须在建立表时配置FREELISTS。可考虑的最佳值为(2*CPU数量)。V$WAITSTAT不可能指示存在freelist竞争，除非存在freelist组，而这种设置只存在于OracleParallelServer中。即便如此，也无法了解哪个表存在竞争中。主动式的freelist竞争调优可以事先预防问题出现。资源竞争的第二个来源与索引有关，即对象块头中配置的事务槽数量。事务槽是块头中的区域，是事务处理进程采用自身识别号进行注册，以便任何被修改的更能够通过特定事务槽数量在低层得以识别的地方。如果所有现存的事务槽已经被其它事务占用，服务器器进程会从块的PCTFREE中请求23个字节，建立一个新的槽。这种情况适用于存在大量同步事务的对象。对于事务槽的竞争，需要设置INITRANS参数。对于块大小为8K的数据库，多数情况下，4为最佳设置，占用的空间仅为92字节，却可以大大减少运行时故障和性能问题。.4O-S监控数据库忙时，应该对操作系统进行监控，因为操作系统的性能指标会揭示数据库活动的性质及其对系统的影响。例如，为了了解CPU的利用率，可以通过systemactivityreporter(sar–uintervalfrequency)、mpstat(SunSolaris),top(多数UNIX)、osview(SGIIrix)及vmstat等命令。Sar和vmstat也可被用于确定包括内存使用率、I-O参数、队列等待、读取/交换区活动等信息。在Solaris上，mpstatutility也可用于获取前面提到的CPU利用率数据。Solaris上的Adrian性能管理工具也很有用。可以利用其中的一到多个工具来确定系统的性能状况，找出可能存在的瓶颈。Oracle数据库性能的管理需要遵循系统的方法论，以确保所有核心问题得以解决。多数问题可以事先得以管理。了解与O-S相关的问题是成功的关键。勿需