After we tested our JDBC connection to Firebird and then migrated from Firebird to MySQL we have just one more step to do:
Migration from MySQL to Oracle using SQL Developer Migration Workbench
For migrating MySQL to Oracle we need to have:
Database installation should be very easy for this particular version, being the only one with a non-Java installer.
SQL Developer installation consists in unzipping the archive.
Now the steps:
1. Open SQL Developer and add MySQL Plugin. This is actually the ODBC driver for MySQL, the well-known MySQL connector. Without this you cannot connect to MySQL from SQL Developer.
2. Create a connection to Oracle with a powerful user (preferable SYS or SYSTEM)
3. Create another Oracle user/schema to hold the migration repository (things needed for the migration process)
4. Grant this user with CONNECT and RESOURCE roles
5. Grant the user with CREATE VIEW system privilege and click Apply
6. Create a connection to the recently created user/schema
7. Transform this empty schema into a migration repository.
8. This will create in the empty schema some internal objects to hold all the migration data. It’s not necessary to deal with this objects directly
9. Create a new connection to MySQL
10. Expand the connection, choose the desired database to migrate and click on “Capture Schema”
11. A window with captured objects will appear. You can also see what was captured by going to the “Captured Objects” pane on the left.
12. Go to Captured Objects pane on the left and choose “Convert to Oracle Model”
13. Tune the default data mappings if needed.
14. A window with converted objects will appear. You can also see what was captured by going to the “Converted Objects” pane on the left. It’s very important not to have any errors at this stage. Having errors at one object means that the object will not appear in the DDL and will not be created in the Oracle Database.
15. Go to Converted Objects pane on the left and choose “Generate”. This will generate a SQL script for creating all the objects in Oracle. The script will not be run yet, but just diplayed.
16. Choose the connection under you want to run the script. This should be a user that have the permission to create other user, so this is recommended to run in under the first connection (SYS or SYSTEM)
17. Run the script by pressing F5 or by using the appropriate icon
18. Create a new connection with the recently created user. This user has now all the objects migrated from MySQL, but no data.
19. Go to Converted Objects pane on the left and choose “Move Data”.
20. Select source and target connections. Make sure that the source is your MySQL connection and target is your recently created Oracle user (step 18).
21. Wait for Data Move to finish. This will work on separate threads. If the database is too big, you can tune the COMMIT interval.
22. Have a look at the Oracle migrated objects.
That was all!