Feb
3
Written by:
Johannes
2/3/2009 9:36 AM
By Johannes Ahrends and John Pocknell
This 3-part blog will help you differentiate between what Oracle Enterprise Manager (OEM) offers the DBA compared to Toad DBA Suite for Oracle and how the same tasks are approached using each.
Toad DBA Suite for Oracle provides a complete solution for DBAs using Oracle Standard Edition, or in smaller shops where OEM is not being used or as a complementary solution for DBAs using Oracle Database Enterprise Edition and OEM.
With the Toad DBA Suite for Oracle, you can improve operational efficiency to lower costs by formalizing database management through a systematic approach that assures maintenance, performance and change. The Toad DBA Suite for Oracle allows you to simplify and automate common database administration tasks and become more proactive in solving database-related performance issues before end users are impacted.
Toad DBA Suite for Oracle is comprised of the following components:
- Toad for Oracle Xpert Edition (includes Quest SQL Optimizer for Oracle)
- DB Admin Module
- Benchmark Factory for Databases (Oracle Edition)
- Toad Data Modeler
- Spotlight on Oracle (seat-based license)
The Toad DBA Suite focuses on the database only and addresses three key areas of database administration:
- Maintenance – managing users, database health checks (including security as it relates to user Oracle accounts and permissions), task automation and scheduling, etc.
- Performance Management – real-time performance diagnostics, database and SQL optimization
- Change Management – configuration changes, schema changes and database workload replay testing
Before we continue, it’s worth clarifying what I mean by OEM (for purpose of comparison in this and the following two parts of this blog).
The full Oracle OEM Grid Control works to combine all aspects of database, application and infrastructure management in one tool. This leads to several distinct menu structures which make administration of only database-related issues (which most customers are interested in) very complicated.
From database startup and shutdown to conversion of a database to RAC, there are tons of hypertext links in OEM Grid Control, which causes problems.
OEM Grid Control is based on Oracle 10g and mainly on 10g Release 1. If you want to install it you first need to install Grid Control 10.2.0.1 which is the management server and agent in version 10.2 and the database in version 10.1.0.4. After the upgrade to 10.2.0.4, both the management server and the agent will be 10.2.0.4 aware (note that this is the final version of Enterprise Manager so still no 11g agent). And the database runs on 10.1.0.5 (which to my knowledge is not supported).
In this blog, we will focus specifically on OEM or OEM Database Control (for Oracle 10g and above) which hereafter will be referred to as OEM.
Now let’s compare some typical tasks using OEM and Toad DBA Suite:
Database Maintenance
OEM is built to manage databases over a web interface. So each function ends up as a hypertext link on a new webpage.
So let’s assume, as a DBA, you want to create a table similar to an existing one:
In Toad DBA Suite :
- Open Schema Browser
- Click the table tab
- Right-click table
- Select Create Like
- Notice the warning regarding the use of user-named constraints
In OEM :
- Targets (new page)
- Databases (new page)
- Instance (new page)
- Administration (new page)
- Schema Tables (new page)
- Search (popup page)
- Select Schema
- Then you select the original table and use “Actions create like” GO.
After specifying the table name you might want to create a duplicate table. But that’s not possible because you are using the same names for the constraints (no warning, nothing). The execution (OK button) gives the error messages “ORA-02264 name already used by an existing constraint”.
Now let’s compare how you would look at overviews of your managed databases.
It is very common to have more than one database running on the same host. So you might want to have an overview about the structures like datafiles, memory layout, or top sessions.
With Toad DBA Suite this is combined in the Database Browser:
- Open Database Browser (main toolbar)
- Click on your host and use the tabs on the right-hand side. All the above details are shown in various tabs. One screen for ALL databases.
- Access to both database objects and schema objects is provided in the navigator on the left-hand side meaning that you no longer need to use Schema Browser as well.
In OEM you first have to select the database again.
- Going to “CPU” gives you some information about the top sessions but not about the layout.
- The memory layout can be found at databases -> administration -> memory.
- The tablespace layout under databases -> administration -> tablespaces.
Yes, everything is available, but keep in mind that you are working with a Web browser so navigation always launches new screens.
These are just a few examples which show that:
- Yes, OEM provides a lot of the same information as Toad
- The navigation is very complex and it costs you lot of time to find the root cause of a problem
- OEM has database maintenance features which are not available in Toad:
- Recovery Manager (RMAN) repository (catalog) management
- Administration of Data Guard (Standby database)
- Database cloning
- Streams and advanced replication management
- Convert to cluster databases
- Administration of flashback recovery area and archiving
- Summary Advisor (for management of materialized views)
- Toad offers several database maintenance functions that OEM does not have (click the links for more information on each function):
- All features related to data (browse, compare, generate, subset, export/import, report, etc)
- Manage user accounts without scripts.
- Editor (yes there is an interface to iSQL*Plus but that product is no longer supported).
- Compare and synchronize schemas (this is available in OEM but only with the add-on Change Management Pack).
- Compare objects (users, tables, PL/SQL, tablespaces, etc) including objects on other ODBC compatible DB platforms
- DBMS Flashback (performs a flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN). Uses the DBMS_FLASHBACK package).
- Flashback data archive (as this is an Oracle 11g feature it’s not available in the full OEM Grid Control).
- Task scheduling – although OEM can enable the DBA to schedule tasks on the server, Toad allows you to schedule many tasks using the Windows Task Scheduler – including schema compare, database health check, script execution, and reporting
- Task automation – Toad’s AppDesigner lets you sequence a group of tasks such as those listed above and have them run against multiple managed databases rather than have to repeat them independently for each database, resulting in a huge amount of time savings on repetitive tasks.
- Database health check (including database security vulnerabilities).
- Database and schema reporting.
- Pre-defined RMAN script templates.
- Script management.
- And my all time favorite: log switch frequency map.
Caveats
Quest relies on information from a variety of sources in preparing this document. While every attempt is made to ensure its accuracy, errors or omissions can occur.
In addition, Oracle might make changes to its offering after this document is published, potentially affecting the comparison to Quest’s offering.
Summary
For database maintenance tasks, Toad dramatically increase the DBAs productivity by providing a single, collective view of all managed databases (instead of one at a time) with the ability to drill-down and perform those tasks in a consistent, efficient way. Task automation and scheduling allow many tasks, which are often repetitive, to be executed without the DBAs direct involvement, thus freeing them up to perform other important tasks or processes.
OEM still lets the DBA perform some of these tasks, but productivity is significantly slower due to navigational inefficiencies. Plus, OEM offers no opportunity to perform multiple tasks against multiple databases.
Look for Part 2 of this blog where we’ll be contrasting Performance Management tasks using Toad DBA Suite for Oracle and OEM.
3 comment(s) so far...
Re: Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 1)
Any time I've spoken to customers using both Quest solutions and OEM, they seem to be very pleased with how quickly and simply they can get the information they need using Quest tools compared to OEM.
By BBoise on
2/3/2009 2:00 PM
|
Re: Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 1)
Nice piece on the DBA Suite Johannes.
By Amit on
2/3/2009 2:11 PM
|
Re: Using Toad DBA Suite for Oracle with Oracle Enterprise Manager (Part 1)
Very helpful for our presentations. Thanks Johannes!
By Kuljit on
2/4/2009 3:33 PM
|