2.8 Configuring Oracle Streams

The Oracle Database High Availability Overview describes many high availability benefits available when you configure Oracle Streams. Oracle Streams is a very flexible and powerful database feature that includes capabilities such as fine-grained replication, multimaster replication, many-to-one replication, data transformation, hub and spoke replication, and message queuing.

This section summarizes the best practices for configuring Oracle Streams for both downstream capture and upstream (local) capture.

  • Downstream capture is typically used to replicate a full database, to offload processing from the source database to the target database, or to reduce data loss with ASYNC or SYNC redo transport.

  • Upstream capture, which is also referred to as local capture, occurs when the Oracle Streams capture process captures changes on the source database. Then, a propagation process dequeues the LCRs from the local buffered queue and propagates them to a destination database where they are enqueued into another buffered queue where typically an apply process is configured. Local capture can be used when source and target databases are on different platforms, different character sets, and with in limits, different database versions.

See Also:

The MAA white paper "Oracle Streams Configuration Best Practices" at


2.8.1 Preparing Oracle Streams Configurations

The general configuration tasks in the following list are relevant for all Oracle Streams databases, whether the implementation is for downstream or local capture:

  • Use Oracle Database 11g or Oracle Database 10g release 2 ( or later) and apply all relevant patches.

  • Verify that the source and capture sites run on the same platform if you plan to use downstream capture.

  • Prepare the source and target database redo logs for Oracle Streams:

    • Configure the source and target databases in ARCHIVELOG mode.

    • Configure the local archive destination, LOG_ARCHIVE_DEST_1, parameter and do not use a flash recovery area.

  • Create a tablespace dedicated to Oracle Streams.

  • Create the Oracle Streams administrator database user.

  • Grant Oracle Streams authorization and DBA privileges.


  • Create database links between the source and target databases.

  • Set up directory objects.

  • Account for object or tablespace name differences when replicating DDLs.

Recommendations for Oracle Streams Downstream Capture

In addition to the previous general configuration tasks, the following list summarizes the tasks you must perform to prepare the source database to ship redo to a downstream database and prepare the target database to receive and apply the redo.

When configuring for Oracle Streams downstream capture:

  1. Specify initialization parameters (such as LOG_ARCHIVE_DEST_n) on the source and target databases.

  2. On the downstream database, configure standby redo logs.

  3. Enable the remote archived redo log destinations.

  4. Run the relevant DBMS_STREAMS_ADM subprogram (MAINTAIN_SCHEMAS, MAINTAIN_TABLES, MAINTAIN_TTS, and so on) to replicate that object on the downstream database.

  5. Run the DBMS_CAPTURE_ADM.SET_PARAMETER procedure to configure the Oracle Streams capture process to perform real-time mining of the redo log that is shipped from the source database.

  6. Query the V$STANDBY_LOG view on the downstream capture database to verify the downstream database is active.

Note: An Oracle Streams capture process is not run on the source database.

Recommendations for Oracle Streams Local Capture

In addition to the general configuration tasks, the following list summarizes the tasks you should perform to configure local capture.

When configuring Oracle Streams for local capture:

  1. Specify initialization parameters (such as LOG_ARCHIVE_DEST_n) on the source and target databases

  2. Run the relevant DBMS_STREAMS_ADM subprogram (MAINTAIN_SCHEMAS, MAINTAIN_TABLES, MAINTAIN_TTS, and so on) to replicate that object on the database where the capture process runs, which in this case is the source database.

  3. Monitor the progress by reviewing the ALERT.LOG file on both databases. The source database's ALERT.LOG shows LogMiner mining the local archive and the online redo log files. After the procedure starts to execute, you can monitor the procedure's actions by querying the DBA_RECOVERABLE_SCRIPT view or by viewing the ALERT.LOG files of both databases.

2.8.2 Finalizing and Verifying the Oracle Streams Configuration

After setting up your downstream or local capture configuration, perform the following steps to finalize and verify the configuration:

  1. Set the CHECKPOINT_RETENTION_TIME capture parameter to specify the number of days of checkpoints the capture process retains. The default value for this parameter is 60 days but the recommended initial setting is 7 days.

  2. Use the DBMS_APPLY_ADM.SET_PARAMETER PL/SQL procedure to set the degree of parallelism for the apply process (start at a minimum of 4). Apply and propagation are created using the DBMS_STREAMS_ADM.MAINTAIN subprograms (described in Section 2.8.1, "Preparing Oracle Streams Configurations").

  3. Run the Oracle Streams Health Check script that is available in support note 273674.1 at http://support.oracle.com/.

  4. Ensure your Oracle Streams configuration is properly configured for use over a network:

    • Tune the network—Set TCP/IP parameters on all servers, set the Oracle Net RECV_BUF_SIZE and SEND_BUF_SIZE parameters equal to three times the Bandwidth Delay Product (BDP), Set the Oracle Net Session Data Unit (SDU) size to 32767, increase the default SEND and RECEIVE queue sizes associated with networking devices, and ensure that the Oracle Net TCP_NODELAY parameter is set to YES.

    • Optimize standby redo log I/O—ensure that Oracle can use ASYNC I/O, maximize the write I/O size through all layers of the I/O stack, place standby redo logs in an ASM disk group that has at least the same number of disks as the ASM disk group where the primary online redo logs reside, and do not multiplex the standby redo logs.

    • Use service names (as defined in the SERVICE_NAMES system parameter) in Oracle Net alias descriptors in the TNSNAMES.ORA file.