2 Replies Latest reply on Aug 27, 2014 12:40 PM by 2739116

    Migration from MySQL to Oracle SQL not working

    2739116

      Hello everybody,

      I'm new to Oracle SQL but since our MySQL database is growing and getting slower I would like to test the performance of Oracle SQL.

      So I downloaded the current Oracle Linux distro and installed Oracle Database 11g Express Edition on it.

      I logged in with Oracle SQL Developer and started to migrate the MySQL database by following each step in this tutorial.

      I had to give all privileges to the migration user (otherwise the creation and deleteion of the migration repository didn't worked) and I deactivated the migration of constraints to make the migration work.

      So I proceeded through all the steps of the tutorial and ended with a "migration successfull".

      The last step in the tutorial was to login to the migrated db (using a generated username/password combination), this was not possible.

       

      What did I already checked?

      • Logged in as the system user, I see that there's no new user created for the migrated db
      • Looking at the overview in the migration repository I can see 3 phases, not knowing if the third is showing an error or if it is not needed at all (I had 58 tables in the source db)
        • Capture 58/58
        • Convert 58/58
        • Compile 0/58

      From here on I do not know where to look for further errors.

      I still cannot use the migrated db and so I cannot proceed with my tests.

      Did I miss a something?

      I'd appreciate your help.

       

      Astaldo

        • 1. Re: Migration from MySQL to Oracle SQL not working
          Dermot ONeill-Oracle

          Hi Astaldo,

           

          First you should use SQL Developer 4.0.2 if you are not already. Its available for free on OTN

          http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index-098778.html


          There are two ways to convert tables from MySQL to Oracle.

          1) Copy To Oracle

          2) Migration To Oracle.

           

          Copy to Oracle is the simplest method. It does not involve creating a migration repository.  But it only migrates tables and data with no constraints or defaults or indexes. The data set to move should be reasonably small as well as it is being moved over JDBC.

          It may suit your purpose of trying out Oracle.

          http://dermotoneill.blogspot.co.uk/2010/11/copy-to-oracle.html

           

          Migrate to Oracle requires setting up a migration repository. But it does migrate constraints , defaults and indexes and can also provide Offline Data Move scripts which can handle the movement of large data sets.

          Heres a small script to create a new user MIGRATIONREPO and grant it the correct privileges.

           

          CREATE USER MIGRATIONREPO IDENTIFIED BY oracle;

          ALTER USER MIGRATIONREPO QUOTA UNLIMITED ON USERS;

          GRANT CONNECT,RESOURCE,CREATE VIEW ,CREATE MATERIALIZED VIEW to MIGRATIONREPO;

           

          Then using SQL Developer, create a connection to MIGRATIONREPO, right click on that connection and choose "Associate Migration Repository".

          Hopefully you have better success creating the migration repository with theses privileges.

           

          Migration Wizard On Step 8 : Target Database

          Choose Offline Mode.

          This will present you with a generation script which you can inspect before running.

          The generation script should be run using SQL Developer worksheet and choosing a sufficiently privileged user/connection in Oracle typically SYSTEM.

           

          Note, If you'd rather work with the repository you currently have you can browse your Migration navigator.

          Browse to Converted Database Objects, Right Click and choose "Generate Target".

          This will open the Migration Wizard at step 8 again. Allowing you to choose Offline Mode so that you can inspect the generation script.

           

          Hope this helps,

          Regards,

          Dermot.

          SQL Developer Team.

           

           


          • 2. Re: Migration from MySQL to Oracle SQL not working
            2739116

            Hello Dermot, thanks for your help on this.

             

            I tried all your suggestions and succeeded.

            First of all the "Copy To Oracle" Function works like a charm since I could just select all tables and copy the data to oracle. That's great.

            Second,the "Migrate to Oracle" works fine, when I use the offline mode. I was able to migrate all data to oracle and i could login with the newly created user.

             

            I deleted the user(with cascade) and started the same migration again (same settings) but with online mode. As described in my first post, migration "succeeded" but I couldn't login and there was no user created when I check with system account.

             

            So I can migrate or copy stuff to oracle and test my application.

            Still there's the question why it is not working in online mode.

             

            Thanks for your help,

            Astaldo