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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Ok. First I'm no DBA. The guy's sick and I need to replace him for the time being (1-2 months). I'll edit my post to better reflect my needs. Thank you for your answer. – Philippe Nov 11 '11 at 15:41
It wasn't much of an answer, but just wanted to see what you really need help with. The edit appears to be much more helpful. We don't mind helping non-DBAs, so don't fret there. – jcolebrand Nov 11 '11 at 16:54
Is this a one off transfer or are you talking about on-going sync (ie replication)? – Jack Douglas Nov 11 '11 at 16:57
On-going sync. Daily. – Philippe Nov 11 '11 at 17:05
you need to '@' me for me to see your comment. Have you looked at transportable tablespaces? – Jack Douglas Nov 14 '11 at 14:19

Golden Gate is answer to these types of problem. Oracle bought it. Therefore it is a Oracle Golden Gate right now.

Look below image which is taken from Oracle Golden Gate Admin Guide

Also you can look to following article:

Golden Gate topologies

Thanks Tevo D for pointing out licensing issue. Actually golden gate may be a expensive solution depending on your circumstances, i.e your company's standing with Oracle, what is your cost of downtime etc.

share|improve this answer
FYI... this requires additional licensing. – Tevo D Nov 15 '11 at 14:01
Additional licensing fees are not an option at the moment but thank you for suggesting. – Philippe Nov 16 '11 at 21:12

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.

share|improve this answer
I don't think TT will let you transport packages, will it? I think Philippe will have to use one of the other options for that - eg your suggestion about change management procedures. – Jack Douglas Nov 17 '11 at 14:19
I was thinking that this would be possible since the metadata can be transferred using datapump and datapump can include PL/SQL. However, you are correct and this cannot be done with transportable tablespaces. I will update my answer and add a comment to yours. – Leigh Riffel Nov 17 '11 at 20:15

Firstly - you will never need to shut down your source server to do this. Oracle has a lot of techniques you can use.

It depends on the data (a bit vague, sorry). Let me give you an example - if you have a table that only has INSERT and has a sequence-generated PK, then you can simply find the max() on the destination database, and do an INSERT INTO ... (SELECT ... ) over a DBLINK.

Another example - if your destination is read-only, then maybe the solution could be Active DataGuard. Or if there are no conflicts to manage, logical standby.

GoldenGate is a fine technology - but before spending all that money, be sure that you really need it! Also consider SharePlex.

share|improve this answer

Oracle Enterprise Edition has a feature called Transportable Tablespaces that "can be used to copy a set of tablespaces from one Oracle database to another."

You can transport tablespaces across platforms and versions, but limitations include:

The source and the destination databases must use compatible database character sets

As long as you can fence the objects you want to copy into tablespaces separate from the objects you don't want copied over, you can use this feature to move many kinds of objects in one go. Data Pump is also needed but only for the metadata.

share|improve this answer
Transportable Tablespaces even though it uses Data Pump cannot take advantage of the Data Pump ability to copy PL/SQL. From the documentation: You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL... – Leigh Riffel Nov 17 '11 at 20:43

Your Answer


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.