| Can't recreate 9i to 10g with EXP/IMP [message #301225] |
Tue, 19 February 2008 13:02  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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 |
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 |
|
Mahesh Rajendran
Messages: 10707
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 #301252 is a reply to message #301248] |
Tue, 19 February 2008 17:24 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
