Currently our application is running using Oracle 11g database. We are developing newer version of the application which runs on MySQL cluster. We need to migrate the data from Oracle database to MySQL database. This is not a direct copy of database objects. In newer version of the application schema(table structure and relationships) is changed. And audit tables also needs to be copied. Is there any way to map tables, columns from old Oracle schema and new MySQL schema and apply copy so that the data will be copied?
We are thinking doing this activity in two steps. 1. Convert Oracle database to MySQL database. 2. Writing stored procedure to copy table by table.
Is there any best practices while performing this kind of database migration from one RDBMS vendor to another? Is there any open source or commercial tools available for this?