database migration 8i to 9i doing first time needs steps.(win2x)

Posted on 2005-04-13
Last Modified: 2012-06-21
Dear Gurus,
Please guide me for steps as i am doing database migration from 8i to 9i on (Win 2000/2003).
Is import export better or migration please provide me with details.
Question by:trivedisushil
    LVL 25

    Accepted Solution

    3 ways to do it....

    1. Install an Oracle9i (same version as prod), do a cold backup of production database, restore it to the TEST server, then you are set.

    2. Install an Oracle9i , do a full export of production database, import into TEST box.

    3. Upgrade Oracle8i to oracle9i using DBUA(upgrade assistant from oracle9i), then update the schema and data.

    Here's a step by step:

    Another recommendation is to make a fresh install of 9i, then follow the step by step to clone your DB at

    This is step 1 above.
    LVL 47

    Expert Comment

    The prefered method is to use Export/Import way.
    You have to investigate the NONSYSTEM schemas and the appropriate tablespaces.
    Create scripts to create the tablespaces in 9i using the new 9i features - locally managed tablespaces.
    After that create the users and grant them all needed quotas and grants.
    Make a NONSYSTEM schemas export, transfer the dump file in binary mode and do schemas level import.
    This method grants you full control over the migration process.

    DBUA is also a good method, but as I know it works only if you use
    LVL 5

    Expert Comment


    if your database size is small exp/imp is the best method

    The oracle9i migration guide
    covers all of that

    Expert Comment

    As for migration from 8i to 9i

    1. Using exp and Imp will help for small size databases ..
       But using exp , imp will help to custamize the data into the target migrated database.

    2. Migrating is usefull for large databasee(warehouse) ..

    LVL 34

    Expert Comment

    "Is import/export better or migration?"

    It depends on your database, your server(s) and your application needs.  Both options have advantages and disadvantages.

    If you have a new server for the new database, I prefer and recommend the export/import approach.  That allows you to install the new Oracle software on the new server then test a full import all without risking any problems in your current database.  Then after you have done this a time or two, and have all of the steps documented and/or automated, you can do the actual conversion.  This will also consolidate all free space in your tablespaces, and recover any space from previously deleted records or objects, so your performance will likely be better than following a migration.  The only disadvantage of this approach: it takes longer than a migration.  How much longer?  That depends on your database size and complexity and on your server and disk hardware.

    If you do not have a new server, it may be best to use the migration approach.  But before you do, make sure that you have a good backup that you can recover to, if there is a problem, since the disadvantage of this approach is the fact that you are making significant changes to your production database.
    LVL 47

    Expert Comment

    Another advantage of Export/Import is that you work with simple tool.
    Migration tools can have more bugs. To be honest a member here said that he has done migrations without be harmed by bugs, but it takes also long time as Export/import.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now