Oracle Streams in 5 minutes

Preamble

Yes 5 minutes not more, well for the basic configuration. For advanced techniques (downstream, big objects instantiation,…) it would requires more time…

Streams basic configuration

Parameter	Source (db1 running on server1)	Destination (db2 running on server2)
GLOBAL_NAMES	TRUE	TRUE
COMPATIBLE	10.1.0 (to benefit from latest functionalities)	10.1.0 (to benefit from latest functionalities)
JOB_QUEUE_PROCESSES	2 (or higher)	N/A
STREAMS_POOL_SIZE	200MB (or SGA_TARGET)	200MB (or SGA_TARGET)

Source database

SQL> CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE sysaux TEMPORARY TABLESPACE temp;
 
USER created.
 
SQL> GRANT CONNECT, RESOURCE, DBA,SELECT_CATALOG_ROLE TO strmadmin;
 
GRANT succeeded.
 
SQL> EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=> 'strmadmin', grant_privileges => TRUE);
 
PL/SQL PROCEDURE successfully completed.

Add the target database to your $TNS_ADMIN/tnsnames.ora file (for the .world please refer to sqlnet.ora):

db2.world=(DESCRIPTION =
       (ADDRESS =
        (PROTOCOL=TCP)
        (HOST=server2.domain.com)
        (PORT=1541)
       )
      (connect_data=(SID=db2))
     )

Test it with:

server1# tnsping db2.world
 
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 14-APR-2010 14:45:27
 
Copyright (c) 1997, 2006, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP) (HOST=server2.domain.com) (PORT=1541)) (connect_data=(SID=db2)))
OK (10 msec)

Create a database link to target database:

SQL> CONNECT strmadmin/strmadmin
Connected.
SQL> CREATE DATABASE LINK db2
CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING 'db2.world';
 
DATABASE LINK created.

When you have done the target configuration you should be able to issue:

SQL> SELECT * FROM v$instance@db2;
 
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 db2
server2
10.2.0.4.0        10-APR-10 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

Create the Streams queues with strmadmin account:

SQL> CONNECT strmadmin/strmadmin
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name => 'STREAMS_QUEUE', queue_table => 'STREAMS_QUEUE_TABLE', queue_user => 'STRMADMIN');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 130
SQL> SELECT name, queue_table, queue_type FROM dba_queues WHERE owner='STRMADMIN';
 
NAME                           QUEUE_TABLE                    QUEUE_TYPE
------------------------------ ------------------------------ --------------------
STREAMS_QUEUE                  STREAMS_QUEUE_TABLE            NORMAL_QUEUE
AQ$_STREAMS_QUEUE_TABLE_E      STREAMS_QUEUE_TABLE            EXCEPTION_QUEUE

Target database

SQL> CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE sysaux TEMPORARY TABLESPACE temp;
 
USER created.
 
SQL> GRANT CONNECT, RESOURCE, dba, select_catalog_role TO strmadmin;
 
GRANT succeeded.
 
SQL> EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=> 'strmadmin', grant_privileges => TRUE);
 
PL/SQL PROCEDURE successfully completed.

Create the Streams queues with strmadmin account:

SQL> CONNECT strmadmin/strmadmin
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name => 'STREAMS_QUEUE', queue_table => 'STREAMS_QUEUE_TABLE', queue_user => 'STRMADMIN');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 130
SQL> SELECT name, queue_table, queue_type FROM dba_queues WHERE owner='STRMADMIN';
 
NAME                           QUEUE_TABLE                    QUEUE_TYPE
------------------------------ ------------------------------ --------------------
STREAMS_QUEUE                  STREAMS_QUEUE_TABLE            NORMAL_QUEUE
AQ$_STREAMS_QUEUE_TABLE_E      STREAMS_QUEUE_TABLE            EXCEPTION_QUEUE

That’s it, configuration is over !! Well move on to basic configuration to set up at least your first table.

Streams basic testing

The minimum you have to set up, that can be used by your future monitoring, is a STREAMS_HEARTBEAT table to measure the latency of the Streams propagation.

On source and target database:

SQL> CONNECT strmadmin/strmadmin
Connected.
SQL> DROP TABLE streams_heartbeat;
 
TABLE dropped.
 
SQL> CREATE TABLE streams_heartbeat (val DATE);
 
TABLE created.

On target database, add supplemental logging to your table:

SQL> ALTER TABLE streams_heartbeat ADD supplemental LOG data (ALL) columns;
 
TABLE altered.
SQL> SELECT * FROM dba_log_groups ORDER BY owner, table_name,log_group_type;
 
OWNER                          LOG_GROUP_NAME TABLE_NAME                     LOG_GROUP_TYPE      ALWAYS      GENERATED
------------------------------ -------------- ------------------------------ ------------------- ----------- --------------
STRMADMIN                      SYS_C00193091  STREAMS_HEARTBEAT              FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME
STRMADMIN                      SYS_C00193090  STREAMS_HEARTBEAT              UNIQUE KEY LOGGING  CONDITIONAL GENERATED NAME
STRMADMIN                      SYS_C00193089  STREAMS_HEARTBEAT              PRIMARY KEY LOGGING ALWAYS      GENERATED NAME
STRMADMIN                      SYS_C00193075  STREAMS_HEARTBEAT              ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
 
4 ROWS selected.

Configure the capture process:

SQL> EXEC DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => 'strmadmin.streams_heartbeat', streams_type => 'capture', streams_name => 'capture_stream', queue_name => 'strmadmin.streams_queue', include_dml => TRUE, include_ddl => TRUE, inclusion_rule => TRUE);
 
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM dba_capture_prepared_tables;
 
TABLE_OWNER                    TABLE_NAME                            SCN TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
------------------------------ ------------------------------ ---------- --------- -------- -------- -------- --------
STRMADMIN                      STREAMS_HEARTBEAT              8.9211E+12 15-APR-10 IMPLICIT IMPLICIT IMPLICIT EXPLICIT

Configure the propagation process:

SQL> EXEC DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(table_name => 'strmadmin.streams_heartbeat', streams_name => 'DB1_to_DB2', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@db2', include_dml => TRUE, include_ddl => TRUE, source_database => 'DB1', inclusion_rule => TRUE);
 
PL/SQL PROCEDURE successfully completed.

Then create the table on the target database. For our heartbeat the table will be created empty but you could imagine using DataPump, RMAN, exp/imp, transport tablespace, database links, and so on to set up the table on target database more rapidly.

Then start the instantiation of the object on the source database. In clear all modification before this SCN will not be captured.

SQL> SET serveroutput ON
SQL>
DECLARE
  source_scn  NUMBER;
BEGIN
  source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  dbms_output.put_line('Source SCN:' || source_scn);
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@db2(source_object_name => 'strmadmin.streams_heartbeat', source_database_name => 'DB1', instantiation_scn => source_scn);
END;
/
 
Source SCN:8921106978593
 
PL/SQL PROCEDURE successfully completed.

Configure the apply process on the target database:

EXEC DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => 'strmadmin.streams_heartbeat', streams_type => 'apply', streams_name => 'apply_stream', queue_name => 'strmadmin.streams_queue', include_dml => TRUE, include_ddl => TRUE, source_database => 'DB1', inclusion_rule  => TRUE);
 
PL/SQL PROCEDURE successfully completed.

Start the capture process on source database:

SQL> EXEC DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream');
 
PL/SQL PROCEDURE successfully completed.

Start the apply process on target database, one good habit is to change the default value of disable_on_error that would stop the apply process in case of error.

SQL> EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'apply_stream', parameter => 'disable_on_error', VALUE => 'n');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_stream');
 
PL/SQL PROCEDURE successfully completed.

Time to test it, on source database:

SQL> INSERT INTO strmadmin.streams_heartbeat VALUES(SYSDATE);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT TO_CHAR(val,'dd-mon-yyyy hh24:mi:ss') FROM strmadmin.streams_heartbeat;
 
TO_CHAR(VAL,'DD-MON-YYYYHH24:
-----------------------------
15-apr-2010 10:45:53

Then if you go on target database, you should see:

SQL> SELECT TO_CHAR(val,'dd-mon-yyyy hh24:mi:ss') FROM strmadmin.streams_heartbeat;
 
TO_CHAR(VAL,'DD-MON-
--------------------
15-apr-2010 10:45:53

the latency can also be seen on source database with:

SQL>  SELECT a.val-b.val AS latency FROM strmadmin.streams_heartbeat a, strmadmin.streams_heartbeat@db2 b;
 
   LATENCY
----------
         0

Streams apply error correction

The table to check is obviously DBA_APPLY_ERROR.

One tips to know when you want to perform hidden modification, ensure the tag is equal to NULL for all databases (source and target(s)):

SQL> SELECT dbms_streams.get_tag FROM dual;
 
GET_TAG
--------------------------------------------------------------------------------
 
 
SQL>

then use the following, for example, before performing your DDL:

SQL> EXEC dbms_streams.set_tag(tag => HEXTORAW('17'));
 
PL/SQL PROCEDURE successfully completed.
 
SQL> TRUNCATE TABLE strmadmin.streams_heartbeat;
 
TABLE truncated.
 
SQL>  EXEC dbms_streams.set_tag(tag => NULL);
 
PL/SQL PROCEDURE successfully completed.

There are 4 must have procedures provided by Oracle that yo can find in Oracle® Streams Concepts and Administration (chapter 22: Monitoring Streams Apply Processes). Those procedures are print_any, print_lcr, print_errors and print_transaction. Copy/paste them and create them in the target database in STRMADMIN account.

Let’s simulate an apply error by updating the heartbeat table on target database:

SQL> SELECT TO_CHAR(val,'dd-mon-yyyy hh24:mi:ss') AS val FROM strmadmin.streams_heartbeat;
 
VAL
--------------------
16-apr-2010 11:18:55
 
SQL> UPDATE strmadmin.streams_heartbeat SET val=SYSDATE;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT TO_CHAR(val,'dd-mon-yyyy hh24:mi:ss') FROM strmadmin.streams_heartbeat;
 
TO_CHAR(VAL,'DD-MON-
--------------------
16-apr-2010 15:37:19

Then update it on source database:

SQL> UPDATE strmadmin.streams_heartbeat SET val=SYSDATE;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT TO_CHAR(val,'dd-mon-yyyy hh24:mi:ss') FROM strmadmin.streams_heartbeat;
 
TO_CHAR(VAL,'DD-MON-YYYYHH24:
-----------------------------
16-apr-2010 15:37:34

Then if you go on target database you will see that the target table has obviously not been updated and if you issue:

SQL> SET serveroutput ON SIZE 999999
SQL> EXEC print_errors
*************************************************
----- ERROR #1
----- Local Transaction ID: 8.27.133322
----- Source Database: DB1.WORLD
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
ORA-01403: no data found
 
--message: 1
TYPE name: SYS.LCR$_ROW_RECORD
source DATABASE: DB1.WORLD
owner: STRMADMIN
object: STREAMS_HEARTBEAT
IS tag NULL: Y
command_type: UPDATE
old(1): VAL
16-APR-10
NEW(1): VAL
16-APR-10
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC print_transaction('8.27.133322');
----- Local Transaction ID: 8.27.133322
----- Source Database: DB1.WORLD
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
ORA-01403: no data found
 
--message: 1
TYPE name: SYS.LCR$_ROW_RECORD
source DATABASE: DB1.WORLD
owner: STRMADMIN
object: STREAMS_HEARTBEAT
IS tag NULL: Y
command_type: UPDATE
old(1): VAL
16-APR-10
NEW(1): VAL
16-APR-10
 
PL/SQL PROCEDURE successfully completed.

Remarks:
1. Procedures return the same result has our transaction was made of only one statement.
2. The new and old values look the same because we don’t have the full details use:

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';

3. Oracle Grid Control may also help a lot as with few mouse clicks you should be able to access to the needed information.

There are multiple method to correct this error, let’s start by just ignoring it to show how it can generate a lovely snow ball effect:

SQL> EXEC dbms_apply_adm.delete_error('8.27.133322');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC print_errors;
 
PL/SQL PROCEDURE successfully completed.

Then re-update the source table and set val column to sysdate again. Then check apply error on target site, obviously the old value is still not good and Streams cannot apply the change:

SQL> EXEC print_errors;
*************************************************
----- ERROR #1
----- Local Transaction ID: 6.21.122983
----- Source Database: DB1.WORLD
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
ORA-01403: no data found
 
--message: 1
TYPE name: SYS.LCR$_ROW_RECORD
source DATABASE: DB1.WORLD
owner: STRMADMIN
object: STREAMS_HEARTBEAT
IS tag NULL: Y
command_type: UPDATE
old(1): VAL
16-APR-10
NEW(1): VAL
16-APR-10
 
PL/SQL PROCEDURE successfully completed.

To definitively acknowledge update the target table with the old value and then apply again the transaction:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
SQL> EXEC print_errors;
*************************************************
----- ERROR #1
----- Local Transaction ID: 6.21.122983
----- Source Database: DB1.WORLD
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
ORA-01403: no data found
 
--message: 1
TYPE name: SYS.LCR$_ROW_RECORD
source DATABASE: DB1.WORLD
owner: STRMADMIN
object: STREAMS_HEARTBEAT
IS tag NULL: Y
command_type: UPDATE
old(1): VAL
16-apr-2010 15:37:34
NEW(1): VAL
16-apr-2010 16:42:53
 
PL/SQL PROCEDURE successfully completed.
 
SQL> UPDATE strmadmin.streams_heartbeat SET val=TO_DATE('16-apr-2010 15:37:34', 'dd-mon-yyyy hh24:mi:ss');
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> EXEC dbms_apply_adm.execute_error('6.21.122983');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC print_errors;
 
PL/SQL PROCEDURE successfully completed.

Streams monitoring

One of the best tool to monitor Streams is Grid Control (You can also use the java release i.e. Enterprise Manager).

To remove Streams configuration

The procedure is suppose to be DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION, but then if you select in dba_queues and dba_rule_sets you will see that’s lots of information still exists. To remove the queue the procedure to use is DBMS_STREAMS_ADM.REMOVE_QUEUE, the rules should be all remove already (check with DBA_RULES) and, as there is no procedure in DBMS_STREAMS_ADM, use DBMS_RULE_ADM to remove the remaining rule sets.

So connect with STRMADMIN and execute on target and destination databases:

BEGIN
  DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;
 
  FOR queues IN (SELECT name FROM dba_queues WHERE owner = 'STRMADMIN')
  LOOP
    DBMS_STREAMS_ADM.REMOVE_QUEUE(queue_name => queues.name, CASCADE => TRUE);
  END LOOP;
 
  FOR rules IN (SELECT rule_set_name FROM dba_rule_sets WHERE rule_set_owner = 'STRMADMIN')
  LOOP
   DBMS_RULE_ADM.DROP_RULE_SET(rule_set_name => rules.rule_set_name, delete_rules => TRUE);
  END LOOP;
END ;
/

References

Streams Performance Recommendations [ID 335516.1]
10gR2 Streams Recommended Configuration [ID 418755.1]
Streams Complete Reference FAQ [ID 752871.1]
Streams Specific Patches [ID 437838.1]
Streams Conflict Resolution [ID 230049.1]
Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786 [ID 265201.1]
Oracle Streams STRMMON Monitoring Utility [ID 290605.1]
Minimum Archived Log Necessary to Restart 10g and 11g Streams Capture Process [ID 290143.1]
Streams Configuration Report and Health Check Script [ID 273674.1]
Capture Process : INITIALIZING/DICTIONARY INITIALIZATION ; WAITING FOR DICTIONARY REDO: FIRST SCN ; WAITING FOR REDO: LAST SCN MINED [ID 313279.1]
How to recover Streams capture when it is ‘WAITING For REDO’ [ID 843267.1]
Usage of RMAN in Streams Environment [ID 421176.1]

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

3 thoughts on “Oracle Streams in 5 minutes”

sateesh says:
December 10, 2014 at 17:16
Dear Yannick,
I have seen your profile and streams articles you have published.Very useful. can you please help me whether this is possible or not. I have setup two way replication streams from A B and A C. So When Insert record in A, it is perfectly getting replicated to B & C. but when I insert into B, it is getting replicated to A but in turn from A its NOT coming to C. IS there any setting we need to do or is it possible or not. Thanks for your help.
Because of that, next time when a update occurs on that record in A, it is not getting replicated to C because the initial record was never insert into C.
PLease help.
wishes
sateesh RD
- Yannick Jaquier says:
  December 11, 2014 at 12:40
  Dear Sateesh,
  Yes on the paper it should work, the peer to peer is described in official documentation. Even if old document you can access it at:
  http://www.oracle.com/technetwork/database/twp-streams-replication-11gr1-130056.pdf
  But overall the best advice I can provide you is to move to GoldenGate: much easier to implement and troubleshoot…
  Thanks, Yannick.

IT World