Home   Oracle 101   Backups   Configuration   Schema   Storage   Users & Privs  |   Data Pump   Flashback   Streams   Enterprise Practices   Misc.  |   Scripts   Screen Shots    
Streams

Contents

Overview   4/26/2009
Implementing Streams   4/26/2009
Streams Maintenance   4/26/2009

Overview

The purpose of Oracle Streams is to enable information sharing. Streams uses the Oracle Redo logs to identify data that has changed, and sends it to a destination database and vice-versa. At the highest level: Capture -> Propagate -> Apply.

Streams is not a replacement for any Oracle technology. Some types of Data Guard use a form of it. Some DBAs feel that it is an easier way to implement Advanced Replication, but like Advanced Replication, it requires that you design it into your entire system from Day 1.

It takes considerable effort to re-tool an existing database to use Streams. The most important, [but not the only] challenges that need to be addressed are:

  • The rewrite of all application interfaces that use Sequences. Commonly, even numbers at primary while odd numbers at the remote site. Many developers opt to rewrite applications to not use Sequences at all in Streams environments.
  • Ensuring that all tables to have a Primary Key.

STREAMS PROS and CONS

PROS

  • Data can be changed at either the source or destination and it will automatically be replicated as required.
  • For environments that require Advanced Replication, Streams may be an easier technology to support.

CONS

  • Requires greater FTE investment, i.e. a DBAs time, to support production databases (conflict detection and resolution, restarting problem services etc.).
  • For production environments, Oracle Streams training may be essential.
  • Streams places a greater load on a database, i.e. there is a performance hit.
  • Because of the requirement for supplemental logging, Streams enabled databases require more disk space (for redo and archived log files etc.).
  • There are security concerns (streams_admin user requires the DBA role, encrypted data cannot be Streamed etc.).
  • There is no easy way to use Sequences without rewriting applications.
  • Does not support Materialized Views, BFiles, Nested Tables or user data "types" (VArrays etc.).
  • Some common SQL commands, which are required to build data from one source to another, are not supported.
    Example: CREATE TABLE AS SELECT [on clustered tables etc.].
  • For many RMAN restore scenarios (Point-In-Time-Recovery etc.) the destination database must be rebuilt.

Streams for Disaster Recovery?

Assuming you have a network SLA that guarantees appropriate bandwidth and stability, Streams can be used to ensure data at the source and destination are in sync almost up to the minute. This can make switching to a remote database transparent.

Alternatives to Using Streams

Oracle's Data Pump can be used to synchronize data in most cases. Sales propaganda aside, you should exhaust this option before attempting other more complex alternatives such as Streams. It can be implemented without a major impact on your FTE base, and without requiring costly specialized training since Data Pump is taught in all beginning Oracle vendor training classes.

Oracle's Data Guard is another option to synchronize data but requires a greater Oracle skill set to install and maintain. Oracle's Advanced Replication can be used to share data between locations also. It is generally accepted that if Advanced Replication cannot be architected into a production database from the beginning the task may be too overwhelming. Streams is easier to install and maintain than Advanced Replication and can provide most of the same features.


Copyright (c) 1998-2010 Michael Elliott. All rights reserved.
Disclaimer