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


Synchronisation of data between MySQL and Oracle

Posted on 2007-10-10
Medium Priority
Last Modified: 2013-11-18

I currently have a Point of Sales system project where we will host a local POS server in each retail outlet and at every 15mins it will replicate the latest data back to the central POS server at the data centre. However POS application requires real time sales data from our online retail system where the data is residing in an Oracle 10g server in the data centre. Thus the MySQL needs to import data from Oracle and then then export back to Oracle again. I have the following concerns:
- Is the method of hosting a local POS server and replicating it to the central POS server at every 15min a good enough practice? If there is a better architecture pls advise.
- What is the best way, process, interval, format for MySQL to import sales and members data from Oracle and sync the POS MySQL back to Oracle? Who draw from who at each point of time?
- There are potentially transactional problems in synchronisation from local POS to POS server, Oracle to POS and POS to Oracle.  How to solve these problems?

Pls advise. Thank you.
Question by:pajiao
  • 4
  • 3

Expert Comment

ID: 20047704
oracle database can be integrated with several other database systems using "transparent gateway". but this requires special licence and database platform which can be integrated using this are only major production systems: MS Sql, IBM, Informix, IBM DB2, Sybase,  ... but no MySql
as for 15 minuted - it depends ... does your POS terminal server real customers ? is the physical location of them at least 15 minutes to move between them? if so, 15 min is enough. you must only ensure, that if customer moves from 1 POS to another, when he arrives to 2nd POS, data which was modified at 1st POS should already be there
i think there is no easiest way than generating CSV files at one database and reading the CSV on second one. oracle has tool - oracle loader which help you to easy import scv to database
if you ensure, that only one POS modifies one set of data - data of customer as mentioned above, you will have no problem with data manipulation contention

Author Comment

ID: 20063668
On consistency issues, especially on order data, POS (mysql) takes care of the retail orders with access to online orders. Online orders are taken care by Online portal Database server (Oracle) with access to retail orders. Both will read from each other. There will be consistency issues unlike customer data where we can afford to read only from online portal database. How to solve them?

Author Comment

ID: 20064089
An example as below:

n - Oracle exports data (eg: Snapshot A)
n+10min - Oracle updates data from portal (eg: Snapshot A + new Oracle updates A)
n+15min - MySQL imports Oracle snapshot A
n+20min - MySQL updates data (eg: Snapshot A + new MySQL updates A)
n+25min - MySQL exports data
n+20min - MySQL updates data (eg: Snapshot A + new MySQL updates A + new MySQL updates B)
n+30min - Oracle updates data (the data updated at n+10 - new Oracle updates A, is overwritten?)
..... the cycle goes again and potentially the updates done at n+20min for MySQL will be overwritten.

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


Expert Comment

ID: 20064227
that's problem :-)
how many times do you have to serve 1 request? can you tolerate that the request will not be procesed due to some one else modified data firstly?
- imagine, that application collects and modifies data about customers
- add "timestamp" column to table customers. timestamp can be filled from sequencer, or it may be date of last modification ...
- each modification in central oracle db will set new value of "timestamp"
- updates from oracle will propagate actual value of timestam together with customer data
- you perform update on customer on mysql database, timestamp is never modified on mysql database
- process of import data from mysql to oracle will check, whether timestamp which is in update from mysql is the same as in oracle. if so, update is performed. if no - update is refused, because user on mysql updated older data than actually is in central oracle db - someone else modified data during time when data from oracle was propagated to mysql and update is propagated to oracle.

Author Comment

ID: 20073077
Questions below:

1) If only Oracle changes timestamp and when importing MySQL data, Oracle only updates data where timestamp remains unchanged. So in this case what happens to the same data that was updated by MySQL but also updated by Oracle? Your suggestion assumes Oracle to be the primary. Updates from MySQL are equally important, thus the context cannot tolerate loss of updated data from MySQL.

Accepted Solution

konektor earned 2000 total points
ID: 20073267
you shoul choose one database as primary storage.
so when oracle modifies data, timestamp alo si modified
if the same data was modified at mysql and is imported to oracle with older timestamp, update is refused.
you shoul just decide how often do you need to exchanxe data between databases to minimize theese data update looses

Author Comment

ID: 20075580
Both MySQL and Oracle are equally important data that should not be lost. Is there any other alternative?

Expert Comment

ID: 20591403
Forced accept.

EE Admin

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Suggested Courses

830 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