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

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

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


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

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
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.