the way to downsize Oracle database to MySQL

hi,

please assist on this too;

https://www.experts-exchange.com/dashboard/#/questions/29257049

ste5an ,

here is no such thing of downsizing a database from one RDBMS to another RDBMS. Cause they are all more or less incompatible. It is a migration. '

yes

There are more or less objects in your database, which must be rewritten to work in the other system. E.g., stored procedures.

SQL logic ,yes ! the oracle logic

https://docs.oracle.com/cd/E17781_01/server.112/e18804/impexp.htm#BABFDCDC to create the basic scripts to start working and any normal ETL tools to transfer the data.

you mean create the scheam first and then use ETL to transfer data?

but 

:https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=28326583812160&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=1477151.1&_afrWindowMode=0&_adf.ctrl-state=x83wtd2kr_4

is the MySQL can read the dump file from Oracle ?

gr8gonzo,

what UI tools allow us to transfer data and schema from Oracle to MySQL, SQL developer
?

Very often a database dump/export is a huge SQL script ..... (with all kinds of embedded options).

yes

YMMV.

what is it ?

So exporting a database and then touching up the file to remove incompatibilities MAY be an options.

The DB dump already the export, right?

YMMV -- Your Milage May Vary (Standard disclaimer that you may have different experiences... and those are to be expected due to "local" circumstances.  ).

Dump DB .. Mostly yes.

I will check how many logic inside oracle and see if we cna simply rewrite that! but only oracle dumpe can exprot data from oracle to MySQL, any other free tools can do

I only know paid tool

hi Tomas ,

MariaDB version 10.3 and newer have one feature additionally compared to MySQL and that is the sql_mode = 'ORACLE' session/global system variable that allows the server to understand subset of Oracle's PL/SQL language.

This one is long time ago, good !

 Packages using native Oracle packages will need to be rewritten to use compatible MariaDB functionality if it exists.

yes

https://www.symmetricds.org/
https://github.com/osalvador/ReplicaDB

free?

and they are replicating between different database platform? 

slightwv,

The only thing that can read an Oracle dump file generated by exp or expdp, is Oracle's corresponding imp programs.  They are in a binary format.

so this one is not going to import to mySQL anyway right?

That way, the data being transferred was never written to disk.  We also set these up to run in parallel so we had 5-10 tables being copied over at the same time.

not sure why data is not written to disk but in CSV files which on the disk ?

may I know how to setup parallel run, you mean only CSV file copies to postgresql  ?  or export to many CSV in parallel?

MySQL has a program to load data from a CSV so you could always do something similar.

so transfer data to mysQL in CSV too ? no other better method right?

 is importing from CSV is a bulk insert operation ?

>>so this one is not going to import to mySQL anyway right?

Correct since MySQL isn't Oracle's imp utility.

>>not sure why data is not written to disk but in CSV files which on the disk ?

Read up on named pipes:
https://www.linuxjournal.com/article/2156

The process that creates the CSV file feeds the pipe and the process that loads the CSV data reads from the pipe.  No need to write the CSV to disk first.

Unless some server cannot see both databases.

>>no other better method right?

I have no idea what options are out there.  I just wanted to point out that exp/imp using Oracle utilities wasn't going to work.

Then I wanted to share what we did when moving from Oracle to Postgres.

I'm sure there are tools out there to move the data if you go looking.  I cannot recommend any since I never went looking.

>> is importing from CSV is a bulk insert operation ?

Guess that depends on the individual tool doing the import?

Hi,

Both ReplicaDB and SymmetricDS are Open Source so they are free to use and are both Cross Platform replication tools. That is, they replicate between different databases and have a wide list of supported databases. See their websites for more info.
And by using these tools you can setup replication that replicate tables in parallel that speeds up the replication to optimal data transfer speed.
That is way faster than to export to a dump/csv file and then transfer the file to target database host and import it.

https://osalvador.github.io/ReplicaDB/docs/docs.html#22-controlling-parallelism
https://www.symmetricds.org/about/features

Using replication tools such as these instead of traditional export/import dmp/csv files makes the transition to a new database easier and you can gradually and safely switch an individual application or large system from one database to another.
At work we used IBM InfoSphere Data Replication ( a paid version ) to transfer very large database and system (24/7 financial system) from Db2 12 z/OS to Oracle 19c on AIX.
And last weekend we did the final switchover on the system to Oracle. From the start of the project to the finish line (the point of no return ) we did not have any system or application downtime or any other interruption. Just simple and smooth transition. :)

Best regards,
    Tomas Helgi

hi,

And by using these tools you can setup replication that replicate tables in parallel that speeds up the replication to optimal data transfer speed.

so by this mean, the tools will also automatically search the right data type of the target tables?

https://osalvador.github.io/ReplicaDB/docs/docs.html#22-controlling-parallelism
https://www.symmetricds.org/about/features

I thought you are saying this one is about using CSV, may I know any really working procedure/URL using CSV for me to export oracle table and data from oracle to MySQL ?

At work we used IBM InfoSphere Data Replication ( a paid version ) to transfer very large database and system (24/7 financial system) from Db2 12 z/OS to Oracle 19c on AIX.

IBM one should be much power than the 2x free one you suggest, and may I know what is it about ? in terms of .. ?

From the start of the project to the finish line (the point of no return ) we did not have any system or application downtime or any other interruption. Just simple and smooth transition. :)

And this is the difference ? free tools you said about must have a lot of down time ?

so here means using tools ,even free one, much better than CSV export and import, right ?

slightwv 

Correct since MySQL isn't Oracle's imp utility.

ok, import and export is not working as the exprot and import utility is not working with the format from others. make sense.

So finally is the 

1) 2 x free tools suggested by Tomas 

2) commerical tools suggested by Tomas 

3) CSV slowest 

will works fine ?

slightwv,

Read up on named pipes:
https://www.linuxjournal.com/article/2156

The process that creates the CSV file feeds the pipe and the process that loads the CSV data reads from the pipe.  No need to write the CSV to disk first.

so the name pipes can work from any platform to any platform and not depends on DB platform too?

and this article is NOT saying the names pipes also create the CSV automatically and transfer to target platform and load to target DB automatically ?

Named pipes is just a way to get data from one program to another without writing the data to disk.  That is all it does.

Named pipes are primary a unix-filesystem-based "connection-point" (Without storage).
The "connection-points"  look like a filename, read/write works like a file.
It allows a process to connect to another process with out the need of a common parent like in:
ls -l | grep whatever
the commandline with a | needs to be started in a single process as "Anonymous" pipe. Where both children inherit each one an end of a pipe.
This also means processes with dissimilar UID are able to use the pipe (if protection alllows it).

At my work we use replicaDB in other projects with large data transfer between databases which has not had any downtime yet.

by this:

https://github.com/osalvador/ReplicaDB 

I read the readme, is it only for linux/unix/aix?

Tomas,

I have a look on https://github.com/osalvador/ReplicaDB 

is that tools do not support Sybase replication to other DB, e..g sybase SAP to Oracle / MS SQL, Azure SQL? 

hi,

Supported databases are clearly mentioned here: https://github.com/osalvador/ReplicaDB#compatible-databases

yes, exactly what I can see, no sybase at all!

sybase has little support at all, e..g from Sybase to Oracle, it seems only SQL developer can do that job....  

actually I am not sure what else is the industrial usage Sybase is,,, it only work with powerbuild for report purpose.... and nothing more... 

hi,

any more update for me ?

I believe the question asked:  Move from Oracle to MySQL has been answered.  I have nothing more I can add.

tks all.