We have ERP application that store the data to ORACLE database and also we have a lot of another web applications that use the ERP database. We got the performance issues when ERP and another application use the same database.
We are planning to do mirroring of oracle database into mysql database. So that mysql db is used for web applications and oracle db is used for ERP. So we could say these new database (mysql) are mirror of ERP database.
What is the best way for mirroring this condition?
We do same in our enviroment. Following is our case
1) out of 100% tables in database, we need just 20% tables to show on website.5% are to be updated back to oracle.
2) We create similar data tables in mysql.
3) we set up cron job in php script (on web interface) (1 script per table). this script connect to both mysql and oracle
3a) small tables are delete completely and all records are updated from oracle to mysql
3b) large tables are checked for update or insert and only those records are updated or inserted to mysql
3c) same way some table are updated from web and oracle depending on last update
4) we schedule all cron script to run at night (once in 24 hours)
5) at last we run one more php script which compares number of records in both databases, if any mismatch found in any table mail is sent to administrator.
urtrivedi wrote
3b) large tables are checked for update or insert and only those records are updated or inserted to mysql
I have created similar tables in mysql and a script prepared to connect to both Oracle and MySQL.
How to compare Oracle table with MySQL table since both are in different servers?
When we update data from mysql to oracle
We used to kept one flag in mysql table say record_status=U when there in any change in record in mysql
So cron will look for such records where record_status ='U' and only those record is updated in oracle.
After updating oracle this flag is set to NULL
Same way when mysql have new record, record_status is set to I and after inserting such record to oracle flag is again set to NULL
I have created a flag in Oracle db and cron script is running dialy once. My question solved.
Thanks urtrivedi.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.