I need your help on some replication matter I guess. I'm actually not a dba, but I need to do some work on my db: I have a remote db, with table1, table2 and a local db with table3 and table4 in local db. table1 and table3 have the same column names and number of columns, but table4 has an id column in addition to table 2 columns.

my objective is to keep data synchronized in both dbs which means have same data on table 1 and table 3, and same common columns data on table 2 and 4.

what I did so far is to create a dblink between local and remote db and merge scripts, but I need to do it manually everytime there are changes on remote db, is there any better way, I thought of a trigger but don't know where to create it and how it will detect changes on remote db to replicate them in local. I appreciate any help or advice even the RTFM ones :D

  • Any reason why you can't put the trigger on the remote tables and have said triggers 'push' the changes through your dblink to the local tables? – markp Mar 4 '18 at 1:01
  • What version of Oracle are you using? If it was me I would use materialized views and make sure that the rowid is there from every table. I would also create change log tables for each table. Then do a fast refresh every 15 minutes or so depending on how long that takes. You can also use triggers to push changes, or the CDC_publisher to push changes from one database to the other. Another strategy is to just create views that have the data you want and just query the view. That way you don't need to keep the data replicated. – Gandolf989 Mar 5 '18 at 22:30
  • thanks for you all, I created triggers on the remote db with stored procedures that did the job – abbr Mar 7 '18 at 18:44
  • @Gandolf989 - CDC was desupported in 12c. Materialized View Logs might be a good approach also. – Michael Kutz Mar 7 '18 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.