Oracle 9i Database Manageability Enhancements

Ease of management of the Oracle databases has been a bone of contention for several years, especially configuration of rollback segments and such like, resulting in the perception that Oracle databases more difficult to manage and therefore more expensive than Microsoft SQL Server databases. With Oracle 9i, great strides have been made to counter this perception and improve manageability by :- • making the database able to manage it's own rollback segments (now called undo segments), • introducing Oracle managed files which means you don't have to worry about creating or removing files at the operating system level • simplifying day to day management of the database by, among other changes, introducing a binary, server-based parameter file which can be written to by the running instance thereby removing the need to remember to change the initialization file when making parameter changes whilst the instance is running. Those are just a few of the changes made to improve manageability. We'll be looking at these and other changes, although not in great depth, but just enough to give you an idea of what's changed and what effect this might have on your role as an oracle dba. These improvements in this area include: • automatic rollback segment management • many enhancements to operations management • enhancements to Oracle Enterprise Manager

New Oracle 9i Self Management Features

Self-managing undo segments

The new features in Oracle 9i include self-managing undo (rollback) segments. No longer does the dba have to worry about the number and size of rollback segments required and which transactions to assign to which rollback segment. All you have to do now is create an undo tablespace and let the Oracle database worry about contention and space allocation and utilization.

Automated PGA memory management

Tuning the PGA in Oracle 8i required the setting of various parameters. However, as it was difficult to find the correct values for the various parameters, memory was often wasted due to over allocation. With Oracle9i, PGA management can be left to the database by setting one parameter, pga_aggregate_target, to define the target memory allocation for the PGA and another parameter, workarea_size_policy (at the system or session level), to enable dynamic tuning of the PGA.

Simplified Operational Management

Even though Oracle databases are now more autonomous, it doesn't mean that there is nothing left for the dba to do, however Oracle have made life easier for the dba by simplifying day to day operational tasks.

Server-based parameter file

Probably the most significant change in the area of operational management is the introduction of a binary, server- based parameter file - known as a spfile. This enables parameter changes made whilst the database is running to be preserved when the database is shut down, which is especially important for changes made by performance tuning tools. When making changes manually via the alter system command, the scope of changes can be set to one of:- • memory (changes are lost when the instance is shutdown) • spfile (changes won't take effort until the instance is restarted, they are made only in the parameter file) • both (changes take effect immediately and are preserved when the instance shuts down)

Resumable space allocation

Another significant change in Oracle 9i is the introduction of resumable space allocation. This means that any transaction that gets an error - such as insufficient quota, max. extents reached, or unable to extend index/table/rollback segment - can be suspended instead of being rolled-back. This enables the dba to fix the problem and then the transaction can resume from the point of interruption.

Oracle-managed files

Ever wished that your Oracle database couldn't create the associated file when a new tablespace was created and similarly delete the associated file when a tablespace was dropped? Well, your wish has come true. Oracle 9i will do this for you if the parameter db_create_file_dest is set.and it can also create and delete the on-line redo log files automatically. This new feature is enabled by setting the db_create_online_log_dest_n parameter(s) for log files which will also cause the database to create a copy of the control file in each of the directories specified by these parameters (n can be between 1 and 5), if the control_files parameter is not set.

Enterprise Manager Enhancements

Many enhancements have also been made to Enterprise Manager in Oracle 9i to make it easier to use and more useful. These enhancements include the introduction of guided, expert problem diagnosis and resolution and the ability to have alerts generated automatically by the database when specified conditions are met which avoids the need for continual monitoring of the database in the hope of spotting problems. See the Oracle9i Database Administrator's Guide for more details on these features or see our overview of the new features. ----------------------------------------------------------- Looking to sky-rocket productivity, slash costs and accelerate innovation? Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our training course schedule, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.