Migration from 8.0.5 to 9.2

OS: Windows NT -> Windows Server 2003
Database: 8.0.5 -> 9.2

We are going to migrate our database from 8.0.5 on Windows NT 4 (sp5) to 9.2 on Windows Server 2003. I'm actually planning the upgrade, so I need your comments and suggestions for the following plans:

1. Export-Import
- Export 8.0.5 and import by schemas into 9.2. Schemas need to be pre-created.

2. Upgrade to 8.1.7 and than upgrade to 9.2
- Oracle 8.0.5 is not supported on Windows Server 2003, therefore the upgrade can only be done on the old NT server.
- After the upgrade restore a cold backup on Windows Server 2003


1. Based on Metalink articles I think we have to use 8.0.5 export utility and 9.2 inport utility. Is that correct?
2. With schema level import, is it possible that something will be missing (some constraints or anything)? Is there any known/published bug/issue?
3. Regarding the speed of export-import. The NT sever has 4 (700 Mhz) CPU and 2 GB ram. The database is about 40 GB. How much time will the export take approx?
4. Which method is preferred (based on your experience etc.)? Could you please give some advantages/disadvantages for both?

As it is not a concrete issue/problem, I'll split the points between all of you who gives some useful feedback.

Thanks & Regards,
Who is Participating?
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.

If you have many schemas and complicated relationship among the schemas, I would recommend you to go the upgrade route. The upgrade should keep all the relationships automatically.

If you have rather simple structure in your database, the export/import might be viable choice to migrate the data from old version to new version of database, even jumping versions.
Instead of upgrading from 8.0.5 to 8.1.7 and after to Oracle9i will be round trip method. Better you export your database using export/import option. I would like to suggest some hints to make your export/import fast

C:\> exp system/manager file=db805.dmp log=db805.log direct=Y buffer=1048576 owner=scott

using DIRECT option will speed up your export operation. sam case you can use commit option with import

C:\> imp system/manager file=db805.dmp log=idb805.log fromuser=scott touser=scott buffer=1048576 commit=Y

When your installing Oracle9i ( on your system don't forget to update latest patch with it ( its available now.


Velu N


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
I just did this.
Since you're on 8.0.5, you CAN'T use the upgrade tool, you need 8.0.6 minimum. So, with this path, you already have to upgrade to 8.0.6 (or 8.1.7), then another upgrade to 9.0.2.
So, this means new temporary installations (805 or 817, and 902).

Even with 40gb, I would suggest a full export, and a full import directly on 902 if you're upgrade window is large enough. No problem will occur from an 805 export to a 902 import.
Just make sure that you have a good tnsnames on the 902 (if you use database links).
Also, to speed things up, on the 902, have the tablespace created before if possible, and latest patch applied.

It can take 30 hours depending on you're configuration (nbr of indexes and all).

Use the commit = y parameter on import to prevent rollback errors.
Do not put the 902 database in archivelog mode during the import.

Good luck
Did you try it ?  What direction did you choose ?
baleeAuthor Commented:
Not yet. Actually we have to wait for the server to arrive. 1-2 weeks...

Thanks anyway.
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.