migration procedure for Oracle database 8i on Windows to 10g on Linux

migration procedure for Oracle database 8i on Windows (NT and 2000) to 10g Linux platform.

1.. Identify developers and business owners for the database and set up schedule to move and test the database.
2.. create the new database on Linux box using dbca with template USON_LINUX_DEV.dbt

2a. enable archive log

SQL> archive log list; # if archive log is not enabled, then shutdown database and startup mount, then alter database archivelog;

2b. enable flashback (a good feature for fixing user errors, need to enable archive log).

SQL> select flashback_on from v$database; the return is NO, the put database in mount stage, then alter database flashback on;

3.. check tablespaces on the source database (i.e., database on Windows). Create missing tablespaces in the target database (database on Linux).
Comments: 8i uses RBS, 10g uses UNDOTBS1.

Default tablespaces on 8i: SYSTEM,RBS,USERS,TEMP, and TOOLS.

Default tablespaces on 9i: SYSTEM, UNDOTBS1,USERS,TEMP, and SYSAUX.

Need to create TOOLS and other user defined tablespaces.

On Linux with ASM: create tablespace tools; command to create tablespace.

4.. Check database link on the source database. get a list of all database aliases listed on host column of dba_db_links. Then copy all entries for those database aliases from tnsnames.ora on source database to target database’ tnsnames.ora. Pay attention to the databases have been moved already.

5.. Check all jobs running against this database. For Window NT, at dos prompt, type at command. For Window 2000, use Window’s service.

6.. Move all source code from the source database host to the target database host in $ORACLE_BASE/admin/$ORACLE_SID/script/. Convert the dos batch job into shell script. Add environment variable setting in the shell scripts. Fix all file directories in the scripts. Put batch job log in $ORACLE_BASE/admin/$ORACLE_SID/log and data in $ORACLE_BASE/admin/$ORACLE_SID/data directories.

7.. Put the source database in the restrict mode.

8.. Make a full export of the target database on the database host (using the original exp command: i.e, 8i)

Exp system/manager full=y file=exp_full_${ORACLE_SID|.dmp log=exp_full_${ORACLE_SID}.log

9.. shutdown the source database.
10.. ftp the dump file and log file over to the target host in the binary mode. Put files in $ORACLE_BASE/admin/$ORACLE_SID/exp

11.. perform a full import with the ignore parameter enabled on the target database.


Imp system/manager full=y file=exp_full_${ORACLE_SID}.dmp log=imp_full_${ORACLE_SID}.log IGNORE=y

12.. review the import log file, fix error, work with developer to test the batch jobs. Ask developers to test the moved database.