Add a New Table/New Schema to an Existing Streams Setup

Step 1: Stop Apply process in Target

SQL> conn strmadmin/strmadmin

-- Check apply process name
select apply_name from dba_apply where apply_user=USER;


-- Stop apply process

SQL>
begin
dbms_apply_adm.stop_apply('TARGET_APPLY');
end;
/


Step 2: Creating Schema Apply rule in Target system

SQL> conn strmadmin/strmadmin
SQL>


BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'STUDENT',
streams_type => 'APPLY',
streams_name => 'TARGET_APPLY',
queue_name => 'STRMADMIN.TARGET_Q',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'MDDRCAT.MDDATACOR.ORG');
END;
/



Step 3: Creating propagation process on Downstream for new schema

SQL> conn strmadmin/strmadmin
SQL>

begin
dbms_streams_adm.add_schema_propagation_rules (
schema_name => 'STUDENT',
streams_name => 'DOWNSTREAM_PROPAGATE',
source_queue_name => 'STRMADMIN.DOWNSTREAM_Q',
destination_queue_name=> 'STRMADMIN.TARGET_Q@MDDPROD.MDDATACOR.ORG',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'MDDRCAT.MDDATACOR.ORG',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
end;
/



Step 4: Creating Schema CAPTURE rules on Downstream for new schema

SQL > conn strmadmin/strmadmin

SQL>


BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name => 'STUDENT',
streams_type => 'CAPTURE',
streams_name => 'DOWNSTREAM_CAPTURE',
queue_name => 'STRMADMIN.DOWNSTREAM_Q',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => FALSE,
source_database => 'MDDRCAT.MDDATACOR.ORG',
inclusion_rule => TRUE);
end;
/


Step 5: Instantiate the Schema at Target



SQL > conn strmadmin/strmadmin
SQL>

DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@MDDRCAT.MDDATACOR.ORG ();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'STUDENT',
source_database_name => 'MDDRCAT.MDDATACOR.ORG',
instantiation_scn => iscn,
recursive => true);
END;
/


-- Check Schema INSTANTIATION

SQL> select * from DBA_APPLY_INSTANTIATED_SCHEMAS;


Step 6: Start Apply process in Target

SQL> conn strmadmin/strmadmin

-- Check apply process name

select apply_name from dba_apply where apply_user=USER;


-- Stop apply process



SQL>
begin
dbms_apply_adm.start_apply('TARGET_APPLY');
end;

/