Home » RDBMS Server » Server Utilities » Can't recreate 9i to 10g with EXP/IMP
Can't recreate 9i to 10g with EXP/IMP [message #301225] Tue, 19 February 2008 13:02 Go to next message
Archanfel
Messages: 3
Registered: February 2008
Junior Member
I've been trying to duplicate a database from 9i to 10g. I'm only interested to recreate the users, roles, database links,
etc. The (production) server that has 9i is outdated, and the 10g is on a new server. I've created a new database on the 10g server, and created all the tablespaces that reflects the 9i server. Next, I used the EXP command on 9i:
exp system/password FILE=allusers.DMP LOG=allusers.log OWNER=(*list of users*) COMPRESS=N ROWS=N DIRECT=Y CONSTRAINTS=Y INDEXES=Y;


In the 10g server, I used the import command:
imp system/password FULL=Y CONSTRAINTS=Y INDEXES=Y FILE=allusers.DMP LOG=allusers.log ROWS=n;


When I try the IMP command in 10g, I get an error that the users doesn't exist. That only way I can think of to resolve this is
if I export the entire database from 9i. Wouldn't that conflict with the 10g SYSTEM tablespace? IE:
exp system/password FULL=Y FILE=full.dmp LOG=allusers.log COMPRESS=N ROWS=N DIRECT=Y CONSTRAINTS=Y INDEXES=Y;


I even tried to import one user at a time, and it didn't work. IE:
imp system/password FROMUSER=user1 CONSTRAINTS=Y INDEXES=Y FILE=allusers.DMP LOG=allusers.log ROWS=n;


I'm stuck. Please help.
Re: Can't recreate 9i to 10g with EXP/IMP [message #301226 is a reply to message #301225] Tue, 19 February 2008 13:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Use lower version to export and use the same lower version to import.
>>if I export the entire database from 9i. Wouldn't that conflict with the 10g SYSTEM tablespace? IE:
Well,
You are not going to import system user. right?
Just precreate the tablespaces as in source.
and import only the custom schema (not sys/system etc).
>>imp system/password FROMUSER=user1 CONSTRAINTS=Y INDEXES=Y FILE=allusers.DMP LOG=allusers.log ROWS=n;
You are missing "touser" option. You must pre-create the user.

Search the forums. Way too many examples.

[Updated on: Tue, 19 February 2008 13:11]

Report message to a moderator

Re: Can't recreate 9i to 10g with EXP/IMP [message #301247 is a reply to message #301226] Tue, 19 February 2008 16:36 Go to previous messageGo to next message
Archanfel
Messages: 3
Registered: February 2008
Junior Member
>>Use lower version to export and use the same lower version to >>import.
Sorry. I'm new to Oracle. When you said to use the lower version, do you mean I should export from 9i exp utility and import from 10g's exp utility. If not, how do I lower the version?

>>if I export the entire database from 9i. Wouldn't that conflict >>with the 10g SYSTEM tablespace? IE:
>Well,
>You are not going to import system user. right?
>Just precreate the tablespaces as in source.
>and import only the custom schema (not sys/system etc).
You mean after I pre-create the tablespaces and users, I can use the full export dump I've made and use this command:
imp sys/password IGNORE=y FILE=C:\full.dmp log=C:\full.log rows=n constraints=y indexes=y FROMUSER=(*list of users*) TOUSER=(*list of users*);
Re: Can't recreate 9i to 10g with EXP/IMP [message #301248 is a reply to message #301247] Tue, 19 February 2008 16:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> import from 10g's exp utility. If not, how do I lower the version?
No.
Use 9i imp in the source machine.
Just edit your tnsnames.ora, add entries to talk to 10g database and use
imp user/pass@10gdatabase

I meant,
precreate the tablespaces and do a full import (by default the full import will try to create
tablespaces. if the path is different, it will fail and all following will fail).

You may have a little more control if you import user by user.
So, precreate tablespaces and users (assigned default tablespaces to the appropriate users) and
do import by users.
imp user/pass fromuser=user1 touser=user1 rows=n etc

>>Sorry. I'm new to Oracle.
As said, Just search this forum. Too many examples are already posted.

[Updated on: Tue, 19 February 2008 16:51]

Report message to a moderator

Re: Can't recreate 9i to 10g with EXP/IMP [message #301250 is a reply to message #301248] Tue, 19 February 2008 16:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
More information here in documentation.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm
http://www.orafaq.com/wiki/Import/_Export_FAQ
Re: Can't recreate 9i to 10g with EXP/IMP [message #301252 is a reply to message #301248] Tue, 19 February 2008 17:24 Go to previous message
Archanfel
Messages: 3
Registered: February 2008
Junior Member
>>Use 9i imp in the source machine.
>>Just edit your tnsnames.ora, add entries to talk to 10g database >>and use
>>imp user/pass@10gdatabase
Thanks for clearing that up.

>>I meant, precreate the tablespaces and do a full import (by >>default the full import will try to create
>>tablespaces. if the path is different, it will fail and all >>following will fail).
I'm guessing I shouldn't worry about it since I've precreated all the tablespaces on the new server. The paths are different in the new server since we got new hard drives.

Again, thank you for being patient with me.
Previous Topic: import full database using import dump
Next Topic: importing data from 9i to 8i
Goto Forum:
  


Current Time: Fri Dec 09 21:35:12 CST 2016

Total time taken to generate the page: 0.10195 seconds