7 Replies Latest reply: Apr 27, 2012 8:07 AM by user5940063 RSS

    Migration from MySQl to Oracle

    Lucyna
      Hi,
      I would like to use SQLDeveloper 3.1 to migrate table from MySQL (5.3) to Oracle (11.2.0.3) .
      I can establish connections to these databases Ok. But when I use Copy to Oracle tool on one of the MySQL tables I get 'Copy to Oracle. Task failed.'
      Where can I lookup for reason of this error?

      Best regards,
      Lucyna
        • 1. Re: Migration from MySQl to Oracle
          Dermot Oneill-Oracle
          Hi Lucyna,


          You can check the log and if you run SQL Developer from the command line there maybe some information presented in there as well.
          Just check that you have the right MySQL driver version 5.04. And no other MySQL driver included.
          If that doesn't help, can you provide the table definition which is failing and we will try and replicate the issue.

          Note you can use Copy to Oracle to just create the table first. Then you can use it again to move the data.
          Try this to identify at which stage the Copy to Oracle fails.

          Thanks,
          Dermot
          SQL Developer Team.
          • 2. Re: Migration from MySQl to Oracle
            Lucyna
            Hi Dermot,

            Thank you very much for the answer.
            I try starting SQL Developer from the command line, but there are no other other messages.
            But in logging page there are lines:
            -----------------------

            SEVERE     43     26351     oracle.dbtools.migration.workbench.core.QuickMigrateInitiator$SchemaTaskListener$1     Copy To Oracle. Task failed.

            SEVERE     33     0     oracle.dbtools.raptor.controls.sqldialog.ObjectAction     initAction caused by

                 Missing class: oracle.dbtools.migration.workbench.core.QuickMigrat eController

                 Dependent class: oracle.dbtools.raptor.controls.sqldialog.ObjectAction
                 Loader: ide-global:11.1.1.0.0
                 Code-Source: /C:/sqldeveloper-3.1.07.42/sqldeveloper/sqldeveloper/extensions/oracle.sqldeveloper.jar
                 Configuration: extension jar in C:\sqldeveloper-3.1.07.42\sqldeveloper\sqldeveloper\extensions

            This load was initiated at ide-global:11.1.1.0.0 using the loadClass() method.

            The missing class is not available from any code-source or loader in the system. at oracle.classloader.PolicyClassLoader.handleClassNotFound(PolicyClassLoader.java:2190)
            ----------------------------


            Greetings,
            Lucyna
            • 3. Re: Migration from MySQl to Oracle
              Gary Graham-Oracle
              Hi Lucyna,

              The missing class should be stored in the following, so make sure that jar file is present:
              C:\sqldeveloper-3.1.07.42\sqldeveloper\sqldeveloper\extensions\oracle.sqldeveloper.migration.jar

              Presumably you installed into an empty directory rather than overwriting an old install and renaming it. You might also verify in your Tools|Preferences|Extensions that all Oracle SQL Developer - Migrations extensions are checked off. Especially for Core and MySQL.

              Regards,
              Gary
              SQL Developer Team
              • 4. Re: Migration from MySQl to Oracle
                Lucyna
                Hi Gary,

                I installed in an empty directory and I have this jar file in the extension catalog.
                I try to migrate table without data and it migrate OK.
                May be this is a data problem.
                The MySQL table has DATE column with DEFAULT '0000-00-00 00:00:00' and NOT NULL varchar column with DEFAULT ''

                When I migrate all database with repository I can see migration errors in MIGRLOG table, but when I migrate only one table I cannot see any errors.

                Greetings,
                Lucyna
                • 5. Re: Migration from MySQl to Oracle
                  930016
                  Not sure if this is related but I am also trying to migrate from MySQL to Oracle. I setup the connections via migration (All my extensions are enabled) however it fails with a popup

                  "Migration actions have failed. Check the mgration reports for details. : ORA-01400: cannot insert NULL into ("UAZ"."MD_PROJECTS"."ID")

                  When I look at logging page I get the following.

                  Illegal Lock State: bind requires connection lock to be held. oracle.dbtools.raptor.controls.grid.OracleRSTModel.prepareStatement(OracleRSTModel.java:89) called oracle.dbtools.db.DBUtil.bind(DBUtil.java:1404) without it

                  Issue running BRIDGE command

                  Warning, unhandled exception: ORA-00942: table or view does not exist

                  Illegal Lock State: bind requires connection lock to be held. oracle.dbtools.raptor.controls.grid.OracleRSTModel.prepareStatement(OracleRSTModel.java:89) called oracle.dbtools.db.DBUtil.bind(DBUtil.java:1404) without it

                  Issue running BRIDGE command

                  initAction caused by

                       Missing class: oracle.dbtools.migration.workbench.core.QuickMigrat eController

                       Dependent class: oracle.dbtools.raptor.controls.sqldialog.ObjectAction
                       Loader: ide-global:11.1.1.0.0
                       Code-Source: /H:/sqldeveloper/sqldeveloper/extensions/oracle.sqldeveloper.jar
                       Configuration: extension jar in H:\sqldeveloper\sqldeveloper\extensions

                  This load was initiated at ide-global:11.1.1.0.0 using the loadClass() method.

                  The missing class is not available from any code-source or loader in the system. at oracle.classloader.PolicyClassLoader.handleClassNotFound(PolicyClassLoader.java:2190)


                  Any idea?
                  • 6. Re: Migration from MySQl to Oracle
                    user5940063
                    MySQL and Oracle database systems are similar but quite different. For example, Oracle does not have auto_increment property. You have to create sequence object to implement it.

                    Also, MySQL differs from Oracle in the way it handles default values for a columns that do not allow NULL value. If you omit value for not-NULL column in MySQL, it will attempt to insert default value for this column if any. In Oracle data must be provided for all columns that do not allow NULL value, it does not generate a default value for columns that have the NOT NULL definition. And this could be reason of ORA-01400 error.

                    In view of these facts I suggest you to use special tools that can migrate MySQL data to Oracle server with indexes and all necessary attributes correctly. For example, take a look at this one: http://www.convert-in.com/sql2ora.htm

                    Edited by: user5940063 on 27.04.2012 6:04
                    • 7. Re: Migration from MySQl to Oracle
                      user5940063
                      Just forgot to mention that these two database systems also have different limitations for object names. In MySQL an unquoted identifier may begin with a digit, and double quotation marks are allowed in a quoted identifier. However, neither of these is allowed in an Oracle identifier. This could be reason of ORA-00942 error reported above.

                      Edited by: user5940063 on 27.04.2012 6:05

                      Edited by: user5940063 on 27.04.2012 6:06