Study Brief: Oracle9i New Features
by Michael Ritacco, Oraclenotes.com	Version 1.3

Oracle Server Security

CONNECT INTERNAL has been discontinued and is no longer available as a DBA access method.

Server Manager is no longer available. SQLPlus has all the features of Server Manager and should be used for all database management activities.

Equivalent Internal Connections

SQL> connect sys/change_on_install as sysdba
SQL> connect / as sysdba

Database Creation Assistant (DBCA) which was called DBASSIST in Oracle8i now creates databases with accounts locked and without default passwords. This increases security by closing access to most of the standard accounts, such as CTXSYS, MDSYS, OUTLN.

The exception are SYS, SYSTEM, and SCOTT which are not locked by the DBCA. However, the tool requires you to enter a password for these account since no default are provided. If you create a database manually, the passwords for SYS and SYSTEM have the Oracle8i and earlier defaults.

The 07_DICTIONARY_ACCESSIBILITY is an initialization parameter previously set to TRUE, which enabled users with the SELECT ANY TABLE privilege to read the data dictionary tables. This parameter is now set to FALSE which requires login as SYSDBA to read the data dictionary, or have explicit object grants from SYS.

Another consideration to improve security is to revoke EXECUTION granted to PUBLIC on some packages. Granting only the privileges to the few users that require execution will limit access to some features which could be used by a hacker to gain access to other parts of the database.

Secure Application Roles

Enabling a role is checked through a package not a password. Using the SYS_CONTEXT mechanism found in the Virtual Private Database.

Global Application Contexts

A context can now be global and shared and is:

• Excellent for web based applications using VPD.
• Cheaper in resources.
• Still able to verify access rights via an identifier.
• Good for connection multiplexing

To help manage application contexts the following interfaces have been added to DBMS_SESSION package.

• CLEAR_CONTEXT
• CLEAR_IDENTIFIER
• SET_CONTEXT
• SET_IDENTIFIER

Fine-Grained Access Control Enhancements

Oracle9i introduces partitioned Fine-Grained Access Control (FGAC) which enables application driven security policies. Previously, when FGAC was defined on a table a row must satisfy all requirements before being visible. When the table was used by users from separate application groups trying to share the same data, it required one very complicated FGAC to include all user groups' requirements.

Policy Groups are used to distinguish policies between different applications. The Driving Context indicated the policy group in effect. When tables and views are accessed, the FGAC engine looks up the driving context to identify the policy group in effect, enforcing all policies that belong to that policy group.

SYS_DEFAULT is predefined and are always executed in addition to policy group specified by the driving context.

Fine-Grained Auditing

Provides extensible intrusion detection, capturing SQL statements, not the retrieved data, with the ability to invoke a procedure as part of the audit.

Audit policies are created with the DBMS_FGA on the tables needing audit. A list of the policies in effect can be found in the DBA_AUDIT_POLICIES view. Audit records are placed in the DBA_FGA_AUDIT_TRAIL. Administrators can define audit event handlers to process events, which could send a e-mail alert to administrators.

Encryption Enhancements

GETKEY has been added to the DBMS_OBFUSCATION_TOOLKIT package. The GETKEY procedure is a Federal Information Processing Standard -140 certified random number generator used for secure key generation.

High Availability

Oracle9i introduces a two-pass instance/crash recovery to reduce recovery time.

Factors affecting instance recovery time:

• Time required to read change information from the redo log files.
• Time required to read, modify, and write the data blocks affected by those changes.

Since the redo logs may contain entries of changes made to data blocks that were not dirty in the buffer cache at the time of failure, Oracle now reads the logs twice. The first read establishes the minimum number of block needed for recovery with the second pass applying only those required changes. Since the first sequential read is very fast, the reduction in workload easily offsets the cost of the additional read and results in an improved recovery time.

This feature is enabled by default and requires no configuration by the DBA.

Fast-Start Time-Based Recovery Limit

The new dynamic initialization parameter FAST_START_MTTR_TARGET allows for the DBA to specify the estimated number of seconds a crash recovery should take. Replaces the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters.

The FAST_START_MTTR_TARGET parameter internally maps to the correct values for FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL, however since this is set by the Oracle Server it greatly simplifies and increases the accuracy of setting these parameters individually.

V$INSTANCE_RECOVERY has the following new columns added:

• TARGET_MTTR
• ESTIMATED_MTTR
• CKPT_BLOCK_WRITES

V$INSTANCE_RECOVERY is used by the DBA to monitor check pointing Every 30 seconds, Oracle calculates an estimate of the current MTTR based on current I/O rates and places this value in V$INSTANCE_RECOVERY. Allowing the DBA to monitor the current estimated MTTR to the setting specified by FAST_START_MTTR_TARGET.

Note: A small value for FAST_START_MTTR_TARGET can have a negative impact on performance because of the additional checkpoints that will be generated. Additional writes due to check pointing are displayed in the column CKPT_BLOCK_WRITES.

Oracle Flashback

Enables users to see a (read-only) consistent view of the database at a point in the past. Only committed transactions up until the time specified are visible.

DBMS_FLASHBACK is the interface with the Oracle Flashback functionality. Users must have the execute privilege on DBMS_FLASHBACK to use the feature. Note: The DBMS_FLASHBACK package cannot be executed as SYS.

A long enough undo retention interval must be set to be able to construct the data.

SQL> alter system set undo_retention= <seconds>;

Resumable Space Allocation

The resumable space allocation feature provides the ability to resume execution of a database operation in the event of a repairable failure. A statement executes in Resumable Space Allocation mode only when explicitly set for the session using the ALTER SESSION ENABLE RESUMABLE command.

The following types of operations are candidates for Resumable Space Allocation:

1. Queries: Select statements which run out of temporary space.
2. DML: Insert, Update, Delete
3. Import/Export, when using the new parameter:RESUMABLE=Y
4. SQL*Loader
5. DDLs: Alter table {MOVE}, Create table as select, Alter Index, Create Materialized View, etc.

The DBMS_RESUMABLE package:

• ABORT
• GET_SESSION_TIMEOUT
• GET_TIMEOUT
• GET_SESSION_TIMEOUT
• SET_TIMEOUT
• SPACE_ERROR_INFO

Note: You cannot use this package to enable Resumable Space Allocation mode for a session. However, the DBA can create a logon trigger that can enable this feature.

The After Suspend System event is new in Oracle9i. Used with a After Suspend trigger the DBA has the ability to handle the user error. Oracle recommends that the After Suspend trigger be created in the SYS schema.

The new RESUMABLE system privilege allows execution of statements in Resumable Space Allocation mode.

The DBA_RESUMABLE and USER_RESUMABLE views display the set of Resumable Space Allocation statements in the system. This information is not persistent and cannot be accessed across database shutdown or startup.

Export/Import Enhancements

New import parameter for statistics (STATISTICS)

• ALWAYS: imports precalculated statistics
• SAFE: imports precalculated statistics only when safe.
• RECALCULATE: executes ANALYZE during the import.
• NONE: Neither recalculate or imports statistics.

New export tablespace parameter (TABLESPACES)

• Exports only the tables residing in a specified tablespace.
• Exports Indexes regardless of location.
• Must have EXP_FULL_DATABASE privilege to export in TABLESPACES mode.

New Import/Export Resumable Space Allocation parameters:

• RESUMABLE: must be set to "Y" before other parameter are available.
• RESUMABLE_NAME
• RESUMABLE_TIMEOUT

New Import/Export Flashback parameters:

• FLASHBACK_SCN: used to set session snapshot back to SCN.
• FLASHBACK_TIME: used to get SCN closest to the specified time.

LogMiner Enhancements

Oracle9i adds the following new features:

• Support for DDL
• Dictionary Staleness detection
• Ability to store dictionary in redo logs
• Ability to use an online dictionary
• Ability to skip log corruption
• A New GUI tool called the LogMiner Viewer

LogMiner Restrictions

• LogMiner does not support LONG or LOB datatypes
• No support for Object Types
• No support for Collections (nest tables and VARRAYS)
• No support for Index Organized Tables

Note: LogMiner can be run in a shared server configuration. However, this is not recommended due to the performance impact. It is suggested to user a dedicated server for a LogMiner connection.

LogMiner Views

• V$LOGMNR_CONTENTS
• V$LOGMNR_DICTIONARY
• V$LOGMNR_LOGS
• V$LOGMNR_PARAMETERS

Backup and Recovery

Oracle9i RMAN Enhancements

• Persistent Configuration parameters
• Automatic Channel Allocation
• Retention Policies
• Backup file optimization
• Restartable backups/restores
• Improved Enterprise Manager interface
• Archive log failover
• Automatic log switch
• Backup piece failover
• Block media recover (BMR)
• Trial recovery
• Reporting commands enhanced
• Supports Oracle Managed Files (OMF)
• Support for Multiple Block sizes
• Improved messages and debugging: No more RMAN-nnnnn prefix for nonerror messages.

Customizable configuration parameters enable the DBA to simplify most RMAN operations. The default settings required for the environment can now be set once and used for all backup jobs. The configuration values are stored in the control file and synchronized to the recovery catalog if applicable.

New commands:
BACKUP DATABASE : a single command to backup the database using RMAN.
CONFIGURE : allows the DBA to override default settings persistently.

RMAN allows for two types of retention policies to manage your backups. These policies are mutually exclusive and set using the CONFIGURE command. Note: The default retention policy is REDUNDANCY 1.

• Recovery Window: establishes a period of time within which a point-in-time recovery must be possible.
• Redundancy: sets a minimum number of backups that must be kept. Any backups exceeding this threshold can be deleted.

Remember to set the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME greater then the space requirements of your retention policy, if not using a recovery catalog. Otherwise, RMAN will not be able to reconstruct a list of backup files required to do a valid restore.

• RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
• RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
• RMAN> CONFIGURE RETENTION POLICY TO NONE;
• RMAN> CONFIGURE RETENTION POLICY TO CLEAR;

Note: the difference between CONFIGURE RETENTION POLICY TO NONE and CONFIGURE RETENTION POLICY TO CLEAR. Setting the retention policy to NONE means there is no retention policy in effect, backups will not expire and the DELETE OBSOLETE command will error. Using the CLEAR command resets RMAN back to the default retention policy of REDUNDANCY 1.

The DELETE OBSOLETE command is used to delete backups that are no longer needed to satisfy the retention policy.

You can use the following data dictionary views to see RMAN configuration settings:

• V$RMAN_CONFIGURATION
• RC_RMAN_CONFIGURATION
• RC_TABLESPACE
• RC_DATAFILE
  

Automatic Channel Allocation
This feature improves and simplifies the interface to RMAN. Now a channel is automatically allocated if one is not explicitly specified in a RMAN script. Since RMAN knows which commands are being run it can appropriately allocated channels based on the type of work being done. For backups, only a single type of channel is allocated. For restores, RMAN know which devices types (DISK/TAPE) are required for the restore, and allocates channels as required.

This feature can be used with the following commands:

• BACKUP
• COPY
• RESTORE

RMAN Configuration Commands

• CONFIGURE CHANNEL
• CONFIGURE DEVICE TYPE PARALLELISM
• CONFIGURE DEFAULT DEVICE TYPE
• CONFIGURE BACKUP COPIES
• CONFIGURE EXCLUDE
• CONFIGURE SNAPSHOT CONTROLFILE
• CONFIGURE AUXNAME
• CONFIGURE CONTROLFILE AUTOBACKUP

Long Term Backups
Backups can be archived for time-periods longer then specified by the retention policy, by using the KEEP option.

RMAN> BACKUP .... KEEP [UNTIL TIME 'date'|FOREVER] [NOLOGS|LOGS]

KEEP can be used with both the BACKUP, COPY, and CHANGE commands.

• UNTIL TIME: specifies the date until the backup must be kept.
• FOREVER: the backup never expires and must be used with a recovery catalog. RMAN will error if you use this option without a recovery catalog.
• LOGS: keeps all required archive logs for the backup so that it is possible to recover this backup to any point in time.
• NOLOGS: not a valid option for online backups.

Restartable Backups
Unsuccessful RMAN backups can now be restarted. RMAN finds only the missing or incomplete files (based on backup time) and continues the backup; files that were successfully backed up before the failure are skipped.

Use the new option NOT BACKED UP option to enable this feature.

RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME '03-FEB-02 15:00:00';

Note: This feature only works with Oracle9i databases, previous releases of Oracle will not restart.

Archive Log Backups
Archive logs which have not been backed up can now be included with data file backups using the PLUS ARCHIVELOG option of the BACKUP command. The PLUS ARCHIVELOG option is also the default when backing to tape. RMAN will not signal an error if no archive logs are found to backup.

The revised non-disk process follows the following steps:

1. Backup of all archive logs that have not been backed up.
2. Backup of the file(s) specified by the backup command.
3. Log switch is performed
4. Backup of any additional logs.

Backupset Backups
This feature enables the DBA to perform disk management using RMAN. The source for this command must be disk, and when the command completes, the backup exists on both disk and tape.

RMAN> BACKUP DEVICE TYPE SBT BACKUPSET ALL;

Using the DELETE INPUT option enables the DBA to have the most current backups on disk while older backups residing just on tape.

RMAN> BACKUP DEVICE TYPE SBT BACKUPSET CREATED BEFORE 'sysdate-2' DELETE INPUT;

Restartable Restores/Restore File Optimization
Using RMAN's restore file optimization, RMAN checks the consistency of each file that is to be restored and if the file header contains the expected information, it will not restore the file to disk again from the backup. Previously, if a restore operation failed RMAN would restore all data files from backup. This was especially painful if the failure occurred on the last data file and your restore had been running for 8 hours. With restore file optimization, RMAN examines all target file headers before beginning the restore and restores just the data files with the incorrect state.

This feature allows restore operations to be restarted after any type of failure without doing unnecessary work. The RESTORE FORCE command still can be used to restore a file regardless of the header state.

Note: Restore optimization only checks the data file headers without scanning the data file body for corruption.

Block Media Recovery (BMR)
A block is now the smallest unit of media restore and recovery. BMR lowers the mean time to recover and increases data availability during media recovery. BMR uses the existing recovery mechanisms to apply changes from the redo stream to block versions restored from backup. This feature is only available through RMAN and is not available via existing SQL interfaces.

Note: Only complete recovery is possible with block media recovery. A incomplete recovery would result in the data files involved to be physically inconsistent.

BMR is not suitable when the extent of data loss or corruption is unknown, and is targeted towards recovering when specific blocks are reported in Oracle errors.

RMAN performs BMR with the BLOCKRECOVER command. RMAN identifies the required backups to obtain the blocks to recover and reads the backups placing the requested blocks into in-memory buffers. RMAN manages the complete block media recovery, reading any archive logs from backup if necessary. BMR always performs a complete recovery.

RMAN> BLOCKRECOVER DATAFILE 1 BLOCK 2;

Two types of corruption can be detected by RMAN when using the BACKUP and COPY commands:

1. Physical corruption (media corruption): The Oracle server does not recognize the block at all. The checksum is invalid, the block contains all zeros, or the header and footer mismatch. Physical corruption checking is on by default, and is turned off by the NOCHECKSUM option.
2. Logical Corruption: The block has a valid checksum, header and footer match but the contents are inconsistent. Logical checking if OFF by default, and is turned on with the CHECK LOGICAL option.

RMAN lists blocks that failed logical validation during backup in:

• V$COPY_CORRUPTION
• V$BACKUP_CORRUPTION

Using the CORRUPTION LIST clause specifies all blocks found in these views to be recovered.

Note: The RESTORE UNTIL clause specifies backups and copies created before the specified date. In this example RMAN would use a backup from 2 days ago.

RMAN> BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE - 2';

RMAN Reporting Enhancements

• REPORT OBSOLETE
• REPORT NEED BACKUP
• CROSSCHECK
  • Finds backup pieces in various Real Application Clusters nodes/devices automatically
• LIST
  • By Backup
  • By File
• DELETE
• EXPIRED
• SHOW: shows the values for the various CONFIGURE commands.

Trial Recovery
If recovery encounters a problem the database is always left in a physically consistent state. The errors encountered and information about them are found in the alert log. Since the alert log only contains information about errors found up until that point, trial recovery can be used to determine the entire extent of the damage.

Trial recovery is a new feature that allows the DBA to find out how many more problematic blocks are going to occur during recovery. Trial recovery only changes blocks in memory and can allow an unlimited number of corrupt data blocks. Errors encountered during a trial recovery are written to the alert log and are marked as test run errors.

The DBA may invoke a test recovery by adding the TEST option to any restore command:

• RECOVER DATABASE USING BACKUP CONTROLFILE TEST;
• RECOVER TABLESPACE emp TEST;
• RECOVER DATABASE UNTIL CANCEL TEST;
  

With the ability to further investigate, the DBA can either choose to open resetlogs at that point or continue recovery if only a few blocks are bad using the ALLOW N CORRUPTION option.

SQL> RECOVER DATABASE ALLOW N CORRUPTION;

A trial recovery ends when:

• An unrecoverable error is signaled.
• The Oracle server runs out of available buffers in memory.
• The DBA cancels or interrupts the recovery session.
• The next redo record changes the control file.
• All redo desired to be applied has been applied.

When a Trial Recovery ends, even if the instance dies, all effects of the test run are automatically removed from the system because trial recovery NEVER writes changes to disk.

Data Guard

Oracle9i Data Guard helps your database survive destructive events by:

• Providing a easy configuration and control through a enhanced GUI interface.
• Providing switchover to another site for planned maintenance.
• Providing failover to another site during unplanned failures.
• Guaranteeing no data loss through a synchronous log transport.
• Preventing the propagation of mistakes and corruption.

Data Guard Architecture:

• Primary database
• Physical standby database
• Network configuration
• Log transport services
• Log apply services
• Data Guard

The DMON process is started using the parameter DRS_START=TRUE.

Data Availability Modes in Data Guard

• Guaranteed protection: No data divergence, LGWR sends redo records to standby.
• Instant protection: No data loss, LGWR sends redo records to standby.
• Rapid protection: No guarantee for modification to be available on standby when primary site commits. LGWR slaves send redo records to standby site.
• Delayed protection: The archiver process transmits the completed archives to the standby sites. Available in releases prior to Oracle9i, sames as in Oracle8i.

Failover
During an unplanned outage the primary role is moved to one of the standby sites.

• In pre-Oracle9i releases, this is the only available option to move processing to the standby site.
• The primary had to be discarded and could not be used as the new standby, due to the restlogs operation that takes place by the ACTIVATE STANDBY command.
• The system is at risk while creating the new standby site.

Switchover
New to Oracle9i, primary and standby databases can alternate roles without rebuilding the standby site, under the following conditions.

• The primary performs a graceful shutdown. ( Immediate, transactional, normal)
• All archive logs are available to bring the standby to the primary's point in time.
• Primary online redo logs, data files, and control files are available and intact.

Standby Switchover Steps

1. End any read or update activity on the primary and standby databases. Terminate all open sessions in both the primary and standby database. The DBA should be the only open session.
2. Verify in the primary database the switchover is possible by checking the SWITCHOVER_STATUS column of V$DATABASE.
   
   SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

This command performs the following:

• Closes the primary database
• Archives any remaining log files and applies them to the standby database.
• Adds an end-of-redo marker to the header of the last log file being archived.
• Creates a backup of the current control file and converts the current control file into a standby control file.

3. Change any initialization parameters to reflect the changing of roles. SHUTDOWN NORMAL the former primary database. Then STARTUP NOMOUNT.
4. Mount the former primary database in the standby database role.
   Execute on the primary database:
   SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
5. Prepare the standby database to switch to primary role
   SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;
6. SHUTDOWN the standby database.
7. Change any initialization parameters to reflect the changing of roles.
   STARTUP;
8. Enable managed recovery mode on the standby database.
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Automatic Recovery of Log Gaps
Oracle9i eliminates any need for operator intervention when any number of situation may arise causing the standby database to be unable to apply the next archive redo log. Managed Recovery must be enabled.

Initialization Parameters:

• FAL_CLIENT: set on the standby database.
• FAL_SERVER: set on the standby database.
• Use the V$ARCHIVE_GAPS view to determine archive gaps.
  

Standby File Management
Adding or dropping files in the standby database has been automated in Oracle9i. However, certain operations are not allowed in the standby database. The following commands are not allowed: ALTER DATABASE RENAME, ADD/DROP LOGFILE, ADD/DROP LOGFILE MEMBER, CREATE DATAFILE AS.

Set the following parameter to enable this feature:

STANDBY_FILE_MANAGEMENT =AUTO

Note: Non-Oracle Managed Files are not dropped on the standby database. If you drop the tablespace on the primary, the tablespace will drop on the standby but the data files must be removed manually.

Background Managed Recovery Mode
There is now a background process called MPR used to perform managed recovery. Freeing the terminal session to execute the recovery command.

• ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
• RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
• Monitor Managed recovery by querying the V$MANGED_STANDBY view on the standby database.

Standby Lag Time
The DBA can delay the availability of archive log files at the primary site to the standby database. The value of this attribute is in minutes and specifies the interval that must expire, after archive log transmittal has completed, before the archive log can be processed. The standby database can override this delay by using the [NO DELAY] or [FINISH] option of the RECOVER MANAGED STANDBY DATABASE command.

• LOG_ARCHIVE_DEST_n= 'SERVICE =stby1 DELAY 30'

Online redo log enhancements:

• ALTER SYSTEM ARCHIVE LOG CURRENT NOSWITCH;
  Archive the current log without switching in open or mounted mode. If done while the database is open mode, the database will automatically shutdown and next startup will force a log switch.
  
• ALTER SYSTEM ARCHIVE LOGFILE '/ora01/oradata/log01.rdo' USING BACKUP CONTROLFILE.
  Archives an online redo log when a backup control file is being used. Previous releases of Oracle required a current control file.
• Archiving a online redo log based on SCN is now possible.
• Off site archival of redo logs is possible with the archive log repository, which is basically a stand-alone standby control file. Set the REMOTE_ARCHIVE_ENABLE=TRUE parameter on both the primary and standby database to enable automatic archival of online redo logs to remote archiving destinations.

Database Resource Manager Enhancements

Active Session Pool
The active session pool allows the DBA to control concurrent system workload. With this functionality the DBA can indirectly control the amount of resources that any resource consumer group uses. By controlling the number of active sessions in the database, and queuing active sessions once the active session pool is filled, a minimum set of available system resources can be guaranteed.

Setting the Active session pool size for a resource consumer group:

• Sets the the maximum number of concurrently active sessions.
• An active session is defined as a session currently part of an active transaction, query, or parallel operation. Individual parallel slaves do not count toward the number of sessions. The entire parallel operation counts as one active session.
• Only one active session pool size is allowed per consumer group.

Parameters:

• ACTIVE_SESS_POOL_P1: sets the number of active sessions possible for the resource consumer group. Default is 1000000.
• QUEUEING_P1: sets the amount of time in seconds that any session will wait on the queue before aborting the current operation. Default is 1000000.

Maximum Estimated Execution Time
The resource manager can now estimate the execution time of an operation proactively. The DBA can specify a maximum estimated execution time for an operation at the resource consumer group level. The benefit is the ability to eliminate large jobs that would consume too many system resources.

• MAX_ESTIMATED_EXEC_TIME: sets the maximum estimated time an operation can take. The operation will not start if the estimate exceeds this thresholds. Default is 1000000.

Automatic Consumer Group Switching
The database resource manager automatically switches a session's consumer group based on the following resource plan directives:

• SWITCH_GROUP: Group switched to. Default is null.
• SWITCH_TIME: Active time in seconds. Default is 1000000.
• SWITCH_ESTIMATE: If set to TRUE, the execution estimate is used to decide whether to switch the operation before it starts. Default is FALSE.

Undo Quota
New plan directive has been added UNDO_POOL. UNDO_POOL is specified in kilobytes and the default is 1000000.

• Limits the amount of undo space that can be used.
• If exceeded, will prevent DML (INSERT, UPDATE, DELETE)
• SELECT statements are not affected by this directive.

The DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE package can be used to change the undo quota anytime during database operation.

Oracle Supplied Plans

• SYSTEM_PLAN: plan assigned to system sessions priority.
• INTERNAL_QUIESCE: plan to internally quiesce the system.
• INTERNAL_PLAN: for testing only.

Modified Views

• V$SESSION adds the new CURRENT_QUEUE_DURATION column.
• V$RSRC_CONSUMER_GROUP adds two new columns:
  • QUEUE_LENGTH
  • CURRENT_UNDO_CONSUMPTION
• DBA_RSRC_PLANS adds a new column QUEUEING_MTH which defines the queuing resource allocation method.
• MAX_ACTIVE_SESS_TARGET_MTH has been renamed to ACTIVE_SESS_POOL_MTH.
• DA_RSRC_PLAN_DIRECTIVES adds the following new columns
  • ACTIVE_SESS_POOL_P1
  • QUEUEING_P1
  • SWITCH_GROUP
  • SWITCH_TIME
  • SWITCH_ESTIMATE
  • MAX_EST_EXEC_TIME
  • UNDO_POOL

Online Operations

Oracle9i can now perform ANALYZE VALIDATE STRUCTURE online. In addition to having the capability to "quiesce" the database. A database that is quiesced only allows transaction to be made by DBA, however all currently connected users are blocked from performing queries, DML and PL/SQL. This enables the DBA to perform maintenance operations without a shutdown and enabling RESTRICTED SESSION.

Quiesce benefits over RESTRICTED SESSION:

• Users do not lose their sessions.
• No cache warm-up required since nothing has been flushed.
• DDL like ALTER TABLE, CREATE OR REPLACE PROCEDURE, and DROP TABLE benefit from this new database state.

SQL> ALTER SYSTEM QUIESCE RESTRICTED;
This command puts the database in a quiesced state.

SQL> ALTER SYSTEM UNQUIESCE;
This command returns the database to a normal state.

The ACTIVE_STATE column of V$INSTANCE can be checked to see what state (3) the database is in:

• Normal
• Quiescing
• Quiesced

Quiesce Limitations

• Must be using Resource Manager since instance startup and without interruption.
• Only SYS and SYSTEM are considered DBA users no matter what privileges have been granted to the account.
  

Online Index Rebuild adds support for the following indexes:

• Reverse key indexes
• Key Compressed indexes on tables.
• Key compress indexes on IOTs (including secondary indexes)
• Function-based indexes

Index Organized Table Enhancements

• Online coalesce of primary indexes
• Ability to create and rebuild as well as online updates of logical ROWIDS on secondary indexes.
• Online moves of IOTs and OVERFLOW segments.

Online Table Redefinition
DBMS_REDEFINITION is the package used for online table redefinition but has several limitations.

The following cannot currently be redefined:

• SYS and SYSTEM tables cannot be redefined
• An IOT overflow table
• Temporary tables
• Clustered tables
• Materialized view tables.
• Advanced queuing tables

Server Parameter File (SPFILE) is a binary file that can persistently store instance parameters across startup and shutdown.

Default location
$ORACLE_HOME/dbs

Default Name
spfile<instance>.ora

You can create a spfile in any database state (IDLE, NOMOUNT, MOUNT, or OPEN) but your user must be granted either the SYSDBA or SYSOPER role.

Use the following command to create a spfile from a existing init.ora:

SQL> CREATE SPFILE 'spfile-name' FROM PFILE 'pfile-name';

Use this command to export the contents of a spfile to a standard init.ora file.

SQL> CREATE PFILE = 'mypfile.ora' FROM SPFILE;

The DBA can view the contents of the spfile with the new V$SPPARAMETER view. Parameters can be changed using the ALTER SYSTEM SET command with the additional cause SCOPE. Scope can be set to MEMORY, SPFILE, and BOTH. If a spfile was used to startup the database BOTH is the default, if a init.ora file was used MEMORY is the default.

Oracle9i database startup behavior has changed now that the STARTUP command by uses the spfile by default and only if not found or is unusable the instance uses the default init.ora file. Note: The init.ora can contain a pointer to call a spfile.

Segment Management

• Automation of Global Index Maintenance operations
• List Partitioning method
• Metadata API
• Oracle9i ETL
• External tables
• Automatic Segment Management
• DBMS_SPACE enhancements
• DBMS_REPAR enhancements
• Bitmap Join Indexes

FULL_BLOCKS - blocks no longer available for INSERTs
FS1_BLOCKS - blocks below the HWM with 0-25% free space
FS2_BLOCKS - blocks below the HWM with 25-50% free space
FS3_BLOCKS - blocks below the HWM with 50-75% free space
FS4_BLOCKS - blocks below the HWM with 75-100% free space

Performance Improvements

• Skip Scanning Indexes
• Cursor Sharing Enhancements
• Cached Execution Plans
• DBMS_STATS enhanced estimation of statistics
• CBO Optimizer Enhancements

Session Management

• (MTS) Shared Server enhancements

Steps for using OCI Connection Pooling

• Allocate the Pool Handle
• Create the Connection Pool
• Log on to the database
• Log off the database
• Destroy the Connection Pool
• Free the Pool Handle

Real Application Clusters

Cache Fusion Block Transfers:
The RAC algorithm passes either clean or dirty block images from one instance to another through the cluster interconnect. Cache Fusion allows data to be shared between instances without the need to write the block to disk. In most cases the request from another instance's memory is faster then the requesting instance reading the block from disk itself.

Initialization Parameters

• INSTANCE_NUMBER
• INSTANCE_NAME
• GC_FILES_TO_LOCK
• CLUSTER_DATABASE
• CLUSTER_DATABASE_INSTANCES

Background Processes

• LMS : Global Cache Service Process
• LMON: Global Enqueue Service Monitor
• LMD: Global Enqueue Service

SRVCTL is the tool used to manage a RAC environment and extends and replaces OPSCTL.
Commands:

• START
• STOP
• STATUS
• DELETE
• GET

One spfile.ora can be used for all RAC instances either on a shared disk or through a ifile pointer in the init.ora file.

File Management

• Oracle Managed Files (OMF)
• OMF initialization parameters
• OMF file naming structure
• Default Temporary tablespaces

Tablespace Management

• Automatic Undo Management
• Undo tablespace commands and data dictionary views
• Undo Retention periods
• Multiple block size support

Memory Management

• SQL Execution Memory Management
• PGA Memory Management
• Dynamic SGA
• Dynamic Shared Pool
• Dynamic Buffer Cache
• Buffer Cache Advisory

Enterprise Manager Enhancements

• Undo Tablespace Support
• Advanced Queuing
• Backup and Recovery enhancements
• User defined Events
• Event Handlers

SQL Enhancements

• SQL:1999 enhancements
• MERGE statement
• New Analytical functions
• Foreign Key Locking enhancements
• Index scans
• Multitable Inserts
• LONG to LOB migration
• PL/SQL performance enhancements
• Common SQL parser

Globalization Support

New date data types in Oracle9i:

• TIMESTAMP: stores data and time, precision is of fractional seconds with the default is 6 digits and maximum 9 digits.
• TIMESTAMP WITH TIME ZONE: absolute time.
• TIMESTAMP WITH LOCAL TIME ZONE: relative time. Normalizes the time to the database time zone.
• INTERVAL YEAR TO MONTH: stores a length of time specified in years and months. Default precision is 2, maximum 9.
• INTERVAL DAY TO SECOND: stores a length of time specified in days, hours, minutes, and seconds. Default precision is 2, maximum 9, and zero disables the field.

Datetime Functions

• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• SYSTIMESTAMP
• DBTIMESTAMP
• SESSIONTIMEZONE
• SYSDATE

Unicode Support

• UTF-8: a variable-width mutibyte encoding.
• UTF-16: a fixed-width multibyte encoding
• UNISTR converts a sting to a Unicode string.

Linguistic sorting
Now uses four-level sorting

• Enables more complex sorting rules.
• Sorting definitions utilize three levels.
• User can define sorting rules be defining the fourth level.

Character Set Scanner
Is a tool used before converting database character sets and performs the following:

• Scans the database for any problems using the csscan command line utility.
• Can perform scans for any character type conversions.
• Output is a report of potential problems with conversion:
  • scan.out
  • scan.txt
  • scan.err

Character Conversion
The following methods are available to perform character set conversion.

• Full Export and Import (Oracle recommended)
• ALTER DATABASE, only if no characters change codes.
• A combination of the above two methods. Use the ALTER DATABASE command and export and import any tables that contain changing character codes.

Oracle Locale Builder
Is a GUI tool that can be used to create your own locale or make adjustments to existing locales. However, building your message files is not supported.

• Language
• Character Set
• Collation
• Territory

SQL*Loader
Supports loading of UTF-16 data.

Database Workspace Management

Database Workspaces allow a virtual environment where version-enabled tables can exist. The workspace can be shared with one or more users allowing versioning of the data in the database. Changes made to version-enabled tables is invisible to other users until they have been merged with the parent workspace.

Guidelines for Version-enabled tables

• Table must have a primary key.
• Table owner or a user with WM_ADMIN_ROLE can version-enable a table
• SYS owned tables cannot be version-enabled
• Referential Integrity constraints are supported
• Triggers are supported.

Creating a Workspace

DBMS_WM.CREATEWORKSPACE('TEST')

Enabling Versioning on a Table

DBMS_WM.ENABLEVERSIONING('TABLE_NAME')

Disabling Versioning on a Table

DBMS_WM.DISABLEVERSIONING('TABLE_NAME')

Granting Privileges for Workspaces
There are two types of privileges for workspaces, workspace-level and system-level and can be specified with the grant option.

Workspace-Level privileges are specific to a particular workspace.
priv_WORKSPACE

System-Level are affect all workspaces.
priv_ANY_WORKSPACE

Available Privileges:

• Access
• Create
• Merge
• Remove
• Rollback

Assigning a Workspace
A user is placed in the Live workspace at login, therefor you must use the GOTOWORKSPACE procedure to move a session the the desired workspace. A user must have the ACCESS_WORKSPACE privilege for the assigned workspace or the ACCESS_ANY_WORKSPACE system privilege.

DBMS_WM.GOTOWORKSPACE('MYWKSP')

History of Version-enabled tables

The history of versions of rows can be tracked using the DBMS_WM.ENABLEVERSIONING procedure.

• VIEW_W_OVERWRITE: most recent modifications, future changes will overwrite earlier changes.