Synchronisation of data between MySQL and Oracle

Posted on 2007-10-10
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
    LVL 9

    Expert Comment

    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

    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

    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.
    LVL 9

    Expert Comment

    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

    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.
    LVL 9

    Accepted Solution

    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

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

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now