Oracle – Migrate database from Oracle 9i to Oracle 10g

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
3. So as not to run out of undo tablespace create the following script and store it on the 9i system:
   

   create table tmp_users as select * from dba_users;
delete tmp_users;

   Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

   ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:
   

   userid=<user>/<password>@<oracle_sid>
log=exp.log
full=y
consistent=y
filesize=5G
file=/u02/oracle/oraback/<oracle_sid>/export/exp1.dmp

5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.
   

   nohup exp parfile=export.par &

6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:
   

   nohup imp parfile=import.par &

2. Create the export parameter file on the 10g system:
   

   userid=<user>/<password>@<oracle_sid>
log=import.log
full=y
commit=y
feedback=50000
buffer=10000000
file=exp1.dmp

3. Create a transactional database using DBCA in a VNC session
4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

1. Check backup to tape has been successful
2. Cleardown any old backups if space is required before export
3. Run the sql script for undo tablespace retention
4. In a separate session start the export using the previously created export shell script
5. Tail nohup.out to see when the export has completed
   

   $  tail -f nohup.out

FTP the files to the 10g server

1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

1. In a VNC session run the import shell script created previously