Steps to import data from 11g to 9i database

Steps to import data from 11g to 9i database


In 11g database Change the definition of view EXU9DEFPSWITCHES from:

CREATE OR REPLACE VIEW exu9defpswitches (
compflgs, nlslensem ) AS
SELECT a.value, b.value
FROM sys.v$parameter a, sys.v$parameter b
WHERE a.name = 'plsql_compiler_flags' AND
b.name = 'nls_length_semantics'

to

CREATE OR REPLACE VIEW exu9defpswitches (
compflgs, nlslensem ) AS
SELECT a.value, b.value
FROM sys.v$parameter a, sys.v$parameter b
WHERE a.name = 'plsql_code_type' AND
b.name = 'nls_length_semantics'

(see new parameter PLSQL_CODE_TYPE)

This need to be change because of following issue.
Export From 11g using EXP Utility Version 9iR2 Produces Corrupt Export Dump [ID 550740.1] 

1- Go to 9i database server

2- Add the tns entry fro 11g database

(Export from 11.1.0.7 using export utility of 9.2.0.6)

( To minimize the number of conversation set NLS_LANG during export to the same as character set of the exported database. In this case WE8ISO8859P1.This means no conversation takes place, all data is stored in export file as it was stored in the database)

3- export NLS_LANG=american_america.WE8ISO8859P1

4- Take the export of 11g database from this server

exp system/manager@11gDB owner=scott file=test.dmp log=a.log

(Run import in 9.2.0.6 database to import the export dump created from 11.1.0.7 with 9.2.0.6 export)

5- export ORACLE_SID of 9i database

6- export NLS_LANG=american_america.WE8ISO8859P1

7- Now import the export of 11g database data which is taken from 9i export utility

imp system/manage fromuser=scott touser=new file=test.dmp log=b.log