import Oracle dump file to Oracle express edition.

marrowyung
marrowyung used Ask the Experts™
on
hi,

I have the oracle 12c enterprise DB dump files to try importing to MariaDB/MySQL in order to see how many data and table structure can be convert to MySQL/MariaDB format.

I am not sure if it is a good idea to setup Oracle express and then import the dump files to it so that migration from Oracle can be easier, any idea?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What should be the reason behind that path?!
Why the detour using Oracle Express?!
Hi marrowyung,

I'm not aware of any tool(s) that will import an Oracle dump file into MySQL.  

And there are significant differences in the SQL.  You've got a lot of work ahead of you....


Kent
There are tools and ways to do this, like
https://www.quora.com/Is-it-possible-to-just-import-an-Oracle-dump-into-MySql-The-dump-was-taken-using-expdp
http://www.sqlines.com/oracle-to-mysql
https://stackoverflow.com/questions/2479500/migrate-from-oracle-to-mysql
BUT keep in mind that if you're using Oracle for more than just plain data storage, you WILL run into problems/issues, just like Kent mentioned.
If it's just data migration, it's just plain SQL in the end ;-)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark GeerlingsDatabase Administrator
Commented:
I agree with the others that using Oracle Express may not add any value to this process.  But if you do want to use Oracle Express (or any other Oracle database) to import a dump file that was created on a different Oracle database, the version of Oracle where you run import (or Import DataPump) must be equal to or higher than the version of Oracle that produced the dump file.
the version of Oracle where you run import (or Import DataPump) must be equal to or higher than the version of Oracle that produced the dump file.

No! For the corresponding compatibility matrices please take this MOS Note into consideration:  Doc ID 132904.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=159538756290723&id=132904.1&_adf.ctrl-state=lzc867vmd_67
Mark GeerlingsDatabase Administrator

Commented:
OK, If I add this qualifier clause "[the Oracle export client]" then this statement is true:

The version of Oracle where you run import (or Import DataPump) must be equal to or higher than the version of [the Oracle export client] that produced the dump file.

But, it may be somewhat unusual to have an earlier version of the export client available than what the source database actually runs on.  So, under "normal" or at least common circumstances, the statement I made earlier is true.
You only confuse people with such statements/comments! As a matter of fact, you can export/import from a higher version into a lower one! Full stop! Most of the people here asking for help regarding exp(dp)/imp(dp) get really confused in first instance  if you "knock them out" with "Oracle DB version vs Oracle export client version vs .....)!
Mark GeerlingsDatabase Administrator

Commented:
In my experience, most people who ask questions like this have only one version of Oracle software available to do an export: the version the DB runs on.
But yes, it is sometimes  possible to do an export with a lower version of Oracle than what the DB runs on, *IF* you happen to have a lower version of the DB utilities available *AND* if that lower version is listed in the compatibility matrix you referred to.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Kent Olsen,

"And there are significant differences in the SQL.  You've got a lot of work ahead of you...."
i know , u know , that's why I list MariaDB too as since v10.3x, it has sql_mode= oracle and someone from oracle can even write PL/SQL in oracle.

Alexander.

this link:  https://www.quora.com/Is-it-possible-to-just-import-an-Oracle-dump-into-MySql-The-dump-was-taken-using-expdp

is funny, it said MySQL SSIS.  MySQL do not have SSIS. MySQL and MariaDB do not have ETL tools.

Mark,

"The version of Oracle where you run import (or Import DataPump) must be equal to or higher than the version of [the Oracle export client] that produced the dump file."

guess so.

"But yes, it is sometimes  possible to do an export with a lower version of Oracle than what the DB runs on, *IF* you happen to have a lower version of the DB utilities available *AND* if that lower version is listed in the compatibility matrix you referred to.
"
good statement too, and I will try.

it seems that I need to setup an Oracle and import that DMP files and use tools like SQL line to migrate data and schema to MariaDB/MySQL.

this is the conclusion ?

it is sad that SQLline can't read from DMP files directly !

what if the DMP file is from Oracle on Linux, can I import it to Oracle on windows ?
what if the DMP file is from Oracle on Linux, can I import it to Oracle on windows ?
Yes
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
it seems there is no express edition on oracle 12c, am I right?

and can Oracle 12c enterprise edition dump import to an oracle 12c express edition?
it seems there is no express edition on oracle 12c, am I right?
No, there isn't a 12c Express

and can Oracle 12c enterprise edition dump import to an oracle 12c express edition?
In general, yes, but there are some restrictions, e.g. 11GB user data limit, no partitioning (if you use that in your EE) etc...
Considering just the "raw data", there should be no problem as long as its overall size does not exceed 11GB.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"No, there isn't a 12c Express"

so what should I do ? use 11g R2 express?

but the DMP is from 12c enterprise.
Yes, it's 11gR2:
https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

If the overall user data to be imported is less than 11GB, I would give it a shot ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
My DMP is 3GB only but the concern is can  I import that to 11gR2 express? it is a DMP file exported by 12c
marrowyungSenior Technical architecture (Data)

Author

Commented:
any step by steps procedure on how to import to an express 11gr2 ?
Maybe you should consider using the VERSION parameter when doing the export from the source database (12), see here for further infos: https://docs.oracle.com/database/121/SUTIL/GUID-BAA3B679-A758-4D55-9820-432D9EB83C68.htm#SUTIL110

Overall, the export/import process should be a no-brainer ;-)
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all,

installed an oracle XE, but seems sqlplus can't connect to it but web interface can!

I have rise another question on it. please take a look.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial