ADD table to existing ORACLE STREAM

Stop the capture and the apply then do:

BEGIN

 DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name  => 'STRMADMIN_CAPTURE');

end;

/

BEGIN

  DBMS_APPLY_ADM.STOP_APPLY (

  apply_name => 'STRMADMIN_APPLY');

end;

/

prepare the 1 tables for instantiation and note the SCN for each table:

execute DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => 'SID.TAB_TEST_REPLIC');

select TABLE_OWNER,TABLE_NAME,SCN, to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') ti from dba_capture_prepared_tables order by table_owner;

execute DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name=> 'SID.PROPAGA',source_database_name => 'LEMURZ' , instantiation_scn => 4760901785913 );

-Export the 1 tables on source site (ORIGEN)
-import the 1 tables on target site (DESTINO)

Get the streams name of the capture streams and reuse its name for each add table rule. Do the same for the propagation and apply.

No need to created different objects

-(ORIGEN)add a dbms_streams_adm.add_table_rules (type catpure) for each table, reusing the already existing queue.

BEGIN

   DBMS_STREAMS_ADM.ADD_TABLE_RULES(

   table_name      => 'SID.PROPAGA',

   streams_type    => 'CAPTURE',

   streams_name    => 'STRMADMIN_CAPTURE',

   queue_name      => 'STRMADMIN.STREAMS_QUEUE',

   include_dml     => true,

   include_ddl     => true,

   source_database => 'lemur');

 END;

/

-(DESTINO)add a dbms_Streams_adm.add_table_propagation_rules for each table, optionally remove the ruleset if there is only one destination - it does nothing but burn cpu in this case.

begin

  dbms_streams_adm.add_table_rules(

  table_name      => 'SID.PROPAGA',

  streams_type    => 'APPLY',

  streams_name    => 'STRMADMIN_APPLY',

  queue_name      => 'STRMADMIN.STREAMS_QUEUE',

  include_dml     => true,

  include_ddl     => true,

  source_database => 'karibu');

end;

/

-(DESTINO)add a dbms_Streams_adm.add_table_rule (type apply) for each table

-(ORIGEN)instantiate each table to its relevant SCN:

DECLARE

  v_scn  NUMBER;

BEGIN

  v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@KARIBU(

    source_object_name    => 'SID.TAB_TEST_REPLIC',

    source_database_name  => 'LEMUR',

    instantiation_scn     => v_scn);

END;

/

set lines 190 pages 66

COL objt HEADING 'Object| Type' FORMAT A9

COL own_obj HEADING 'Object Name' FORMAT A45

COLUMN lnk HEADING 'Using|Dblink' format A18

COL SOURCE_DATABASE HEADING 'Source Database' FORMAT A38

COL INSTANTIATION_SCN HEADING 'Instantiation| SCN' FORMAT 99999999999999 justify c

select distinct SOURCE_DATABASE, source_object_owner||'.'||source_object_name own_obj,

        SOURCE_OBJECT_TYPE objt, instantiation_scn, IGNORE_SCN, apply_database_link lnk

from  DBA_APPLY_INSTANTIATED_OBJECTS order by 1,2;

Restart the capture and the apply, the capture will now start capture all SCN for the new table above the table instantiation, so it is important to have stopped the capture before running the table prepare instantiation so that the capture takes mutation for the 3 tables that comes after the export. However, mutations on source that occurred after the prepare table and BEFORE the start of export may be in conflict on target site. If you do the prepare table AFTER the export then rows on target will be missing (insert)or not found (update and delete).

Workaround 1) is to consider locking the 3 tables then before doing anything but even that transaction started before the lock (they got their SCN) will result in a ORA-1403 data not found at apply time.

Workaround 2) Run query on v$logminer_contents after having mounted the proper archive and check no transaction between the prepare table and the export. if so then adapt the apply table instantiation.


Note: If your target table have only insert (no update or delete) and you don't mind the data before now! then you can skip the export/import it is useless. Just instantiate and setup on both sites.