4 Replies Latest reply: May 6, 2008 12:13 PM by 636487 RSS

    MySQL to Oracle - Migrate Data in SQL Developer

    636487
      I tried to migrate MySQL 4.2 to Oracle 10g using the Quick Migrate method in Oracle SQL Developer. Everything went fine except for a few tables which have data loading errors, two tables simply got no data migrated at all every time I tried. Two other tables got partial data migrated. My questions are:

      1. How can I migrate individual table data?

      2. What could be the possible causes of no data migrated on the two tables? One has 176,074 rows, the other 1,792,318 rows.

      3. How can I see a detailed list of errors occured during data migratation?

      4. Is there any way to speed up the data migration? I postponed the index creation until after the data load.

      Any help would be much appreciated. Thanks!

      Tracy Liang
      xl2191@att.com
        • 1. Re: MySQL to Oracle - Migrate Data in SQL Developer
          Dermot Oneill-Oracle
          Hi Tracy,

          SQL Developer has 2 ways to move data.
          1) Online Data Move.
          This is the way data is moved in the Quick Migrate or if you use Migration>Move Data. It uses the MySQL JDBC driver you specified.
          This is really only for small datasets. Its compounded by the MySQL JDBC driver which by default returns all the rows, into memory, when a table is queried.

          2) Offline Data Move.
          SQL Developer will create for you scripts which utilize MySQL mysdump and Oracles SQL*Loader. These tools are designed to move data around. The scripts dump the data in .dat files and then takes these files and load them into your table. This is the recommended approach for larger data sets. These scripts give you the maximum amount of flexibility as well, allowing you to migrate 1 table at a time if you wish.

          Note: For Offline Data Move, you need to perform a standard migration with a Capture / Convert / Generate phase. Dont use the Quick Migrate. As you will want to reference the migration repository to create the scripts.

          1. How can I migrate individual table data?
          Use the Offline Data Move approach and modify the script to load only the table you want.
          2. What could be the possible causes of no data migrated on the two tables? One has 176,074 rows, the other 1,792,318 rows.
          This is most likely due to an out of memory exception as all rows are returned into memory. We have a bug logged against this and we are working on using more advanced solution to move MySQL data online. But the best approach for large datasets is to use the Offline Data Move approach.
          3. How can I see a detailed list of errors occured during data migratation?
          The Offline Data Move scripts use MySQL mysqldump and Oracles SQL*Loader. These tools give lots of feedback in log files. So if issues occur you should be given all the info you require.

          4. Is there any way to speed up the data migration? I postponed the index creation until after the data load.
          With large datasets, the Offline Data Move will be much more performant as you are using the "real" database datamove tools .


          Heres a short viewlet/video of someone doing a SQL Server Offline Data Move. Its very similar in MySQL.
          http://www.oracle.com/technology/tech/migration/workbench/viewlets/ofdm.html

          I hope this helps
          Dermot
          • 2. Re: MySQL to Oracle - Migrate Data in SQL Developer
            636487
            Thanks for the reply. I tried the offline approach, but got an error when trying to use mysqldump utility to dump data from a table in Mysql database:

            I modified the unload_script.sh from

            mysqldump -h <HOST> -u <USERNAME> -p<PASSWORD> -T <DESTINATION_PATH> fields-terminated-by="<EOFD>" lines-terminated-by="<EORD>" <DBNAME> pca.H_TEMPLATE_SPLITS

            to

            mysqldump -h speech1.research.att.com -u pcaall -p ***** -T /home/pca fields-terminated-by="," lines-terminated-by=";" pca pca.H_TEMPLATE_SPLITS

            then run the script, but got the following message

            speech1.research.att.com$ ./unload_script.sh
            Usage: mysqldump [OPTIONS] database [tables]
            OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
            OR mysqldump [OPTIONS] --all-databases [OPTIONS]
            For more options, use mysqldump --help

            What did I miss here?

            Tracy
            • 3. Re: MySQL to Oracle - Migrate Data in SQL Developer
              Turloch O'Tierney-Oracle
              Hi Tracy,

              $ mysqldump -h localhost -u root -pPASSWORD -T /home/turloch/tmp fields-terminated-by="," lines-terminated-by=";" turloch simpletab
              works for me,
              note
              1/there is no space after the -p and
              2//home/turloch/tmp has to be writable by the mysql daemon process.
              3/simpletab has no database prefix
              4/This is mysql 5.0

              -Turloch
              • 4. Re: MySQL to Oracle - Migrate Data in SQL Developer
                636487
                I corrected the command as suggested, and use root to login instead, it worked this time. Thank you so much for the help!

                Tracy