PROBLEM: using SQL*Developer's Migration tool to migrate from MySQL to Oracle, I have successfully captured, converted, and generated MySQL tables into an Oracle database, but the Data Move part will not work, I get error in the SQL Developer logging page:
The message repeats for all 66 tables I am trying to migrate.
QUESTION: how can I get this data move to work successfully?
Running on Mac OS X Version 10.7.3
MySQL Version 5.1.44 running on Linux
Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production
PL/SQL Release 184.108.40.206.0 - Production
CORE 220.127.116.11.0 Production
TNS for Linux: Version 18.104.22.168.0 - Production
NLSRTL Version 22.214.171.124.0 - Production
Just so you know the Data Move operation actually performs 3 different actions in the following order:
1. Disable all constraints (FKs,PKs).
2. Move data using multiple streams so that it can migrate more than 1 table at any time.
3. Enable all constraints (FKs,PKs)
It appears that for some reason SQL Developer is failing on step 1 where it is trying to disable the constraints. Have you tried to generate the data move scripts for an off-line data migration?. You can look at the disable constraint scripts and run them manually before attempting the on-line data migration again. Hope that works.
I was trying to move data into a different schema/username than the original source MySQL username.
SQL*Developer's Migration tool seems to want to create source user/schema in the target database, then tries to disable constraints for that user's version of the tables, but when the disabling constraints fails (because the source username does not), then move of data stops and does not proceed any further.
I did some experimenting with a local windows database (oracle 11.1) that I have full DBA controls over and I allowed the SQL*Developer's Migration tool to create the target username/schema, then the migration worked better and the "Failed to disable constraints: Data Move" errors vanished, however, not all the tables received data, some table had zero rows in them even though the source table has thousands of rows.
When capturing data, I did change some of the MySQL dataytpe to Oracle datatype mappings, specifically any MySQL text column that mapped to Oracle CLOB I changed to map to Oracle VARCHAR2(2000) because I intend to read these migrated tables over a DB LINK, and we all know CLOBs cannot be selected over DB LINK unless you CTAS, INSERT, or UPDATE via subquery...which I don't want to do. I am confident the source data in the columns will not exceed the size of a VARCHAR2(2000), therefore CLOB not needed. These tables where I changed the datatype mapping are curiously the tables that received zero data during the data move.
1.) When performing capture and move, where in SQL*Developer interface can I specify the target schema name that is different than the source schema name? I need to do this so the generated DDL migration and data move scripts will generate SQL for my preferred target username, and not the default source schema username.
2.) For my last partially successful migration attempt where some of the tables got populated and some did not, where can I find the error logs indicating the problems for the tables that contain zero rows? NOTE: when data move completed, there was no logging page with errors in the SQL*Developer interface, just a message that stated Data Move completed successfully, yet many tables received no data.
1. Yes you can specify a different schema (need to use the connection for it). But you will have to use the same repository that you used for migration so that it can pick up the mappings for the source database.
2. For the Data move issues, you can try the followiing:
a. For moving the failed tables one by one i.e. right click on the table in the source database and choose 'copy to oracle' and then choose only data migration (append mode).
b. You can also try changing the default DATE formats for the source database in the SQL Developer to the actual one used in the source database. The default it uses is mm/dd/yyyy which may not be the right one in your environment( --> Preferences-->Migration-->Data Move options). Some times this also causes problems when migrating data
From my personal experience, SQL Developer creates a log file in an XML format under <Documents and Settings>\localuser\applications\... directory. So you will have to dig a bit more to find out the log file which will contain the actual error.
I don't believe SQL Developer uses DB Links as it uses thin JDBC Drivers for db connections. It opens up JDBC connections to the source and target databases and then copies over the data.
I'll try the different target schema once I have the Data Move working with the original source schema name that the migration tool wants to use at the target.
I tried defining the default data format of MySQL source to dates could be properly interpretted, but I don't know if it is truly working because of another blocking problem below.
I tried your "Copy to Oracle" suggestion and discovered a new problem. There is a column in the MySQL source table that has a NOT NULL constraint, but contains an empty string instead of nulls. In MySQL, and empty string ('') is not the same as a null string, but this is not the case for Oracle, if you try to insert empty string ('') in Oracle, it is the same as a null. Since the column got migrated as NOT NULL into Oracle, when I attempt to perform "copy to Oracle", I get NOT NULL constraint violated because migration tool passes an empty string ('') which is the same as null to Oracle. Is there any way to change mapping for this column to specify that is it NULLABLE when created in Oracle?
I know I could disable the NOT NULL constraint and try the data move again, but I have over 60 table and 1000 columns, I'd rather define the migration method in the tool, then perform move.
May be the Not NULL constraint issue is obstructing the data migration for these tables when you do a full database migration also. If you encounter it when performing a 'copy to oracle' for a single table then you will run into the same issue with a full database migration.
The handling of zero length strings is something that you run into with pretty much every other database when migrating to Oracle. SQL developer cannot make the determination that a character column (CHAR/VARCHAR2) will contain NULLs so it has to disable the NOT NULL constraints. On the other hand, you can simply do a search and replace in SQL Developer (regular expression) to remove the NOT NULL constraints in the DDL before generating the target Oracle schema. So just don't create the schema on-line during the migration instead choose the off-line mode and modify the DDL script. After that in the preferences under 'Migration'-->Data Move options , you will see the first option to treat '' as NULL in Oracle.
You will have a bigger issue if the column is part of a primary key constraint. Then you will have to come up with some default values for the column instead of having NULLs.
Since the Data Move appeared to be working better against my local windows Oracle 11.1 database, I thought I would try creating a different schema username (like I have in my production linux database) and see if Data Move would work successfully to schema with a different name that source schema name. The tables got created, but no data got moved.
SOURCE, SOURCE_SCHEMA, TARGET_SCHEMA, TABLES_MIGRATION, DATA_MOVE
MySQL, film_db, film_owner(linux), tables migrated, failed no data moved
MySQL, film_db, film_owner(Windows), tables migrated, failed no data moved
MySQL, film_db, film_db(Windows), tables migrated, data migrated few tables w/o data
In above scenarios, when data move failed, it was due to error that started this entire forum thread: Failed to disable constraints: Data Move. I think this is because the tool is trying to disable constraints specifically for the film_db username's tables, but target schema doesn't match source(film_owner), then you get failure to disable constraints and data move fails too.
When the source schema and target schema name matched, then Data Move worked for almost all the tables. The tables that failed were due to NOT NULL column receiving empty string data from MySQL. If I disabled NOT NULL constraints on target table and tried the "copy to Oracle" in SQL*Developer on source MySQL table and select APPEND option, then the table data got moved from MySQL to Oracle. However, if I disable the NOT NULL constraints and attempted the Data Move, the Data Move still did not work and displayed no error in the SQL*Developer interface, which is a shame because that means to get the data from MySQL to Oracle I must use the "Copy to Oracle" option individually for each table that did not get migrated via the Data Move, and it also means that Data Move can not be used to migrate all the table data in one shot, I get some tables with Data Move, then must manually "Copy To Oracle" remaining tables. If I wanted to use Data Move as a way to refresh data from MySQL into Oracle on a regular interval for a month or two, then I would never get all the tables and must use "Copy to Oracle" to make the data migration complete.
I don't think Data Move from MySQL to Oracle works unless the source and target schema names are the same.
If you have try this. Before generating the Oracle schema, go to the 'converted databases' in the 'Migration Projects' Navigator window (left hand side, bottom window) and rename the schema to the new name that you want. Then generate the Oracle schema and try the data migration. It should work.
I went to Migration Projects, navigated down to 'Converted Databases Objects', located the schema name, but I cannot rename it. Double clicking on schema name just expands and collapses the folders underneath. Right mouse click over the schema name reveals only one option: Delete.
Ok my apologies. Looks like we disabled the schema re-naming capability in 3.0+. I just tested it in my local box. We can only rename the project now. So that means you have to have the same schema name to be able to do the online data migration.
If you want to experiment a bit further then you can update the schema name in MD_SCHEMAS table in the migration repository. Be careful to update the row related to the conversion process of the actual project (NAME column) and also update the connect string properly (NATIVE_SQL column if it is populated.
WARNING: It is not a documented method of renaming schema so any potential sideaffects, you will have to drop the project and redo the whole process.
Thanks the suggestion from the BLOG. I renamed the schema under Object Naming, performed the migration to new schema owner (which worked before anyway), and the tables get created in new schema in Oracle. When I look at Converted Database Objects, under MySQL that schema now appears to be new target schema name, not the original schema in name that truly exists in MySQL, so it appear that the conversion to new schema name is working.
However, when I go to perform the data move, no tables receive any data, all tables have zero rows moved, and I get two errors:
MESSAGE: Connection to null not available
MESSAGE:Failed to disable constraints: Data Move
so I am back to original error that started this entire chain.
At this stage, I think I am going to ask my DBAs to just create a matching schema name in target Oracle database, then perform the migration and data move, and use 'Copy to Oracle' feature for any tables that do not get receive rows from Data Move.
1/The first post said you were using SQLDeveloper 3.0 - we have released 3.1 - try that
2/Offline capture will show you all the commands it uses in terms of data download and SQLLoader upload - could show the issue.
Do you have all the privileges including 'alter any table' privilege? (not having that would stop the enable/disable constraints)
(In fact check for all permissions, search the 3.1 help for the following words/paragraph heading: Requirements for Creating the Destination Oracle Objects)
3/If you have a small reproducible test case please post it so I can log a bug if appropriate.