For Better, Faster, Smarter,
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
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.
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
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
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
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
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.