Oracle Data Pump is made up of three distinct parts:

The command-line clients, expdp and impdp
The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively.

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line. These parameters enable the exporting and importing of data and metadata for a complete database or for subsets of a database.

When metadata is moved, Data Pump uses functionality provided by the DBMS_ METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and re-creation of dictionary metadata.

The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.

Oracle Data Pump Architecture

Example

同一个数据库中克隆schema,本例中为克隆用户OA到OADev(以下操作均采用SYS用户)。

1.新建用户

create user oadev identified by "oadev";

2.表空间和数据文件

单独创建表空间,数据文件

create temporary tablespace oadev_tmp tempfile '/db/oracle/oradata/DBTEST/oadev_tmp01.dbf' size 1000M;

create tablespace oadev_data datafile '/db/oracle/oradata/DBTEST/oadev_data01.dbf' size 1000M;

create tablespace oadev_idx datafile '/db/oracle/oradata/DBTEST/oadev_idx01.dbf' size 1000M;

设置默认表空间

SELECT 'alter user ' || 'OADEV' || ' default tablespace ' ||
	DU.DEFAULT_TABLESPACE || ' temporary tablespace ' ||
	DU.TEMPORARY_TABLESPACE || ' ;'
FROM DBA_USERS DU
WHERE DU.USERNAME IN ('OA');


alter user OADEV default tablespace OADEV_DATA temporary tablespace OADEV_TMP;

验证

SELECT * FROM DBA_USERS DU WHERE DU.USERNAME IN ('OA', 'OADEV');

3.权限设置

查询已有系统权限

SELECT 'grant ' || DSP.PRIVILEGE || ' to DEV;'
  FROM DBA_SYS_PRIVS DSP
 WHERE DSP.GRANTEE IN ('OA')
 ORDER BY 1;

执行以上SQL输出的结果

grant CREATE ANY PROCEDURE to oadev;
grant CREATE ANY VIEW to oadev;
grant DEBUG ANY PROCEDURE to oadev;
grant DEBUG CONNECT SESSION to oadev;
grant UNLIMITED TABLESPACE to oadev;

验证

SELECT *
  FROM DBA_SYS_PRIVS DSP
 WHERE DSP.GRANTEE IN ('OA', 'OADEV')
 ORDER BY 1;

4.使用expdp/impdp准备工作

在OS上创建expdp/impdp目录:/dba/exp,并分配Oracle读写权限

创建Directory

CREATE DIRECTORY EXPDP AS '/dba/exp';

授权

GRANT READ, WRITE ON DIRECTORY EXPDP TO OA, OADEV;

验证

SELECT * FROM DBA_DIRECTORIES;

SELECT * FROM ALL_TAB_PRIVS ATP
 WHERE ATP.TABLE_NAME = 'EXPDP';

5.使用expdp导出oa数据

运行expdp命令,导出schema

[oracle@oradb exp]$ expdp  oa/oatest directory=expdp dumpfile=oa_20121227.dmp logfile=oa_20121227.log parallel=2

6.使用impdp导入5中导出的数据到oadev

运行impdp命令,导入数据,注意重新映射schema,tablespace

[oracle@oradb exp]$ impdp oadev/oadev directory=expdp dumpfile=oa_20121227.dmp logfile=oadev_20121227.log remap_schema=oa:oadev remap_tablespace=oa_data:oadev_data,oa_idx:oadev_idx

7.验证

查看导出、导入日志,有异常则进行检查即可。 使用oadev登录,更改应用数据库的用户即可,启动测试。

SELECT 'All Objects owned by OA: ' || COUNT(1)
  FROM ALL_OBJECTS O
 WHERE O.OWNER = 'OA'
UNION
SELECT 'All Objects owned by OADev: ' || COUNT(1)
  FROM ALL_OBJECTS O
 WHERE O.OWNER = 'OADEV';

8.Utility

进行data pump测试时,可能会反复重建用户,这里可以根据已有用户生成新用户的创建脚本,包括角色、系统权限、目录对象权限。

--generate create user sq
SELECT ' create user ' || DU.USERNAME || ' identified by "' || DU.USERNAME || '"' ||
       ' profile ' || DU.PROFILE || ' default tablespace ' ||
       DU.DEFAULT_TABLESPACE || ' temporary tablespace ' ||
       DU.TEMPORARY_TABLESPACE
  FROM DBA_USERS DU
 WHERE DU.USERNAME = upper('&username');


--generate grant roles and system or objects privileges sql
SELECT 'grant ' || DRP.GRANTED_ROLE || ' to ' || '&new_grantee' || ' ;' -- roles
  FROM DBA_ROLE_PRIVS DRP
 WHERE DRP.GRANTEE = upper('&grantee')
UNION ALL
SELECT 'grant ' || DSP.PRIVILEGE || ' to ' || '&new_grantee' || ' ;' --sys privileges
  FROM DBA_SYS_PRIVS DSP
 WHERE DSP.GRANTEE = upper('&grantee')
UNION ALL
SELECT 'grant ' || ATP.PRIVILEGE || ' on directory ' || --directory privileges
       ATP.TABLE_NAME || ' to ' || '&new_grantee' || ' ;'
  FROM ALL_TAB_PRIVS ATP
 WHERE ATP.TABLE_NAME = upper('&directory')
   AND ATP.GRANTEE = upper('&grantee');

expdp command line

[oracle@oradb exp]$ expdp help=Y

Export: Release 11.2.0.1.0 - Production on Fri Jan 4 10:44:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.

impdp command line

[oracle@oradb exp]$ impdp help=Y

Import: Release 11.2.0.1.0 - Production on Fri Jan 4 11:04:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.

Reference



blog comments powered by Disqus

Published

04 January 2013

Categories

Tags

Github