| Sync MySQL table data with Oracle tables [message #587309] |
Fri, 14 June 2013 01:33  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
The purpose is to migrate data from 10 mysql tables to oracle tables which are similar in structure and have same primary key defined on both database.
This is done by sending incremental data in csv format everyday at 0 hrs for the change happened to these MySQL tables.
There will be seprate csv for each table and will be in a single zip file.The zip file would be uncompressed in a separate server where Oracle resides.
Based on the existance of primary key id, data is inserted or updated in the Oracle Live tables.In the existing functionality SQL loader is being used to load data into staging table and then to move the data to live tables.The existing process is doing full refresh on all the tables(including 10 tables) on weekly basis.
If we use the existing process, can we use external tables over sql loader? The records will be in .csv file format delimited by Tab ,separated by newline
character with 1 header (column names) in each file (can SKIP the header in external table using LOAD WHEN column!='column').
Approach can be - Set up cron job to copy the data from the external tables on a specific time? This external tables can be treated as staging tables.And then can
copy the incremental data from external table to Live tables.The next day again, thru unix script we can uncompressed the csv files and put the new csv in the specified path. So there will be 10 external tables for 10 csv daily. Can use a wrapper to call the a)shell script to decompress the csv files in specified directory
and then b) to execute stored procedure to insert/update data into oracle tables.The deletion file would be a single file for those tables and there is identifier to
which table to delete records.
In this case, the arrival time of the file will be important and also the time when data will be loaded to Oracle live tables. The loading of data and to drop the files can be done using Stored Procedure and cron job.To record last run,we can maintain a table that stores last run date info and procedure name.
Is there a better approach to do it without using any tool ? We also need to ensure that performance is not degraded.The csv can contain million data for a table.The Oracle database version is in 9.2.0.3!!
The basic purpose is to keep the Oracle table data used by an application to be in sync with MySQL tables which modified daily.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sync MySQL table data with Oracle tables [message #587517 is a reply to message #587365] |
Sun, 16 June 2013 13:46  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
There is no transparent gateway between the servers containing the Oracle database and the MySQL database.Not sure if ODBC connectivity works. Is there any link or document which demonstrate this feature for Oracle 9i db OS: HP Unix server
Thanks.
|
|
|
|
|
|
| Re: Sync MySQL table data with Oracle tables [message #587576 is a reply to message #587526] |
Mon, 17 June 2013 02:24 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
BlackSwan wrote on Sun, 16 June 2013 17:39>There is no network link between the servers containing the Oracle database and the MySQL database.
when no network connectivity exists between the 2 systems, any discussion regarding ODBC is moot.
The two database are in different network. There is an sftp server in between where files are being sent from the server that have MySQL database. This is a secured network and can not be accessed directly from server that contain Oracle DB(It's under different network).There is an SFTP server in between and thru sql loader files are loaded in staging table to Oracle DB
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
