Oracle claims that Oracle9i raises the competitive bar by which all future database servers will be judged. Oracle's latest release contains enhancements in virtually all areas of the database server, resulting in an Oracle database with improvements in scalability, availability, performance, manageability, multimedia datatype support and functionality. This article is not intended to be an all-inclusive list of features, but rather an overview of some of the more beneficial (and hopefully interesting) enhancements contained in this release.

The information on Oracle9i will be provided in three installments. In this article, we'll take a look at the following features: persistent initialization parameter files, remote startup/shutdown, database managed undo segments, resumable space allocation and flashback query.

Part two will cover external tables, tablespace changes, Oracle managed files, multiple block sizes and cache configuration, list partitioning, on-line table reorganization and index monitoring.

The last article in this series will cover RAC (Real Application Clusters), fail safe, data guard, fine-grained resource management, fine-grained auditing and label security.

With all of new features contained in this release, Oracle9i promises to be the most exciting Oracle release to date. This series will focus on what Oracle customers can look forward to when using the "latest and greatest" version of Oracle's flagship database product, Oracle9i.

Persistent Initialization Parameter Files

Oracle9i introduces on-line parameter changes that persist across database shutdowns and startups. This feature allows administrators to make changes to database initialization parameters and have them take affect immediately. In the past, these changes would require the administrator to edit the database's parameter file (initsid.ora). Because Oracle only reads the parameter file during startup, the changes would not take affect until the next time the database was shutdown and restarted.

In Oracle9I, a server-based parameter file, called a SPFILE, is used as the repository for initialization parameters. Oracle9i documentation now refers to the old initsid.ora parameter file as the PFILE. The SPFILE is initially created by using the PFILE (initsid.ora) parameter file as the source. It is important to note that the database is initially created using the old PFILE parameter file. Administrators then use the "CREATE SPFILE FROM PFILE" command to create the server-based parameter file. At system startup, the default behavior of the STARTUP command is to look for the SPFILE before it looks for the PFILE. The Oracle administration guides provide information on default location and naming conventions for server-based parameter files.

The administrator uses the ALTER SYSTEM statement to dynamically change initialization parameters. A parameter can be changed immediately or deferred until the next database startup. Although the majority of the parameters can be dynamically changed, there are a few configuration parameters that can only be changed by editing the old initsid.ora parameter file (PFILE).

Here are a few hints on PFILEs and SPFILEs:

• Never, ever edit a SPFILE manually. Although you can view it in both UNIX and NT editors, editing it can produce a "less than desirable" outcome. The SPFILE was edited three times during our beta testing and the result was three database failures.
• If you are required to edit the PFILE to change a static parameter, don't forget to execute the 'CREATE PFILE FROM SPFILE' statement to refresh the PFILE with all of the dynamic changes recorded in the SPFILE. Execute the SPFILE to PFILE refresh before you edit the PFILE. If you don't you could lose the dynamic changes recorded in the SPFILE. Remember, the database looks for the SPFILE first during startup, so you will need to execute the "CREATE SPFILE FROM PFILE" after you edit the PFILE to migrate your changes. If your PFILE doesn't have a record of all your dynamic parameter changes, you will lose them when you execute the 'CREATE PFILE FROM SPFILE' statement. The recommended procedure is to always execute the 'CREATE PFILE FROM SPFILE' command after dynamically changing a parameter to back up the changes recorded in the SPFILE to the PFILE.

Oracel9i provides a new static parameter called MAX_SGA_SIZE which specifies the maximum size of SGA for the lifetime of the instance. Another new Oracle9i parameter DB_CACHE_SIZE replaces DB_BLOCK_BUFFERS. DB_BLOCK_BUFFERS is still provided for backwards compatibility. DB_BLOCK_BUFFERS and the MAX_SGA_SIZE parameters are static so they can only be changed by editing the PFILE, then executing the 'CREATE SPFILE FROM PFILE' statement and restarting the instance.

During our testing, we found that the online changes to parameters worked well. We did find that changing SGA parameters produced some interesting results. We were able to dynamically alter the initialization parameters that affect the size of the buffer caches, shared pool, and large pool, but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) did not exceed the value specified by SGA_MAX_SIZE.

When we reduced memory from the data buffers by decrementing DB_CACHE_SIZE, Oracle allocated the memory saved to the shared pool. When we reversed the operation by reducing the memory allocated to the shared pool, Oracle allocated the freed memory to the data buffers.

Remote Startup/Shutdown

Oracle9i's persistent parameter files provide administrators with the ability to start an Oracle instance using SQL*Plus on remote clients.

Before we begin, some background information is in order. Oracle has been promising to desupport server manager and CONNECT INTERNAL for some time now. Administrators would use server manager on the host to connect to the database using the INTERNAL account to start and stop an Oracle instance.

Server manager and CONNECT INTERNAL are officially desupported in Oracle9i and are replaced by SQL*Plus and a special privilege called SYSDBA. SQL*Plus and SYSDBA have been available for some time but were never a primary means of starting and stopping an Oracle instance.

During an instance start, Oracle reads instance configuration parameters from a SPFILE or PFILE. In order to facilitate remote startup and shutdown, SQL*Plus is now able to reference the server-based parameter file (SPFILE) from remote clients. This solves the problem of propagating copies of the PFILE to all remote clients that require the ability to start an Oracle instance. By having all clients point to a single source, administrators can rest easy knowing the same parameters are used to configure the instance during each startup.

The steps to access a SPFILE from a remote client are as follows:

1. A server-based parameter file (SPFILE) is configured on the database server by executing the 'CREATE SPFILE FROM PFILE' command.

2. Create a parameter file on the remote client that contains a single line that references the server-based SPFILE:

    spfile=/u01/app/oracle/product/9.0.0/dbs/spfiledemo1.ora

3. Start SQL*Plus without connecting to the database by executing:

    SQL*PLUS /nolog

4. Connect to the remote instance as SYSDBA:

    CONNECT username/password@connect_identifier AS SYSDBA

5. Start the instance by executing:

    STARTUP PFILE=pfilename.ora

* where pfilename.ora is the parameter file name created in step 2.

Standard operating practice in UNIX environments is to embed STARTUP and SHUTDOWN commands in server manager to start and stop an Oracle instance. Shops migrating existing databases to Oracle9i should change the startup scripts from server manager to SQL*PLUS.

Database Managed Undo Segments

You don't have to be an Oracle expert to know that rollback segments can be "somewhat troublesome." Out of space conditions, contention, poor performance and the perennial favorite "snap shot too old" errors have been plaguing Oracle database administrators for over a decade. Oracle finally decided that the database could probably do a better job of managing before images of data than we could.

A transaction uses a rollback segment to record before images of data it intends to change. If the transaction fails before committing, Oracle uses the before images to rollback or undo the uncommitted data changes. Oracle also uses rollback segments for statement-level read consistency. Read consistency ensures that all data returned by a query comes from the same point-in-time (query start time). Lastly, rollback segments provide before images of data to help the instance roll back failed transactions during instance recovery.

In Oracle9i, administrators have their choice of continuing to manage rollback segments on their own (manual undo management) or configuring the database to manage its own before image data (automatic undo management). Oracle refers to system managed before image segments as undo segments.

Administrators must create a tablespace to hold undo segments by using the new UNDO keyword in the tablespace create statement:

    CREATE UNDO TABLESPACE undots1
    DATAFILE 'undotbs_1a.f'
    SIZE 10M AUTOEXTEND ON;

The following initialization parameters are used to activate automatic undo management:

• UNDO_MANAGEMENT - AUTO configures the database is to use automatic undo segments. MANUAL configures the database to use rollback segments.
• UNDO_TABLESPACE - Specifies the tablespaces that are to be used to hold the undo segments. The tablespace must be created using the UNDO keyword. If no tablespace is defined, Oracle will select the first available undo tablespace. If no undo tablespaces are present in the database, Oracle will use the system rollback segment during startup. This value can be set dynamically by using the ALTER SYSTEM statement.
• UNDO_RETENTION - specifies the amount of time that Oracle attempts to keep undo data available. This parameter becomes important when the Oracle9i flashback query option is used.

You cannot create database objects in undo tablespaces. It is reserved for system-managed undo data. The view DBA_UNDO_EXTENTS can be accessed to retrieve information relating to system managed undo data. For those of us familiar with V$ROLLSTAT, it is still available and the information reflects the behavior of the undo segments in the undo tablespace.

We found automatic undo management to be pretty reliable during our initial beta testing of Oracle9i. The key to success is to allocate sufficient disk storage to the undo tablespace and to set AUTOEXTEND on to allow the tablespace datafiles to grow automatically. During our beta testing, numerous heavy batch update jobs were simultaneously run to simulate heavy work loads. We found that the system managed undo segments worked as advertised. During performance comparisons, we did find that system managed undo segments did seem to add some extra processing time to the batch loads. We found that the rollback segment tablespace used in our comparison testing auto extended sooner than its system managed undo tablespace counterpart. One possible explanation is that the overhead can be attributed to the system managed undo segments performing additional actions to squeeze more undo data in the tablespace before giving up and auto expanding the undo tablespace datafile.

Resumable Space Allocation

Running update jobs that insert or update large amounts of data also cause their fair share of problems. Estimating the space required by large operations can be quite a formidable forecasting effort.

Do you add extra space to data and index tablespaces? Do you make the table and index INITIAL and NEXT extent sizes bigger? Do you increase the size of the rollback segments to handle the additional load? Should you increase the size of your TEMP tablespace and make your default INITIAL and NEXT extent sizes larger?

In previous releases, when an out of space condition occurs, the statement quit running and the database rolled back the unit of work. Rolling back can be a time-consuming (sometimes a VERY time-consuming) process. The DBA corrected the problem and the program was run again (hopefully successfully the second time). How many times have there been a third, fourth and fifth time?

Oracle 9i solves this problem with resumable statements. Oracle9i temporarily pauses SQL statements that suffer from out of space conditions (no freespace in tablespace, file unable to expand, maxextents or maximum quota reached). The DBA is able to easily identify the problem and correct the error. The statement will then resume execution until completion.

The ALTER SESSION ENABLE RESUMABLE statement is used to activate resumable space allocation for a given session. Developers are able to embed the ALTER SESSION statement in programs to activate resumable space allocation. A new parameter, called RESUMABLE, is used to enable resumable space allocation for export, import and load utilities.

Statements do not suspend for an unlimited amount of time. A timed interval can be specified in the ALTER SESSION statement to designate the amount of time that passes before the statement wakes up and returns a hard return code to the user and rolls back the unit of work.

If no time interval is specified, the default time interval of two hours is used.
When a resumable statement suspends because of an out of space condition, the following actions occur:

• A triggerable system event is initiated. Developers are able to code triggers that fire when a statement suspends.
• Entries are placed into system data dictionary tables. The data dictionary views DBA_RESUMABLE and USER_RESUMABLE can be accessed to retrieve the paused statement's identifier, text, status and error message.
• Messages are written to the alert log identifying the statement and the error that caused the statement to suspend.

Flashback Query

How many times have database recoveries been performed because of incorrect changes made to database data? Were your users ever unsure of the damage? There are times when a simple before change and after change comparison was all that was needed. If the damage was limited, a simple update may have been able to correct the problem. A process that is much less painless than a database restore.

Oracle9i's flashback query provides users with the capability of viewing data in the past. Oracle describes this new feature as "Oracle Invents the Time Machine" in many of its advertisements. It may not be a time machine, but it does allow data to be viewed in the past and it is easy to use. I must admit, I thought "It sounds too good. It has to be hard to use or not be reliable." I was wrong on both counts.

To take advantage of flashback queries, the database must use system managed undo segments. If flashback query is to be used, the administrator is tasked with determining how much of the old data should be kept available. The undo tablespace must be sized to hold the desired amount of undo data. Oracle documentation provides calculations that use update frequency and the amount of data being changed to estimate the required size of the undo tablespace.

The configuration parameter UNDO_RETENTION which specifies the amount of time that Oracle attempts to keep undo data available plays an important role in flashback query. Although Oracle documentation recommends flashback query for applications that want to view data in the past, it is important to understand that the UNDO_RETENTION parameter does not force Oracle to keep the old data in the undo tablespace. Depending on the available disk storage allocated to the undo tablespace, the database might not always be able to keep all of the requested undo data available. Providing active transactions with undo image space takes precedence over flashback query requirements. As a result, applications should not be designed to depend on the availability of historical data retrieved from undo segments.

The system supplied package DBMS_FLASHBACK is used to provide flashback query capabilities. Standard date and time SQL functions can be used to determine the time in the past the data will be retrieved from. Here is an example that goes back five minutes:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE - (5/(24*60)));

The above statement sends the session five minutes back in time for the duration of that session or until the EXECUTE DBMS_FLASHBACK.DISABLE is executed. Oracle recommends that the session not be ended without executing the FLASHBACK.DISABLE procedure. I have seen a few sessions ended without executing FLASHBACK.DISABLE without any detrimental affects. It is better to be safe than sorry, so the recommendation is to always execute FLASHBACK.DISABLE before ending the session.

Currently, flashback query is able to provide 5 days (uptime not wallclock) worth of data using the date and time parameter. To query data older than this, you must specify an SCN rather than a date and time.

Two important points to remember when using flashback query:

1. The current data dictionary is used. If DDL changes have been made to the table between the time stated in the flashback query and the current point in time, an error will be returned.
2. Data can not be updated during a flashback query enabled session. To save historical data, the old data can be placed into a cursor. The contents of the cursor can be dumped into a work table after the FLASHBACK.DISABLE procedure is executed.

Remember, although flashback query is promising to be a beneficial feature in Oracle9i, it is not a panacea. Applications should not be designed to depend upon flashback query data. In addition, although it may prevent an occasional database recovery, it must be used cautiously. If data has been changed incorrectly, administrators must determine if other transactions have used that incorrect data as input. If the transactions using incorrect data as input have also made data changes, bad data is now being propagated throughout the database. It may be safer to perform a database recovery to a previous point in time.

In our next discussion of Oracle9I, we'll discuss external tables, tablespace changes, Oracle managed files, multiple block sizes and cache configuration, list partitioning, on-line index table and index reorganization and index monitoring.

See you in class!

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.