Synchronous Streams capture in 11g

This new streams feature is introduced in 11g, for replicating the data instantly to target destination.

Some specific points to understand about Synchronous capture

– Instant replication after commits
– Not dependant on logminer
– Uses anydata type, commit-time persistent queues
– Can only capture DML statements not DDL
– Should only be used for relatively small number & less activity tables

Setting up the synchronous streams

1. Create 2 databases (source & target). Asia & Europe in this case
2. Create a schema on both sides, where tables need replicating
3. Create Dblink from source-to-target & target-to-source
4. Create streams administrator (strmadmin, in this case)

SourceDB setup

sqlplus strmadmin/strmadmin@sourcedb

— create queue for apply
begin
dbms_streams_adm.set_up_queue(
queue_table => ‘sapply_queue’,
queue_name => ‘sapply_queue’,
queue_user => ‘strmadmin’);
end;
/

— create queue for capture
begin
dbms_streams_adm.set_up_queue(
queue_table => ‘sdb_sync_capture’,
queue_name => ‘sdb_sync_capture’,
queue_user => ‘strmadmin’);
end;
/

— create apply process
begin
dbms_apply_adm.create_apply(
queue_name => ‘strmadmin.sapply_queue’,
apply_name => ‘sdb_sync_apply’,
apply_captured => false);
end;
/

— add tables to be replicated
begin
dbms_streams_adm.add_table_rules
(
table_name => ‘sdata.real_test’,
streams_type => ‘apply’,
streams_name => ‘sdb_sync_apply’,
queue_name => ‘strmadmin.sapply_queue’,
source_database => ‘europe’
);
end;
/

— add propagation rule
begin
dbms_streams_adm.add_table_propagation_rules
(
table_name => ‘sdata.real_test’,
streams_name => ‘send_sync_data’,
source_queue_name => ‘strmadmin.sdb_sync_capture’,
destination_queue_name => ‘strmadmin.tapply_queue@europe’,
source_database => ‘asia’,
queue_to_queue => true
);
end;
/

— add synchronous capture to selected table
begin
dbms_streams_adm.add_table_rules
(
table_name => ‘sdata.real_test’,
streams_type => ‘sync_capture’,
streams_name => ‘sync_capture’,
queue_name => ‘strmadmin.sdb_sync_capture’
);
end;
/

— instantiate the table at target site
declare
iscn number; — variable to hold instantiation scn value
v_global_name global_name.global_name%type;
begin
iscn := dbms_flashback.get_system_change_number();
select global_name into v_global_name from global_name;
dbms_apply_adm.set_table_instantiation_scn@europe(
source_object_name => ‘sdata.real_test’,
source_database_name => v_global_name,
instantiation_scn => iscn);
end;
/

TargetDB Setup

— create queue for apply
begin
dbms_streams_adm.set_up_queue(
queue_table => ‘tapply_queue’,
queue_name => ‘tapply_queue’,
queue_user => ‘strmadmin’);
end;
/

— create queue for capture
begin
dbms_streams_adm.set_up_queue(
queue_table => ‘tdb_sync_capture’,
queue_name => ‘tdb_sync_capture’,
queue_user => ‘strmadmin’);
end;
/

— create apply process
begin
dbms_apply_adm.create_apply(
queue_name => ‘strmadmin.tapply_queue’,
apply_name => ‘tdb_sync_apply’,
apply_captured => false);
end;
/

— add tables to be replicated
begin
dbms_streams_adm.add_table_rules
(
table_name => ‘sdata.real_test’,
streams_type => ‘apply’,
streams_name => ‘tdb_sync_apply’,
queue_name => ‘strmadmin.tapply_queue’,
source_database => ‘asia’
);
end;
/

— add propagation rule
begin
dbms_streams_adm.add_table_propagation_rules
(
table_name => ‘sdata.real_test’,
streams_name => ‘send_sync_data’,
source_queue_name => ‘strmadmin.tdb_sync_capture’,
destination_queue_name => ‘strmadmin.sapply_queue@asia’,
source_database => ‘europe’,
queue_to_queue => true
);
end;
/

— add synchronous capture to selected table
begin
dbms_streams_adm.add_table_rules
(
table_name => ‘sdata.real_test’,
streams_type => ‘sync_capture’,
streams_name => ‘sync_capture’,
queue_name => ‘strmadmin.tdb_sync_capture’
);
end;
/

— instantiate the table at sourcec site
declare
iscn number; — variable to hold instantiation scn value
v_global_name global_name.global_name%type;
begin
iscn := dbms_flashback.get_system_change_number();
select global_name into v_global_name from global_name;
dbms_apply_adm.set_table_instantiation_scn@asia(
source_object_name => ‘sdata.real_test’,
source_database_name => v_global_name,
instantiation_scn => iscn);
end;
/

sqlplus strmadmin/strmadmin@sourcedb

— start the apply process at source
begin
dbms_apply_adm.start_apply(apply_name => ‘sdb_sync_apply’);
end;
/

sqlplus strmadmin/strmadmin@targetdb

— start the apply process at target
begin
dbms_apply_adm.start_apply(apply_name => ‘tdb_sync_apply’);
end;
/

— to check the tables using synchronous capture, following query can be used
select * from dba_sync_capture_tables
/

— synchronous capture, rule, table details
select r.streams_name, r.rule_name, r.subsetting_operation,t.table_owner, t.table_name, t.enabled from dba_streams_table_rules r, dba_sync_capture_tables t
where r.streams_type = ‘sync_capture’ and r.table_owner = t.table_owner and
r.table_name = t.table_name
/

–to check sync capture name, queues & rule sets associated
select capture_name, queue_name, rule_set_name, capture_user from dba_sync_capture
/

Cleanup Source & Target

sqlplus strmadmin/strmadmin@sourcedb

begin
dbms_capture_adm.drop_capture( capture_name => ‘sdb_sync_capture’, drop_unused_rule_sets => true);
dbms_apply_adm.delete_all_errors(apply_name => ‘sdb_sync_apply’);
dbms_apply_adm.drop_apply(apply_name =>’sdb_sync_apply’);
dbms_streams_adm.remove_queue(queue_name =>’strmadmin.sapply_queue’, cascade=> true);
dbms_streams_adm.remove_queue(queue_name =>’strmadmin.sdb_sync_capture’, cascade=> true);
end;
/

sqlplus strmadmin/strmadmin@targetdb

begin
dbms_capture_adm.drop_capture( capture_name => ‘tdb_sync_capture’, drop_unused_rule_sets => true);
dbms_apply_adm.delete_all_errors(apply_name => ‘tdb_sync_apply’);
dbms_apply_adm.drop_apply(apply_name =>’tdb_sync_apply’);
dbms_streams_adm.remove_queue(queue_name =>’strmadmin.tapply_queue’, cascade=> true);
dbms_streams_adm.remove_queue(queue_name =>’strmadmin.tdb_sync_capture’, cascade=> true);
end;
/

Advertisements
This entry was posted in Oracle Replication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s