What's New in Oracle Streams?

This section describes new features of Oracle Streams for Oracle Database 10g Release 2 (10.2) and provides pointers to additional information. New features information from previous releases is also retained to help those users migrating to the current release.

The following sections describe the new features in Oracle Streams:

• Oracle Database 10g Release 2 (10.2) New Features in Streams

• Oracle Database 10g Release 1 (10.1) New Features in Streams

Oracle Database 10g Release 2 (10.2) New Features in Streams

The following sections describe the new features in Oracle Streams for Oracle Database 10g Release 2 (10.2):

• Streams Performance Improvements

• Streams Configuration and Manageability Enhancements

• Streams Replication Enhancements

• Rules Interface Enhancement

• Information Provisioning Enhancements

Streams Performance Improvements

Oracle Database 10g Release 2 includes performance improvements for most Streams operations. Specifically, the following Streams components have been improved to perform more efficiently and handle greater workloads:

• Capture processes

• Propagations

• Apply processes

This release also includes the following specific performance improvements:

• More types of rules are simple rules for faster rule evaluation. See "Simple Rule Conditions".

• Declarative rule-based transformations perform transformations more efficiently. See "Declarative Rule-Based Transformations".

• Real-time downstream capture reduces the amount of time required for a downstream capture process to capture changes made at the source database. See "Real-Time Downstream Capture".

• Enhanced prefiltering during capture process rule evaluation enables capture processes to capture changes in the redo log more efficiently. See "Capture Process Rule Evaluation".

• The new ANYDATA_FAST_EVAL_FUNCTION function in the STREAMS$_EVALUATION_CONTEXT provides more efficient access to values inside an ANYDATA object. See "Evaluation Contexts Used in Streams".

Streams Configuration and Manageability Enhancements

The following are Streams configuration manageability enhancements for Oracle Database 10g Release 2:

• Automatic Shared Memory Management of the Streams Pool

• Streams Tool in Oracle Enterprise Manager

• Procedures for Starting and Stopping Propagations

• Queue-to-Queue Propagations

• Declarative Rule-Based Transformations

• Commit-Time Queues

• Supplemental Logging Enabled During Preparation for Instantiation

• Configurable Transaction Spill Threshold for Apply Processes

• Conversion of LCRs to and from XML

• Retrying an Error Transaction with a User Procedure

• Enhanced Support for Index-Organized Tables

• Row LCR Execution Enhancements

• Information About Oldest Transaction in V$STREAMS_APPLY_READER

Automatic Shared Memory Management of the Streams Pool

The Oracle Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

Streams Tool in Oracle Enterprise Manager

The Streams tool in Oracle Enterprise Manager enables you to configure, manage, and monitor a Streams environment using a Web browser.

Procedures for Starting and Stopping Propagations

The START_PROPAGATION and STOP_PROPAGATION procedures are added to the DBMS_PROPAGATION_ADM package.

Queue-to-Queue Propagations

A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Also, in an Oracle Real Application Clusters (RAC) environment, when the destination queue in a queue-to-queue propagation is a buffered queue, the queue-to-queue propagation uses a service for transparent failover to another instance if the primary RAC instance fails.

Declarative Rule-Based Transformations

Declarative rule-based transformations provide a simple interface for configuring a set of common transformation scenarios for row LCRs. No user-defined PL/SQL function is required to configure a declarative rule-based transformation.

Commit-Time Queues

Commit-time queues provide more control over the order in which user-enqueued messages in a queue are browsed or dequeued.

Supplemental Logging Enabled During Preparation for Instantiation

The following procedures in the DBMS_CAPTURE_ADM package now include a supplemental_logging parameter which controls the supplemental logging specifications for the database objects being prepared for instantiation: PREPARE_TABLE_INSTANTIATION, PREPARE_SCHEMA_INSTANTIATION, and PREPARE_GLOBAL_INSTANTIATION.

Configurable Transaction Spill Threshold for Apply Processes

The new txn_lcr_spill_threshold apply process parameter enables you to specify that an apply process begins to spill messages for a transaction from memory to disk when the number of messages in memory for a particular transaction exceeds the specified number. The DBA_APPLY_SPILL_TXN and V$STREAMS_APPLY_READER views enable you to monitor the number of transactions and messages spilled by an apply process.

Conversion of LCRs to and from XML

The following functions in the DBMS_STREAMS package convert a logical change record (LCR) to or from XML:

• CONVERT_LCR_TO_XML converts an LCR encapsulated in a ANYDATA object into an XML object that conforms to the XML schema for LCRs.

• CONVERT_XML_TO_LCR converts an XML object that conforms to the XML schema for LCRs into an LCR encapsulated in a ANYDATA object.

Retrying an Error Transaction with a User Procedure

A new parameter, user_procedure, is added to the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package. This parameter enables you to specify a user procedure that modifies one or more LCRs in an error transaction before the transaction is executed.

Enhanced Support for Index-Organized Tables

Streams capture processes and apply processes now support index-organized tables that contain the following datatypes, in addition to the datatypes that were supported in past releases of Oracle:

• LONG

• LONG RAW

• CLOB

• NCLOB

• BLOB

• BFILE

Logical change records (LCRs) containing these datatypes in index-organized tables can also be propagated using propagations.

Also, Streams now supports index-organized tables that include an OVERFLOW segment.

Row LCR Execution Enhancements

In previous releases, the EXECUTE member procedure for row LCRs only execute row LCRs in an apply handler for an apply process. In Oracle Database 10g Release 2, the EXECUTE member procedure can execute user-constructed row LCRs, row LCRs in the error queue, and row LCRs that were last enqueued by an apply process, user, or application.

Information About Oldest Transaction in V$STREAMS_APPLY_READER

The following new columns are added to the V$STREAMS_APPLY_READER dynamic performance view: OLDEST_XIDUSN, OLDEST_XIDSLT, and OLDEST_XIDSQN. These columns show the transaction identification number of the oldest transaction being assembled or applied by an apply process. The DBA_APPLY_PROGRESS view also contains this information. However, for a running apply process, the information in the V$STREAMS_APPLY_READER view is more current than the information in the DBA_APPLY_PROGRESS view.

Streams Replication Enhancements

The following are Streams replication enhancements for Oracle Database 10g Release 2:

• Simple Streams Replication Configuration

• LOB Assembly

• Virtual Dependency Definitions

• Instantiation Using Transportable Tablespace from Backup

• RMAN Database Instantiation Across Platforms

• Apply Processes Allow Duplicate Rows

• View for Monitoring Long Running Transactions

Simple Streams Replication Configuration

The following new procedures in the DBMS_STREAMS_ADM package provide simplify configuration of a Streams replication environment:

• MAINTAIN_GLOBAL configures a Streams environment that replicates changes at the database level between two databases.

• MAINTAIN_SCHEMAS configures a Streams environment that replicates changes to specified schemas between two databases.

• MAINTAIN_SIMPLE_TTS configures a Streams environment that replicates changes to a single, self-contained tablespace between two databases. This procedure replaces the MAINTAIN_SIMPLE_TABLESPACE procedure.

• MAINTAIN_TABLES configures a Streams environment that replicates changes to specified tables between two databases.

• MAINTAIN_TTS configures a Streams environment that replicates changes to a self-contained set of tablespaces. This procedure replaces the MAINTAIN_TABLESPACES procedure.

• PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP configure a Streams environment that replicates changes at the database level or to specified tablespaces between two databases. These procedures must be used together, and instantiation actions must be performed manually, to complete the Streams replication configuration.

LOB Assembly

LOB assembly simplifies processing of row LCRs with LOB columns in DML handler and error handlers.

Virtual Dependency Definitions

A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions at a destination database. Virtual dependency definitions enable an apply process to detect dependencies that it would not be able to detect by using only the constraint information in the data dictionary.

Instantiation Using Transportable Tablespace from Backup

A new RMAN command, TRANSPORT TABLESPACE, enables you to instantiate a set of tablespaces while the tablespaces in the source database remain online. The tablespaces can be added to the destination database using Data Pump import or the ATTACH_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package.

RMAN Database Instantiation Across Platforms

The RMAN CONVERT DATABASE command can be used to instantiate an entire database in a replication environment where the source and destination databases are running on different platforms that have the same endian format.

Apply Processes Allow Duplicate Rows

In releases prior to Oracle Database 10g Release 2­, an apply process always raises an error when it encounters a row LCR that changes more than one row in a table. In Oracle Database 10g Release 2­, the new allow_duplicate_rows apply process parameter can be set to true to allow an apply process to apply a row LCR that changes more than one row.

View for Monitoring Long Running Transactions

The V$STREAMS_TRANSACTION dynamic performance view enables monitoring of long running transactions that currently are being processes by Streams capture processes and apply processes.

Rules Interface Enhancement

In Oracle Database 10g Release 2, a new procedure, ALTER_EVALUATION_CONTEXT in the DBMS_RULE_ADM package, enables you to alter an existing evaluation context.

Information Provisioning Enhancements

Information provisioning makes information available when and where it is needed. Oracle Database 10g Release 2­ makes it is easier to bulk provision a large amount of information and to incrementally provision information using Streams.

Oracle Database 10g Release 1 (10.1) New Features in Streams

The following sections describe the new features in Oracle Streams for Oracle Database 10g Release 1 (10.1):

• Streams Performance Improvements

• Streams Configuration and Manageability Enhancements

• Streams Replication Enhancements

• Streams Messaging Enhancements

• Rules Interface Enhancements

Streams Performance Improvements

Oracle Database 10g Release 1 includes performance improvements for most Streams operations. Specifically, the following Streams components have been improved to perform more efficiently and handle greater workloads:

• Capture processes

• Propagations

• Apply processes

This release also includes performance improvements for ANYDATA queue operations and rule set evaluations.

Streams Configuration and Manageability Enhancements

The following are Streams configuration manageability enhancements for Oracle Database 10g Release 1:

• Negative Rule Sets

• Downstream Capture

• Subset Rules for Capture and Propagation

• Streams Pool

• Access to Buffered Queue Information

• SYSAUX Tablespace Usage

• Ability to Add User-Defined Conditions to System-Created Rules

• Simpler Rule-Based Transformation Configuration and Administration

• Enqueue Destinations Upon Apply

• Execution Directives Upon Apply

• Support for Additional Datatypes

• Support for Index-Organized Tables

• Precommit Handlers

• Better Interoperation with Oracle Real Application Clusters

• Support for Function-Based Indexes and Descending Indexes

• Simpler Removal of Rule Sets When a Streams Client Is Dropped

• Simpler Removal of ANYDATA Queues

• Control Over Data Dictionary Builds in the Redo Log

• Additional Streams Data Dictionary Views and View Columns

• Copying and Moving Tablespaces

• Simpler Streams Administrator Configuration

• Streams Configuration Removal

Negative Rule Sets

Streams clients, which include capture processes, propagations, apply processes, and messaging clients, can use two rule sets: a positive rule set and a negative rule set. Negative rule sets make it easier to discard specific changes so that they are not processed by a Streams client.

Downstream Capture

A capture process can run on a database other than the source database. The redo log files from the source database are copied to the other database, called a downstream database, and the capture process captures changes in these redo log files at the downstream database.

Subset Rules for Capture and Propagation

You can use subset rules for capture processes, propagations, and messaging clients, as well as for apply processes.

Streams Pool

When Streams is used in a single database, memory is allocated from a pool in the System Global Area (SGA) called the Streams pool. The Streams pool contains buffered queues and is used for internal communications during parallel capture and apply. Also, a new dynamic performance view, V$STREAMS_POOL_ADVICE, provides information that you can use to determine the best size for Streams pool.

Access to Buffered Queue Information

The following new dynamic performance views enable you to monitor buffered queues:

• V$BUFFERED_QUEUES

• V$BUFFERED_SUBSCRIBERS

• V$BUFFERED_PUBLISHERS

SYSAUX Tablespace Usage

The default tablespace for LogMiner has been changed from the SYSTEM tablespace to the SYSAUX tablespace. When configuring a new database to run a capture process, you no longer need to relocate the LogMiner tables to a non-SYSTEM tablespace.

Ability to Add User-Defined Conditions to System-Created Rules

Some of the procedures that create rules in the DBMS_STREAMS_ADM package include an and_condition parameter. This parameter enables you to add custom conditions to system-created rules.

Simpler Rule-Based Transformation Configuration and Administration

A new procedure, SET_RULE_TRANSFORM_FUNCTION in the DBMS_STREAMS_ADM package, makes it easy to specify and administer rule-based transformations.

Enqueue Destinations Upon Apply

A new procedure, SET_ENQUEUE_DESTINATION in the DBMS_APPLY_ADM package, makes it easy to specify a destination queue for messages that satisfy a particular rule. When a message satisfies such a rule in an apply process rule set, the apply process enqueues the message into the specified queue.

Execution Directives Upon Apply

A new procedure, SET_EXECUTE in the DBMS_APPLY_ADM package, enables you to specify that apply processes do not execute messages that satisfy a specific rule.

Support for Additional Datatypes

Streams capture processes and apply processes now support the following additional datatypes:

• NCLOB

• BINARY_FLOAT

• BINARY_DOUBLE

• LONG

• LONG RAW

Logical change records (LCRs) containing these datatypes can also be propagated using propagations.

Support for Index-Organized Tables

Streams capture processes and apply processes now support processing changes to index-organized tables.

Precommit Handlers

You can use a new type of apply handler called a precommit handler to record information about commits processed by an apply process.

Better Interoperation with Oracle Real Application Clusters

The following are specific enhancements that improve Streams interoperation with Oracle Real Application Clusters (RAC):

• Streams capture processes running in a RAC environment can capture changes in the online redo log as well as the archived redo log.

• If the owner instance for a queue table containing a queue used by a capture process or apply process becomes unavailable, then queue ownership is transferred automatically to another instance in the cluster and the capture process or apply process is restarted automatically (if it had been running).

Support for Function-Based Indexes and Descending Indexes

Streams capture processes and apply processes now support processing changes to tables that use function-based indexes and descending indexes.

Simpler Removal of Rule Sets When a Streams Client Is Dropped

A new parameter, drop_unused_rule_sets, is added to the following procedures:

• DROP_CAPTURE in the DBMS_CAPTURE_ADM package

• DROP_PROPAGATION in the DBMS_PROPAGATION_ADM package

• DROP_APPLY in the DBMS_APPLY_ADM package

If you drop a Streams client using one of these procedures and set this parameter to true, then the procedure drops any rule sets, positive and negative, used by the specified Streams client if these rule sets are not used by any other Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set.

Simpler Removal of ANYDATA Queues

A new procedure, REMOVE_QUEUE in the DBMS_STREAMS_ADM package, enables you to remove an ANYDATA queue. This procedure also has a cascade parameter. When cascade is set to true, any Stream client that uses the queue is removed also.

Control Over Data Dictionary Builds in the Redo Log

You can use the BUILD procedure in the DBMS_CAPTURE_ADM package to extract the data dictionary of the current database to the redo log. A capture process can use the extracted information in the redo log to create the LogMiner data dictionary for the capture process. This procedure also identifies a valid first system change number (SCN) value that can be used by the capture process. The first SCN for a capture process is the lowest SCN in the redo log from which a capture process can capture changes. In addition, you can reset the first SCN for a capture process to purge unneeded information in a LogMiner data dictionary.

Additional Streams Data Dictionary Views and View Columns

This release includes new Streams data dictionary views and new columns in Streams data dictionary views that existed in past releases.

Copying and Moving Tablespaces

The DBMS_STREAMS_TABLESPACE_ADM package provides administrative procedures for copying tablespaces between databases and moving tablespaces from one database to another. This package uses transportable tablespaces, Data Pump, and the DBMS_FILE_TRANSFER package.

Simpler Streams Administrator Configuration

In this release, granting the DBA role to a Streams administrator is sufficient for most actions performed by the Streams administrator. In addition, a new package, DBMS_STREAMS_AUTH, provides procedures that make it easy for you to configure and manage a Streams administrator.

Streams Configuration Removal

A new procedure, REMOVE_STREAMS_CONFIGURATION in the DBMS_STREAMS_ADM package, enables you to remove the entire Streams configuration at a database.

Streams Replication Enhancements

The following are Streams replication enhancements for Oracle Database 10g Release 1:

• Additional Supplemental Logging Options

• Additional Ways to Perform Instantiations

• New Data Dictionary Views for Schema and Global Instantiations

• Recursively Setting Schema and Global Instantiation SCN

• Access to Streams Client Information During LCR Processing

• Maintaining Tablespaces

• Control Over Comparing Old Values in Conflict Detection

• Extra Attributes in LCRs

• New Member Procedures and Functions for LCR Types

• A Generated Script to Migrate from Advanced Replication to Streams

Additional Supplemental Logging Options

For database supplemental logging, you can specify that all FOREIGN KEY columns in a database are supplementally logged, or that ALL columns in a database are supplementally logged. These new options are added to the PRIMARY KEY and UNIQUE options, which were available in past releases.

For table supplemental logging, you can specify the following options for log groups:

• PRIMARY KEY

• FOREIGN KEY

• UNIQUE

• ALL

These new options make it easier to specify and manage supplemental logging at a source database because you can specify supplemental logging without listing each column in a log group. If a table changes in the future, then the correct columns are logged automatically. For example, if you specify FOREIGN KEY for a table's log group, then the foreign key for a row is logged when the row is changed, even if the columns in the foreign key change in the future.

Additional Ways to Perform Instantiations

In addition to original export/import, you can use Data Pump export/import, transportable tablespaces, and RMAN to perform Streams instantiations.

New Data Dictionary Views for Schema and Global Instantiations

The following new data dictionary views enable you to determine which database objects have a set instantiation SCN at the schema and global level:

• DBA_APPLY_INSTANTIATED_SCHEMAS

• DBA_APPLY_INSTANTIATED_GLOBAL

Recursively Setting Schema and Global Instantiation SCN

A new recursive parameter in the SET_SCHEMA_INSTANTIATION_SCN and SET_GLOBAL_INSTANTIATION_SCN procedures enables you to set the instantiation SCN for a schema or database, respectively, and for all of the database objects in the schema or database.

Access to Streams Client Information During LCR Processing

The DBMS_STREAMS package includes two new functions: GET_STREAMS_NAME and GET_STREAMS_TYPE. These functions return the name and type, respectively, of a Streams client that is processing an LCR. You can use these functions in rule conditions, rule-based transformations, apply handlers, error handlers, and in a rule condition.

For example, if you use one error handler for multiple apply processes, then you can use the GET_STREAMS_NAME function to determine the name of the apply process that raised the error. Also, you can use the GET_STREAMS_TYPE function to instruct a DML handler to operate differently if it is processing messages from the error queue (ERROR_EXECUTION type) instead of the apply process queue (APPLY type).

Maintaining Tablespaces

You can use the MAINTAIN_SIMPLE_TABLESPACE procedure to configure Streams replication for a simple tablespace, and you can use the MAINTAIN_TABLESPACES procedure to configure Streams replication for a set of self-contained tablespaces. Both of these procedures are in the DBMS_STREAMS_ADM package. These procedures use transportable tablespaces, Data Pump, the DBMS_STREAMS_TABLESPACE_ADM package, and the DBMS_FILE_TRANSFER package to configure the environment.

Control Over Comparing Old Values in Conflict Detection

The COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package enables you to specify whether to compare old values of one or more columns in a row LCR with the current value of the corresponding columns at the destination database during apply.

Extra Attributes in LCRs

You can optionally use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to include the following extra attributes in LCRs:

• row_id

• serial#

• session#

• thread#

• tx_name

• username

New Procedure for Point-In-Time Recovery in a Streams Environment

The GET_SCN_MAPPING procedure in the DBMS_STREAMS_ADM package gets information about the SCN values to use for Streams capture and apply processes to recover transactions after point-in-time recovery is performed on a source database in a multiple-source Streams environment.

New Member Procedures and Functions for LCR Types

You can use the following new member procedures and functions for LCR types:

• The GET_COMMIT_SCN member function returns the commit SCN of the transaction to which the current LCR belongs.

• The GET_EXTRA_ATTRIBUTE member function returns the value for the specified extra attribute in an LCR, and the SET_EXTRA_ATTRIBUTE member procedure enables you to set the value for the specified extra attribute in an LCR.

• The GET_COMPATIBLE member function returns the minimal database compatibility required to support an LCR.

• The CONVERT_LONG_TO_LOB_CHUNK member procedure converts LONG data in a row LCR into a CLOB, or converts LONG RAW data in a row LCR into a BLOB.

A Generated Script to Migrate from Advanced Replication to Streams

You can use the procedure DBMS_REPCAT.STREAMS_MIGRATION to generate a SQL*Plus script that migrates an existing Advanced Replication environment to a Streams environment.

Streams Messaging Enhancements

The following are Streams messaging enhancements for Oracle Database 10g Release 1:

• Streams Messaging Client

• Simpler Enqueue and Dequeue of Messages

• Simpler Configuration of Rule-Based Dequeue or Apply of Messages

• Simpler Configuration of Rule-Based Propagations of Messages

• Simpler Configuration of Message Notifications

  See Also:

  Oracle Streams Advanced Queuing User's Guide and Reference for more information about Streams messaging enhancements

Streams Messaging Client

A messaging client is a new type of Streams client that enables users and applications to dequeue messages from an ANYDATA queue based on rules. You can create a messaging client by specifying dequeue for the streams_type parameter in certain procedures in the DBMS_STREAMS_ADM package.

Simpler Enqueue and Dequeue of Messages

A new package, DBMS_STREAMS_MESSAGING, provides an easy interface for enqueuing messages into and dequeuing messages from an ANYDATA queue.

Simpler Configuration of Rule-Based Dequeue or Apply of Messages

A new procedure, ADD_MESSAGE_RULE in the DBMS_STREAMS_ADM package, enables you to configure messaging clients and apply processes, and it enables you to create the rules for user-enqueued messages that control the behavior of these messaging clients and apply processes.

Simpler Configuration of Rule-Based Propagations of Messages

A new procedure, ADD_MESSAGE_PROPAGATION_RULE in the DBMS_STREAMS_ADM package, enables you to configure propagations and create rules for propagations that propagate user-enqueued messages.

Simpler Configuration of Message Notifications

A new procedure, SET_MESSAGE_NOTIFICATION in the DBMS_STREAMS_ADM package, enables you to configure message notifications that are sent when a Streams messaging client dequeues messages. The notification can be sent to an email address, a URL, or a PL/SQL procedure.

Rules Interface Enhancements

The following are rules interface enhancements for Oracle Database 10g Release 1:

• Iterative Evaluation Results

• New Dynamic Performance Views for Rule Sets and Rule Evaluations

Iterative Evaluation Results

During rule set evaluation, a client now can specify that evaluation results are sent iteratively, instead of in a complete list at one time. The EVALUATE procedure in the DBMS_RULE package includes the following two new parameters that enable you specify that evaluation results are sent iteratively: true_rules_interator and maybe_rules_iterator.

In addition, a new procedure in the DBMS_RULE package, GET_NEXT_HIT, returns the next rule that evaluated to TRUE from a true rules iterator, or returns the next rule that evaluated to MAYBE from a maybe rules iterator. Also, the new CLOSE_ITERATOR procedure in the DBMS_RULE package enables you to close an open iterator.

New Dynamic Performance Views for Rule Sets and Rule Evaluations

You can use the following new dynamic performance views to monitor rule sets and rule evaluations:

• V$RULE_SET_AGGREGATE_STATS

• V$RULE_SET

• V$RULE