?
Solved

sync up 2 oracle databases

Posted on 2010-04-06
8
Medium Priority
?
529 Views
Last Modified: 2013-12-18
We have a task of synching up data on remote oracle instance (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ) and oracle EBS database (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 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.
0
Comment
Question by:mahjag
  • 4
  • 4
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29938998
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.
0
 

Author Comment

by:mahjag
ID: 29939378
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29940167
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:mahjag
ID: 29941476
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29944458
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.
0
 

Author Comment

by:mahjag
ID: 30025809
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
0
 

Author Comment

by:mahjag
ID: 30025903
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
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 30026581
>>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.

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question