i have oracle database 9i installed under HPUX 11.0 and i want to export it and import it to oracle database 10g under HPUX 11.0 .
what utility to use ? and how to use them ? should i use " exp system/manager@db1 from the first server and import system/manager@db1 to the second one " ? ...do i need to create tablespace and schemas on the second server ??
There are two basic methods are export/import, and upgrade in place. A third and more exciting method is cloning followed by upgrade in place.
Let's assume you have created a new 10g database. Go back to the old database and use the 9i home's export utility to suck out the data. Then go to the 10g database and use the 10g home's import utility to load the data. If the export is full=yes, then it will include all users (schemas) and tablespaces, datafiles, etc. Ignore errors about perfstat not loading, as any statspack snapshots will need to be deleted anyways (spdrop then spcreate.)
Here's one catch: is the directory structure used by DB 1 is not the same as DB 2, then the CREATE TABLESPACE commands contained in the dump file will not work in the new database. Just reverse engineer rather than export.