Data Migration: Moving an Oracle Schema to Another Server
Author: Sean Howe, Ross Group, Inc.
February 2010
Situation:
You have been using an Oracle database to contain your business’ data for many years. You have a production database, and periodically you move the data to a development or test database to meet development or testing needs.
Problem:
You have always used Oracle’s exp and imp tools to achieve moving data between Oracle systems. You hear that these tools will be obsolete in future Oracle versions.
Solution:
Use Oracle’s expdp and impdp tools to replace what you’ve customarily done with exp/imp. This enables you to continue to meet the needs of your business; in addition, you are able to achieve this task more quickly.
Some advantages of Data Pump:
- perform fine-grained object selection
- ability to estimate jobs times
- ability to restart failed jobs
- monitor running jobs
- directly load a database from a remote instance via the network
- remapping capabilities
- improved performance using parallel executions
Setup:
Oracle Data Pump uses Oracle directory objects. One of the parameters for the expdp/impdp is DIRECTORY=. You must create an Oracle directory or it will default to a directory. This default directory can be obtained with the following query:
SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
1. Create Oracle Directory SQL Command:
Create a directory on both the source and target databases with the following command.
create or replace directory expdp_dir as 'c:\backup\database\expdp';
2. Extract DDL for user to be moved (ProdUser) to the Target System:
select dbms_metadata.get_ddl('USER', username) || '/' usercreate
from dba_users where username = ‘PRODUSER’;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','PRODUSER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','PRODUSER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PRODUSER') FROM DUAL;
Move the Data
3. Run export of data
C:\>expdp system directory=expdp_dir dumpfile=export_produser.dmp schemas=PROSUSER logfile=export_produser.log
- Create user in Target Database (use output from step #2). If you want to modify the output to change the username, feel free to do so. I will explain how to accomplish that change below.
- Move the output from step #3 to the target machine to 'c:\backup\database\expdp' directory folder.
- 6. Import Data into the target database.
C:\>impdp system directory=expdp_dir dumpfile=export_produser.dmp schemas=PRODUSER logfile=produser_import.log
If you want to change the name of the user that the data is loaded into then you will use the remap_schema parameter (see below). You may also want to remap_tablespace(s)
remap_schema=PRODUSER:TESTUSER remap_tablespace=PROD_DATA:TEST_DATA remap_tablespace=PROD_INDX:TEST_IND
Conclusion:
Oracle’s Data Pump utility is a much improved tool over the prior tool. Change is difficult, but if you begin to use the new tool, you will grow to appreciate the Data Pump tool. It has many enhancements that ease the task of moving data.
Complete documentation on Data Pump can be found at:
http://download.oracle.com/docs/cd/B19306_01/server.102/e10320/utils.htm#sthref49
If you need more information on how the Ross Group can help you solve you Data Migration challenges, contact 
