ORACLE数据迁移--EXPDP/IMPDP

在开始某一个项目的时候，我们经常需要搭建测试环境，初始数据的导致其中不可避免涉及异构平台数据迁移。oracle提供的工具聆郎满目，有时候也会挑花眼，在这种情况下DBA就需要根据不同的场景，指定不同的迁移方案，这里我们对oracle涉及的迁移方案进行一下简单的总结，然后重点介绍一下DATA PUMP的使用。
oracle提供的可以用来数据迁移的工具主要数据迁移工具(DATA MIGRATION TOOLS)，数据复制工具(DATA REPLICATION TOOLS)，数据备份恢复工具(DATA BUCKUP/RESTORE TOOLS)三大类。
其中DMT的可用工具有：oracle data  pump,transportable tablespace,copy over db link,sql * loader utility,sql devoloper,sql*plus copy command
custom pl/sql procedure
DRT可用的工具有：ORACLE GODEN GATE,ORALCE STREAM
DBR的工具需要配套使用：oracle recovery manager,oracle active data guard 
每种工具使用的场景都不同，如copy over db link,必须保证source DB与target DB网络连通，而 SQL * LOADER则是导入外部数据源txt/csv/asii的最好选择。
在考虑数据迁移的时候，考虑的主要因素有三个，数据库平台，数据库版本，平台版本，还有就是是否有数据时效性要求。这里我们考虑对简单的场景：异构平台不同oracle数据迁移，网络不同，这里我们最好的选择即EXPDP/EXIMP
EXPDP/EXIMP 的主要特点有：
Some advantages of Data Pump:
x perform fine-grained object selection 更细的对象颗粒度控制
x ability to estimate jobs times 可以预估作业执行时间
x ability to restart failed jobs  断点重提
x monitor running jobs  监控作业运行进度
x directly load a database from a remote instance via the network 通过网络可以直接实现类似COPY OVER DB LINK 功能。
x remapping capabilities 模式重命名
x improved performance using parallel executions 并行执行
总而言之，言而总之，该工具是你数据迁移的首选。

EXPDP一般的使用步骤如下：
1 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_us
username = ‘PRODUSER’;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','PRODUSER') FR
DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','PRODUSER')
DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','PRODUSER')
DUAL;

3 Move the 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)
5 Move the output from step #3 to the target machine
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

6 通过DBlink直传，同时支持数据加密


这里在简单的介绍一下DATA PUMP的对象颗粒度控制
以schema=(hr,scott)为例进行介绍
schema=hr

select table_name from user_tables;

REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

schema=soctt

select table_name from user_tables;
DEPT
EMP
BONUS
SALGRADE

1 支持模式匹配：

C:\Users\kinfinger>expdp xx/xx directory=hrdir  dumpfile=%u.dump tables=
%e% logfile=mt.log

2 支持多个TABLES

C:\Users\kinfinger>expdp hr/kinfinger directory=hrdir  dumpfile=%u.dump tables=
(jobs,regions) logfile=mt.log

3 不同SCHEMA下table的导出，输出结果支持多个dump文件，参数%u 同filesize配合使用，范围01-99

C:\Users\kinfinger>expdp system/kinfinger directory=hrdir   dumpfile=%u.dump ta
bles=(hr.jobs,scott.emp) logfile=mt.log

场景实际测试：

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
JVIEW
MVIEW

SQL> select table_name ,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
REGIONS                        HRSPACE
LOCATIONS                      HRSPACE
DEPARTMENTS                    HRSPACE
JOBS                           HRSPACE
EMPLOYEES                      HRSPACE
JOB_HISTORY                    HRSPACE
COUNTRIES

已选择7行。

C:\Users\kinfinger>expdp hr/kinfinger directory=hrdir  dumpfile=hr.dump logfil
e=mt.logle

导入HR3 ,REMAP to hr3 

drop user hr2 cascade;
drop tablespace mapsp ;
 create tablespace mapsp datafile 'g:/mapsp/m.data' size 20m  nologging;

create user hr3 identified by kinfinger default tablespace mapsp quota unli
mited on users;
grant create session,connect to hr3;
 grant read,write on directory hrdir to hr3;
成功。

grant create table,create view to hr3

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: 内部错误
ORA-39068: 在 PROCESS_ORDER=-4 的行中主表数据无效
ORA-01950: 对表空间 'MAPSP' 无权限
ORA-39097: 数据泵作业出现意外的错误 -1950

grant resource to hr3;
SQL> select * from hr.mview where rownum < 2 union all  select * from hr3.mview
where rownum < 2 ;


MAX_SALARY
----------
     40000

知道数据导入成功，且REMAP SCHEMA成功。

关于resource，其中涉及很多权限，涉及的查询如下：

SELECT TYPE FROM USER_SOURCE

查询当前用户：

show user;