Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need a way to temporarily replicate data between two data centers on two continents for a small number of large tables in Oracle. The data in the tables is around 30Gb but the transfer rate is only around 5Gb/hour.

I guess that the data does not change greatly on a day to day basis.

What options do we have for replicating the data that are simple and cost effective for this short term requirement?

Clarifications

  • Temporarily i.e. two months
  • A subset of the database, 2 or three large tables
  • One way, until the database is migrated to the new datacenter
share|improve this question
    
Can you clarify what you mean by "temporarily"? –  Gaius Mar 1 '12 at 13:06
    
Sorry, a solution that will run for 2 months. –  Stuart Woodward Mar 2 '12 at 8:37
    
The whole database, or a subset of tables? Is the replication one-way, so the remote site is read-only, or two_way? Have you looked at the options here? –  Alex Poole Mar 2 '12 at 9:50

2 Answers 2

up vote 2 down vote accepted

If you are using the enterprise edition of the database, it sounds like you could use either Streams or materialized views to replicate the data.

Streams works by asynchronously mining the redo data that is generated on the source system to find changes to the tables in question which then get delivered to the destination database using Oracle's Advanced Queues (AQ). Streams is generally the preferred architecture-- it is the newer technology, it generates less load on the source system, it doesn't involve creating any new objects in the source system and has lower latency. The downside to Streams is that there can be a decent amount of administrative work getting Streams set up and working depending on the Oracle version(s) involved.

Materialized views are an older replication technology that would also seemingly meet your needs. Materialized views work can also replicate changes but they have to do so by recording the change data in materialized view logs that are created on the tables that you want to replicate. On the source system, you'd need to create materialized view logs on the tables you want to replicate-- those logs would be automatically written to when changes are made to the table but those writes will be synchronous with the transaction so it may affect INSERT and UPDATE performance. On the destination side, you would then create a materialized view that did an incremental refresh on a set schedule (i.e. every few minutes) that would read the data from the materialized view logs, pull the data over the network, and update the materialized view's copy of the data. Materialized views tend to be easier to set up but they're not as efficient and the requirement of creating materialized view logs in the source database can be a point of contention.

Either solution is relatively easy to shut down in 2 months when you no longer need the replication.

share|improve this answer

Of free solutions, I can only think of rolling your own with a trigger over a database link, or setting up streams.

I'd set up streams.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.