OCA/OCP: Oracle9i DBA Fundamentals I Study Guide

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide

by Biju Thomas

ISBN: 9780782140637

Publisher Sybex

Published in Reference/Study Guides, Reference/Education, Nonfiction/Education, Computers & Internet/Certification Central, Computers & Internet/Computer Science, Computers & Internet/Programming, Computers & Internet/Databases, Computers & Internet/General, Computers & Internet/Software

Are you an AUTHOR? Click here to include your books on

Sample Chapter

Chapter One

Oracle9i New Features for Administrators

The Oracle9i platform picks up where Oracle8i left off. The Oracle9i database was enhanced across all major functional areas: server availability, scalability, performance, security, and manageability.

Of course, as an Oracle Certified DBA candidate, you need to know about all aspects of the Oracle database, not just the new features; however, if you have a good background in previous Oracle versions, you can certainly benefit from an Oracle9i new-features overview.

Although the Oracle9i platform is also enhanced in the application server and development tools areas, this chapter focuses on the new features of Oracle9i that are database-related.

As with any new release of the Oracle Server, a lot of the new features replace or make obsolete features that exist in previous versions of the Oracle Server. The last section of this chapter discusses the deprecated and unsupported features in Oracle9i.

High Availability

The Oracle9i database was enhanced in a number of areas to make sure that the database is available during maintenance operations, even if those maintenance operations are occurring on the user objects currently in use.

The Oracle DBA has more control over the recovery of the database in the case of instance failure, and the user has more options to re-create data even after changes or deletions have been committed.

More flexibility has been added to the import/export process, and LogMiner has been expanded to include DDL (Data Definition Language) statement support. RMAN (Recovery Manager) is more automated and more efficient; it is also easier to use with the new OEM (Oracle Enterprise Manager) interface.

You can perform additional operations on Index Organized Tables (IOTs), as well as redefinition operations on regular tables without any downtime for the users of those tables. The flexibility of the SPFILE initialization file option frees the DBA from having to edit a text-based initialization file and having to wait for a shutdown and restart for the new parameter values to take effect.

Disaster Recovery

In previous versions of Oracle, the DBA had to contend with a number of different parameters to strike a balance between high performance, availability, and minimal recovery time. Oracle9i introduces the new parameter FAST_START_MTTR_TARGET to allow the DBA to specify the maximum number of seconds that a crash recovery should take.

Database users can implement their own style of disaster recovery by using Oracle Flashback Query. A user can essentially move back to a particular point of time in the past and view the contents of a table or tables. Using this feature, users can "undo" changes made in the past by seeing which operations led to the change and then manually re-inserting or repairing the changes to the database. This feature can also be used as a historical query tool, for example, to give a bank customer an account balance as of a particular time in the past. This flashback capability is supported by the new system package DBMS_FLASHBACK.

Oracle Data Guard makes standby databases easier to use, with more robust failover features and an easy to use GUI interface. It essentially combines the primary and standby databases into a single "high availability" resource. Oracle's native standby database functionality (which can be managed under the Data Guard umbrella) has been enhanced to allow the primary database to be used as the new standby, instead of being discarded as in previous versions of Oracle.


Oracle9i contains a number of enhancements to make the Import and Export utilities more precise and efficient. Instead of having to manually recalculate table statistics after an import, the DBA can use statistics that were saved with the table during the export with the STATISTICS import parameter. This feature goes well beyond a simple "yes" or "no": the DBA can trust the Import engine to reject the saved statistics if they are questionable and to recalculate appropriately.

Another "fine-grained" enhancement to the Export utility is the ability to specify the tables to be exported by specifying the tablespace(s) that contain the tables to export. In addition to exporting all tables within a given tablespace, all indexes are exported with their corresponding tables regardless of where the index itself is stored.

The new Export and Import utilities support components of the Oracle Flashback Query feature, in which parts of an export can be extracted using new flashback parameters.


LogMiner, already a robust tool in previous versions of Oracle, has been significantly enhanced in Oracle9i. Unlike previous versions, the new version can support DDL statements, chained or migrated rows, and direct path inserts. Additionally, you can extract the database's data dictionary to the redo logs and analyze the logs with LogMiner.

In previous versions of LogMiner, all DDL statements were indirectly represented in the log files as several transactions against the data dictionary, making it difficult for the DBA to determine what the actual DDL statement was. Now, LogMiner will log both the DDL statement that the DBA or user typed, plus the multiple DML (Data Manipulation Language) statements run against the data dictionary.

Being able to extract the dictionary to a flat file or redo logs has several advantages:

There is no performance hit against the live data dictionary, reducing dictionary contention with other transactional users of the database.

Because all the information needed is in the redo logs, the database need not be open to use LogMiner.

In a quickly changing data dictionary, the table metadata in the redo logs may not match what is currently in the live data dictionary.

A couple of other features are worthy of mention. In previous versions of LogMiner, the analysis stopped when a corrupted redo log file was encountered. In the new version of LogMiner, the SKIP_CORRUPTION option in the DBMS_LOGMNR.START_LOGMNR procedure notes and ignores the bad block(s). The other new option in this procedure is COMMITTED_DATA_ONLY. With this option enabled, any LogMiner operation will return results only from committed transactions.

Backup and Recovery

The enhancements to Recovery Manager (RMAN) are numerous. They fall into three basic categories:

Persistent configuration parameters

General enhancements to backup and restore

A redesigned, easier-to-use graphical interface

RMAN now supports the CONFIGURE command, which allows the DBA to set the backup parameters persistently across backup sessions. Once all the appropriate parameters are set correctly, the DBA can do a full backup with one command, BACKUP DATABASE. The CONFIGURE command applies to many RMAN operations: backup retention policies, channel allocations, device type specifications, backup copies, and control file backups.

General enhancements to RMAN include long-term backups, mirrored backups, restartable backups, and archive log backups. Long-term backups are backups that you can explicitly archive for longer than the default retention policy. Mirrored backups are an enhanced version of the duplexing option originally released in Oracle8i, with the added capability to specify different formats (destinations) for backup copies. Time savings can be realized with the new restartable backup feature of RMAN. When you restart a backup with the NOT BACKED UP option, only missing or incomplete files are backed up, based on backup time. And finally, you can now include archive logs that have not been backed up in a datafile backup, instead of or in addition to using a BACKUP ARCHIVELOG command.

A significantly enhanced user interface to RMAN makes the DBA's job even easier. All the new options available in the command line interface are also available in the GUI version of the tool.

Online Operations

Many of the new features in Oracle9i allow online operations to proceed without interruption; in other words, access to tables and other database objects is continuously available to users even though redefinition and reorganization operations may be going on in the background. Of particular note are high availability enhancements related to Index-Organized Tables (IOTs), online reorganization of tables, and server-side parameter files (SPFILEs).

In previous versions of the Oracle database, an IOT was unavailable for most reorganization and index operations. In Oracle9i, a number of operations on IOTs are allowed while the table is in use. For example, you can create and rebuild IOT secondary indexes; you can update stale logical ROWIDs; you can rebuild IOTs by using the ALTER TABLE ... MOVE option, which can not only rebuild the primary key index but also rebuild the overflow data segment.

Problematic for an enterprise DBA are large tables that are heavily used around the clock and occasionally need some kind of modification or reorganization. In Oracle9i, many of the common operations that would previously have made the table unavailable can now be done "on the fly" with minimal impact to the table's users. For example, you can convert non-partitioned tables to partitioned tables, and vice versa. You can convert IOTs to heap-based tables. You can drop non-primary key columns, add new columns, and rename columns. In addition, you can modify storage parameters for a table. Tables without a primary key or tables with user-defined data types cannot be altered in this way, however.

SPFILEs enhance the online availability of databases by no longer requiring manual parameter file edits that may necessitate a restart of the database. An SPFILE is binary, not directly editable, and resides on the server. When you change SPFILE parameters with an ALTER SYSTEM command, they can be changed for the current instance only, the next restart of the instance (in other words, in the SPFILE), or both.


The scalability of the Oracle9i Server is improved in three areas:

Changes to the internal database structures to keep downtime to a minimum

Expansion of the Oracle clustering technology (Real Application Clusters) to add additional resources without changes to application programs

More flexibility in user session management to use session memory resources more efficiently


Numerous changes to the Oracle9i architecture make the Oracle database even more scalable as the enterprise grows, with little or no changes to applications or procedures. In many cases, these new features smooth the operation and maintenance of the database for the DBA. These features include global index architecture changes, metadata extraction capabilities, and tablespace block management changes along with various memory management enhancements.

Global index improvements allow users and DBAs to execute DDL commands without invalidating the entire global index. This keeps the availability of the index as high as possible while at the same time making the DBA's life easier by reducing the number of steps and commands required to keep the indexes valid.

Extracting the metadata from a database was a complicated task in previous versions of Oracle, involving multiple queries or doing special export/ import operations. Oracle9i adds a new package called DBMS_METADATA to either browse all metadata or to extract metadata for specific database objects. The output can be in either SQL or XML format.

The use of external tables in Oracle9i extends the reach of SQL select statements to external files. Although there are a number of restrictions on how external tables can be accessed, external tables provide a useful way to stage intermediate tables for data warehouse ETL (extract, transform, load) operations without loading the intermediate data into the database itself.

Automatic segment space management within a tablespace makes the DBA's life easier by essentially eliminating a lot of the guesswork when attempting to specify the default segment parameters in the tablespace. The free and used space is managed with bitmaps instead of free lists; tablespaces whose segment space is automatically managed must also be locally managed (that is, not managed in the data dictionary).

In the area of memory management, major changes were made in Oracle9i to ease the maintenance and improve the utilization of memory in the SGA (System Global Area). In essence, SGA memory and its sub-components can grow or shrink in response to changes in load or types of database operations being performed at the time. Memory in the SGA is now allocated in units called granules, whose size depends on the total estimated size of the SGA itself. In response to changing conditions, the DBA can dynamically change memory in each of the sub-components, such as the shared pool and buffer cache. To help the DBA in specifying an optimal buffer cache size, statistics collection can be enabled using the buffer cache advisory feature.

Real Application Clusters

In a nutshell, Real Application Clusters (RACs) allows multiple instances to run against the same database. Special hardware is required to allow a group of shared disks to be accessed at a very high throughput rate by each node in the cluster. Each node in the cluster can have more than one CPU.

There are a couple of benefits to using RACs. It's easy to add an additional node when the workload increases, without having to change any application code or operational procedures. Additionally, as each node is added to the cluster, the total availability of the database increases, as an instance failure on any particular node automatically initiates transparent application failover on one of the other nodes.

Cache Fusion, one of the new features included with RACs, allows data blocks to be shared between instances without the use of the shared disk resources. Retrieving a block from another instance's cache is significantly faster than retrieving that same block from a disk subsystem.

Session Management

Oracle Shared Server, formerly known as multithreaded server (MTS), contains many enhancements to further increase the performance and reduce the overhead of shared server connections. Changes to the connection establishment process reduce the total number of messages required to establish the connection between the client and the dispatcher. The new Common Event Model in the dispatcher handles both network and database events similarly, reducing overhead and the amount of polling required to capture the event notifications.

OCI (Oracle Call Interface) connection pooling allows middle-tier products to more efficiently manage a pool of connections for an application, rather than having the middleware explicitly manage the connections to the database.


Performance gains in the Oracle9i Server are realized with new features that are highly visible to the user or application developer. Conformance to the latest SQL standards makes coding more efficient for the developer and makes the execution of this code potentially more efficient on the server side. The DBA has a new feature set to help monitor index usage, allowing the DBA to drop indexes that are used infrequently or not at all.

SQL and PL/SQL Optimization

Oracle9i complies much closer with the SQL:1999 standards and syntax. Some of the standards now reflected in the Oracle SQL processor include enhancements to join operations, case statements, FK (foreign key) and PK (primary key) caching operations, and multi-table inserts. Significant enhancements to the PL/SQL processor allow for dramatic decreases in execution time for PL/SQL procedures, especially those that do not have SQL references.

You can now explicitly specify query join types in FROM clauses, rather than in the WHERE clause. The join types supported include cross joins (Cartesian products), natural joins (equijoins), and full, left, and right outer joins.

Oracle9i expands on the CASE expression that has been available since Oracle8i. A new type of CASE expression, a searched CASE expression, operates much like an IF ... THEN ... ELSE construct and allows for multiple predicates within the WHEN clauses. The NULLIF and COALESCE functions operate much like "abbreviated" CASE statements for returning and evaluating null values.

Unindexed foreign keys still require table-level share locks when an update or delete on the primary key takes place; however, the overhead is reduced and availability increased because the lock is immediately released after it is obtained. Foreign key creation is faster because Oracle9i caches the first 256 primary key values for DML statements that process at least two rows.

The new multi-table insert feature allows for easier coding and less SQL processing overhead, because all source and destination tables are specified in the same INSERT statement. You can also use this feature to easily refresh materialized views in a data warehouse environment.


Excerpted from "OCA/OCP: Oracle9i DBA Fundamentals I Study Guide" by Biju Thomas. Copyright © 2002 by Biju Thomas. Excerpted by permission. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher. Excerpts are provided solely for the personal use of visitors to this web site.
Thanks for reading!

Join BookDaily now and receive featured titles to sample for free by email.
Reading a book excerpt is the best way to evaluate it before you spend your time or money.

Just enter your email address and password below to get started:


Your email address is safe with us. Privacy policy
By clicking ”Get Started“ you agree to the Terms of Use. All fields are required

Instant Bonus: Get immediate access to a daily updated listing of free ebooks from Amazon when you confirm your account!

Author Profile

Amazon Reviews