Oracle的exp/imp详解(原创)

原创

wx6323d657ef2b3 2022-09-20 17:51:39 博主文章分类：Oracle日常管理 ©著作权

©著作权归作者所有：来自51CTO博客作者wx6323d657ef2b3的原创作品，请联系作者获取转载授权，否则将追究法律责任

备份概述
逻辑备份：
备份可分为两类，物理备份和逻辑备份

物理备份：该方法实现数据库的完整恢复，但需要极大的外部存储设备，例如磁带库，具体包括冷备份和热备份。冷备份和热备份(热备份要求数据库运行在归档模式下)都是物理备份，它涉及到组成数据库的文件，但不考虑逻辑内容。
逻辑备份：使用软件技术从数据库中导出数据并写入一个输出文件，该文件的格式一般与原数据库的文件格式不同，只是原数据库中数据内容的一个映像。因此，逻辑备份文件只能用来对数据库进行逻辑恢复，即数据导入，而不能按数据库原来的存储特征进行物理恢复。逻辑备份一般用于增量备份，即备份那些在上次备份以后改变的数据。
在进行逻辑备份时，首先要确认数据库的字符集和操作系统的字符集是否一致，如果不一致则会报错ora-00091，具体设置方法如下：
SQL> select * from v$nls_parameters where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     WE8ISO8859P1
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

exp

可以通过exp help=y或者imp help=y查看exp或imp的详细参数，下面以exp为例解释参数意义
USERID：用户名/口令
FULL：导出整个数据库，只有拥有exp_full_database角色的用户或者特权用户如sys，system等才能进行全库导出。示例如下
exp "'/ as sysdba'" full=y
BUFFER：制定数据缓冲区大小，主要用于提高exp/imp速度，该单位为字节，不能写成buffer=1m的形式，应写成字节为单位的参数，如buffer=1048576
exp hr/hr file=t_b.dmp buffer= 1048576 tables=T
OWNER：需要导出的用户，示例如下
exp "'/ as sysdba'" owner=$hr,scott$ file=hr_scott.dmp
上例中由于是在linux平台进行测试的，需要对 owner=$hr,scott$使用\进行转义
FILE：输出文件
TABLES：需要导出的表
COMPRESS：导入到一个区 (Y) 。主要目的是为了消除存储碎片，以保证某张表的所有记录都存储在连续的空间里。但是负面效应很明显，如果该参数值为y，则会将高水位线以下的所有extent导入到一个区中，因此在导入时很有可能出现，明明表中数据很少，但是却花了很多时间在建立的extent上。且自oracle9i开始，使用了本地管理的表空间，存储碎片的问题应该比低版本好多了，笔者个人建议将compress设为n。可参加如下实验过程
SQL> create table t as select * from dba_objects;
Table created.
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T';
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768         21          65536
采用delete的方式删除表中数据，不降低HWM
SQL> delete from t;
50625 rows deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T';
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768         21          65536
将compress设为n和y分别对t表进行两次导出
exp hr/hr tables=t file=t_n.dmp compress=n
exp hr/hr tables=t file=t_y.dmp compress=y
删除t表
SQL> drop table t purge;
Table dropped.
导入compress值为y的dmp文件
imp hr/hr file=t_y.dmp fromuser=hr touser=hr;
查询t表初始extent大小
SQL> select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T'
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T                 6        768          6        6291456
查看建表语句
SQL> select dbms_metadata.get_ddl('TABLE','T','HR') FROM DUAL;
CREATE TABLE "HR"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
再次删除t表
SQL> drop table t purge;
Table dropped.
导入compress值为n的dmp文件
imp hr/hr file=t_n.dmp fromuser=hr touser=hr;
查询t表初始extent大小
SQL>select segment_name,bytes/1024/1024 MB,blocks,extents,initial_extent from user_segments where segment_name='T'
SEGMENT_         MB     BLOCKS    EXTENTS INITIAL_EXTENT
-------- ---------- ---------- ---------- --------------
T             .0625          8          1          65536
查询建表语句
SQL>select dbms_metadata.get_ddl('TABLE','T','HR') FROM DUAL;
CREATE TABLE "HR"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
可以看到当compress为y时，exp将数据导入到一个extent中，而在imp导入的时候则体现为改变原有表的存储参数，将初始化extent增大到了6291456，个人建议通常情况下将compress设置为n，避免由于初始extent过大，给降低HWM带来的不必要麻烦。
RECORDLENGTH：IO记录的长度
DIRECT：直接路径 (N)。
传统模式导出和直接路径导出的原理
传统模式导出相当于使用select语句从表中取出数据，数据从磁盘上先读到buffer cache中，记录被转移到一个评估检测的缓冲区中，数据经过语法检测后没有问题，将数据传给PGA，最后写入导出的文件中。如果使用Direct Path模式导出，数据直接从磁盘上读取到导出的PGA中：记录直接被转换导出会话的私有buffer中。这也就是意味着SQL语句处理层被忽略掉了，因为数据已经是符合导出的格式了，不需要其他的转换处理了。数据直接被传送给导出的客户端，最后写入导出文件。过程可概况如下
direct=n datafile---->sga----->pga----->dump
direct=y datafile---->pga----->dump
传统模式导出和直接路径导出的差异
Direct Path导出模式速度上明显快于Conventional Path导出模式，因为Direct Path导出模式忽略了SQL语句处理这一层。
当使用Direct Path导出模式的时候，可以增大参数RECORDLENGTH的值来提高导出的性能。导出的性能主要取决以下的因素：DB_BLOCK_SIZE、导出表上列的类型、导出文件的I/O层（主要是指导出文件尽量要和数据库的数据文件在不同的磁盘上，避免I/O上的竞争）。一般来说，参数 RECORDLENGTH设置为操作系统I/O的block size或者是DB_BLOCK_SIZE的整数倍，例如65535。
使用哪种模式导出数据都不会影响导入数据，也就是说导入数据的时间是一样的。
传统模式导出和直接路径导出的限制
Direct Path导出模式只能使用命令行或者参数文件的方式来导出，不能使用交互式的方式导出数据，只有Conventional Path导出模式可以使用交互式的方式。
Direct Path导出模式不能用于导出传输表空间，即设置参数TRANSPORT_TABLESPACES=Y，其他的FULL、USER、TABLE模式均可以使用Direct Path导出模式。
在Oracle8i以前的版本里面，如果表里面存在LOB的对象，是不能使用Direct Path导出模式导出表的，如果使用Direct Path导出模式导出表，那些存在LOB对象的记录是不会被导出的。自从Oracle8i之后，这种限制就被取消了。对于Oracle8i之后的版本，如果使用Direct Path导出模式导出表，那些存在LOB对象的记录是会自动以Conventional Path导出模式来导出。但是如果你用低于Oracle8i的客户端的exp工具的Direct Path导出模式导出Oracle8i以上的数据库存在LOB对象的表，那些包含LOB的记录还是同样不会被导出。
Exp工具中的QUERY参数只能用于Conventional Path导出模式，QUERY参数允许导出一个表的满足一定条件的部分记录。
Exp工具中的BUFFER参数只能用于传统模式导出，BUFFER参数设置了用于fetch记录的缓存的大小，以字节为单位，即在array中最大数量的记录。
参数RECORDLENGTH指定文件记录的最大长度，以字节为单位，即导出I/O的buffer，最大为65535。这个参数决定了在没写入导出文件中缓存中堆积数据的多少。如果没有设置这个参数，取决于操作系统平台，在大多数平台的默认值是1024字节。
只有当环境变量中的NLS_LANG设置成跟导出数据库中的字符集一致的时候才能使用Direct Path导出模式导出数据。如果环境变量中的NLS_LANG和数据库的字符集不一致的时候，导出就会报类似下面的错误：
EXP-41 "Export done in server's UTF8, different from user's character set WE8ISO8859P1"
EXP-0 "Export terminated unsuccessfully".
这种限制只对于Oracle8i及其更低版本的Oracle有效，Oracle8i以上的版本不会出现此类错误。
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
GRANTS：导出权限 (Y)
INCTYPE：增量导出类型，已废除
INDEXES：导出索引 (Y)
RECORD：跟踪增量导出 (Y) ，已废除
TRIGGERS：导出触发器 (Y)
LOG：屏幕输出的日志文件
STATISTICS：在导出文件中保留对象的统计信息，默认值ESTIMATE，还可以为compute或者none。如果导出时出现
EXP-00091: Exporting questionable statistics
可以考虑将 STATISTICS设置为NONE
ROWS：确定表中的数据行是否导出，默认为Y，导出
QUERY：用于导出表的子集的select子句，示例如下
exp hr/hr file=emp_q.dmp tables=employees query=\"where hire_date \>to_date$\'1999-01-01\'\,\'yyyy-mm-dd\'$\"
PARFILE：参数文件名，可以用如下方式导出
exp hr/hr parfile=parfile
$ cat parfile
file=t_p.dmp
compress=y
rows=y
tables=(t,empl%s)
使用parfile参数可以对频繁进行的导出操作进行反复调用，同时也可以避免不同操作系统之间需要对特定字符进行转义的烦恼，如下例
exp hr/hr parfile=parfile
$cat parfile
file=t_p.dmp
compress=y
rows=y
tables=employees
statistics=none
query="where hire_date>to_date('1999-01-01','yyyy-mm-dd')"
CONSISTENT：在导出时，将影响正在导出的表的事务设为只读，主要作用于嵌套表和分区表，默认为N。
CONSTRAINTS：导出的约束条件 (Y)
OBJECT_CONSISTENT:只在对象导出期间设置为只读的事务处理 (N)
FEEDBACK:每 x 行显示进度，默认为0
FILESIZE：每个导出文件的最大大小
FLASHBACK_SCN：用于将会话快照设置回以前状态的SCN
FLASHBACK_TIME：用于获取最接近指定时间的SCN的时间
RESUMABLE：遇到空间不足时的错误时挂起，默认为N，需与 RESUMABLE_NAME和 RESUMABLE_TIMEOUT一起使用
RESUMABLE_NAME：用于标示哪个会话需要使用 RESUMABLE选项，格式为 User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID
RESUMABLE_TIMEOUT：RESUMABLE的等待时间，默认为7200s，如果在指定时间内未解决问题，则操作中断
TTS_FULL_CHECK：对TTS执行完整或部分相关性检查
TABLESPACES：要导出的表空间列表，示例如下
exp "'/ as sysdba'" file=t_ts.dmp tablespaces=(users,example)
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
直接备份到磁带上
exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 file=/dev/rmt0 log=exp.log tables=(tab1,tab2,tab3)
注：在磁盘空间允许的情况下，应先备份到本地服务器，然后再拷贝到磁带。出于速度方面的考虑，尽量不要直接备份到磁带设备
imp imp的参数和exp的大致相同，下面是常用参数的解释，与exp相同的这就不再赘述
ignore：Oracle在恢复数据的过程中，当导入某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。若 ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，唯一索引等，则出错的记录不会插入，但合法的记录会添加到表中。若 ignore=n，Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续导入下一个表。
注意：如果表中的字段并没有唯一性约束，那么在使用ignore=y的情况下很有可能插入重复数据。
indexes：在恢复数据的过程中，若indexes=n，则表上的索引不会被恢复，但是对 LOB 索引, OID索引和主键索引等系统自动生成的索引将无条件恢复。
indexfile：不进行导入操作而是将创建对象的文本保存到文件中，可以通过编辑使用该文本文件创建数据库对象。
fromuser,touser:这两个参数可以组合使用，也可以分开使用。他们可以实现将源用户的对象数据，导入到目标用户schema底下的功能。这里要注意，导入时的用户需要有imp_full_database角色，示例如下

导入一个或一组指定用户所属的全部对象
$imp system/manager file=full_all.dmp log=seapark fromuser=hr
$imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

将一个或一组指定用户所属的全部对象导入到另一个用户下
$imp hr/hr fromuser=hr touser=czm file=hr_all.dmp
$imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
commit：默认值为 COMMIT=N，及在没插入玩一个对象后提交。当COMMIT=Y时候是根据你BUFFER的大小决定每次提交的数量。对于包含了LONG、RAW、 DATE等类型的表，不论BUFFER设置多大，都是每插入一行进行提交。设置commit=y可以防止减少回滚段的压力，但由于频繁提交，会带来性能上的影响，推荐使用COMMIT=N。
不同版本的EXP/IMP问题
一般来说，从低版本导入到高版本问题不大，麻烦的是将高版本的数据导入到低版本中，在Oracle9i之前，不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决：
1、在高版本数据库上运行低版本的catexp.sql；
2、使用低版本的EXP来导出高版本的数据；
3、使用低版本的IMP将数据库导入到底版本数据库中；
4、在高版本数据库上重新运行高版本的catexp.sql脚本。
但在9i中，上面的方法并不能解决问题。如果直接使用底版本EXP/IMP会出现如下错误：
EXP-00008: ORACLE error %lu encountered
ORA-00904: invalid column name
这已经是一个公布的BUG，需要等到Oracle10.0才能解决，BUG号为2261，你可以到METALINK上去查看有关此BUG的详细信息。
BUG归BUG，我们的工作还是要做，在没有Oracle的支持之前，我们就自己解决。在Oracle9i中执行下面的SQL重建exu81rls视图即可。
CREATE OR REPLACE view exu81rls
(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)
AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')
|| decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')
|| decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')
|| decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),
r.check_opt, r.enable_flag,
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
from user$ u, obj$ o, rls$ r
where u.user# = o.owner#
and r.obj# = o.obj#
and (uid = 0 or
uid = o.owner# or
exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')
)
/
grant select on sys.exu81rls to public;
/
常见问题及解决方法
数据库对象已经存在一般情况, 导入数据前应该彻底删除目标数据下的表, 序列, 函数/过程,触发器等；数据库对象已经存在, 按缺省的imp参数, 则会导入失败。如果用了参数ignore=y, 会把exp文件内的数据内容导入，如果表有唯一关键字的约束条件, 不合条件将不被导入如果表没有唯一关键字的约束条件, 将引起记录重复 字符集转换
数据库对象有主外键约束
不符合主外键约束时, 数据会导入失败,
解决办法:
1、先导入主表, 再导入依存表
2、disable目标导入对象的主外键约束, 导入数据后, 再enable它们
导入大表时存储分配失败
默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上。导入时, 如果不存在连续一个大数据块, 则会导入失败. 导出80M以上的大表时, 记得compress= N, 则不会引起这种错误.
imp和exp使用的字符集不同
如果字符集不同, 导入会失败, 可以改变unix环境变量或者NT注册表里NLS_LANG相关信息。导入完成后再改回来.

参考至：http://baike.baidu.com/view/3781838.htm
             http://www.itpub.net/thread-1220596-1-1.html
             http://space.itpub.net/21634752/viewspace-706677
             http://www.itpub.net/361441,2.html
             http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:968789918647
             http://www.itpub.net/thread-1196993-1-1.html
             http://space.itpub.net/519536/viewspace-694577
             http://www.xifenfei.com/2402.html
             http://space.itpub.net/35489/viewspace-613625
             http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch02.htm
本文原创，转载请注明出处、作者
如有错误，欢迎指正