Primary Note for Streams Setup and Administration (Doc ID 789445.1)

Last updated on JUNE 15, 2022

Applies to:

Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2

Details

Oracle Streams enables the sharing of data and events in a data stream either within a database or from one database to another. This document provides a compilation of references regarding the configuration of a new Streams environment and administration of existing Streams environments.

Setup Samples

The setup samples are to be used by DBAs and application developers to configure streams replication. The user needs to make all the required changes to the samples according to their requirements. This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Additional Primary Notes

Primary notes provide recommendations for configuration a Streams environment for optimal performance as well as addressing more commonly-encountered errors and messages.

<Document 418755.1> Primary Note for Streams Recommended Configuration
<Document 335516.1> Primary Note for Streams Performance Recommendations
<Document 1264598.1> Primary Note for Streams Downstream Capture 10g and 11g [Video]
<Document 265201.1> Primary Note for Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786,Conflict Resolution
<Document 313279.1> Primary Note for Troubleshooting Streams capture 'WAITING For REDO' or INITIALIZING
<Document 233099.1> Primary Note for Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues

Oracle Product Documentation For Streams

Oracle reference manuals for Streams are available for each database release in the Oracle Documentation section of Oracle Technology Network (OTN):
- Oracle® Streams Concepts and Administration;
- Oracle® Streams Replication Administrator's Guide

Actions

Setting up a New Streams Configuration

Initial Setup

Initial considerations

<Document 238455.1> Streams DML Types Supported and Supported Datatypes
<Document 238455.1> What DML and DDL is Captured by Streams
<Document 762484.1> Streams Replication of Materialized Views

Database Preparation

Databases in a Streams environment should have applied the latest patchset, PSU (10g and above), and relevant Streams patches.

<Document 854428.1> Patch Set Updates for Oracle Products
<Document 437838.1> Streams Recommended Patches

Review the following notes for recommendations regarding configuring databases and creating the Streams Administrator user in preparation for setting up a Streams environment.

<Document 418755.1> Primary Note for Streams Recommended Configuration
<Document 1264598.1> Primary Note for Streams Downstream Capture 10g and 11g [Video]

Overview of Setup Procedures

Various procedures are available for Streams configuration, and capabilities of each are dependent on database release - please review the Oracle® Database PL/SQL Packages and Types Reference for specifics, available for each database release in the Oracle Documentation section of Oracle Technology Network (OTN):

- The DBMS_STREAMS_ADM.MAINTAIN_* procedures for table, tablespace, schema, or database replication can be used to configure the environment directly or to generate a script to be executed at a later time. Streams capture, propagation, and apply are created for a single-direction or bi-directional configuration to include Data Pump export/import instantiation. These procedures are most inclusive, executing various behind-the-scenes commands for a basic configuration including queue, Streams processes, rule sets and rules, enabling of supplemental logging, and instantiation.

- Streams processes can be configured individually using the DBMS_STREAMS_ADM.ADD_*_RULES for subset, table, schema, or database replication. These procedures will create the queue, designated Streams process, rule sets and rules, and with the capture process also enable default supplemental logging and prepare tables for instantiation (with caveats for downstream capture). Data Pump export/import instantiation is not included as part of procedure execution.

- Streams processes can also be configured individually using the Streams package for the specific process i.e. DBMS_CAPTURE_ADM.CREATE_CAPTURE. The Streams process and rule sets can be created with these procedures, but creation of queues and rules, supplemental logging, and instantiation are handled separately.

For initial configuration, the DBMS_STREAMS_ADM procedures are recommended for ease of use and convenience. The Streams process-specific packages and procedures are typically used for administrative purposes rather than for initial configuration of a Streams environment.

Setup Samples and References

The following document references provide commands for setting up a sample Streams environment.
These commands will require appropriate substitutions to tailor to a customer's environment.

Migration to Streams

<Document 249443.1> Migrate 9i Advanced Replication to 10g Streams

Uni-directional (Single Direction)

<Document 864973.1> How to setup Streams replication using DBMS_STREAMS_ADM.MAINTAIN_* set of procedures

<Document 878638.1> How to setup Streams Schema level replication using MAINTAIN_SCHEMAS procedure

<Document 789500.1> Streams Table Level Replication Setup Script
- Sample configuration using DBMS_STREAMS_ADM.ADD_TABLE_*_RULES.

<Document 301431.1> How To Setup One-Way Schema Level Streams Replication
- Sample configuration using DBMS_STREAMS_ADM.ADD_SCHEMA_*_RULES.

<Document 459922.1> How To Setup Database Level Streams Replication
- Sample configuration using DBMS_STREAMS_ADM.ADD_GLOBAL_*_RULES.

<Document 382745.1> Setup Tablespace Streams Replication
- Sample configuration using DBMS_STREAMS_ADM.MAINTAIN_SIMPLE_TTS or DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET.

<Document 1109293.1> Streams Synchronous Capture 11g New Feature Example
- Sample configuration using DBMS_STREAMS_ADM.ADD_TABLE_*_RULES.

<Document 780642.1> How To Setup Table Level Streams Replication with Propagation Subset Rules
- Sample configuration using DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES to replicate a subset of the rows in a table. Subsetting can be configured for capture and apply as well using ADD_SUBSET_RULES.

Bi-directional, Multi-directional, and Hub/Spoke

Downstream Capture

<Document 1264598.1> Primary Note for Streams Downstream Capture 10g and 11g
<Document 394575.1> FAQ on Downstreams Capture
<Document 753158.1> How To Configure Streams Real-Time Downstream Environment
<Document 733691.1> How To Setup Schema Level Streams Replication with an Archived-log Downstream Capture Process with Implicit Log Assignment

Source / Target Instantiation

<Document 550955.1> Instantiating Objects in a 10g Streams Using Original Export/Import and Data Pump Export/Import Example
<Document 551106.1> Instantiating Objects in Streams Using Transportable Tablespace or RMAN

Additional Setup Options

<Document 316893.1> DML and DDL Auditing Using Streams
<Document 461278.1> Example of a Streams Heartbeat Table
<Document 1079554.1> Procedural Replication with Streams
<Document 735277.1> Using DBMS_CRYPTO Package With Streams
<Document 1427914.1> How To Use MAINTAIN_CHANGE_TABLE To Record DML Changes In a change table

Online database Upgrade / Maintenance Using Streams

Streams can be used to perform an online database upgrade or maintenance such as migrating the database to a different platform or character set, upgrading user-created applications, and applying Oracle Database patches.
For more information, refer to the Appendixes provided in the Oracle® Streams Concepts and Administration Guide available for each database release in the Oracle Documentation section of Oracle Technology Network (OTN) .

Heterogenous Streams

<Document 818013.1> Are all the features of Streams supported when applying through a transparent gateway to DB2, SQL Server, or Sybase?
<Document 315836.1> Implementing Streams Heterogeneous (non-Oracle ) Change Capture
<Document 313766.1> Streams Heterogeneous Apply To SQL*Server

Advanced Setup

Transformations

Transformations enable the conversion of table names and schemas as well as addition or removal of table columns, changes to datatypes.

<Document 455797.1> Streams Transformation
- Overview and examples of declarative and custom rule-based transformations, and one-many transformation functions.

<Document 783203.1> How to Setup Custom Rule Based Transformation
- Sample configuration using DBMS_STREAMS_ADM.ADD_SCHEMA_*_RULES and apply process transformation of one schema to another in the same Oracle database for DML, DDL, and LOBs.

<Document 781625.1> How to Add/Remove a column from a Streams Replicated Table using Declarative Rule Transformations
- Sample single-direction configuration using DBMS_STREAMS_ADM.ADD_SCHEMA_*_RULES and declarative transformations with the apply process.

<Document 309575.1> How To Transform a Schema Name In Streams for DML and Simple DDL Operations
- Sample single-direction configuration using DBMS_STREAMS_ADM.ADD_SCHEMA_*_RULES and transformation of one schema to another in the same Oracle database using custom transformation functions with the apply process.

<Document 784126.1> How to Transform a Schema Name in Streams using Declarative Rule Transformations
- Sample single-direction configuration using DBMS_STREAMS_ADM.ADD_SCHEMA_*_RULES and schema transformation by the propagation process.

Operation Exclusions

<Document 814602.1> How to Exclude Operations in a Streams Environment
- Examples of negative rules and positive-rule modifications to exclude objects such as tables or synonyms, schemas, and specific types of DDL from replication.

<Document 239571.1> How To Exclude Specific DML Operations From The Capture Process When Using Streams
- Examples of negative rules, positive-rule modifications, and DML handlers to exclude objects, DDL, and DML such as INSERTs.

Apply Handlers and Conflict Resolution

<Document 234094.1> Usage and Restrictions of Streams Apply Handlers
- Overview of the various types of apply handlers i.e. DML/DDL, message, error, conflict handlers.

<Document 779801.1> Streams Conflict Resolution

<Document 784899.1> Setup Streams Replication Between Different Source and Target Schemas with Different Table Structures
- Sample configuration using DBMS_STREAMS_ADM.ADD_TABLE_*_RULES and an apply DML handler to change table columns.

<Document 252042.1> Configuring a Precommit Handler for the Streams Apply

<Document 401275.1> Handling Apply Insert And Delete Conflicts In A Streams Environment - Error Handlers
- Sample code to ignore errors ORA-1 (Unique key violation) on INSERT and ORA-1403 (No data found) on DELETE.

<Document 561431.1> How to Achieve CDC Using Oracle Streams Apply Handler
- Sample Streams setup for change data capture.

<Document 865634.1> Example Streams Apply DML Handler to change the datatype of a column in the Apply LCR

<Document 889934.1> Statement DML Handlers
- Overview and examples of the new 11g Release 2 'Statement DML Handler' feature to use SQL statements to process row LCRs.

<Document 387829.1> Auto Correction Example for Streams using Error Handlers

Handling Unsupported Datatypes

<Document 556742.1> Extended Datatype Support (EDS) for Streams
<Document 873803.1> How To Setup Extended Data Support (EDS) to replicate changes made to a table with unsupported data types within the same database

Administration of an existing Streams environment

Monitoring

<Document 735976.1> All Replication Configuration Views For Streams, AQ, CDC and Advanced Replication
<Document 471713.1> Different States of Capture & Apply Process
<Document 761815.1> Determining Latency for Streams Processes at Different States
<Document 784021.1> Managing Streams from Oracle Enterprise Manager 10g Release 5 Grid Control
<Document 290605.1> Oracle Streams STRMMON Monitoring Utility. Monitoring tool available in 9i and 10g.
<Document 732644.1> Oracle Streams Performance Advisor. Monitoring tool available in 11g onwards.

<Document 273674.1> Streams Configuration Report and Health Check Script
- Includes video on how to run the script and interpret the results.

<Document 779395.1> Displaying Statistics for Streams Components using UTL_SPADV Package
- Collect and analyze statistics for the Streams components using the Oracle Streams Performance Advisor.

<Document 1163083.1> How to Install, Setup and Use the AQ Performance Monitor Package DBMS_AQ_MONITOR

General Streams Administration

<Document 1083608.1> 11g Streams and Oracle Scheduler
<Document 463820.1> Streams Combined Capture and Apply in 11g
<Document 1273359.1> How To Alter The Table In Existing Streams Environment
<Document 313478.1> Performing Manual DDL in a Streams Environment
<Document 788906.1> How to Avoid Capturing changes made by EXP/IMP Session
<Document 550593.1> Minimize Performance Impact of Batch Processing in Streams
<Document 234096.1> Managing Streams Bulk Load Operations and Redo Nologging/Unrecoverable Operations
<Document 405541.1> Creating a Procedure That Prints The Value in a Specified ANYDATA
<Document 429599.1> How to reduce the Highwater of LOGMNR_RESTART_CKPT$ in 10.2
<Document 1282385.1> Stream has switched from CCA mode to non-CCA after restart of Streams processes
<Document 290143.1> Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process
<Document 275323.1> Minimum Archive Log Necessary to Restart 9iR2 Streams Capture Process

Regarding Streams and handling of database backups:

<Document 336265.1> Best Practices For Managing Backups In A Streams Environment
<Document 421176.1> Usage of RMAN in Streams Environment

Adding to a Streams Configuration

<Document 472423.1> Steps To Add An Active Site To An Existing Uni-directional Streams Environment
<Document 779407.1> Steps To Add An Active Site To An Existing Bi-directional Streams Environment
<Document 753233.1> Steps To Add An Active Table To Existing Streams Environment

Streams Tuning

<Document 335516.1> Primary Note for Streams Performance Recommendations
<Document 780733.1> Streams Propagation Tuning with Network Parameters
<Document 1377929.1> Network Tuning Best Practices for Oracle Streams Propagation
<Document 1077626.1> Streams 11g Performance Technical Brief
Oracle Streams Performance Tuning Best Practices: OracleDatabase 10g Release 10.2
<Document 749079.1> Scripts for Oracle Streams Performance Tuning Best Practice: Oracle 10g Release 10.2
<Document 365648.1> Explain TXN_LCR_SPILL_THRESHOLD in Oracle10GR2 Streams
<Document 1338120.1> How to effectively size Streams Apply parameter TXN_LCR_SPILL_THRESHOLD

Removing Streams configurations or Migrating to GoldenGate

<Document 260028.1> Remove Streams in 10g and 11g
<Document 224159.1> Known Problems With Dropping Streams Components
<Document 1383303.1> Oracle GoldenGate Best Practice: Oracle Streams to Oracle GoldenGate Conversion for Reporting Databases

Streams Troubleshooting and Recovery

Documents provided below include Primary notes for troubleshooting the more-commonly encountered issues with Streams as well as techniques to investigate errors, skip problematic transactions, and address issues with spill. This is not a comprehensive list and does not include notes addressing failures due to code defects or unique situations.

If the documents below do not assist with troubleshooting a Streams error and/or process failure, please search the knowledge repository in My Oracle Support for documents relevant to your issue.

Refer to the monitoring section above for tools useful in troubleshooting Streams errors and failures.

Troubleshooting

<Document 273674.1> Streams Configuration Report and Health Check Script. Includes video on how to run the script and interpret the results.
<Document 265201.1> Primary Note for Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786, Conflict Resolution
<Document 233099.1> Primary Note for Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues
<Document 313279.1> Primary Note for Troubleshooting Streams capture 'WAITING For REDO' or INITIALIZING
<Document 1264598.1> Primary Note for Streams Downstream Capture 10g and 11g
<Document 746247.1> Troubleshooting Streams Capture when Status is Paused for Flow Control
<Document 883372.1> Streams Troubleshooting Guide
<Document 730036.1> Troubleshooting Streams Performance Issues
<Document 729860.1> Troubleshooting Queries in Streams
<Document 783927.1> Troubleshooting Long-Running Transactions in Oracle Streams

<Document 736806.1> Collecting Information to Identify Cause of ORA-26773: Invalid Data Type For Column "malformed redo"

<Document 563774.1> Streams Message Tracking in 11g. New 11g feature to track messages across databases or within the same database.
<Document 855964.1> How to do SQL Trace for the Streams Processes

<Document 311620.1> How to Use LogMiner to Determine Problem SQL in a Streams
<Document 422252.1> How to skip a transaction at APPLY site

<Document 556183.1> SYSAUX tablespace grows quite fast due to Apply spilling
<Document 472440.1> How to Purge Apply Spill in 10.2

Streams Recovery

<Document 471695.1> Required Steps to Recreate a Capture Process
<Document 335502.1> How To Reinstantiate a Single Table in a Streams Environment
<Document 749036.1> How to Re-Synchronize Streams Replicated Objects Online
<Document 761816.1> How To Replace Streams Setup If Downstream Database Crashes
<Document 732642.1> Split and Merge of a Streams Destination
- A new feature in 11G to split a stream to a new target in cases where the original target is unavailable, allowing return of the original target at a later time.

Regarding
* point-in-time recovery of Streams databases
* renaming of Streams database global name and/or DBID
Refer to the Oracle® Streams Concepts and Administration Guide available for each database release in the Oracle Documentation section of Oracle Technology Network (OTN) .

<Document 463295.1> Compare and Converge in an Oracle Streams Replication Environment
- Use of DBMS_COMPARISON, a new package in 11g.
<Document 858554.1> SQL to Compare Streams Replicated Table Objects between Source and Target Sites
- Scripts to compare differences in source and target tables via query over dblink.

<Document 550955.1> Instantiating Objects in a 10g Streams Using Original Export/Import and Data Pump Export/Import Example
<Document 551106.1> Instantiating Objects in Streams Using Transportable Tablespace or RMAN