All geek questions in one place

Data transfer between different DBMS

I want to transfer the entire database available on Informix to Oracle. We have an application that works in both Databases, one of our clients moves from Informix to Oracle, and he needs to transfer the entire database to Oracle (the structure is the same).

We often need to transfer data between oracle / Mssql / Informix, sometimes only one table, and not the entire database.

Does anyone know of any good program that does the job?

+3
source share
5 answers

Pentaho Data Integration ETL tools are available as open source (also known as the "Maker") cross-database and many other use cases.

From their data sheet:

Common use cases

  • Data warehouse population with built-in support for slow resizing, garbage sizes
  • Export database (s) to text files or other databases.
  • Import data into databases, ranging from text files to excel sheets
  • Transfer data between database applications
  • ...

A list of accepted I / O data formats can be found in the accepted answer to this question: Does anyone know a list of Pentaho Data Integration (Kettle) connection lists? It supports all databases using the JDBC driver, which means most of them.

+4
source

Check out this question, it includes some very good ideas: Search (freeware) database migration tools

+1
source

You can try working with Oracle Migration Workbench tools. See http://download.oracle.com/docs/html/B15858_01/toc.htm If you want to regularly read Informix data in Oracle, using Heterogenic Services might be the best option. Check hs4odbc or dg4odbc, depending on the release of Oracle.

Hope this helps, Ronald.

+1
source

I have done this in the past, and this is not a trivial task. We ended up writing each table to a flat file with channel separators and reloading each table in Oracle using Oracle SQL Loader. There was a ton of Perl scripts to clean up the source data and shell scripts to automate the process as much as possible and run operations in parallel.

Gotchas that may arise: 1. Select a delimiter that is as unique as possible. 2. Try to find data types that are as close to Informix as possible. those. date and time stamp 3. Try to get the data as clean as possible before flushing flat files. 4. HS is likely to be too slow.

This was done many years ago. You might want to explore the Golden Gate software (now owned by Oracle) that might help in this process (GG did not exist when I did this)

Another idea is to use the ETL tool to read Informix and upload the data to Oracle (Informatica comes to mind)

Good luck :)

+1
source

sqlldr - Oracle import utility

Here is what I did to transfer 50 TB of data from MySQL to ORacle. Generated csv files from MySql and sqlldr utility in oracle to export all data from files to oracle db. This is the fastest way to import data. I have been researching this for several weeks and have done many test cases, and sqlldr is the best and fastest way to import into oracle.

+1
source

Source: https://habr.com/ru/post/886645/

More articles:


All Articles