[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sync up two databases using PL/SQL or free software

Posted on 2009-12-26
17
Medium Priority
?
933 Views
Last Modified: 2013-12-18
How do I sync up 2 dbs the quickest or easiest? I want it to be a button in my APEX app so it probably needs to be PL/SQL.
0
Comment
Question by:bcarlis
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 13

Expert Comment

by:sonicefu
ID: 26126895
How many tables are there in both databases ?

After how much time (interval) you want to synch these both databases ?

Are the databases running at the same version of oracle ?
0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 26127955
This depends on how much of the databases you wan tot sync. If it is just your 'customer' data as opposed to system info or complete system, then you can achieve this with MATERIALIZED VIEWS.

This will update/refresh one database info with data from a remote database.

R.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 26128577
I have about 20 tables and yes it is just the Customer data. BUT, one is intranet and the other is local, only at time of connect. The local app is on a laptop with a local db and may be used while not being connected while the intranet  app is being used by someone else. Then at a point in time I want to connect the laptop and sync the two dbs at that time.
Both apps are identical, both db structures are identical, just syncing data between the two.
I know I'd have to create some business rules too, like which DML has priority over the other.
B
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Expert Comment

by:Richard Olutola
ID: 26129773
Definitely explore Materialized Views (MV).

Is tthis 2 way or 1-way update? I mean will the laptop be read-only on read-write?

If you want 2-way then you may need to implement Multi-Master Replicaiton (Streams). This can also employ MVs in a single direction update or 2-way (Master-Master) configuration.

R.
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 26129872
Oracle Streams is the best choice for your choice, and its better than Advanced replication. You can define the business rules very easily.
0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 26129882
If this is production system, ensure you're licensed for Oracle Streams.

R.
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 26129978
If there is Enterprise Edition then Streams options is automatically included in it.
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 26129984
If you're using oracle 10g then you can configure Oracle Streams using Database Control or Grid Control (Web Based Enterprise Manager), and you dont need to indulge into the PL/SQL code etc.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 26132214
I am using the free EX version and have no licenses, that is why I was asking about PL/SQL code.
This is an app I am trying to use myself that maybe one day, if I complete it I could sell it. I'm trying to keep it free at first though.

Any ideas?
0
 
LVL 13

Accepted Solution

by:
sonicefu earned 2000 total points
ID: 26132320
1- Create a Database Link (DB Link) for example LAPTOP (for the database which is at LAPTOP and PC for the Database which is on PC)
2- write insert statements  for example you're using laptop and then use the statement

INSERT into tble
select * from tble@PC;

or

MERGE statement

3- You can make an SQL script which will contain the insert statments for all the tables at your PC database and when you'll execute it at the LAPTOP then all the tables will be up synch.

or

You can include all these INSERT or MERGE statements in a PL/SQL procedure when you'll execute that at the LAPTOP database then this will be synch with the PC database.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 26138151
>>MERGE statement

There are issues with using Merge over a dblink.

OP, does the APEX app reside on the remote server or on your laptop?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 26138461
Two apps, two DBs, one on intranet, one on laptop. Both are identical.
I run the one on the laptop while no internet available. Others may be changing data while I am off line on the intranet app.
Each table has a column LAST_UPDATED - I was thinking to process all NEW records first, easy just an INSERT. All updates and deletes WHERE LT.LAST_UPDATE = IT.LAST_UPDATE no problem, all that have different LAST_UPDATED could be prompted in my APEX app screen, like Outlook does while syncing.
??????????????
0
 
LVL 2

Author Comment

by:bcarlis
ID: 26160644
I was looking for a sample script.
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 26160723
This is not a small task to write a script.

No one knows that what is the names of your tables and other details.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 26173098
No, I was asking for an existing script being used somewhere just to give me a head start on my own script.
I use to work for an outfit that the DBA team had a team library of scripts for about everything u could think of, including this topic, but it was a secure environment and a long time ago so I haven't ask them or know who to ask.
Thank you, Bill
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 31670111
I was looking for a sample PL/SQL script but that you for your input.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

829 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