Improved 10g management with OEM
Donald K. Burleson
Just a few years
ago, many senior Oracle DBAs detested Oracle Enterprise Manager (OEM).
Viewed as a crutch for beginners who could not memorize the command
syntax, OEM was largely ignored by the veteran DBA who preferred the
ease and certainty of the SQL*Plus command-line interface. This is
about to change.
(OEM) has undergone a major overhaul in Oracle 10g. From its humble
beginnings as a GUI-based statement generator, Oracle has invested
millions of dollars to make 10g EM a robust full-function product.
Oracle 10g OEM now
offers far more than simple command generation and schema viewing. The
10g OEM now allows the DBA to easily manage every aspect of the 10g
database, even non-traditional tasks like applying patches and
The most important
new areas of EM include:
OEM - You
can now make OEM available over the Internet with full password
security and access from any Internet-enabled web browser, anywhere in
- OEM now allows you to manage your 'ecosystem' of server and
instances, allowing you to manage external components such as Oracle
HTTP servers, Oracle web cache servers, app servers and other external
database components. You can even use OEM agents to incorporate
third-party tools such as Tivoli
and EM has built-interfaces to global monitoring tools such as Remedy.
Best of all, you can now justify getting 'root' access because OEM now
gives you the ability to perform all server management functions
including kernel changes and reboots. Your Systems administrator will
be thrilled about this new feature.
- Building upon the foundation of the Oracloe8i STATSPACK utility,
Oracle10g OEM interfaces with the new Workload Repository History (WRH$)
tables to provide unparalleled time-series analysis. Instead of
slogging through plain-text time-series STATSPACK output, you can now
view exception reports in an easy-to-use GUI.
- The 10g OEM reads data from the Automatic Workload Repository (AWR)
and interfaces with the Automatic Database Diagnostic Monitor (ADDM)
to produce alerts and automated recommendations. If you concur with a
recommendation, you can simply direct EM to issues the commands to
implement the change.
- The new 10g EM2go product allows you to use you web-enabled PDA to
administer any 10g database. You can monitor Oracle, receive exception
reports and even make database changes anytime, anywhere. There is
nothing in the world like the felling you get when you can make a
critical database change while sitting on a toilet in a seedy Tijuana
- Using the incredibly stupid name of 'fetchlets', 10g OEM now allows
you to embed your own SQL*Plus, UNIX shell scripts, or even the Cobol
program your Grandpa wrote in 1962, can now be incorporated into OEM.
- Before the advent of the dbms_job packages the DBA had to use
cumbersome UNIX cron jobs (or the MS-Windows AT command) to schedule
repeating Oracle task. Oracle now takes these internal and external
job scheduling techniques and melds them into a single interface. The
DBA can now use OEM to manage tasked scheduled at either the database
level (dbms_job) or the external server level.
- The 10g EM now has far superior interfaces into the standard Oracle
utilities, especially export/import and SQL*Loader.
- Like any good citizen, 10g EM care about the environment. EM will
automatically detect new hardware and place it into the pool of
available resources. This feature is sure to piss-off your Systems
Administrator who will not like the DBA taking-charge of the servers.
For the more
sophisticated DBA, Oracle10g OEM has full support for Grid computing.
Using EM, you can manage pools of ready-to-user blade servers, moving
them into, and out of the Oracle architecture on an as-needed basis.
Dubbed "re-provisioning", EM allows the 10g Grid DBA to manage
hardware resources through a single interface.
Interface to AWR
introduced the Automated Workload Repository (AWR) as a built-in
kernel component to collect time-series performance information for
the DBA. The AWR automatically collects performance data and stores it
inside a special database of Workload Repository History (WRH$)
Figure 1 - ER Model for some 10g AWT views
When we use EM to
drill-down into the metric list, EM displays hundreds of individual
tuning metrics from the AWR and provides you with the ability to set
personalized alert 'thresholds' (Figure 2). Note that EM allows you to
specify numeric criteria (greater then, less than), and has full
pattern matching capabilities for text-based metrics such as alert log
Figure 2 - Setting alert thresholds within EM
Once set, EM will
send you alerts (via e-mail, pager, telephone or EM2GO on your PDA) to
alert you about a pending problem before it effects your performance.
OEM and ASH
Oracle10g OEM also
interfaces with the Active Session History (ASH) component to track
details on all Oracle sessions (Figure 3).
Figure 3 - A partial listing of the Oracle ASH structures
The EM interface to
ASH metrics makes it easy for the DBA to drill-down and see the exact
wait components of an Oracle session (Figure 4),
Figure 4 - The EM drill-down for session wait details over time
As a senior DBA, my
favorite features are the time-series performance monitoring where I
can quickly look at how important events (such as buffer busy waits,
latch misses) behave over time. Although I'll probably never issue
create table commands via EM, I'm starting to appreciate the display
and graphical capabilities of this powerful new tool, especially as a
replacement for STATSPACK and an easy-to-use tool for proactive