Oracle10G OEM Grid Control Overview and Test Plan

10G OEM Overview
Enterprise manager allows administrators to monitor, administer and report on every Oracle software element in their environment.  Each target that OEM manages has its own home page that provides a snapshot of its health and general performance status.   Although the target's home page displays the most important metrics and notifications, OEM offers drill downs that provide more in-depth analysis information.

The entire toolset is accessed through a HTML-based console interface.  Like previous versions of OEM, 10G OEM is a multi-tier architecture consisting of the HTML console, a management service with an integrated information repository and management agents running on all monitored targets.

The target home pages contain navigation tabs that allow administrators to easily access configuration information, management tools and administrative functions.  This presentation style is much different than previous versions of OEM that focused primarily on displaying administrative tools to the end user.  The focus of Oracle Enterprise manager has been changed dramatically and it took me a while to become comfortable using it.   There is a BIG learning curve with this release and I still find myself stumbling around in it at times.   OEM's new layout focuses on providing proactive feedback in the form of alerts, hints and monitoring output to help DBAs understand what is happening in their environments.   I'm assuming that I will become more proficient with the tool as I execute our OEM test plan below.  I intend to take copious notes so I can help my fellow DBAs at Giant Eagle become comfortable using the product as soon as possible.   I'll make sure I provide any interesting information to you in upcoming blogs.

Proactive Monitoring
10G OEM takes a much more proactive role in monitoring and reporting.  The management service and agents have their own watchdog processes that monitor their health and performance.   If any of their associated processes abnormally terminates, the watchdog processes automatically restarts them without administrator intervention.

This proactive monitoring and reporting functionality is also applied to all monitored targets.  10G OEM immediately begins monitoring all targets at default settings when they are added.  Administrators are then able to fine-tune the threshold values for individual targets when desired.  Monitored metrics are stored and aggregated in the repository to provide trend analysis capabilities. Those of us who have SLA agreements with their customers will find 10G OEM's status reports to be of great benefit.  10G OEM automatically provides an availability status for the last 24 hours, 7 days or one month.   

Intelligent Thresholding
One of the new features I intend to thoroughly test is "Intelligent Thresholding Through Baseline Metrics".    Wow, now that even sounds high-tech!   10G OEM allows administrators to review statistics for a monitored target and choose a particular time period to act as a benchmark for future performance evaluations.   The DBA reviews past statistical information, determines when performance was good and 10G OEM will capture the statistics for that time period.   The DBA then sets one warning and one critical threshold for each monitored statistic.  10G OEM will begin comparing all future statistics to the baseline.  If any of the statistics exceed their baseline threshold, a warning or critical alert message will be generated.

10G OEM also provides advice-driven responses to generated alerts.  The administrator is able to use the advice to speed the problem determination and resolution process.  The advice may include historical information to allow administrators to perform trend analysis on a particular statistic or threshold.

Proactive SQL Monitoring
In addition, I will be paying particular attention to 10G OEM's SQL monitoring feature, which automatically analyzes SQL statements for performance and resource consumption.   10G OEM is supposed to be able to identify poorly performing SQL statements and also make recommendations to correct them.  

10G Day-to-Day Administration Functionality Testing
Before my unit begins taking advantage of 10G OEM's new features, we need to make sure that we can use the tool for day-to-day DBA grunt work.   I want to ensure that when we "flip the switch" we will be ready to provide a full set of administrative services for our new environment.   So, I'll be personally testing a myriad of day-to-day functions in 10G OEM.   Please note that this is not an exhaustive list of 10G OEM's capabilities.  These are the functions that must work if we are to begin implementing the product at Giant Eagle.

Here's the checklist I'll be using:

1)Viewing database information

• Parameters
• Data buffers
• Log buffers
• Shared pool
• Sort areas
• Active processes
• Locks
• Show object DDL

2)Database Structures

• On-line redo log group creation
• On-line redo log  group adding member
• On-line redo log group dropping member
• Permanent tablespace creation
• Permanent datafile - adding to tablespace
• Permanent datafile - increasing size of
• Temporary tablespace creation
• Temporary  datafile - adding to tablespace
• Temporary datafile - increasing size of
• Undo tablespace creation
• Undo datafile - adding to tablespace
• Undo datafile - increasing size of
• Undo tablespace  - switching undo tablespaces
• Taking above tablespaces offline

3) Object  Administration

• Table creation
• Table alteration (add columns)
• Table alteration (drop columns)
• Table alteration (changing columns)
• Table alteration (moving to a different tablespace)
• Table alteration (adding constraint referential integrity, check and unique constraints)
• Table alteration (dropping constraint)
• Constraints enabling
• Constraints disabling
• Table analyze
• Drop table
• Index creation
• Index alteration (moving to different tablespace)
• Index analyze
• Index drop
• Table partitioning (range, hash, list, composite)
• View creation
• View drop
• Sequence creation
• Sequence drop
• Synonym creation
• Synonym drop
• Hashing and sequence cluster creation
• DBlink creation
• DBlink drop
• Array type creation
• Array type drop
• Table type creation
• Table type drop
• Create like command for above objects
• Show dependencies for above objects

4) Space Management

• Monitoring tablespace free space allocations
• Tablespace map
• Tablespace reorg
• Monitoring schema object (table, index) growth
• Monitoring and managing rollback and undo segments
• Reorganizing data objects to reduce block and file fragmentation
• Monitoring log switch frequency   

5) Backup and Recovery

• RMAN 10G interface

6) Monitoring for Performance

• Hardware Monitoring
• Memory utilization and allocation parameters
• CPU utilization
• Disk configuration, I/O load balancing and sizing
• Database Monitoring
• Memory
• Disk
• Process

7) Application code/SQL Statement Monitoring

• Explains and optimization
• Parallelization
• Performance Overview
• Session Details
• Top  SQL
• Top Session

8) Advanced database architectures

• Replication
• Transaction queuing
• Distributed database
• Access to disparate systems (gateways, middleware)
• Highly available systems (Oracle Data Guard)

9) Transaction management

• Distributed transactions
• Locking

10)Security

• User creation
• Role creation
• Security grants
• Security revocation
• Profiles
• Password management
• Account locking
• Account aging and expiration
• Fine-grained access control

11) Auditing

• Audit trail creation
• Audit trail drop

12) Oracle Utilities

• Export/Import
• Loads
• LogMiner Interface

13) Process Management

• Monitoring
• Killing sessions

14) Change Manger

• Take a baseline
• Compare database to baseline
• Compare database to database
• DB propagate
• Quick Change

15) Resource Manger

• Manage resource consumer groups
• Manage resource plans

16) Alerts

• Node up
• Database up
• Listener up
• Archiver hung
• 0600 in alert log
• Process deadlocks
• Target directory full
• Tablespace freespace alerts

17) Jobs

• Analyze schema objects
• Export
• Backup
• Statspack

Next Up
An analysis of our initial performance results and a review of our recent discussions on Automatic Storage Management (ASM) with EMC and Oracle.