Oracle RAC carries the promise of better
performance through horizontal scalability. With proper load
balancing, dividing the work among multiple nodes allows for greater
throughput overall. For instance, if a single instance is
highly I/O bound, the CPU may be wasted due to the physical I/O
bottleneck. With RAC and services, the load can be distributed
to multiple servers, allowing more resources to be used across the
In order to monitor and tune the system, Oracle
introduced the AWR and Automatic Database Diagnostic Monitor ADDM in
Oracle 10g. The AWR is not only responsible for storing
database snapshot statistics for automatic tuning, but also for
producing reports that are useable by a Remote DBA to further tune their
system. ADDM creates human readable suggestions on ways to
optimize the performance by eliminating bottlenecks based on
information from the AWR. In Oracle 11g, ADDM reports have
been extended to also provide diagnostic information about RAC
ADDM for RAC
The Automatic Database Diagnostic Monitor (ADDM)
was introduced in Oracle 10g. Using information from the AWR,
ADDM would provide “plain English” solutions to the Remote DBA in the form
of findings. The report would show the impact of issues on a
database instance and offer solutions to fix the problem.
In Oracle 11g, ADDM has been extended to include
RAC, and provides information on the entire cluster including
latency issues on the cluster interconnect, global cache hot blocks
(blocks with concurrency issues across multiple nodes), and general
object usage information across multiple nodes.
The key to using ADDM for RAC is running the
tool in database mode. Remember that a RAC cluster is a single
database with multiple instances; normally ADDM is run against an
instance. In database mode, a single instance system will run
the same; however, a RAC cluster will report on multiple nodes.
Creating an ADDM Task
If the object is to run in database mode, the
following syntax can be used:
task_name => ‘name for your task’, -
begin_snapshot => begin_snapshot_num, -
end_snapshot => end_snapshot_num, -
db_id => db_id_optional –
For example, a call to the report may look like
exec dbms_addm.analyze_db(‘My ADDM
Task’, 100, 102);
Note that an ADDM report can also be run for RAC
across a subset of instances in the cluster using
dbms_addm.analyze_partial(‘My Partial ADDM Task’, ‘1,2’, 100, 102);
The second parameter (with value ‘1,2’)
represents the instance numbers that should be analyzed ADDM report.
Gathering the Report
The report can be gathered via a function called
The task name that was assigned the ADDM run will be needed.
set pages 0
select dbms_addm.get_report(‘My ADDM Task’) from dual;
The reports in Database Control can also be
viewed on the Cluster Database home page. The report is found
under the “Diagnostic Summary” area by clicking the link next to
“ADDM Findings”. Doing so will allow a graphical version of
the ADDM report to be viewed.
The high availability enhancements to Oracle 11g
primarily focus upon upgrading, which is considered planned
downtime. However, there are times when planning downtime is
difficult. Oracle 11g alleviates this issue by allowing
cluster nodes to be patched to the latest release one node at a
time, allowing the entire cluster to remain online longer.
Please note that it is always advisable to
perform a backup before attempting any upgrade, rolling or
otherwise. In addition, rolling upgrades are not possible if a
shared home is used for the Clusterware, ASM, or Oracle Database.
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off.