Advisors and Fault Diagnostics and Flashback Enhancements

Advisors and Fault Diagnostics

Oracle Database 10g introduced an impressive plethora of database performance advisors like the Segment Advisor, the Undo Advisor, the SQL Access Advisor, the SQL Tuning Advisor, the MTTR Advisor, and the ultimate expert system for tuning database performance: the Automatic Database Diagnostic Monitor (ADDM). Oracle Database 11g expands this advisory framework with several new Database Repair Advisors. The chief goals of these new Advisors are to locate root causes of a failure, identify and present options for repairing these root causes, and even correct the identified problems with self-healing mechanisms. Oracle Database 11g also adds a series of improved fault diagnostics to make it extremely easy for even an inexperienced DBA to detect and quickly resolve problems with Oracle Database 11g. Here are the highlights of these new features-

# Automatic Health Monitoring. When a problem within the database is detected, the new Health Monitor (HM) utility will automatically perform a series of integrity checks to determine if the problem

can be traced to corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks. HM can also be fired manually to perform checks against the database's health on a periodic basis.

# Automatic Diagnostic Repository. The Automatic Diagnostic Repository (ADR) is at the heart of Oracle Database 11g's new fault diagnostic framework. The ADR is a central, file-based repository external to the database itself, and it's composed of the diagnostic data -- alert logs (in XML format), core dumps, back-ground process dumps, and user trace files -- collect-ed from individual database components from the first moment that a critical error is detected.

# Support Workbench. Though it's stored outside of the database itself, the ADR can be accessed via

either Enterprise Manager or command-line utilities. Once the ADR has detected and reported a critical

problem, the DBA can interrogate the ADR, report on the source of the problem, and in some cases

even implement repairs through the Support Workbench, a new facility that's part of Enterprise Manager.

# Incident Packaging Service. If the problem can't be solved using these tools, it may be time to ask for help from Oracle Support. The new Incident Packaging Service (IPS) facility provides tools for

gathering and packaging all necessary logs that Oracle Support typically needs to resolve a Service

Request.  

# Hang Manager. Oracle Database 10g introduced the Hang Analysis tool in Enterprise Manager, and

Oracle Database 11g now expands this concept with the Hang Manager. Through a series of dynamic

views, it allows the DBA to traverse what's called a hang chain to determine exactly which processes and sessions are causing bottlenecks because they are blocking access to needed resources. And since it's activated by default on all single-instance databases, RAC clustered databases, and ASM instances, it's now possible to track down the source of a hang from one end of the system to the other.

Flashback Enhancements

Oracle Database 10g dramatically expanded database recoverability with the ability to perform an incomplete recovery of the database with Flashback Database. Oracle Database 10g also provided four new logical database recovery features: Flashback Table, Flashback Drop, Flashback Version Query, and Flashback Transaction Query. Oracle Database 11g expands this arsenal of recovery tools with two new. Flashback features-

# Flashback Transaction. Essentially an extension of the Flashback Transaction Query functionality introduced in Oracle Database 10g, Flashback Transaction allows the DBA to back out of the database one or more transactions -- as well as any corresponding dependent transactions -- by applying the appropriate reciprocal UNDO statements for the affected transaction(s) to the corresponding affected rows in the database.

# Total Recall. This new feature offers the ability to retain the reciprocal UNDO information for critical data significantly beyond the point in time that it would be flushed out of the UNDO tablespace. Therefore, it's now possible to hold onto these reciprocal transactions essentially indefinitely. Once this feature is enabled, all retained transaction history can be viewed, and this eliminates the cumbersome task of creating corresponding history tracking tables for critical transactional tables. And as you might expect, Oracle Database 11g also provides methods to automatically purge data retained in the data archive once a specified retention period has been exceeded.