Oracle Database 9i Release 2: What's New, Release History & Support Lifecycle

What Is New in Oracle Database 9i Release 2 (9.2)

Oracle9i Database Release 2 (9.2) is a broad platform release that raises the bar across high availability, manageability, security, XML support, and information integration. The themes below cover the changes that matter most in day-to-day production operations and upgrade planning.

How does Oracle9i Release 2 improve high availability and disaster recovery?

Oracle9i 9.2 delivers the most significant Data Guard expansion since the feature was introduced, adding a fully operational Logical Standby database alongside the existing physical standby model.

Physical standby applies redo block-for-block, keeping the standby offline to user queries. The new Logical Standby applies changes as SQL statements regenerated from redo logs -- meaning the standby can simultaneously serve live reporting queries while receiving updates from the primary. If the primary fails, the logical standby is activated as the new primary with no data loss.

The Data Guard broker now manages up to nine standby databases in a single configuration, supporting mixed physical and logical configurations with scripted role transitions. In practice, teams running read-heavy analytics should evaluate the logical standby as a way to offload reporting without a separate ETL pipeline.

Flashback Query is extended to work inside a SQL statement rather than only within a session, making row-level undo a realistic self-service option for end users. Deleted rows or overwritten values can be recovered without DBA involvement and without database downtime:

-- Recover a deleted row using flashback query
SELECT * FROM orders AS OF TIMESTAMP
  (SYSTIMESTAMP - INTERVAL '60' MINUTE)
WHERE order_id = 100042;

-- Use the result to re-insert the lost row
INSERT INTO orders
SELECT * FROM orders AS OF TIMESTAMP
  (SYSTIMESTAMP - INTERVAL '60' MINUTE)
WHERE order_id = 100042;

Watch out for undo retention settings -- if UNDO_RETENTION is too short, flashback queries against recent but rolled-off transactions will fail with ORA-01555. Tune UNDO_RETENTION and monitor undo space before enabling self-service flashback for end users.

What are the most important RMAN and backup improvements in Oracle9i 9.2?

RMAN in 9.2 gains SPFILE backup and restore, making it the first release where a single RMAN strategy can cover the full database recovery footprint -- datafiles, control files, and now the server parameter file.

Key operational improvements:

• SPFILE backup and restore. Run RESTORE SPFILE to recover a lost parameter file without starting a full instance rebuild. RMAN can optionally auto-include the SPFILE in every control file backup set.
• Control file autobackup. Setting CONFIGURE CONTROLFILE AUTOBACKUP ON causes RMAN to trigger an automatic control file and SPFILE backup after any structural change (tablespace add/drop, datafile resize). This is off by default -- enable it on every production instance.
• FORCE DELETE. Removes backup pieces that are listed as AVAILABLE or EXPIRED in the repository but no longer exist on media, without requiring a prior CROSSCHECK. Useful after manual file cleanup by OS utilities.
• NOT BACKED UP integer TIMES clause. Targets only archive logs that have fewer than N backup copies, making it easy to enforce a minimum tape redundancy policy in a single BACKUP ARCHIVELOG command.
• DBNEWID utility. A new standalone tool that changes the DBID and/or DBNAME of a database without re-creating the control file. This unblocks the common DBA task of registering a cloned seed database alongside the source database in the same RMAN catalog.
• V$DATABASE_BLOCK_CORRUPTION. A new dynamic view populated after BACKUP VALIDATE that lists corrupt blocks by file and block number. Pair with BLOCKRECOVER CORRUPTION LIST to perform targeted block media recovery rather than a full tablespace restore.

Most teams upgrading from 9.0.1 should set CONFIGURE CONTROLFILE AUTOBACKUP ON immediately post-upgrade. The DBNEWID utility alone resolves a common pain point for teams running shared RMAN catalogs across cloned environments.

What is Oracle Streams and how does it differ from Advanced Replication?

Oracle Streams is a new general-purpose information sharing infrastructure introduced in 9.2 that unifies redo log mining, Advanced Queuing, and apply processing into a single configurable pipeline -- replacing the need for separate point solutions for replication, CDC, and event distribution.

Where Advanced Replication is symmetric and tightly coupled, Streams is asymmetric and loosely coupled. Changes are captured from the redo log into Logical Change Records (LCRs), staged in an AQ queue, propagated across database links, and applied at the destination by a configurable apply process. Each stage can filter, transform, or route events based on rules, and the destination does not have to be an Oracle database -- Streams supports heterogeneous targets via Oracle Gateways and MQ/TIBCO via Message Gateway.

The three Streams pipeline stages are:

• Capture. Log-based implicit capture mines redo for DML and DDL changes at the source with minimal overhead. Applications can also explicitly enqueue their own events into the stream.
• Staging and Propagation. LCRs are held in an AQ queue with security and auditing. Propagation rules control which queues receive which events.
• Apply. The default apply process dequeues and applies LCRs. A custom apply function routes events to a user-written PL/SQL handler for transformation before apply.

This matters if you are currently running multi-master Advanced Replication across more than two nodes, managing custom CDC jobs, or building event-driven ETL pipelines. Streams provides a single infrastructure for all three use cases. The migration effort from Advanced Replication is non-trivial -- plan a parallel run period before decommissioning existing replication groups.

What security hardening changes does Oracle9i 9.2 introduce that affect existing databases?

Oracle9i 9.2 ships with several security changes that affect default behavior and have direct implications for auditing, privilege management, and network encryption on databases migrated from 9.0.1 or 8i.

SYS auditing. All operations performed as SYS -- including AS SYSDBA and AS SYSOPER connections -- can now be audited. This is the first release where you can produce a complete, tamper-resistant audit trail of the most privileged account on the system. Enable this via the AUDIT_SYS_OPERATIONS initialization parameter. In practice, this is a compliance requirement for most financial and government environments.

Mandatory SYS and SYSTEM passwords at CREATE DATABASE. The CREATE DATABASE statement now accepts a SYS and SYSTEM password clause, eliminating the well-known default passwords that were left in place on newly created databases in earlier releases:

CREATE DATABASE mydb
  USER SYS IDENTIFIED BY <strong_password>
  USER SYSTEM IDENTIFIED BY <strong_password>
  ...;

AES encryption support. Oracle Advanced Security adds AES (128, 192, and 256-bit key lengths) for network encryption, meeting FIPS 140-1 requirements. Teams running DES or 3DES across government or regulated networks should plan to migrate cipher configuration in sqlnet.ora after the upgrade.

DBA GRANT/REVOKE on another user's objects. DBAs can now grant or revoke object privileges on objects owned by another schema without requiring the owner to delegate GRANT OPTION. This simplifies privilege administration in large consolidated databases but requires a review of any scripts that previously required the object owner to be present in the session.

Watch out for: the AES cipher is not backwards-compatible with pre-9.2 clients using Oracle Advanced Security -- mixed-version environments require careful sqlnet.ora configuration during a rolling upgrade window.

What changed in LogMiner 9.2 that could break existing log mining scripts?

LogMiner 9.2 introduces one default behavior change that silently breaks any existing setup that relied on the 9.0.1 default: supplemental logging is now OFF by default, reversed from 9.0.1 where minimal supplemental logging was on by default.

If your LogMiner consumers rely on before-image column values for UPDATE statements -- common in CDC and audit pipelines -- you must now explicitly enable supplemental logging at the database or table level:

-- Enable minimal supplemental logging at database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Enable all-column logging for a specific table
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA ALL COLUMNS;

-- Enable conditional logging (only when column changes)
ALTER TABLE hr.employees
  ADD SUPPLEMENTAL LOG GROUP log_grp1 (employee_id, salary) ALWAYS;

Additional useful additions in 9.2:

• DBMS_LOGMNR.CONTINUOUS_MINE. Automatically adds newly archived redo logs to an active LogMiner session without restarting the session. This is critical for long-running CDC daemons -- without it, you had to restart the session each time a new log appeared.
• DBMS_LOGMNR.PRINT_PRETTY_SQL. Formats reconstructed SQL for human readability. Useful for audit report generation.
• DBMS_LOGMNR_D.SET_TABLESPACE. Relocates LogMiner internal tables out of SYSTEM into a designated tablespace, addressing a common SYSTEM tablespace bloat issue in high-activity LogMiner deployments.
• LONG and LOB support. LONG and LOB column changes in redo generated on 9.2 and later are now mineable. Note the version requirement -- redo from a pre-9.2 database does not carry this information.

The NO_DICT_RESET_ONSELECT option is no longer needed; LogMiner now internally preserves DDL metadata versions across multiple SELECT operations from the same session.