oracle管理与优化

管理与优化第1页共88页（一）故障诊断1、监控数据库1.1tracefile每个服务器进程和后台进程，都会将侦测到的内部错误的相关信息写进对应的trace文件。可以使用MAX_DUMP_FILE_SIZE参数指定所有的trace文件（不包括alert日志）的最大尺寸可以通过设置SQL_TRACE参数为TRUE开启SQL追踪功能，开启后所有的SQL语句就会形成性能统计信息存储在ADR的trace文件中；也可以使用语句altersessionsetSQL_TRACEtrue开启指定会话的SQL追踪功能。同时可以使用DBMS_SESSION或DBMS_MONITOR包控制一个会话的SQL追踪。1.2alertlog告警日志主要包含以下信息和错误日志：（1）所有的内部错误(ORA-600),块中断错误(ORA-1578)以及死锁(ORA-60)（2）管理操作,例如CREATE、ALTER、DROP语句和STARTUP、SHUTDOWN、ARCHIVELOG语句（3）有关共享服务和调度进程的信息和错误（4）物化视图自动refresh过程中发生的错误（5）数据库实例启动时所有有非默认值的初始化参数的值告警日志同时维护XML格式文件和text格式文件。可以通过text编辑器查看两种格式的文件，也可以使用ADRCI组件查看XML文件。Alert日志文件盒所有的trace文件都写在AutomaticDiagnosticRepository（ADR）中，ADR目录由DIAGNOSTIC_DEST初始化参数指定；trace文件名由操作系统指定，不过名称通常包含进程名1.3关于Server-GeneratedAlertsAserver-generatedalertisanotificationfromtheOracleDatabaseserverofan迫切的问题，通知可能包含修改问题的建议；问题清楚后也会提供通知；告警会在问题发生或数据没有达到预期度量时自动形成，例如：（1）PhysicalReadsPerSecond（每秒物理读）（2）UserCommitsPerSecond（每秒用户提交数）（3）SQLServiceResponseTime（SQL服务响应时间）Server-generatedalerts可以是基于阈值的或者是基于事件发生的；其中阈值可以是自定义的，也可以是内部估值；例如当表空间的使用比超过85%或97%时，会分别形成warning、critical级别的Server-generatedalert；常见的非基于阈值的服务器告警有：（4）SnapshotTooOld（快照过旧）（5）ResumableSessionSuspended（可恢复会话暂停）（6）RecoveryAreaSpaceUsage（恢复区域空间使用）一个告警信息会被发送至预定义的持久队列ALERT_QUE，该队列属于sys用户。OEM读取ALERT_QUE队列，并将显著的告警显示在OEM的主页；同时还可以配置将这些告警通过E-mail发送至指定的管理员；如果一个告警不能写到ALERT_QUE不能写到告警队列，管理与优化第2页共88页则一条关于其的信息会被写到alertlog中；后台进程会定期将告警数据刷至ADR中，同时捕获历史度量；alert的历史表和ALERT_QUE会定期被自动清除。可以通过DBMS_SERVER_ALERTS包的SET_THRESHOLD、GET_THRESHOLD程序查看、改变服务器告警度量的阈值设置；还可以通过以下动态性能视图查看服务器告警的相关信息：V$METRICNAME：Containsthenames,identifiers,andotherinformationaboutthesystemmetricsV$METRIC：Containssystem-levelmetricvaluesV$METRIC_HISTORY：Containsahistoryofsystem-levelmetricvaluesV$alert_types：显示关于服务器预警类型的信息dba_thresholds：显示数据库中所有阈值的名称以及警告、严重预警的值dba_outstanding_alerts:包含仍需解决的所有阈值预警仅包含阈值预警，即有状态预警）dba_alert_history：提供不再重要的所有预警历史信息包含已解决的阈值预警，以及所有的基于事件的预警（基于事件的预警直接进入此视图）1.4监控性能1监控锁Oralce数据库在执行SQL语句时，自动获取和管理必要的锁，所以不需要关心其中的细节，但是oracle也允许手动使用锁。对于死锁，oracle会自动侦测并回滚其中一个语句，以释放锁冲突。utllockt.sql脚本显示系统中正在等待锁的会话，以及这些这些锁的详情。catblock.sql脚本创建utllockt.sql需要的锁视图，需要在utllockt.sql之前使用2监控等待事件会话可能会因为各种原因而等待，如：等待输入、等待磁盘写、等待锁或闩；事件等待预兆很多问题，如闩争用、缓冲池争用以及I/O争用详情可阅读OracleDatabasePerformanceTuningGuide.3有关性能的视图V$LOCK：ListsthelockscurrentlyheldbyOracleDatabaseandoutstandingrequestsforalockorlatchDBA_BLOCKERS：DisplaysasessionifitisholdingalockonanobjectforwhichanothersessioniswaitingDBA_WAITERS：DisplaysasessionifitiswaitingforalockedobjectDBA_DDL_LOCKS：ListsallDDLlocksheldinthedatabaseandalloutstandingrequestsforaDDLlockDBA_DML_LOCKS：ListsallDMLlocksheldinthedatabaseandalloutstandingrequestsforaDMLlockDBA_LOCK：ListsalllocksorlatchesheldinthedatabaseandalloutstandingrequestsforalockorlatchDBA_LOCK_INTERNAL：Displaysarowforeachlockorlatchthatisbeingheld,andonerowforeachoutstandingrequestforalockorlatchV$LOCKED_OBJECT：Listsalllocksacquiredbyeverytransactiononthesystem管理与优化第3页共88页V$SESSION_WAIT：ListstheresourcesoreventsforwhichactivesessionsarewaitingV$SYSSTAT：ContainssessionstatisticsV$RESOURCE_LIMIT：ProvidesinformationaboutcurrentandmaximumglobalresourceutilizationforsomesystemresourcesV$SQLAREA：ContainsstatisticsaboutsharedSQLareaandcontainsonerowforeachSQLstring.AlsoprovidesstatisticsaboutSQLstatementsthatareinmemory,parsed,andreadyforexecutionV$LATCH：Containsstatisticsfornonparentlatchesandsummarystatisticsforparentlatches2、管理诊断数据2.1概论当关键性的错误发生时，它会被分配一个事故号，同时相关诊断数据立即被捕获；然后诊断数据将会被存储在ADR中；错误诊断结构的目标主要是：■First-failurediagnosis■Problemprevention■Limitingdamageandinterruptionsafteraproblemisdetected■Reducingproblemdiagnostictime■Reducingproblemresolutiontime■SimplifyingcustomerinteractionwithOracleSupport完成以上目标的关键技术有：（1）错误发生时自动收集诊断数据---收集的数据存储在ADR中，可以通过EM和命令行访问。（2）标准的追踪格式---方便数据库管理员和OracleSupport人员分析（3）健康检查（Healthchecks）---关键错误发生时，会有一个或多个健康检查进行深度分析，其结果会附件在其它的诊断数据之中。作为DBA可以手动运行（4）Incidentpackagingservice(IPS)---自动收集诸如追踪文件、dumps、健康检查报告等诊断数据，并打包生成ZIP文件发送给OracleSupport；在生成ZIP文件之前，IPS会将收集的诊断数据放入叫做”incidentpackage“的中间逻辑结构，incidentpackage存储在ADR中；可以选择的是，能够访问、修改incidentpackage的内容，同时在任何时刻增加或删除额外的诊断数据，在准备好后再生成ZIP文件并发送至OracleSupport。（5）DataRecoveryAdvisor---数据恢复顾问结合healthchecks和RMAN查询数据中断问题，并评估每个问题的程度（critical,highpriority,lowpriority），描述问题的影响，给出修复操作的建议。（6）SQLTestCaseBuilder---快速收集错误发生时信息以及环境，然后上传给OracleSupport，供其技术人员再现问题的发生。2.2关于错误1ADR错误可分为两个概念：问题（problem）和事故（incident）管理与优化第4页共88页problem：问题是数据库中的危险错误，其可以是内部错误，例如ORA-00600，也可以是其它服务器错误，例如ORA-07445、ORA-04031等；每个problem都有问题键值（key），并附含字符串描述incident：事故是问题的单一事件；每个事故都由唯一的事故ID确定，当事故发生时，数据库完成以下行为：■在alertlog中记录信息■发送事故告警至OEM页面■收集首次事故的诊断数据至incidentdumps中■在incidentdumps之上附件事故ID标签■在ADR中存储对应的incidentdumps2事故洪流控制鉴于同一个problem在短时间内会出现多次incident，对应的诊断数据的收集会对空间和性能带来一定的影响，所以引入事故洪流控制；当同一个问题下的事故出现次数达到指定阈值后，会引发洪流控制，变为受控事故。受控事故只形成alertlog信息，并不形成incidentdumps。洪流控制的阈值是预定义且无法修改的，具体如下：■相同problemkey在一个小时内出现5次incident后，该问题下后续出现的事故变为受控事故；下一个小时将会恢复正常■相同problemkey在一天内出现25次incident后，该问题下后续出现的事故也会变为受控事故；下一天将会恢复正常此外，如果同problemkey一小时内出现50次incident，或一天内出现250次，则该problemkey随后的incident将完全不在ADR中记录；对于这种情况，oracle只在alertlog中提示将不会再记录incident信息，并每十分钟提示一次，直至1个小时或1天到期恢复正常。2.3错误诊断的组成结