import Oracle dump file to Oracle express edition.

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?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
What should be the reason behind that path?!
Why the detour using Oracle Express?!
0
Kent OlsenDBACommented:
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
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 .....)!
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
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 ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
what if the DMP file is from Oracle on Linux, can I import it to Oracle on windows ?
Yes
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
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?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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.
0
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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
0
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
any step by steps procedure on how to import to an express 11gr2 ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.