Migrating schema from Oracle 11g Enterprise to 10g Express Edition
Here's the situation: you are working in an environment that uses Oracle Enterprise 11g, you want to do some development on your personal computer using your own database, and for whatever reason you cannot simply install a full-blown non-free version of Oracle on your laptop.
Your best free option is to install Oracle 10g XE, especially if you are running Ubuntu because installation is a snap.
http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html
Of course you should know that XE is stripped down, so read up and make sure that you won't be losing features that you'll absolutely need to run your production or test database.
The first step is to export your database. If you're like me an you attempt to simply export using the exp tool that ships with 11g, you will discover that there are incompatibilities between version which will prevent you from importing with 10g XE.
The easy solution? Just use the 10g version of the tools that ship with XE to dump the 11g database. In my environment, everything is Linux, so my quick-and-dirty approach was to create a temp directory on the 11g server and scp the 10g version of exp over to it.
Of course I don't have the needed 10g libs on the 11g server, so I just copy the all Oracle libs over to the same directory.
Now I ssh over to the 11g server and set up my environment to add the current directory (temp) to the LD_LIBRARY_PATH.
Now to dump...
Now copy the resulting export.dmp file back to your target computer, the one running 10g XE. (You know how to do it!)
Okay, now cross your fingers and attempt the import, hoping to succeed though you just know that critical 11g features won't be supported on XE and will probably cause all your effort to be for naught. But do it anyway.
In my case I now yell "Doh!" because I get the same damn error I had before. Did I mention the error? Yes, the one I got after importing initially, the export file created using 11g's exp. This is what I see on my development laptop.
I check the head...aha I see my error. I was still using the darn 11g exp because I failed to put the 10g exp on the path on the 11g server before I ran exp.
Oh, and in case you are thinking you might just break out a HEX editor and change the EXPORT version in the header to the correct version, it won't work. Yes I thought of that too.
Okay, back to the 11g server to re-run. This time notice the ./ before exp to pick up the correct version of the executable.
Once you "get it right" move the export back to your development box. In my case, I had to create matching tablespaces and users before the importing with success. Once that was done, I ran the following...
I believe my warnings are due to character set incompatibilities, and since I'm set to blow up this copy of the database in a development environment anyway, I'm not going to research them now.
I fired up SqlDeveloper, connected to my 10g XE instance, and selected some data. No problem! Who says you can't move an Oracle Enterprise 11g database to Oracle 10g XE?
Your best free option is to install Oracle 10g XE, especially if you are running Ubuntu because installation is a snap.
http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html
Of course you should know that XE is stripped down, so read up and make sure that you won't be losing features that you'll absolutely need to run your production or test database.
The first step is to export your database. If you're like me an you attempt to simply export using the exp tool that ships with 11g, you will discover that there are incompatibilities between version which will prevent you from importing with 10g XE.
The easy solution? Just use the 10g version of the tools that ship with XE to dump the 11g database. In my environment, everything is Linux, so my quick-and-dirty approach was to create a temp directory on the 11g server and scp the 10g version of exp over to it.
cd $ORACLE_HOME/bin
scp exp me@11gserver:~/temp
Of course I don't have the needed 10g libs on the 11g server, so I just copy the all Oracle libs over to the same directory.
scp /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/* me@11gserver:~/temp
Now I ssh over to the 11g server and set up my environment to add the current directory (temp) to the LD_LIBRARY_PATH.
export LD_LIBRARY_PATH=.:/usr/lib32:/usr/local/lib32
Now to dump...
exp SYSTEM/stuff@11gserver:1521/yeah.stuff.com FILE=export.dmp OWNER=me_the_owner
Now copy the resulting export.dmp file back to your target computer, the one running 10g XE. (You know how to do it!)
Okay, now cross your fingers and attempt the import, hoping to succeed though you just know that critical 11g features won't be supported on XE and will probably cause all your effort to be for naught. But do it anyway.
In my case I now yell "Doh!" because I get the same damn error I had before. Did I mention the error? Yes, the one I got after importing initially, the export file created using 11g's exp. This is what I see on my development laptop.
imp SYSTEM/xx FILE=/home/me/export.dmp FULL=y
Import: Release 10.2.0.1.0 - Production on Fri Nov 6 08:32:09 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
I check the head...aha I see my error. I was still using the darn 11g exp because I failed to put the 10g exp on the path on the 11g server before I ran exp.
head export.dmp
EXPORT:V11.01.00
DSYSTEM
RUSERS
Oh, and in case you are thinking you might just break out a HEX editor and change the EXPORT version in the header to the correct version, it won't work. Yes I thought of that too.
Okay, back to the 11g server to re-run. This time notice the ./ before exp to pick up the correct version of the executable.
./exp SYSTEM/stuff@11gserver:1521/yeah.stuff.com FILE=export.dmp OWNER=me_the_owner
Once you "get it right" move the export back to your development box. In my case, I had to create matching tablespaces and users before the importing with success. Once that was done, I ran the following...
imp SYSTEM/xx FILE=/home/me/export.dmp FULL=Y
...
lots of output here, such as
row rejected due to ORACLE error 12899
...
Import terminated successfully with warnings.
I believe my warnings are due to character set incompatibilities, and since I'm set to blow up this copy of the database in a development environment anyway, I'm not going to research them now.
I fired up SqlDeveloper, connected to my 10g XE instance, and selected some data. No problem! Who says you can't move an Oracle Enterprise 11g database to Oracle 10g XE?
Comments