Migration from 8.0.5 to 9.2

Posted on 2004-11-16
Last Modified: 2010-05-18
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,
Question by:balee
    LVL 12

    Expert Comment

    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.

    Accepted Solution

    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

    LVL 10

    Assisted Solution

    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
    LVL 10

    Expert Comment

    Did you try it ?  What direction did you choose ?
    LVL 1

    Author Comment

    Not yet. Actually we have to wait for the server to arrive. 1-2 weeks...

    Thanks anyway.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now