6

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?

Improve this question
5

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.

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
5
+25

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 http://download.oracle.com/docs/cd/E18101_01/doc.1111/e17341.pdf

Also you can look to following article:

http://www.dbasupport.com/oracle/ora11g/Oracle-Replication-Streams-vs-GoldenGate.shtml

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.

Improve this answer
  • 1
    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
4

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.

Improve this answer
4

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.

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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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