How to sync instance A to instance B without losing any of the data already on instance B

oracle-11g-r2replication

Like I said in a comment below, I'm no DBA and I'm still in a learning process.

Here's my scenario :

I have an Oracle server running a couple schemas (let' call it Source). What I would like to do is send a 1:1 copy of every objects to another Oracle server (let's call it Target) where there's already some objects that I need to preserve.

Ideally, I don't want to shutdown the Source server.

Also, I need to keep the already existing schemas of the Target server. So the new objects from Source need to co-exist on the Target server with the already existing objects. Maybe by using 2 instances that could be accomplish? I don't know, I'm just suggesting.

What could be the solutions to achieve that?

Best Answer

The default, built-in, Oracle answer would probably be Oracle Streams.

The stream can propagate information within a database or from one database to another.

A possible alternative would be to create materialized views of A on B. You may have to union the data from the materialized views with the existing tables or create views that do the union. The materialized views could be refreshed daily.

The options already listed may also be good fits depending on the requirements. You should consider them all.

Update: As your comments indicate that you would like to replicate packages as well, you could integrate those into your change management process so that when changes are made to A they are also made to B. You would need to use a different schema since you want to preserve the originals.

On the other hand, the requirement for moving lobs as well makes things more difficult. If you want to use just one technology to handle all of your requirements you will probably have to go with datapump. B could be setup to pull the tables with blobs and the packages from A into a separate schema.

You may still want to consider creating some views to union the data from the existing tables in B with the transported tables from A.