This content has been marked as final. Show 6 replies
Maybe you should have a look to Streams replication http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_over.htm#CHDJACGF (feature included in database) or use Golden Gate (separate product).
Thanks for the link. I will verify that. But as per my current understanding on Oracle Stream, the server will capture each and every change, creates LCR and then applied to destination database. And its always a good idea to have constraints defined so that the updates will be faster. But do you think this is the best approach for fact tables where the transactions may be high compared to dimension where it grows slowly. Please clarify.
If not, please let me know is there any other best approach. Or export and import data dump option will do it? Please clarify.
If this will be a one time job, you are better off using one of two options:
2. Create DB Link between S1 & S2. Then use CTAS (Create Table As Select).
If this will be permanent, I would not personally recommend Oracle Streams, better use MV's over DB Link, so basically you would:
1. Create DB Link between S1 & S2.
2. Create Fast Refresh MV's in S2 referencing tables in S1.
3. Run one complete refresh for MV's in S2.
4. Then you can schedule daily Fast Refresh for MV's in S2.
Once you reach step 4, you would be living happily ever after.
if you have to do it on a daily basis, the best option you have is streams, you can setup streams for a specific schema or a set of tables. However the all the redologs have to be scanned, but you can offload this part to the target DB by using downstream capture without creating any overhead to your source.
Thanks All. As we have decided to pull only incrementals, I have chosen DB Links to pull the data from one database to another database.
Any other comments on it is much appreciated.
Just need one more input, will adding more db links to different database degrade its performance?
Please see below mentioned link