CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Sean Howe, CommitDBA staff Database Administrator

Moving an Oracle Schema to Another Server

By: Sean Howe, CommitDBA staff Database Administrator

October 11, 2011



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

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

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

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

From database assessments to complete database management, CommitDBA can assist your company today. For more information about how CommitDBA can work for you – give us a call at (800) 734-9304.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today