sync up 2 oracle databases

We have a task of synching up data on remote oracle instance (Oracle Database 11g Enterprise Edition Release - 64bit ) and oracle EBS database (Oracle Database 10g Enterprise Edition Release - 64bit), what are the ways that can be done? Also the tables are not the same on both the database and we do not need all the tables in APPS database on EBS side, we do have a finite set of tables that needs to be in sync. It will be nice to list out all the options available so that I can look into feasbility of this issue.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can I assume the two systems share a common network?

What is the timing of the sync?  real-time or on a schedule?

Replication is the first thing that comes to mind.  I've never tried this across versions and I'm not sure Advanced Replication can handle different object definitions.

I've never done it but Streams replication might handle the column differences.  The docs say you can create a user-defined Apply process.

I would also look at materialized views.  Depending on the sync schedule and amounts of data involved maybe transportable tablespaces or some custom code/triggers/??? to move the data around.
mahjagAuthor Commented:
The 2 systems are not on same network but they are both installed in linux and solaris unix servers - timing of the sync is real-time or atleast that is the expection to avoid data loss. I thought about replication as well but I have not applied on different versions before - that is why I am asking the experts to see if anyone had to do similar things before. - what is streams replication? how does it handle column difference - is there work involved to create mapping? My final resort was anyway writing custom code (aPIs) but not sure how I can sync it real time.
slightwv (䄆 Netminder) Commented:
If the servers can't talk, that leaves out any form of replication, triggers, and I believe materialized views.  How did you plan on some custom app working?

you are down to transportable tablespaces, custom code and somethink like sql*loader.  Neither of these are real time.

>>what is streams replication?
A new (and way better) form of replication.  I'll defer you to the online docs for the overview.

>>how does it handle column difference
Can't guarantee that it dies.  I just know there is a user-defined apply function that you might be able to get working.

>>My final resort was anyway writing custom code (aPIs) but not sure how I can sync it real time.

If you can figure out how to get the app(s) to see both systems,  off the top of my head possible a trigger based "Needs synced" table in both databases the app can pull from.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mahjagAuthor Commented:
I am sorry to confuse you totally by saying both database cannot talk  - it can surely talk using db links today but they are not hosted on same unix box is what I meant. - would like to see any other options other than database links as sometimes it is very difficult for custom oracle form to get data from remote database for any DML action if using database links.
slightwv (䄆 Netminder) Commented:
Thanks for clearing that up.

Advanced and Streams replication both rely on database links.  

>>very difficult for custom oracle form to get data from remote database for any DML action if using database links.

I can't speak to Oracle forms and database links but as long as the network is stable there should be no problems using them.
mahjagAuthor Commented:
how cool is to setup up stream replcation? what is the feasibility if the network is not stable and sync is not real time - is there any other solutions to consider - can custom APIs work to estabilish the same result - I know it is more development but I am trying to find out the available solutions, feasiblity of use - any disadv from the approach and then come to conclusion
mahjagAuthor Commented:
we do have custom form application that is getting the data using db links we have data integrity issues when 2 users trying to access the same information - I do not want to increase the problem by bringing in the solution that would not keep the data in sync between the 2 systems
slightwv (䄆 Netminder) Commented:
>>how cool is to setup up stream replcation?

It's easy once you understand what it is doing.  Gaining this understanding is not that easy.  Oracle even has a 5 Day class on Streams.  That should give you a hint...

There are several ways to configure Streams and multiple ways it does what it does:  For example, do you want DATABASE, SCHEMA or Table replication?  Do you want DDL commands to be automatically replicated?   etc...

You are adding another layer of complexity by introducing a user-defined apply process.

I suggest starting with the online docs to see if this will work for you.

>>what is the feasibility if the network is not stable

You should be OK.  Streams replication has three pieces: Capture, Propagation and Apply.  Propagation needs the network.  If it can't get it, it will continue to retry.  Capture is still capturing changes.

The problem might be conflict resolution ( the two databases update the same column about the same time, who gets to commit the update) if the connection remains down for to long.

>>we have data integrity issues when 2 users trying to access the same information

this is more likely an application issue more than a dblink issue.  Likely the second 'commit' gets the update.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.