Friday, August 15, 2008

Streams with Downstream Log Mining

# first create standby redo logs on the destination and setup dataguard archival to that destination.
# set priveleges etc. On source and target

sqlplus bobj_login/password
ALTER TABLE bobj_login.orders ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
grant all on bobj_login.orders to strmadmin;

# on target create queue
sqlplus strmadmin/password

begin
DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table => 'strmadmin.orders_queue_t',
queue_name => 'strmadmin.orders_queue',
queue_user => 'strmadmin');
end;
/

# create database links from source to target, target to source.

# create the cature process and rules on target
connect strmadmin/password

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'strmadmin.orders_queue',
capture_name => 'orders_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'P01CFD',
use_database_link => TRUE,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/

BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'orders_capture',
parameter => 'downstream_real_time_mine',
value => 'Y');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'BOBJ_LOGIN.orders',
streams_type => 'capture',
streams_name => 'orders_capture',
queue_name => 'strmadmin.orders_queue',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => FALSE,
source_database => 'P01CFD',
inclusion_rule => TRUE);
END;
/

# start capture on target

exec DBMS_CAPTURE_ADM.START_CAPTURE('orders_capture');

#exec dbms_capture_adm.stop_capture('orders_capture', TRUE);

# create apply process and rules

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'BOBJ_LOGIN.orders',
streams_type => 'apply',
streams_name => 'orders_apply',
queue_name => 'strmadmin.orders_queue',
include_dml => true,
include_ddl => true,
source_database => 'P01CFD',
inclusion_rule => true);
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'orders_apply',
parameter => 'disable_on_error',
value => 'n');
END;
/

# create schema transformation. (check in OEM to get the apply DML rule name - or select from dba_rules)
BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => 'ORDERS57',
from_schema_name => 'BOBJ_LOGIN',
to_schema_name => 'BI_DATA',
step_number => 0);
END;
/

# get SCN on source

SET NUMF 999999999999
select current_scn from v$database;

# instantiate the table on target

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name=> 'BOBJ_LOGIN.orders',
source_database_name => 'P01CFD' ,
instantiation_scn => 26427653914);
end;
/

# start apply process on target
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'orders_apply');
END;
/

# enter a transaction on the source

insert into bobj_login.orders values (1);
commit;
alter system switch logfile;
alter system switch logfile;


# select on target
select * from bobj_login.orders;