Database Management ( DBA ) Enhancements in Oracle 9i

September 3, 2001

Database and Application management has been the primary goal of Database Administrators for many years. With the introduction of Oracle 9i, Oracle has improved many areas of the management infrastructure to make it easier for the DBA to automate the day to day management aspects of the database. In this article we will look at some improvements that are on their way

Recovery Manager Improvements:

RMAN in 9i implements some new features that are designed to reduce the time and effort spent by administrators in performing routine activities related to backup and recovery. RMAN features a new friendly interface and enhanced reporting. To ease backup and recovery operations, RMAN provides for backup configuration, automatic management of backups and archived logs based on user specified recovery window. RMAN also implements a new policy named recovery window which will control when backups expire. These features reduce the time spent by DBA's on day to day backup management activities.

Rollback Segment Management:

Until 8i, DBA's had to carefully plan for rollback segment size, assignment of transactions and also tune the number and size of the segments. With 9i, Administrators can assign a tablespace for Rollback Space and let the database take care of issues like size, space utilization, block contention and consistent read retention.

Dynamic Memory Management:

Database Administrators had to traditionally shutdown Oracle Instances in order to grow or shring the SGA components. Dynamic Memory Management feature allows the resizing of the buffer cache and shared pool dynamically. 9i also introduces self tuning of working memory for SQL execution by tuning the initialization parameters controlling the allocation of private memory.

Resumable Statements:

9i allows Database Administrators to pause or suspend large operations such as a batch update or data load when the DBA finds an Out of Space or other condition. This feature allows the DBA to pause the operation fix the problem and resume the operation from the point of interruption without affecting the normal database operations.

Persistent INIT.ora:

9i introduces the persistence of INIT.ora across multiple shutdowns. This allows remote activities like startup without having a local copy of the INIT.ora and also provides immense help in database performance tuning as parameter changes that happened due to Internal Self Tuning and tuning changes made by tools like Oracle Enterprise Manager can persist across shutdowns.

Oracle 9i also introduces various features that ease management like Execution plan history that lets the DBA investigate a bad statement without having to reexecute it. The introduction of Oracle Managed Files lets the DBA handle the space management and removes the extra work of having to manage the files in the database. A default TEMPORARY tablespace has been introduced in 9i reducing the annonying SYSTEM Tablespace usage for temporary space.