expdp/impdp 表空间模式迁移实施实验

最近有朋友问我关于表空间的迁移方案，今天我就做一个实验，来为大家解答一下关于表空间的迁移方法。

 

OS平台：windows

ORACLE 版本：11.2.0

 

实验目的：将测试表空间YY内的数据迁移到U1表空间内

 

 1：首先，我们来创建一个测试用户，用户名为u1，密码为u1，权限为DBA（为方便实验，生产中不建议直接赋予用户DBA权限）

 

SQL> alter user u1 account unlock;

 

SQL> grant dba to u1;

 

 2：为u1用户创建一个默认的表空间，表空间名为u1

 

SQL> create tablespace u1 datafile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/u1.dbf' size 1024M;

 

SQL> alter user u1 default tablespace u1;

 

SQL> select username,default_tablespace from dba_users where username='U1';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
U1                             U1

  

3：创建一个测试表空间，表空间名为YY，大小为10G

 

SQL> create tablespace yy datafile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/YY01.DBF' size 10240m;

 

SQL> alter user u1 default tablespace yy;

 

SQL> select username,default_tablespace from dba_users where username='U1';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
U1                             YY

 

4：以scott用户创建测试表，并把测试表存储在测试表空间YY内，并往测试表内插入大量的数据。

 

C:/Documents and Settings/songyang>conn scott/tiger

SQL> create table y1 tablespace yy as select * from emp;

SQL> create table y2 tablespace yy as select * from emp;

SQL> create table y3 tablespace yy as select * from emp;

SQL> create table y4 tablespace yy as select * from emp;

SQL> create table y5 tablespace yy as select * from emp;

 

SQL> insert into y1 select * from emp;

已创建14行。

SQL> insert into y1 select * from y1;

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> /

已创建114688行。

SQL> /

已创建229376行。

SQL> /

已创建458752行。

SQL> /

已创建917504行。

SQL> /

已创建1835008行。

SQL> insert into y2 select * from y1;

已创建3670016行。

SQL> insert into y3 select * from y1;

已创建3670016行。

SQL> insert into y4 select * from y1;

已创建3670016行。

SQL> insert into y5 select * from y1;

已创建3670016行。

SQL> commit;

提交完成。

 

5：检查测试表空间YY内有多少张表

 

SQL> select table_name from dba_tables where tablespace_name='YY';

TABLE_NAME
------------------------------
Y1
Y2
Y3
Y4
Y5

 

6:使用expdp工具，以tablespace=yy模式导出数据

 

C:/Documents and Settings/songyang>expdp system/oracle ESTIMATE=BLOCKS  parallel=2 directory=my_job dumpfile=data_01.dmp,data_02.dmp tablespaces=yy

Export: Release 10.2.0.1.0 - Production on 星期三, 18 5月, 2011 19:50:03

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** ESTIMATE=BLOCKS parallel=2 directory=my_job dumpfile=data_01.dmp,data_02.dmp tablespaces=yy

正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
.  预计为 "SCOTT"."Y1"                                  176 MB
.  预计为 "SCOTT"."Y2"                                  176 MB
.  预计为 "SCOTT"."Y3"                                  176 MB
.  预计为 "SCOTT"."Y4"                                  176 MB
.  预计为 "SCOTT"."Y5"                                  176 MB
使用 BLOCKS 方法的总估计: 880 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "SCOTT"."Y1"                                145.2 MB 3670016 行
. . 导出了 "SCOTT"."Y2"                                145.2 MB 3670030 行
. . 导出了 "SCOTT"."Y3"                                145.2 MB 3670030 行
. . 导出了 "SCOTT"."Y4"                                145.2 MB 3670030 行
. . 导出了 "SCOTT"."Y5"                                145.2 MB 3670030 行
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLESPACE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TABLESPACE_01 的转储文件集为:
  D:/IMPDP/DATA_01.DMP
  D:/IMPDP/DATA_02.DMP
作业 "SYSTEM"."SYS_EXPORT_TABLESPACE_01" 已于 19:50:52 成功完成

 

7：使用impdp工具，并使用参数remap，将scott用户下存在于YY表空间的数据全部迁移到U1用户下的U1表空间内

 

C:/Documents and Settings/songyang>impdp system/oracle parallel=2 remap_schema=scott:u1 remap_tablespace=yy:u1 directory=my_job dumpfile=data_01.dmp,d
ata_02.dmp

Import: Release 10.2.0.1.0 - Production on 星期三, 18 5月, 2011 20:11:48

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_01"
启动 "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** parallel=2 remap_schema=scott:u1 remap_tablespace=yy:u1 directory=my_job dumpfile=data_01.dmp,dat
a_02.dmp
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "U1"."Y1"                                   145.2 MB 3670016 行
. . 导入了 "U1"."Y2"                                   145.2 MB 3670030 行
. . 导入了 "U1"."Y3"                                   145.2 MB 3670030 行
. . 导入了 "U1"."Y4"                                   145.2 MB 3670030 行
. . 导入了 "U1"."Y5"                                   145.2 MB 3670030 行
作业 "SYSTEM"."SYS_IMPORT_FULL_01" 已于 20:13:50 成功完成

 

 

8:验证迁移是否成功

 

conn u1/u1

 

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
Y2                             TABLE
Y3                             TABLE
Y4                             TABLE
Y5                             TABLE
Y1                             TABLE

 

 

 

 

 

EXP/EXPDP工作原理的不同之处：（以下文章借鉴DAVID DAI博主）

 

（1）Conventional path Export. 

Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.

 

exp/imp 默认会是传统路径， 这种模式下，是用SELECT 加数据查询出来， 然后写入buffer cache， 在将这些记录写入evaluate buffer. 最后传到Export客户端，在写入dump文件。

 

（2） Direct path Export.

 When using a Direct path Export, the data is read from disk directly into the export session's program global area (PGA): the rows are transferred directly to the Export session's private buffer. This also means that the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file.

 

The default is DIRECT=N, which extracts the table data using the conventional path.

This parameter is only applicable to the original export client. Export DataPump (expdp) uses a Direct Path unload by default and switches to External Table mode if required

 

 

直接路径模式下，数据直接从硬盘读取，然后写入PGA， 格式就是export 的格式，不需要转换， 数据再直接传到export 客户端，写入dump 文件。 这种模式没有经过evaluation buffer。 少了一个过程，导出速度提高也是很明显。

 

网络和磁盘影响

expdp/impdp 是服务端程序，影响它速度的只有磁盘IO。

exp/imp 可以在服务端，也可以在客户端。所以，它受限于网络和磁盘。

 

 

 

 

exp/imp 与 expdp/impdp 功能上的区别

（1）把用户usera的对象导到用户userb,用法区别在于fromuser=useratouser=userb ,remap_schema='usera':'userb' 。例如

imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log;

impdp system/passwd directory=expdp dumpfile=expdp.dmp remap_schema='usera':'userb' logfile=/oracle/exp.log;

（2）更换表空间，用exp/imp的时候，要想更改表所在的表空间，需要手工去处理一下，如alter table xxx move tablespace_new之类的操作。用impdp只要  用remap_tablespace='tabspace_old':'tablespace_new'

（3）当指定一些表的时候，使用exp/imp 时，tables的用法是 tables=('table1','table2','table3')。expdp/impdp用法是

tables='table1','table2','table3'。

 

（4）是否要导出数据行

exp　（ROWS=Y 导出数据行，ROWS=N 不导出数据行）

expdp content（ALL:对象＋导出数据行，DATA_ONLY：只导出对象，METADATA_ONLY：只导出数据的记录）

 

 

使用中的优化事项

 

exp

通过上面的分析，知道采用direct path可以提高导出速度。 所以，在使用exp时，就可以采用直接路径模式。 这种模式有2个相关的参数：DIRECT 和RECORDLENGTH参数。

 

DIRECT参数定义了导出是使用直接路径方式(DIRECT=Y)，还是常规路径方式(DIRECT=N)。常规路径导出使用SQL SELECT语句从表中抽取数据，直接路径导出则是将数据直接从磁盘读到PGA再原样写入导出文件，从而避免了SQL命令处理层的数据转换过程，大大提高了导出效率。在数据量大的情况下，直接路径导出的效率优势更为明显，可比常规方法速度提高三倍之多。

 

和DIRECT=Y配合使用的是RECORDLENGTH参数，它定义了Export I/O缓冲的大小，作用类似于常规路径导出使用的BUFFER参数。建议设置RECORDLENGTH参数为最大I/O缓冲，即65535(64kb)。其用法如下：

            如：exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

 

 

一些限制如下：

You cannot use the DIRECT=Y parameter when exporting in tablespace-mode  (i.e. when specifying the parameter TRANSPORT_TABLESPACES=Y).  You can use the DIRECT=Y parameter when exporting in full, user or table mode (i.e.: when specifying FULL=Y or OWNER=scott or TABLES=scott.emp).

--直接路径不能使用在tablespace-mode

 

The parameter QUERY applies ONLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).

-- 直接路径不支持query 参数。 query 只能在conventional path模式下使用。

 

In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs.

-如果exp 版本小于8.1.5，不能使用exp 导入有lob字段的biao。 不过现在很少有有8版本的数据库了。 这点可以忽略掉了。

 

The BUFFER parameter applies ONLY to conventional path Export. It has no effect on a direct path Export. This BUFFER parameter specifies the size (in bytes) of the buffer used to fetch rows. It determines the maximum number of rows in an array, fetched by Export.  For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.

-- buffer 选项只对conventional path exp有效。 对于直接路径没有影响。 对于直接路径， 应该设置RECORDLENGTH 参数。

 

The RECORDLENGTH parameter specifies the length (in bytes) of the file record. You can use this parameter to specify the size of the Export I/O buffer (highest value is 64 kb). Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to disk.  It does not affect the operating system file block size.  If you do not define this parameter, it defaults to your platform-dependent value for BUFSIZ (1024 bytes in most cases).

 

invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH

 

-- 对于直接路径下， RECORDLENGTH参数建议设成64k（65535）。这个值对性能提高比较大。  如：

> exp system/manager FILE=exp_full.dmp LOG=exp_full.log

FULL=y DIRECT=y RECORDLENGTH=65535

> imp system/manager FILE=exp_full.dmp LOG=imp_full.log

FULL=y RECORDLENGTH=65535 

 

 

IMP

 

Oracle Import进程需要花比Export进程数倍的时间将数据导入数据库。某些关键时刻，导入是为了应对数据库的紧急故障恢复。为了减少宕机时间，加快导入速度显得至关重要。没有特效办法加速一个大数据量的导入，但我们可以做一些适当的设定以减少整个导入时间。

 

（1）避免I/O竞争

Import是一个I/O密集的操作，避免I/O竞争可以加快导入速度。如果可能，不要在系统高峰的时间导入数据，不要在导入数据时运行job等可能竞争系统资源的操作。

（2）增加排序区

Oracle Import进程先导入数据再创建索引，不论INDEXES值设为YES或者NO，主键的索引是一定会创建的。创建索引的时候需要用到排序区，在内存大小不足的时候，使用临时表空间进行磁盘排序，由于磁盘排序效率和内存排序效率相差好几个数量级。增加排序区可以大大提高创建索引的效率，从而加快导入速度。

（3）调整BUFFER选项

Imp参数BUFFER定义了每一次读取导出文件的数据量，设的越大，就越减少Import进程读取数据的次数，从而提高导入效率。BUFFER的大小取决于系统应用、数据库规模，通常来说，设为百兆就足够了。其用法如下：

imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000

（4）使用COMMIT=Y选项

COMMIT=Y表示每个数据缓冲满了之后提交一次，而不是导完一张表提交一次。这样会大大减少对系统回滚段等资源的消耗，对顺利完成导入是有益的。

（5）使用INDEXES=N选项

前面谈到增加排序区时，说明Imp进程会先导入数据再创建索引。导入过程中建立用户定义的索引，特别是表上有多个索引或者数据表特别庞大时，需要耗费大量时间。某些情况下，需要以最快的时间导入数据，而索引允许后建，我们就可以使用INDEXES=N 只导入数据不创建索引，从而加快导入速度。

我们可以用INDEXFILE选项生成创建索引的DLL脚本，再手工创建索引。我们也可以用如下的方法导入两次，第一次导入数据，第二次导入索引。其用法如下：

imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=y indexes=n

 

imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_index_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=n indexes=y

（6）增加  LARGE_POOL_SIZE

如果在init.ora中配置了MTS_SERVICE，MTS_DISPATCHERS等参数，tnsnames.ora中又没有(SERVER=DEDICATED)的配置，那么数据库就使用了共享服务器模式。在MTS模式下，Exp/Imp操作会用到LARGE_POOL，建议调整LARGE_POOL_SIZE到150M。

 

检查数据库是否在MTS模式下：

SQL>select distinct server from v$session;

如果返回值出现none或shared，说明启用了MTS。

 

 

Expdp/Impdp

 

据泵与exp/imp 来说性能有很大的提高， 其中影响最大的就是paralle。 可以这么来看:  expdp/impdp=exp/imp+direct moe + paralle. 所以， 使用数据泵，要想提高速度，就要设置并行参数。

 

先看2个参数：

Setting Parallelism

For export and import operations, the parallelism setting (specified with the PARALLEL parameter) should be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the performance will not be optimal because multiple threads of execution will be trying to access the same dump file.

The PARALLEL parameter is valid only in the Enterprise Edition of Oracle Database 10g.

Using Substitution Variables

Instead of, or in addition to, listing specific filenames, you can use the DUMPFILE parameter during export operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02, 03, and so on. Enough dump files are created to allow all processes specified by the current setting of the PARALLEL parameter to be active. If one of the dump files becomes full because its size has reached the maximum size specified by the FILESIZE parameter, it is closed, and a new dump file (with a new generated name) is created to take its place.

 

From：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref44

 

 

如果我们使用如下语句：

expdp full=y directory=dump dumpfile=orcl_%U.dmp parallel=4

 

那么expdp将为parallel 创建4个文件： ORCL_01.DMP，RCL_02.DMP

，ORCL_03.DMP，RCL_04.DMP。 每个进程一个文件。 这样的话，每个文件的大小会因进程而不同。 可以某个文件很大，某个文件却很小。 要解决这个问题，就是设置filesize 参数。 来指定每个文件的最大值。 这样当一个文件达到最大值的之后，就会创建一个新的文件。

 

如：

expdp full=y directory=dump dumpfile=orcl_%U.dmp parallel=4 filesize=50M

 

 

导出的dump文件和paralle有关系，那么导入也有关系。 paralle要小于dump文件数。 如果paralle 大于dump文件的个数，就会因为超过的那个进程获取不到文件，就不能对性能提高。

 

            一般parall 参数值等于CPU 的个数。而且要小于dump文件的个数。

 

查看CPU 个数：

SQL> show parameter cpu

 

 

注意事项：

（1）导入的时候可能会停在某个地方，比如在创建索引的时候，可能在一个地方停了十几分钟。  这个时候切记不要中断过程。  这个时候可能是需要导入的数据比较多。

 

            可以在不同时段观察下表空间大小的变化。 如果表空间一直在变化，说明还在导入，这个时候耐心等待就好。