Recently in Linkedin asked me detailing a technique for migrating MySQL databases to Oracle, which preserve the table names over 30 characters that Oracle database does not support.
This entry is not intended as a guide to how to do this in detail, but to show an idea of how to do it.
The picture below is a scratch of one procedure for how to do, trying to highlight the most relevant.
In the initial stage we have three actors:
- A MySQL database server
- An intermediate node which acts as a connector
- An Oracle database server
getDBStructures: First we extract the structure of the database tables with dumpint to a text file containing the DDL (1).
getTableName: with php, shell, or perl we partition a copy of the resulting file into multiple files, one for each of the tables containing the instruction-building.
getColumnName: with php, shell, or perl create a flat file containing the table name, column name, if null, the data type, length and position. A single file for all tables in the database.
exportDBStructure: From the first column of previous instruction generated a data dump of each table.
At this stage we have: a flat file that invented the tables and columns of the entire database, and each table DDL file and our flat file data. Compress and send to the intermediate node for processing.
In the intermediate node I use an Oracle XE database to carry out the necessary changes to adapt the MySQL database to Oracle.
translateDBStructure: Basically it is to incorporate the original table name in the COMMENT field of the dictionary table USER_TAB_COMMENTS and the name of the original column in the table USER_COL_COMMENTS dictionary (2).
There are a lot of methods to create names with one or multiple hash, so here the creativity of each is the standard for doing so.
NOTE: If you deploy this environment to a production environment of course we will not include these comments! ; )
And then incorporate those changes in the data types to create appropriate or necessary.
NOTE: As an example, I never use DATE datatype in Oracle database, are not usually necessary at data lower-level and it’s necessary in the application layers for display to users.
describeDataStructure: We can create a table or tables to transform the table and column names and likewise the data types and length. And from them we generate the files with the corresponding DDL.
The end result has to be the generation of new table creation statements, incorporating the changes mentioned above and poured into flat files.
We prove that in fact the table creation statements are compatible with Oracle and we can load the data correctly, in this case I use external tables (3) instead of sqlloader if the Oracle RDBMS version allows.
packingDBStructure: We pack and ship to the destination node server Oracle database.
putDBStructures: Execute the DDL statements for external tables to load data and final tables , processing procedures, if any to adapt to new target tables.
loadDBData: And finally, we locate the dump files for each table in the Oracle directory defined and then execute the necessary statements to insert them into their target.
Possibly there will be tools to do this process, but the experience recommend me to use this method.
I hope you have been helpful.