Migrate Oracle database from 11g to 10g on another server

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SE SP2, Oracle 11gR2(11.2.0.2.0, source, dev db) and Oracle 10g(10.2.0.4, dest, prod db)
Objective: Migrate/merge/mix the database on 11gr2 to 10g.


Concept:
1. find out all schemas on source db.
2. create tablespaces with suffix _dev on dest db
3. rename schemas with suffix _dev after import by dmpdp/impdp
4. check compatibility level from source, make sure it's less or equal to dest db version

Steps:
1. export on source db to get tablespace and users creation script, and password of schemas
[source] exp system file=path_to_dump.exp log=path_to_log.exp full=y

only let it run for a while to get those users/tablespaces creation definition.

2. create tablespace on dest db
e.g.
create tablespace training_dev datafile 'path_to' size 100m autoextend on next 10m [flashback off]
create user training_dev identified by values 'xxxxx'(get from step1) default tablespace training_dev temporary tablespace temp;

3. check compatibility level on source db
SELECT name, value FROM v$parameter WHERE name = 'compatible';

4. dump database
expdp system dumpfile=path_to_dump.expdp logfile=path_to_log.logdp exclude=statistics schemas=training

note: if you don't specify directory, it will use default directory 'data_pump_dir'. You can get it from command
sqlplus / as sysdba
select * from dba_directories;

5. import database
impdp system dumpfile=xxxxx logfile=xxxxx remap_schema=training:training_dev remap_tablespace=training:training_dev transform=oid:n

note: you can test it if you are able to import between 10g and 11g without creating training_dev tablespace and users first.