Oracle database backup and recovery

Migrating data from 9i on Unix to 10g on Linux

Migrating data from 9i on Unix to 10g on Linux

By  Brian Peasland, Database Administrator, SGT, Inc.

What about migrating data from Oracle 9i on Unix to 10g on Linux? Will your advice work in the same way then? We had a 400 GB database and doing this didn't work for us. We had to extract data into a flat file using WisdomForce FastReader and then load it back with input for SQL*Loader that FastReader created. What other choices did we have? By the way FastReader was able to extract 400 GB in less then an hour.
If you are mixing platforms in this manner, then you have a few options at your disposal.

  1. Use Oracle's export/import utilities to dump the data and ingest into the new database. This works well since you can also copy indexes, stored procedures, triggers, views, etc.
  2. Dump the data to a text file as you have indicated, then ingest with SQL*Loader or other utility (like External Tables).
  3. Create a database link from one system to another, then pull (or push) the data across the link with INSERT SELECT statements or the SQL*Plus COPY command.

In the last two options, you will have to generate the DDL (using the DBMS_METADATA pkg) to create the non-table objects in the new database.

19 Oct 2005