Home » Other » General » Sync MySQL table data with Oracle tables (Oracle 9.2.0.3)
Sync MySQL table data with Oracle tables [message #587309] Fri, 14 June 2013 01:33 Go to next message
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 #587322 is a reply to message #587309] Fri, 14 June 2013 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there a network link between the servers containing the Oracle database and the MySQL database?
If so, you can use HS (Heterogeneous Service) and mviews.

Regards
Michel
Re: Sync MySQL table data with Oracle tables [message #587349 is a reply to message #587322] Fri, 14 June 2013 04:54 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for your reply.
There is no network link between the servers containing the Oracle database and the MySQL database.
Re: Sync MySQL table data with Oracle tables [message #587365 is a reply to message #587349] Fri, 14 June 2013 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>There is no network link between the servers containing the Oracle database and the MySQL database.
then how does the data get from MYSQL Server to the Oracle DB Server?
Re: Sync MySQL table data with Oracle tables [message #587425 is a reply to message #587365] Fri, 14 June 2013 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With USB key by UPS. Wink

Regards
Michel
Re: Sync MySQL table data with Oracle tables [message #587517 is a reply to message #587365] Sun, 16 June 2013 13:46 Go to previous messageGo to next message
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 #587526 is a reply to message #587517] Sun, 16 June 2013 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.
Re: Sync MySQL table data with Oracle tables [message #587576 is a reply to message #587526] Mon, 17 June 2013 02:24 Go to previous messageGo to next message
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
Re: Sync MySQL table data with Oracle tables [message #587579 is a reply to message #587576] Mon, 17 June 2013 02:33 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use Oracle "connection manager", it is made for such cases.

Regards
Michel
Previous Topic: log miner
Next Topic: Oracle 10g Installed with the TNS port no 1522
Goto Forum:
  


Current Time: Thu Mar 28 18:54:20 CDT 2024