Forewords

1. Introduction and goals

Our goal in this tutorial is to guide the reader among all the variations on a theme that exists within Streams and present the essential minimum needed at each level of difficulty. We will focus the most used features in Streams and leave to Oracle Documentation the case of niches. Specifically we will develop the following feature in Streams

Installation and setup

Single Table replication

Single Table replication with row transformation

Master-master table replication with conflict handler

Schema level Replication

multi-master schema replication

Heterogeneous replication from Oracle to MQseries

We will setup replication on the famous demo user SCOTT/TIGER schema and replicate first the table 'EMP', then we will extend to MASTER-MASTER replication so that to discuss DML_HANDLER, more specifically focusing on conflict resolution and DML ERROR. handlers. After that we will replicate the whole schema up to mutli-master mode replication. Last we will show how to setup a monitoring queue on emp table in order to send selected info to an MQseries queue in order to illustrate a bit more of Advance Queuing capabilities.

1.1 naming convention

During this tutorial we will create various streams objects and following precise naming convention.

(in bold are the more used)

Objects on site one will comprise an '_1_' in their name ie: ) 'cap_scott_1_q' for the schema level capture process of user scott

Objects on site one will comprise an '_2_' in their name ie: ) 'cap_scott_2_q' for the schema level capture process of user scott

Source DB_SID=DEV1

Target DB_SID=DEV2

The schema to replicate will be called scott

We will replicate the table 'EMP'

<obj>_QT stands for a 'Queue table'

<obj>_Q stands for a 'QUEUE'

<obj>_RS stands for 'Rule set name'

<obj>_RN stands for 'Rule Name'

cap_<name> stands for 'Capture process'

app_<name> stands for 'Apply process'

prop_<name> stands for 'Propagation process'

You may encounter the expression 'Source DB' or 'Target DB'. They stands respectively for 'DEV1' and 'DEV2'. These 'source' and 'target' are only a quick and easy means to figure out which site is concerned

1.2 It is all about 10g

The current tutorial is aimed to demonstrate much of what can be done using Oracle Streams. We will focus mainly on oracle streams version 10gR2 as of this time of writing it is by far the most used versions. We will possibly extent this tutorial later to the new features of Oracle 11g.

2. Presentation of the available packages.

One of the difficulty when you start the study of the streams is the sheer number of procedures. Many of these packages seems to do the same thing. It is not really the case. They differs following the end-users. However, the most basic operations may be reached through different ways and still achieve the exact same result. This may give the impression of disorder and the beginners will realize too late that he has used the wrong set of package later.

2.1. Which set of package is the good one for you?

Since there are different ways to do the same thing, one may wonder which way is best one. For instance let's consider the setup procedure of the scott.emp table replication from DEV1 to DEV2. You can set up a capture process using the 2 following ways :

Test case : 2 ways to achieve the same result

1st method : Using DBMS_STREAMS_ADM

exec dbms_streams_adm.set_up_queue( queue_table           => 'cap_scott_emp_1_qt',
                                     queue_name            => 'cap_scott_emp_1_q',
                                     queue_user            => 'strmadmin');

exec dbms_streams_adm.add_table_rules(
                                     table_name            => 'scott.emp',
                                     streams_type          => 'capture',
                                     streams_name          => 'cap_scott_emp_1',
                                     queue_name            => 'strmadmin.cap_scott_emp_1_q',
                                     include_dml           => true,
                                     include_ddl           => false,
                                     include_tagged_lcr    => true,
                                     inclusion_rule        => true);

exec dbms_streams_adm.add_table_propagation_rules(
                                     table_name            => 'scott.emp',
                                     streams_name          => 'prop_scott_dev1_2_dev2',
                                     source_queue_name     => 'strmadmin.cap_scott_emp_1_q',
                                     destination_queue_name=> 'strmadmin.app_scott_emp_2_q@dev2',
                                     include_dml           => true,
                                     include_ddl           => false,
                                     include_tagged_lcr    => true,
                                     source_database       => 'DEV1.domain.com',
                                     inclusion_rule        => true,
                                     queue_to_queue        => true);

2nd method : Using DBMS_CAPTURE_ADM, DBMS_RULE_ADM, DBMS_PROPAGATION_ADM:

	exec dbms_streams_adm.set_up_queue ( queue_table =>'cap_scott_emp_1_qt', queue_name =>'cap_scott_emp_1_q', queue_user =>'strmadmin');
	exec dbms_rule_adm.create_rule_set ( rule_set_name => 'strmadmin.rs_cap_scott' , evaluation_context => 'sys.streams$_evaluation_context') ;
	exec dbms_capture_adm.create_capture( queue_name => 'strmadmin'. capture_name => 'cap_scott_emp_1, rule_set_name => 'strmadmin.rs_cap_scott');
	exe dbms_rule_adm.create_rule ( rule_name => 'rul_cap_scott_emp', condition => ':dml.get_object_owner() = ''scott' AND '\|\| ':dml.get_object_name() = ''emp' AND ' \|\| ':dml.get_source_database_name() = ''scott'');
	exec dbms_rule_adm.add_rule ( rule_name => 'strmadmin.rul_cap_scott_emp', rule_set_name => 'strmadmin.rs_cap_scott', evaluation_context => NULL);
	exec dbms_propagation_adm.create_propagation( propagation_name => 'strmadmin.prop_scott_dev1_2_dev2', source_queue => 'strmadmin.cap_scott_emp_1_q', destination_queue => 'strmadmin.app_scott_emp_2_q', destination_dblink => 'dev2.domain.com'

2.2 Difference of the two methods

You can legitimately wonder what is the difference? The answer is there is none! Both procedures creates a queue name 'cap_scott_emp_1_q' over a queue table 'cap_scott_emp_1_qt'. A capture process 'cap_scott_emp_1' is created that will capture all dml changes (no ddl will be captured) for the table 'scott.emp'. The dml will be propagated through the propagation process ''prop_scott_dev1_2_dev2'. to the remote db 'DEV2' and will feed the remote queue 'app_scott_emp_2'. As for the rule set and the rule which are created in the second procedure, they are created by default with a system generated name in the first procedure.
So the only difference between the 2 methods is that the rule set name and rule name is system generated in method 1? Not so fast. In this example it is true but only for we did not add more parameters in second method. The real difference is that method one is for a capture of LCR and only LCR while second method . In this respect you can say that method 1 is a simplified version.

For instance method 2 allows us to setup queues like that :

dbms_aqadm.create_queue_table( queue_table           => v_mq_queue_table_name,
                               queue_payload_type    => 'sys.mgw_basic_msg_t',
                               multiple_consumers    => TRUE);

That's the way you declare a queue table to connect to MQseries. There are others possible queue payload type :

	adtmsg
	raw
	sys.mgw_tibrv_msg_t
	mgwuser.book_order_t
	sys.aq$_jms_text_message
	etc..

The queue_payload_type used by dbms_streams_adm in first method can only be of type 'SYS.ANYDATA'. So for now we will retain that DBMS_STREAMS_ADM is a simplified method to create replication of queue type ANYDATA, and we will focus on this unless we need to complicated things, which we will :p

3 . CDC and Synchronous capture

CDC (Change data Capture is another API developed by the data warehouse team to capture change and replicate them elsewhere. It use the same technology stack than streams but with a different API. In my view it brings only confusion. Synchronous capture that appears in 11g is also another of these goodies API that a subset of the streams. Here you will only capture dml and ddl of a table and you are not supposed to apply any rules to transform the data

How many specific API Oracle will invent to satisfy a simple case ? Sound like people at Oracle Corps have not understood well the difference between an alphabet and pictogram collection. Every one of those specific API which are not flexible just bring a bit more confusion.