Oracle 逻辑备份 EXP和IMP

Oracle 逻辑备份


一、概述
1、Oracle备份有3种标准方式：导出(Export、Import)、脱机备份(Offline Backup)和联机(Archivelog)备份(Online Backup)，前一种是逻辑备份，后两种是物理备份。
2、导出方式就是逻辑备份，通过Export实用程序来实现，恢复通过Import实用程序来实现。
3、Export实用程序用来读取数据库(包括数据字典)和把输出写入一个称为导出转储文件(.dump)的二进制文件中。
4、逻辑备份恢复有三种模式：表、用户、完全。可以导出指定表、指定用户、整个数据库。
5、导出期间可以选择是否导出与表相关的数据字段信息，如权限、索引、约束等。
6、Export所写的文件包括完全重建全部被选对象所需的命令。
7、如果执行完全导出，则导出的转储文件中包含所有数据库对象，包括表空间、数据文件和用户。
8、当在不相邻的主要版本之间迁移数据时，应该先将数据导入到中间版本，然后再从这个版本的数据库导入更高的版本。
9、EXP和IMP是OS级的command，在linux的shell窗口或windows的命令提示符窗口执行，在sqlplus中执行需要在前面加上host。




二、EXP和IMP的语法


1、exp


格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例如: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) 或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表


USERID 必须是命令行中的第一个参数。


关键字   说明 (默认值)        
------------------------------
USERID   用户名/口令           
FULL     导出整个文件 (N)
BUFFER   数据缓冲区大小        
OWNER    所有者用户名列表，你希望导出哪个用户的对象就用owner=username1,username2...
FILE     输出文件 (EXPDAT.DMP)  
TABLES   表名列表,指定导出的table名称，如：TABLES=table1,table2...
COMPRESS 导入到一个区 (Y)   
RECORDLENGTH   IO记录的长度
GRANTS   导出权限 (Y)          
INCTYPE  增量导出类型，已弃用
INDEXES  导出索引 (Y)         
RECORD   跟踪增量导出 (Y)，已弃用
DIRECT   直接路径 (N)         
TRIGGERS 导出触发器 (Y)
LOG      屏幕输出的日志文件    
STATISTICS  导出分析对象的信息(ESTIMATE)，还可以为compute或者none。如果导出时出现报EXP-00091，可以考虑设置为NONE。建议改为none，加快导出速度，因为分析数据可以导入后在analyze获得。
ROWS     导出数据行 (Y)
PARFILE  参数文件名,如果你exp的参数很多，可以存成参数文件.
CONSISTENT 交叉表的一致性 (N)，在导出时，将影响正在导出的表的事务设为只读，主要作用于嵌套表和分区表。
CONSTRAINTS  导出的约束条件 (Y)
OBJECT_CONSISTENT    只在对象导出期间设置为只读的事务处理 (N)
FEEDBACK             每 x 行显示进度 (0)
FILESIZE             每个转储文件的最大大小
FLASHBACK_SCN        用于将会话快照设置回以前状态的 SCN
FLASHBACK_TIME       用于获取最接近指定时间的 SCN 的时间
QUERY                用于导出表的子集的select子句
RESUMABLE            遇到空间不足时的错误时挂起，默认为N，需与 RESUMABLE_NAME和 RESUMABLE_TIMEOUT一起使用
RESUMABLE_NAME       用于标示哪个会话需要使用 RESUMABLE选项，格式为 User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID
RESUMABLE_TIMEOUT    RESUMABLE 的等待时间，默认为7200s，如果在指定时间内未解决问题，则操作中断
TTS_FULL_CHECK       对 TTS 执行完整或部分相关性检查
TEMPLATE             调用 iAS 模式导出的模板名


下列关键字仅用于可传输的表空间
TABLESPACES          要导出的表空间列表，示例如下exp "'/ as sysdba'" file=t_ts.dmp tablespaces=(users,example)
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)


系统帮助请查看exp help=y


2、imp


格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例如: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N 或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表


USERID 必须是命令行中的第一个参数。


关键字   说明 (默认值)        
------------------------------
USERID   用户名/口令
FULL     导入整个文件 (N)
BUFFER   数据缓冲区大小        
FROMUSER 所有者用户名列表
FILE     输入文件 (EXPDAT.DMP)
TOUSER   用户名列表
SHOW     只列出文件内容 (N)     
TABLES   表名列表
IGNORE   忽略创建错误 (N)    
RECORDLENGTH  IO 记录的长度
GRANTS   导入权限 (Y)          
INCTYPE  增量导入类型
INDEXES  导入索引 (Y)         
COMMIT   提交数组插入 (N)，即在每插入完一个对象后提交。
ROWS     导入数据行 (Y)        
PARFILE  参数文件名
LOG      屏幕输出的日志文件    
CONSTRAINTS    导入限制 (Y)
DESTROY                覆盖表空间数据文件 (N)
INDEXFILE              将表/索引信息写入指定的文件，不进行导入操作而是将创建对象的文本保存到文件中，可以通过编辑使用该文本文件创建数据库对象
SKIP_UNUSABLE_INDEXES  跳过不可用索引的维护 (N)
FEEDBACK               每 x 行显示进度 (0)
TOID_NOVALIDATE        跳过指定类型 ID 的验证
FILESIZE               每个转储文件的最大大小
STATISTICS             始终导入预计算的统计信息，默认是always，建议制定为none，之后再analyze，这样加快导入速度。
RESUMABLE              在遇到有关空间的错误时挂起 (N)
RESUMABLE_NAME         用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT      RESUMABLE 的等待时间
COMPILE                编译过程, 程序包和函数 (Y)
STREAMS_CONFIGURATION  导入流的一般元数据 (Y)
STREAMS_INSTANTIATION  导入流实例化元数据 (N)


下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户


系统帮助请查看imp help=y


如何使exp的帮助以不同的字符集显示：set nls_lang=simplified chinese_china.zhs16gbk，通过设置环境变量，可以让exp的帮助以中文显示，
如果set nls_lang=American_america.字符集，那么帮助就是英文的了


3、常用参数说明


3.1、FULL
说明：这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。
例如：exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y


3.2、 OWNER和TABLE
说明：这两个选项用于定义EXP的对象。OWNER定义导出指定用户的对象；TABLE指定EXP的table名称。
例如：exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl
exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap


3.2、BUFFER和FEEDBACK
说明：在导出比较多的数据时，我会考虑设置这两个参数。
例如：exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT


3.4、FILE和LOG
说明：这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。


3.5、COMPRESS
说明：参数不压缩导出数据的内容。用来控制导出对象的storage语句如何产生。默认值为Y，使用默认值，对象的存储语句的init extent等于当前导出对象的extent的总和。
推荐使用COMPRESS＝N。


3.6、FILESIZE
说明：该选项在8i中可用。如果导出的dmp文件过大时，最好使用FILESIZE参数，限制文件大小不要超过2G。
例子：exp userid=duanl/duanl file=f1,f2,f3,f4,f5 filesize=2G owner=scott
这样将创建f1.dmp, f2.dmp等一系列文件，每个大小都为2G，如果导出的总量小于10G，EXP不必创建f5.bmp.


3.7、FROMUSER和TOUSER
说明：使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。要注意，导入时的用户需要有imp_full_database角色
例如：假设我们做exp时导出的为test的对象,现在我们想把对象导入用户：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1
$imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)


3.8、IGNORE、
说明：IGNORE参数将忽略表的存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。
Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。
若ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。
若ignore=n，Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表。 -
注意：如果表中的字段并没有唯一性约束，那么在使用ignore=y的情况下很有可能插入重复数据。


3.9、INDEXES
说明：如果想使用新的存储参数重建索引，或者为了加快到入速度，我们可以考虑将INDEXES设为N，N的话表上的索引不会被恢复，但是对 LOB 索引, OID索引和 主键索引等系统自动生成的索引将无条件恢复。
例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N


3.10、direct
说明：direct=y为直接导出，=n为传统导出。
传统导出为direct=n datafile---->sga----->pga----->dump
直接导出为direct=y datafile---->pga----->dump (跳过SQL语句处理引擎)
直接模式更快，导出性能主要由RECORDLENGTH决定，RECORDLENGTH设置为操作系统I/O的block size或者是DB_BLOCK_SIZE的整数倍例如65535。
有一些参数于direct=y不兼容,无法用直接路径导出可移动的tablespace,或者用query参数导出数据库子集。当导入导出的数据库运行在不同的os下时,必须保证recordlength参数的值一致.


Direct Path导出模式只能使用命令行或者参数文件的方式来导出，不能使用交互式的方式导出数据，只有Conventional Path导出模式可以使用交互式的方式。
Direct Path导出模式不能用于导出传输表空间，即设置参数TRANSPORT_TABLESPACES=Y，其他的FULL、USER、TABLE模式均可以使用Direct Path导出模式。


Exp工具中的QUERY参数只能用于Conventional Path导出模式，QUERY参数允许导出一个表的满足一定条件的部分记录。
Exp工具中的BUFFER参数只能用于传统模式导出，BUFFER参数设置了用于fetch记录的缓存的大小，以字节为单位，即在array中最大数量的记录。


3.11、RECORDLENGTH
参数RECORDLENGTH指定文件记录的最大长度，以字节为单位，即导出I/O的buffer，定义了Export I/O缓冲的大小，最大为65535。
这个参数决定了在没写入导出文件中缓存中堆积数据的多少。如果没有设置这个参数，取决于操作系统平台，在大多数平台的默认值是1024字节。


3.12、parfile
使用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')" 


3.12、commit
默认值为 COMMIT=N，即在每插入完一个对象后提交。当COMMIT=Y时候是根据你BUFFER的大小决定每次提交的数量。对于包含了LONG、RAW、DATE等类型的表，不论BUFFER设置多大，都是每插入一行进行提交。
设置commit=y可以防止减少回滚段的压力，但由于频繁提交，会带来性能 上的影响，推荐使用COMMIT=N。




三、EXP和IMP的三种模式


1、EXP
1.1、完全模式
说明：整个库的所有对象被备份到文件。
例子1：exp system/mana@26DWDQ file=c:\full.dmp full=y
例子2：exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 full=y  file=exp_.dmp log=exp.log


1.2、用户模式
说明：用户的所有对象被备份到文件。
例子1：exp system/mana@26DWDQ file=c:\sonic.dmp owner=sonic,zdqmp
例子2：exp icdmain/icd owner=icdmain rows=y indexes=n compress=n buffer=65536 feedback=100000 file=exp.dmp log=exp.log


1.3、表模式
说明：用户的表被备份到文件。
例子1：exp gzdqmp/gzdqmp_123@26DWDQ file=d:\sonic.dmp tables=aa query=\" where name like '%00%'\"
例子2：exp icdmain/icd rows=y indexes=n compress=n buffer=65536 feedback=100000 file=exp.dmp log=exp.log tables=tab1,tab2,tab3


2、IMP
2.1、完全模式
说明：把文件里所有的表导入，如果恢复的表存在就会报错，这时需要加参数ignore=y
例1：imp mid_gis/mid_gis@IP_address/orcl file =E:\topway\1505\TABLE4.DMP full=y
例2：imp mid_sc/mid_sc@IP_address/orcl IGNORE=y statistics=none file=F:\Downloads\dis_device_0320_150603.dmp log=F:\Downloads\dis_device_0320_150603imp.log full=y
例3：imp system/manager rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=y volsize=0 full=y file=exp.dmp log=imp.log 


2.2、用户模式
说明：必须指定FROMUSER、TOUSER参数，这样才能导入数据。如果恢复的表存在就会报错，需要加参数ignore=y
例1：imp sonic/sonic@26DWDQ file=c:\sonic.dmp owner=sonic fromuser=sonic touser=sonic
例2：imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp.dmp log=imp.log
例3：imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=exp.dmp log=imp.log tables=t1,t2,t3;


2.3、表模式
说明：如果恢复的表存在就会报错，需要加参数ignore=y
例1：imp sonic/sonic@26DWDQ file=c:\sonic.dmp owner=sonic tables=(temp_kk)
例2：imp icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp.dmp log=imp.log full=y


3、表空间传输
3.1.设置表空间为只读（假定表空间名字为APP_Data 和APP_Index）
alter tablespace app_data read only;
alter tablespace app_index read only;
3.2.发出EXP命令
SQL>host exp userid=”””sys/password as sysdba””” 
transport_tablespace=y tablespace=(app_data, app_index)
3.3.拷贝数据文件到另一个地点，即目标数据库
可以是cp(unix)或copy(windows)或通过ftp传输文件（一定要在bin方式）
3.4.在目标数据库附加该数据文件
imp file=expdat.dmp userid=”””sys/password as sysdba””” transport_tablespace=y “datafile=(c:\temp\app_data,c:\temp\app_index)”
3.5.设置目标数据库表空间为读写
alter tablespace app_data read write;
alter tablespace app_index read write;




四、exp/imp 的优化


1、使用直接路径direct=y
说明：适用于exp、imp
oracle会避开sql语句处理引擎,直接从数据库文件中读取数据,然后写入导出文件。
有一些参数于direct=y不兼容,无法用直接路径导出可移动的tablespace,或者用query参数导出数据库子集。
当导入导出的数据库运行在不同的os下时,必须保证recordlength参数的值一致。


2、避免磁盘排序
说明：适用于imp。将sort_area_size设置为一个较大的值,比如100M


2.避免日志切换等待
说明：适用于imp。增加重做日志组的数量,增大日志文件大小.


3.优化日志缓冲区
说明：适用于imp。比如将log_buffer容量扩大10倍(最大不要超过5M)


4.使用阵列插入与提交
说明：适用于imp。commit = y
注意:阵列方式不能处理包含LOB和LONG类型的表,对于这样的table,如果使用commit = y,每插入一行,就会执行一次提交.


5.使用NOLOGGING方式减小重做日志大小
说明：适用于imp。在导入时指定参数indexes=n,只导入数据而忽略index,在导完数据后在通过脚本创建index,指定 NOLOGGING选项




五.常见问题及解决方法
1、数据库对象已经存在
一般情况, 导入数据前应该彻底删除目标数据下的表, 序列, 函数/过程,触发器等;  数据库对象已经存在, 按缺省的imp参数, 则会导入失败如果用了参数ignore=y, 会把exp文件内的数据内容导入如果表有唯一关键字的约束条件, 不合条件将不被导入；如果表没有唯一关键字的约束条件, 将引起记录重复。


2、数据库对象有主外键约束
不符合主外键约束时, 数据会导入失败,
解决办法: 先导入主表, 再导入依存表
disable目标导入对象的主外键约束, 导入数据后, 再enable它们


3、权限不够
如果要把A用户的数据导入B用户下, A用户需要有imp_full_database权限


4、导入大表( 大于80M ) 时, 存储分配失败
默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上.
导入时, 如果不存在连续一个大数据块, 则会导入失败. 导出80M以上的大表时, 记得compress= N, 则不会引起这种错误.


5、imp和exp使用的字符集不同
如果字符集不同，导入会失败，可以改变unix环境变量或者NT注册表里NLS_LANG相关信息，导入完成后再改回来.
对于单字节字符集（例如US7ASCII），恢复时，数据库自动转换为该会话的字符集（NLS_LANG参数）；
对于多字节字符集（例如ZHS16CGB231280），恢复时，应尽量使字符集相同（避免转换），如果要转换，目标数据库的字符集应是输出数据库字符集的超集。
在EXP/IMP过程中我们需要注意四个字符集的参数：导出端的客户端字符集，导出端数据库字符集，导入端的客户端字符集，导入端数据库字符集。
select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET'当前数据库端的字符集;
客户端字符集的参数为NLS_LANG，
在windows中，查询和修改NLS_LANG可在注册表中进行：HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOMExx\
xx指存在多个Oracle_HOME时的系统编号。控制客户端应用程序使用的字符集。通常设置或等于客户端的代码页。或者对于unicode应用设为UTF8。
在unix中：
$ env|grep NLS_LANG
NLS_LANG=simplified chinese_china.ZHS16GBK
修改可用：
$ export NLS_LANG=AMERICAN_AMERICA.UTF8


6、imp和exp版本不能往上兼容
可以从低版本导入高版本，但从高版本导入到低版本就非常麻烦，10g后此类问题得到改善。
必须正确地使用EXP和IMP的版本： 
1、总是使用IMP的版本匹配数据库的版本，如：要导入到817中，使用817的IMP工具。
2、总是使用EXP的版本匹配两个数据库中最低的版本，如：从9201往817中导入，则使用817版本的EXP工具。


7、ORA-20005: object statistics are locked (stattype = ALL) 和 ORA-38029: 对象统计信息已锁定
原因：多由不同版本的Oracle中exp、imp中引起，或者同版本不同字符集。
说明：报这个错误是因为导入的时候imp的时候无法统计信息，如果用了STATISTICS=NONE则日志中不会显示此类错误信息，
但STATISTICS=always(默认)则在log文件或命令行窗口中显示ORA-20005。analyze table的时候则会报ORA-38029。
解决办法是先解锁DBMS_STATS.UNLOCK_TABLE_STATS('MID_SC','DM_OLD_FEATURE_VALUE');再analyze。




另外如果dmp文件不是太大，可以编辑的话，用类似UE工具打开，修改里面高版本的版本号为低的就能导入了。




参考文献：http://blog.csdn.net/tianlesoftware/article/details/4718366
参考文献：http://blueicer.blog.51cto.com/395686/101600/
参考文献：http://czmmiao.iteye.com/blog/1522278