Full exp 8.1.7 and Full imp 9i

Hi,

i'm trying to make a migration from 8.1.7 to 9i. Both are on the same Machine, with Windows XP (please don't blame me...)

It's my first attempt to make a full exp, so i'm guessing it's a dummy mistake, but HOW do i get the users into the new database?

I've found different suggestions. Some are saying that i have to create the users, tablespaces and roles manually in 9i.
Is that right? If yes, How can i do that, efficient?

I tried to find the "create user" statements in the error.log
(imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y indexfile=index.sql show=y)
but they aren't there.

Here are my Import/Export statements :

exp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y consistent=y

imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y ignore=y

Any Idea of what i'm missing here?

thanks,
Stefan

why are you using exp / imp when you should be upgrading the database using the upgrade scripts.

Anyway if they are on the same box you must pre-create the tablespaces as it will try and create and create them in the same place as the ones that already exist. Will all work after that.

I presume that you are using the correct version of exp / imp for your versions as well

davey, thanks for your reply!

Quote:

---

why are you using exp / imp when you should be upgrading the database using the upgrade scripts.

---

Because it seems to me, that the full exp/imp way is the most flexible one.

Could you please explain, what do you mean with "upgrade scripts"? Where can i found them?

Quote:

---

Anyway if they are on the same box you must pre-create the tablespaces as it will try and create and create them in the same place as the ones that already exist. Will all work after that.

---

Ok, but how will the users and be created? Because the "create role" and "create user" statements aren't in the export file which we created from 8.1.7.

Quote:

---

I presume that you are using the correct version of exp / imp for your versions as well

---

Yes, i'm using exp from the 8.1.7 database and imp from the 9i db.

exp / imp is by no means the most flexible (or correct way) to upgrade a database.

Run the upgrade assitant program (will be in the start menu -> programs) area and it will do it for you.

you sure you did a full export then? at the top of an export file you should have stuff like this

Code:

---

CREATE USER "ANONYMOUS" IDENTIFIED BY VALUES 'anonymous' DEFAULT TABLESPACE "XDB" TEMPORARY TABLESPACE "TEMP" ACCOUNT LOCK

---

Quote:

---

you sure you did a full export then? at the top of an export file you should have stuff like this

---

yes, if full=y is the necessary argument then i did a full export.

I am thinking, that this is a specific 8.1.7 behaviour! Could anybody confirm that?

Because if i do a full export on version 9i (with the exact same statement as i used for the 8.1.7) i can find the "create user" statements on the .dmp file (like you postet, davey).

Quote:

---

Originally Posted by buck

I am thinking, that this is a specific 8.1.7 behaviour! Could anybody confirm that?

---

No, it is not a specific 8.1.7 behaviour, I can confirm that. Full exp/full imp should create users on the new database without any problem. But you are not performing full import, that's it.

Quote:

---

Originally Posted by buck

imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y indexfile=index.sql show=y

---

The bold part in your command is your problem. With INDEXFILE= parameter you are telling imp to create you only the CREATE INDEX commands and that's what you see in your log file, all the other stuff is simply skipped by the imp utility. Remove your INDEXFILE= from your command and you'll be able to see the CREATE USERS command in the log file.

And BTW, do not run imp or exp utilities by connecting as sysdba - that's a guaranteed way to problems...

Hi,

the

Code:

---

indexfile=index.sql

---

Code was just a try. I tried it also without this parameter. (only show=y ). But i couldn't find the "create user" statements in there also.

jmodic: Could you give me an good example for the exp and imp statement?

Do we have to create anything in 9i bevor we do the full import?

Actually it's impossible that this simple statement is so tricky, but this is the 2nd day i'm trying to do the full exp/imp but it still doesn't work (and freaking me out, btw. ;))


thanks!

Code:

---

(using 8i exp.exe)
exp userid=system/manager file=exp_8_1_7.dmp compress=N log=error.log full=y consistent=y

(using 9i exp.exe)
imp userid=system/manager file=exp_8_1_7.dmp log=error.log full=y ignore=y

---

Those two should be just fine.

Do you need to precreate anything before importing? As davey23uk allready mentioned, in your case you have to precreate all your tablespaces manualy because you are importing to the same box where the original 8i database exists and you do not want the imp to overwrite your 8i datafiles. Apart from that, imp will take care for everything else.

Give it a try (maybe with show=Y), but this time not with SYSDBA user, use some other "ordinar" user like SYSTEM - I have a feeling your problems have a root cause exeatly at the SYSDBA connection!

id still use the upgrade scripts - its what they are there for

Quote:

---

Originally Posted by davey23uk

id still use the upgrade scripts - its what they are there for

---

I agree 100% with that!

Hi,

ok, jmodic, i tried it with your proposal. thanks for the exampe! (with an ordanary user with the profile "dba" and "exp_full_database").

I looked in the file=exp_8_1_7.dmp in the beginning of the export (the end file is about 4.3GB).

I saw the "create user" statements in there! But they didn't appear in the log=error.log .

Does anybody have an explanation for this behaviour?

If i create the users prior to the import, the import is with error, but at least, the import creates a few of the tables.

I'm guessing if i would also create also the roles and profiles bevor i'm doing the import, it would work (have to try it tomorrow).

I have NO idea why i have to do this manually!

thanks in advance for any type of proposal...

Observation...

http://download-west.oracle.com/docs...mp.htm#1005557

(But I don't advocate it myself.)

Migration... !

http://download-west.oracle.com/docs...de.htm#1008864
(Shouldn't they change that to the 'old' 9i release?)

i'm trying to create Users, Roles, Profiles and Tablespace upfront manually right now. Because i couldn't find what's going wrong here (the DBUA is still the backup method ;)) with the full exp/imp.

I am gonna post the result.

run dbua, you will done in an hour, save yourself lots of hassle with exp /imp