Can I use imp/exp tools to migrate database from Oracle 9 to Oracle 10

Question

I'm subcontractor and my client wants to upgrade Oracle database from 9 to 10. Other vendor is going to perform the upgrade process, and I was asked to create whatever backup I need before the upgrade, and then recreate the environment in Oracle 10. All my data is stored in a separate database in a single schema. No fancy relations, scripts or anything like this (actual app supports different dbs: Oracle, SQL Server, Postgres so we want to avoid any DB-specific code).

I was hoping to use imp/exp but I'm not sure if imp/exp are backward compatible (exp from O9 and imp to O10)?

If there is a better/recommended way of dealing with similar situation, I'll be grateful for any advice.

Answer 1

Yes, you can use imp/exp to achieve that. And when you are on Oracle10, use Oracle's new Data Pump utilities instead of imp/exp.

Answer 2

You can for sure use the exp tool to export your database and then use the imp tool to import it to the new database.

Can one import/export between different versions of Oracle?

Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.

Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

For more information on this matter, take a look at Import Export FAQ.

Answer 3

While you can use exp/imp to "upgrade" from an older version, i wouldn't do it!

By using exp/imp you are effectively creating a new database. You are going to loose all table statistics. Sure, you can recreate them. But why? You will also need to create new logins/passwords. Make sure, that there are no 3-rd party tools involved, which are used for querying the database. Perhaps in the meantime, a second schema (eg. for report generation) has been created which has grants on "your" application tables.

In my opinion it would be better to install the oracle 10 software and then to upgrade the existing database. Oracle provides sufficient migration tools.

No matter what road you are choosing, you should test it throroughly on an intergration server using the full volume of the "production" data. Also be aware, that the import can take much much longer than the export did.