pajiao
asked on
Synchronisation of data between MySQL and Oracle
Hi,
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.
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.
ASKER
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?
ASKER
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.
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.
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?
scenario:
- 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.
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?
scenario:
- 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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both MySQL and Oracle are equally important data that should not be lost. Is there any other alternative?
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
1.
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
2.
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
3.
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