Database management by automation 

Oracle Database 10g lowers administrative requirements with an array of automated features

If you’re one of those database managers who thinks Oracle must pride itself on making its database overly complicated and difficult to manage, Oracle Database 10g will be a refreshing change. Simplifying everything from installation to tuning and troubleshooting to backup and recovery, the new release is packed with features designed to make the DBA’s job easier, either by completely automating tasks or by transferring control of important functions to the server. Gone are the days when you need a rocket scientist to run your database.

Automated features such as memory management, storage management, and self-diagnosis, as well as centralized configuration and patch management capabilities, allow DBAs to manage large, complex environments with very little meddling from day to day.

Oracle has also introduced significant improvements to the XML handling capabilities in Database 10g. Along with vast reductions in the size of server footprints required to traverse large DOMs comes XML schema evolution. Rather than discuss them here, I will fully explore 10g’s XML capabilities (as well as those of IBM DB2, Microsoft SQL Server 2000, and Sybase ASE) in an April 26 feature.

‘G’ Is for Grid

An Oracle 10g grid might be best described as a dynamic cluster. Application servers can be added to a cluster as needed, and the cluster resources can be rearranged to suit changing business needs. For example, say you have six servers doing OLTP (online transaction processing) and two more doing data warehousing, and you discover that the OLTP servers are getting bogged down at certain times of the day or week. By combining all eight servers in a grid, the two data warehouse servers could become part of the OLTP pool at peak times, and lend their resources to the mix.

The benefits of a 10g grid, including more flexible use of processing power and increased fault tolerance, are obvious, but configuring one is a complex process. Before you can set up a grid, you must have a clustered environment. Once you have your cluster built, you then define services and the machines on which the services will run. This is done by specifying a primary and secondary server for each service. You then define the level of resources (70 percent, for example) that a service can consume on any particular server.

I have not yet thoroughly tested 10g’s grid functionality (stay tuned), but some limitations are clear. For instance, I would like to see more automation in grid management, such as the ability to set up resource groups that redefine server roles dynamically at different times of the day or night. 

While grid management enables more efficient use of database clusters, a number of other new features make it much easier to manage large numbers of database servers. One of these is ECM (Enterprise Configuration Management), which is available both in Grid Control and as a single database control. ECM provides a centralized repository where you can store configuration policies. These policies can define anything from Oracle patches to operating system service packs to disk configurations. You could specify that all of your Windows 2000 Oracle servers should be on Windows Service Pack 3 and on a certain level of Oracle security patch. ECM will poll the servers every day and report back on the ones that are in violation of policy. You can then download the patches and push the updates out to the offending servers. ECM will also check the Oracle site for any security updates and download them.

Other new administrative features will be as welcome to small shops as large ones. Quickly becoming one of my favorites is ASM (Automatic Storage Management), which automatically balances the data load across all disks in a defined disk group. As new disks are added or removed, ASM rebalances the data to its optimum configuration, spreading the load across the drives and improving performance. You accomplish this is by adding a separate instance of Database 10g on the server that will control the production instance. Within the controlling instance, you define disk groups and assign data and log files to them, a process that takes only a few clicks. It is within these groups that ASM will completely manage your files across all the disks. ASM supports both mirroring and striping, helping to ease data redundancy.

Database 10g also sports significant improvements in backup and restore. Most importantly, RMAN (Recovery Manager) now supports compression for backups, allowing DBAs to keep more backups on disk for immediate recovery. In my tests, the compression rate varied quite a bit depending on the type of data stored, but I was able to squeeze anywhere from 17 percent to 30 percent more data onto disk. Further, using compression doesn’t seem to affect performance; my backup and restore rates were comparable to those for normal, uncompressed backups.

Monitoring backups

Perhaps one of the most significant improvements to Oracle backups is what’s called the Bounded Backup Window. A Bounded Backup Window allows DBAs to specify a window of time that a backup will take to complete. The more time you allow, the fewer I/O resources the backup process will take, and the less it will interfere with production operations. Two other improvements worth mentioning: The RMAN reporting enhancement allows DBAs to view all backups that it would take to restore a database and archive log. DBAs can also write scripts in plain text, test them before adding them to the recovery catalog, and receive alerts when space is running low.

Any Oracle DBA who has spent valuable time trying to gather the performance data needed to diagnose a problem will love the AWR (Automatic Workload Repository). AWR automatically stores operational data to the database, making it readily available any time you need to track down performance issues. By default, AWR keeps a seven-day history, but you can capture as much data as you’re willing to store. You can view graphs of performance metrics and drill down into specifics. History and snapshot intervals are completely configurable. Furthermore, each snapshot is merely a delta comparison of the previous snapshot, which helps keep storage requirements to a minimum.

Further, Database 10g can now help to diagnose itself. The ADDM (Automatic Database Diagnostic Monitor) taps the performance diagnostics stored in the AWR and provides the DBA with reports that show what problems exist, and many times presenting detailed analysis into the root cause and treatment. If ADDM is unable to suggest a proper fix it will often refer you to other Advisory components.

Database 10g can also manage the shared memory on the server automatically. Oracle DBAs have never had their memory managed for them before, and the new Automatic Shared Memory Tuning feature will greatly reduce poor performance brought about by memory configuration mistakes made by less experienced DBAs.

Finally, with the exception of being unable to perform a few tasks via the Enterprise Manager client (which I could perform using the new Web interface), and a bug that prevented an XML database-related WebDAV feature from working on Windows Server 2003, my tests with the beta version for Windows went as smoothly as those with the database on Linux. Oracle DBAs should feel at home on both platforms.

Oracle Database 10g represents a major step forward for enterprise database managers. Large operations will definitely find reasons to upgrade, and small shops stand to benefit from 10g, too. Even if you have no use for dynamic clusters, the global configuration management and backup throttling capabilities alone are worth their weight in gold for any company with more than one database.

Jennifer Nelson, database administrator at Info Integration, contributed to this review.

InfoWorld Scorecard
Availability (20.0%)
Performance (25.0%)
Scalability (20.0%)
Value (10.0%)
Manageability (25.0%)
Overall Score (100%)
Oracle Database 10g Release 1 9.0 8.0 9.0 9.0 9.0 8.8

Copyright © 2004 IDG Communications, Inc.

How to choose a low-code development platform