Oracle Streams

Oracle Streams allows data propagation between Oracle Databases (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment).

Oracle Streams can be used for:

• Replication
• Message Queuing
• Loading data into a Data Warehouse
• Event Notification
• Data Protection

History

Oracle Streams was first shipped with Oracle 9i release 9.2.

Oracle Streams is only available with the Oracle Database Enterprise Edition.

Oracle 11gR2 is the last release supporting Streams. Oracle now recommends to use Golden Gate.

Components of Oracle Streams

Oracle Streams consists of the following components:

Capture

Database changes (DDL and DML) are captured from the redo logs and packaged into Logical Change Records or LCRs. The LCRs are then moved into the Staging environment. Data and events may be changed or formatted by a predefined set of rules before they are packaged into an LCR.

Your database must be in ARCHIVE log mode.

alter database archivelog;

Additionally, you may also need to enable supplemental logging for the database to ensure all changes are captured in the log files:

alter database add supplemental log data (primary key, unique index) columns;

You also need to enable force logging to ensure NOLOGGING operations are considered.

alter database force logging;

Create a queue table - used to queue captured changes:

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table    => 'strm_scott_qtable',
    storage_clause => 'storage (initial 10M next 10M maxextents unlimited)',
    queue_name     => 'strm_scott_queue',
    queue_user     => 'scott');
END;
/

Staging

LCRs are stored in the Staging environment until a subscriber picks them up to be used or consumed. The subscriber may be another staging environment or a user application.

Consumption

During Consumption, LCRs are picked up and applied to a database. Consumption allows the LCR to be modified before it is applied to the database.

Replication at schema level

Oracle Streams can be used to share data and messages between two oracle databases at different levels. Oracle streams will have two or more databases independent of each other and similar at replication level only.

1. Table level (1 or more tables are replicated between two Oracle databases)
2. Schema level (1 or more schemas are replicated between two Oracle databases)
3. Tablespace level (1 or more tablespaces are replicated between two Oracle databases)
4. Database level (changes in one database are replicated in another database)

Oracle streams is flow of information from one database to another database by capturing information at one database, staged and applied to another database. Oracle uses advance queuing concept to capture, propagate and apply the the data between databases.

Oracle streams setup can be
1. Homogeneous ( between two oracle databases)
2. Heterogeneous (between oracle and non oracle databases)

In this example, we will replicate a schema called NASA between two databases and have taken

1. Source database (TESTDB) on server DBA1
2. Target database (DEMODB) on server DBA2

On source database and target database do the following steps (TESTDB):

SQL> create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
SQL> create user strmadmin identified by strmadmin default tablespace strts;
SQL> grant dba to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('STRMADMIN');

With user STRMADMIN:

SQL> alter database add supplemental log data;

On target database do the following steps (DEMODB):

SQL> create tablespace strts datafile '/disk1/oradata/srcdir/strts.dbf' size 100m;
SQL> create user strmadmin identified by strmadmin default tablespace strts;
SQL> grant dba to strmadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('STRMADMIN');

With user STRMADMIN:

SQL> alter database add supplemental log data;

listener.ora at source database (TESTDB):

TESTDB =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dba1)(PORT = 7546))
     )
   )
 )
SID_LIST_TESTDB =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = TESTDB)
     (ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
   )
 )

listener.ora at target side (DEMODB):

DEMODB =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 7589))
     )
   )
 )
SID_LIST_DEMODB=
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = DEMODB)
     (ORACLE_HOME = /oraeng/app/oracle/product/11.2.0)
   )
 )

tnsnames.ora at source (TESTDB):

to_DEMODB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST=dba2)(PORT=7589))
   )
   (CONNECT_DATA =
     (SID = DEMODB)
   )
 )

tnsnames.ora at target side (DEMODB):

to_testdb =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST=dba1)(PORT=7546))
   )
   (CONNECT_DATA =
     (SID = testdb)
   )
 )

Streams parameter at source (TESTDB):

###########source side streams parameters###################
db_unique_name=testdb
log_archive_dest_1='location=/disk1/oradata/srcdir/arch valid_for=(online_logfiles,primary_role)mandatory'
log_archive_config='send,dg_config=(testdb,demodbdb)'
log_archive_dest_2='service=to_tgtlist lgwr async noregister valid_for=(online_logfiles,primary_role) db_unique_name=demodb'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=oracle.com
log_archive_max_processes=10
global_names=true

Streams parameter at target (DEMODB):

############target streams parameters################
db_unique_name=demodb
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,primary_role)'
log_archive_config='receive,dg_config=(testdb,demodb)'
log_archive_dest_2='location=/disk1/oradata/tgtdir/arch valid_for=(standby_logfiles,primary_role)'
aq_tm_processes=1
shared_pool_size=400m
job_queue_interval=1
job_queue_processes=20
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
processes=150
statistics_level=typical
timed_statistics=true
streams_pool_size=500m
db_domain=oracle.com
log_archive_max_processes=10
global_names=true
db_recovery_file_dest=/disk1/oradata/tgtdir/flash
db_recovery_file_dest_size=500m 

On source side create password file and send to target side:

$ orapwd file=orapw$ORACLE_SID password=sys

On target server, rename the password file according to instance name of target:

$ mv orapwTESDB orapwDEMODB

Log on to STRMADMIN:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEMODB

SQL> alter database rename global_name to demodb.oracle.com;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEMODB.ORACLE.COM

On source side create database link which points to target database.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEMODB.ORACLE.COM

SQL> create database link DEMODB.ORACLE.COM connect to strmadmin identified by strmadmin using 'to_demodb';

SQL> select * from dual@DEMODB.ORACLE.COM;

D
-
X

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TESTDB

SQL> alter database rename global_name to testdb.oracle.com;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TESTDB.ORACLE.COM

On target database (DEMODB):

SQL> create database link TESTDB.ORACLE.COM connect to stradm identified by stradm using 'to_tesdb';

Database link created.

SQL> select * from dual@TESTDB.ORACLE.COM;

D
-
X

On source database (TESDB):

$ mkdir /disk1/oradata/srcdir/strdir

SQL> create directory strdir as '/disk1/oradata/srcdir/strdir';

Directory created.

On target database (DEMODB):

$ mkdir /disk1/oradata/tgtdir/strdir

SQL> create directory strdir as '/disk1/oradata/tgtdir/strdir';

Directory created.

SQL> alter database add standby logfile ('/disk1/oradata/tgtdir/str1.log','/disk1/oradata/tgtdir/str2.log','/disk1/oradata/tgtdir/str3.log') size 10m;

SQL> select group#,sequence#,status from v$standby_log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------
         3          0 UNASSIGNED

In parameter file of source:

log_archive_dest_state_2=enable

On source side run the following (TESTDB):

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  966729728 bytes
Fixed Size                  1340552 bytes
Variable Size             943721336 bytes
Database Buffers           16777216 bytes
Redo Buffers                4890624 bytes
Database mounted.
Database opened.

SQL> alter system switch logfile;

System altered.

On target side run the following (DEMODB):

begin
  dbms_streams_adm.maintain_schemas (
    schema_names=>'nasa',
    source_database=>'testdb.oracle.COM',
    source_directory_object=>'strdir',
    destination_database=>'demodb.oracle.COM',
    destination_directory_object=>'strdir',
    capture_queue_name=>'DS_STREAMS_QUEUE',
    apply_queue_name=>'DS_STREAMS_QUEUE',
    bi_directional=>true,
    include_ddl=>true);
end;
/

Some Streams Notes

DECLARE iscn NUMBER;
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@A(
    source_object_name => 'schema.table',
    source_database_name => 'B',
    instantiation_scn => iscn);
END;
/

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => \'schema.table\');
END;
/

External links

• Oracle Streams feature overview
• Oracle Golden Gate