When Oracle 10g was released, Oracle introduced its common
manageability infrastructure, also known as the advisory framework.
This framework includes server-based advisors, chief among them
being the Automatic Database Diagnostic Monitor (ADDM). Embedded
within ADDM, since it can call other lower-level advisors, are
advisors related to SQL, memory and space management.
The named advisors include:
In release 11g, there are advisors and other tools introduced in 10g,
and there are also two new utilities: the Automatic Diagnostic
Repository (ADR) and the SQL Performance Advisor (SPA).
To further clarify, the following question should be addressed: Is
Feature X an advisor, a utility, or both? Depending on the feature,
the answer in many cases is both. Access to several advisors outside
of a GUI, such as Enterprise Manager, can be accomplished by executing
the underlying SQL scripts. A classic example of that is the Automatic
Workload Repository. An AWR report contains several advisory-like
sections. Deep inside an AWR report, there is a section on Advisory
Statistics.
Figure 8.1: Advisory
Statistics in AWR
The PGA and SGA statistics captured in the report can also be obtained
via canned SQL scripts.
As a caution, it is up to the DBA to determine access to use extended
licensed features. If one is not licensed to use the Tuning or
Diagnostics packs, for example, not only does that mean these
utilities cannot be used within Enterprise Manager, or Database
Control and GUI variations thereof, it also means one cannot use any
script - the DBA's or Oracle's - to query the contents of any of the
workload repository tables and views. If there are any doubts about
what one is licensed for, contact the Oracle sales representative. The
Licensing Information guide published with each release can also be
reviewed.
With that caution out of the way, start by looking at two new features
in 11g and then cover some historical utilities. The first two
sections cover ADRCI and SQL Performance Analyzer (SPA). Both sections
are somewhat lengthy because of their nature: they are quite powerful
and useful. One can only hope that they will be back-ported to older
versions of Oracle as that is how good they are.
ADRCI: ADR Command Interpreter
The ADR of interest here is the
Automatic Diagnostic Repository, not the ADR (Automatic Data Repair)
associated with LogMiner. The Automatic Diagnostic Repository
maintains diagnostic data that can be uploaded to Oracle using the
Incident Packaging Service (IPS).
As in the old days, part of
what Oracle still asks for is a zipped or compressed set of files
created by having downloaded and run the RDA tool. The remote diagnostics script(s) collected
information about the system. In one sense, ADR can be thought of as a
prepositioned hRDA tool within the database.
If support analysts can look at
diagnostic data, then so can the DBA. From Oracle's perspective, it is
easy to see where they are coming from, as in "Let's make it easier
for customers to not only diagnose failure, but also to package it up
and send us information in a standardized format". The side benefit of
this perspective is that now the data can be seen in the same format
or package. This could be done before to a large degree with RDA, but
this is even better.
Changes in Oracle 11g
In addition to this idea,
the architecture of how the RDBMS is installed on a computer has
also undergone some significant changes. To be honest, use of Oracle 11g is going to be
somewhat of a culture shock to people accustomed to older versions of
Oracle. The number one shock will be saying goodbye to the Windows
SQL*Plus interface for what is there now
is a command prompt window. Number two is the location of dump and
trace files in relation to the old admin directory. And the number
three most significant shock, though there are more than just three,
is how the contents of the alert log are produced. The standard alert
log in 11g is an XML file. Not to worry since Oracle still provides
the old version of the alert log in another directory.
Part of the new architecture includes a
"diag" directory in what is analogous to
ORACLE_BASE/diag/rdbms/<SID>. The arrangement of certain
files is referred to as a unified directory structure and this
structure can apply to more than one instance. Each instance has its
own ADR Home, and just like
ORACLE_HOME, there is an ADR Base.
The ADR Home turns out to be a
file-based repository of diagnostic related data such as the alert
log, trace files, and dumps. The idea of an ADR Home even extends to
RAC and ASM. Crashes, faults, problems - whatever the errant condition
is - can be viewed as incidents, and that leads back to being able to
package up files relevant to an incident, hence the Incident Packaging
Service. All of this is part of the fault diagnosibility
infrastructure.
The command interpreter interface into
the ADR is the ADRCI command-line tool (adrci.sh or adrci.exe). ADRCI
enables viewing diagnostic data, viewing Health Monitor reports, and
as mentioned, performing incident packaging operations.
There are two ways to access the
Automatic Diagnostic Repository. One is via the Enterprise Manager
Support Workbench. Since it is "known" that GUI tools are for the weak
and lazy, it is best to concentrate on the command-line interface. In
all seriousness, the ability to invoke the command line utility should
hover around 100%, even if the database is not open or the console is
not running, or host credentials are not set, or Web interface is not
enabled or blocked in the environment. The idea that relying on
Enterprise Manager factors in many points of failure should be coming
clearer. Therefore, knowing how to use ADRCI is evidently useful.
NOTE: Rampant author Laurent Schneider has some additional
insight into
creating an Oracle Automatic Diagnostic Repository (ADR).
and include the URL for the page.
