1 Reply Latest reply: Sep 14, 2009 2:38 AM by 442520 RSS

    ADD table to existing STREAM

      I want to add 3tables to existing stream configuration HOW do i must do it as THIS CHANGES do'nt effect on existing stream work?
      (my existing stream configuration is on table to table.DATABASE 10G)
        • 1. Re: ADD table to existing STREAM
          Stop the capture and the apply then do:

          -prepare the 3 tables for instantiation and note the SCN for each table:
          1x : 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;
          note each SCN for each table
          -Export the 3 tables on source site
          -import the 3 tables on target site

          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

          -(source db)add a dbms_streams_adm.add_table_rules (type catpure) for each table, reusing the already existing queue
          -(source db)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.
          -(target db)add a dbms_Streams_adm.add_table_rule (type apply) for each table

          -(target db)instantiate each table to its relevant SCN:
                   source_object_name=> 'OWNER.TABLE', 
                   source_database_name => 'SRC_GLOBAL_NAME' ,  
                   instantiation_scn => nn );
          -- check everything is ok
          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 999999999999 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;
          -- check the rules  and rules set
          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.