This content has been marked as final. Show 1 reply
Stop the capture and the apply then do:
-prepare the 3 tables for instantiation and note the SCN for each table:
-Export the 3 tables on source site
3x : execute DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => 'OWNER.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
-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:
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).
3x : execute DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_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
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.