【Oracle篇】一文搞清exp/imp逻辑迁移工具的用法和定时全备实例（第一篇，总共四篇）

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

    好久不见又是新的一周，今天给大家的介绍的内容想必大家已经在文章标题了解了，那么话不多说，让我们开始今天的内容。

    在Oracle中作为一款备受推崇的转储工具，exp/imp在多个方面都表现出色。首先，它特别适用于小型数据库的转储工作，能够轻松实现数据的导出与导入，为数据库管理员提供了极大的便利。其次，在表空间迁移方面，exp/imp同样发挥着不可或缺的作用，能够确保数据在迁移过程中的完整性和准确性。此外，对于表的抽取操作，exp/imp也表现得游刃有余，无论是单个表的抽取还是多个表的批量抽取，都能高效完成。

    因此，无论是数据库管理员还是数据库开发者，掌握exp/imp逻辑迁移工具的使用方法都是非常重要的。在接下来的文章中，我将详细介绍exp/imp工具的基本概念、使用方法以及应用场景，希望能够为大家在数据库管理工作中提供有益的参考和帮助。

    除了exp/imp逻辑迁移工具，在10g还引入了expdp/impdp，在效率上要比exp/imp要高。一篇文章去整理两个逻辑迁移工具和案例篇幅会过长，所以我将分成四篇来进行介绍，以便大家因为篇幅过长而感到阅读疲惫。四篇的内容分别如下：

• 第一篇：一文搞清exp/imp逻辑迁移工具的用法和定时全备实例（当前篇）
• 第二篇：一文搞清expdp/impdp逻辑迁移工具的用法和定时全备实例
• 第三篇：expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移
• 第四篇：expdp/impdp高效完成全部生产用户的全库迁移

---

                                           

目录

1、exp用法：

exp导出案例：

案例1：实例完全导出(普通用户完全导出，需要有EXP_FULL_DATABASE和IMP_FULL_DATABASE角色)

案例2：导出时限制导出文件大小（os文件大小有限制或者导出量太大）

案例3：用户模式：(用户的所有对象被输出到文件中)

案例4：表模式：(用户的表被输出到文件中)

案例5：导出全库所有对象包括表结构，但不包含表数据（rows=n）

案例6：导出生产用户某些表的前10行（query=" 'where rownum<10' "）

2、imp用法：

imp导出案例：

案例1：完全导入

案例2：用户导入

案例3：表导入（不支持在导入时重命名，也就是不支持异表导入）

3、exp/imp导出和导入常见问题

4、linux系统上制定exp的定时全库备份

---

                

    exp/imp工具在数据库逻辑迁移和备份中发挥着重要的作用，它拥有许多明显的优点，但同时也存在一些不容忽视的缺点。

优点：

       1. 操作简便：exp工具的使用相对直观和简单，即使对数据库不是很熟悉的管理员也可以较快上手，进行数据的导出操作。
       2. 逻辑备份：exp工具主要进行逻辑备份，这意味着它备份的是数据库对象的结构和数据，而非底层的物理文件。这样的备份更易于理解、查看和编辑，适合进行跨平台或跨版本的迁移。

       3. 灵活性：exp工具允许用户选择性地导出特定的表、表空间或用户，这为用户提供了很大的灵活性，可以根据实际需求进行定制化的备份。

            

缺点：

       1. 性能问题：对于大型数据库，exp工具可能会遇到性能瓶颈，导出过程可能会变得非常缓慢，影响业务的正常运行。
       2. 不支持增量备份：exp工具只能进行全量备份，无法只备份自上次备份以来发生变化的数据。这意味着每次备份都会生成大量的数据，占用较多的存储空间。
       3. 依赖数据库版本：不同版本的数据库可能使用不同版本的exp工具，这可能导致在不同版本之间进行迁移时出现问题。此外，随着数据库技术的不断发展，新的功能和特性可能无法在旧版本的exp工具中得到支持。

          

官方文档对exp/imp的介绍（12c版本）：

Original Export

Original Import

                

1、exp用法：

exp -help选项：

参数选项	描述
userid	用于指定执行导出操作的用户名，口令，连接字符串
FULL	指定数据库模式导出，默认为n。为y时，表示执行数据库全库导出
Buffer	指数据行的缓冲区大小（byte），默认值根据系统而定。exp时建议>64000，imp时建议>100000
Owner	用于指定执行导出操作的方案（用户）
File	用于指定导出的文件名
Tables	用于指定执行导出操作的表
ROWS	是否导出数据，默认为y，表示导出数据
TABLESPACES	要导出的表空间列表
Log	指定导出日志文件文件的名称
query	指定导出表的前多少行（从where子句后开始）
FEEDBACK	每多个行显示进度，比如：feedback=100000
FILESIZE	每个导出文件的最大大小
FLASHBACK_SCN	用于将会话快照设置回以前状态的SCN
FLASHBACK_TIME	用于获取最接近指定时间的 SCN 的时间
COMPRESS	导入到一个区 (Y)，建议为n 。主要目的是为了消除存储碎片，以保证某张表的所有记录都存储在连续的空间里。但是负面效应很明显，如果该参数值为y，则会将高水位线以下的所有extent导入到一个区中，因此在导入时很有可能出现，明明表中数据很少，但是却花了很多时间在建立的extent上。 且自oracle9i 开始，使用了本地管理的表空间，存储碎片的问题应该比低版本好多了，建议将compress设为n。
RECORDLENGTH	IO记录的长度(最大为 64k)
STATISTICS	在导出文件中保留对象的统计信息，默认值ESTIMATE，还可以为compute或者none。如果导出时出现：EXP-00091: Exporting questionable statistics，可以考虑将STATISTICS设置为NONE
OBJECT_CONSISTENT	只在对象导出期间设置为只读的事务处理 (N)
PARFILE	指定导出参数文件的名称，可以写入exp的所有参数。parfile=[directory_path] file_name。表比较多的情况下建议用parfile，各个参数在parfile里写好。tables=test.liu,test.liu2
Direct	直接路径，值为n|y，默认为N。            传统模式导出(n)：direct=n  datafile---->sga----->pga----->dump 直接路径导出(y)：direct=y  datafile---->pga----->dump       传统模式导出时使用select语句从表中取出数据，数据从磁盘上先读到buffer cache中，记录被转移到一个评估检测的缓冲区中（SGA），数据经过语法检测后没有问题，将数据传给PGA，最后写入导出的文件中。        直接路径导出数据直接从磁盘上读取到导出的PGA中，记录直接被转换导出会话的私有buffer中。意味着SQL语句处理层被忽略掉了，因为数据已经是符合导出的格式了，不需要其他的转换处理了。数据直接被传送给导出的客户端，最后写入导出文件。         传统路径导出 VS 直接路径导出(oracle exp direct=y)直接路径导出的限制               a、直接路径导出不支持交互模式               b、不支持表空间传输模式(即 transport_tablespaces=y 不被支持)，支持的是full,owner,tables 导出方式               c、不支持query查询方式，如 exp scott/tiger tables=emp query=\"where job=\'salesman\' \"不被支持               d、直接路径导出使用 recordlength 设置一次可以导出数据的量，取代传统路径使用 buffer 的设置             e、直接路径导出要求 nls_lang 环境参数等于数据库字符集，负责收到 exp-41警告及exp-0终止错误

                    

exp导出案例：

案例1：实例完全导出(普通用户完全导出，需要有EXP_FULL_DATABASE和IMP_FULL_DATABASE角色)

[oracle@11g ~]$ exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 full=y file=exp_orcl_full.dmp log=exp_orcl_full.log
###在linux/unix上需要设置  "/ as sysdba"  为  \"/ as sysdba\"  转译符号。

                

案例2：导出时限制导出文件大小（os文件大小有限制或者导出量太大）

[oracle@11g ~]$ exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 full=y file=exp_orcl_full_01.dmp,exp_orcl_full_02.dmp log=exp_orcl_full.log filesize=200g
###exp导出之前先预估数据量，避免因为限制了文件而导致错误，如果file指定了2文件，限制每个文件200g，如果数据量有450g，多出来的数据，exp会自动弹出expdat.dmp，也就是用这个文件名继续导出。

注意：exp中没有%U参数，设置file=exp_orcl_full_%U.dmp，导出时不会进行生成1、2、3..文件，expdp支持

            

案例3：用户模式：(用户的所有对象被输出到文件中)

方式一：通过生产用户直接导出，但是只能导出这一个用户的数据

[oracle@11g ~]$ exp user1/password compress=n buffer=4096000 feedback=100000 file=exp_orcl_user1.dmp log=exp_orcl_user1.log

              

方式二：使用sys用户，在linux /unix上需要设置"/ as sysdba"为 \"/ as sysdba\"转译符号。这种方式可以导出多个生产用户

[oracle@11g ~]$ exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 owner=user1,user2 file=exp_orcl_user1.dmp log=exp_orcl_user1.log

               

案例4：表模式：(用户的表被输出到文件中)

方式一：使用sys用户，在linux /unix上需要设置"/ as sysdba"为 \"/ as sysdba\"转译符号

[oracle@11g ~]$ exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 tables=user1.table_name1,user1.table_name2 file=exp_orcl_table.dmp log=exp_orcl_table.log

            

方式二：通过生产用户直接导出

[oracle@11g ~]$ exp user1/password compress=n buffer=4096000 feedback=100000 tables=table_name1,table_nam2 file=exp_orcl_table.dmp log=exp_orcl_table.log

             

案例5：导出全库所有对象包括表结构，但不包含表数据（rows=n）

[oracle@11g ~]$ exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 full=y file=exp_orcl_full.dmp log=exp_orcl_full.log rows=n

             

案例6：导出生产用户某些表的前10行（query=" 'where rownum<10' "）

[oracle@11g ~]$ exp user1/password compress=n buffer=4096000 feedback=100000 tables=table_name1 file=exp_orcl_table.dmp log= exp_orcl_table.log query=" 'where rownum<10' "     
###普通用户导出时需要EXP_FULL_DATABASE和IMP_FULL_DATABASE角色，dba权限包括这2个角色

                  

2、imp用法：

imp -help选项：

参数选项	描述
Userid	用于指定执行导入操作的用户名，口令，连接字符串
Tables	用于指定执行导入操作的表
Formuser	指出导出时dmp文件中记载的用户信息（源用户数据），多用户时fromuser=(user1,user2)。配合touser使用
Touser	dmp 文件要导入到什么目标用户中（目标用户）。配合formuser使用
ROWS	是否导入数据，默认为y，表示导入数据
FULL	指定数据库模式导入，默认为n。为y时，表示执行数据库全库导入
Ignore	默认n（有表时中断导入，不冲突的数据也不导入），建议y。 恢复数据时，当恢复到某个已经存在的表，就要根据ignore参数的设置来决定如何操作。若ignore=Y，Oracle不执行CREATE TABLE 语句，直接将数据插入到表中。如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。 注意：如果表中的字段并没有唯一性约束，那么在使用ignore=y的情况下很有可能插入重复数据，ignore=y类似impdp的TABLE_EXISTS_ACTION=append，没违反约束的值会全部插入
Buffer	指数据行的缓冲区大小，默认值根据系统而定，通常应设置为高值，exp 的buffer 最好>64000，imp 的buffer 最好>100000
Show	导入时可以显示执行的DDL语句，默认n
commit	默认N，及在每插入完一个对象后提交。当COMMIT=Y时是根据BUFFER的大小决定每次提交的数量。对于包含了LONG、RAW、DATE等类型的表，不论BUFFER设置多大，都是每插入一行进行提交。设置commit=y可以防止减少回滚段的压力，但由于频繁提交，会带来性能上的影响，推荐使用COMMIT=N
PARFILE	指定导入参数文件的名称，可以写入imp的所有参数。parfile=[directory_path] file_name。表比较多的情况下建议用parfile，各个参数在parfile里写好。tables=test.liu,test.liu2
TRANSPORT_TABLESPACE	导入可传输的表空间元数据
TABLESPACES	将要传输到数据库的表空间
DATAFILES	将要传输到数据库的数据文件
TTS_OWNERS	拥有可传输表空间集中数据的用户

                

imp导出案例：

对于imp而言：除了在恢复全库数据外(FULL=Y)，对恢复单个用户、表对象时，fromuser/touser或者TABLES参数是必不可少的。

案例1：完全导入

[oracle@11g ~]$ imp \"/ as sysdba\" buffer=4096000 feedback=100000 full=y file=exp_orcl_full.dmp log=imp_orcl_full.log ignore=y
###使用sys用户，在linux/unix上需要设置  "/ as sysdba"  为  \"/ as sysdba\"  转译符号。

              

案例2：用户导入

方式一：使用sys用户，在linux /unix上需要设置"/ as sysdba"为 \"/ as sysdba\"转译符号

[oracle@11g ~]$ imp \"/ as sysdba\" fromuser=user1 touser=user1 buffer=4096000 feedback=100000 file=exp_orcl_user1.dmp log=imp_orcl_user1.log  ignore=y

        

方式二：通过生产用户直接导入

[oracle@11g ~]$ imp  user1/password  fromuser=user1  touser=user1  buffer=4096000  feedback=100000  file=exp_orcl_user1.dmp  log=imp_orcl_user1.log  ignore=y

              

案例3：表导入（不支持在导入时重命名，也就是不支持异表导入）

方式一：使用sys用户，在linux /unix上需要设置"/ as sysdba"为 \"/ as sysdba\"转译符号

[oracle@11g ~]$ imp \"/ as sysdba\" fromuser=user1 touser=user1 buffer=4096000 feedback=100000 tables=table_name1,table_name2 file=exp_orcl_table.dmp log=imp_orcl_table.log  ignore=y

            

方式二：通过生产用户直接导入

[oracle@11g ~]$ imp user1/password fromuser=user1 touser=user1 buffer=4096000 feedback=100000 tables=table_name1,table_nam2 file=exp_orcl_table.dmp log=imp_orcl_table.log ignore=y  

                      

3、exp/imp导出和导入常见问题

             

1）imp导入时避免磁盘排序。涉及到 sort_area_size 参数,也就是我们的 PGA 要够大。

                 

2）imp导入时避免日志切换等待。

解决版本：增加重做日志组的数量,增大日志文件大小  

           

3）一般来说，从低版本数据库导入到高版本问题不大，麻烦的是将高版本数据库的数据导入到低版本中，所以必须正确地使用 EXP 和 IMP 的版本。

解决办法：可以跨版本的使用 EXP/IMP，但必须正确地使用EXP和IMP的版本（官方可查）：

1）使用IMP的版本匹配数据库的版本，如：要导入到 11.2中，使用11.2 的 IMP 工具。

2）使用EXP的版本匹配两个数据库中最低的版本，如：从11.2往10.2中导入，则使用10.2版本的EXP工具。

3）高版本的Export导出来的转储文件，低版本的Import读不了；低版本的 Export导出来的转储文件，高版本的Import可以进行读取。

4）从Oracle低版本的Export 数据可以Import到Oracle高版本中，但限于Oracle的相邻版本，两个不相邻版本间进行转换应借助中间版本。

5）exp/imp可以做到在不同版本Oracle、不同数据库上的迁移。

                 

4）imp导入时数据库对象已经存在

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

                 

5）imp导入时权限不足

解决办法：如果要把A用户的数据导入B用户下, A 用户需要有imp_full_database权限

             

6）imp导入时大表存储分配失败

解决办法：默认EXP参数compress = y，也就是把所有的数据压缩在一个数据块上导入时，如果不存在连续一个大数据块则会导入失败，导出大表时记得compress= n, 则不会引起这种错误。

                                

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

              

8）当需要exp/imp的数据量比较大时，这个过程需要的时间是比较长的，使用直接路径direct=y的话oracle会避开sql语句处理引擎，直接从数据库文件中读取数据，然后写入导出文件。可以在导出日志中观察到exp-00067: table xxx will be exported in conventional path。如果没有使用直接路径，必须保证buffer参数的值足够大，有一些参数和direct=y不兼容，无法用直接路径导出可移动的tablespace，或者用query参数导出数据库子集。当导入导出的数据库运行在不同的os下时，必须保证recordlength参数的值一致。

                

4、linux系统上制定exp的定时全库备份

    通过crontab制定备份策略，并结合exp工具的使用，我们可以有效地保障数据库数据的完整性和可恢复性。

（1）数据备份的路径规划：

在root用户下进行路径规划：
[root@lf ~]$ mkdir /backup      
###通过存储挂到/backup目录下，权限oracle用户
       
[root@lf ~]$ cd /backup         
[root@lf backup]$ mkdir full
[root@lf backup]$ mkdir script
[root@lf backup]# mkdir logs    
### full（exp全备的数据）、script（备份脚本）、logs（备份日志）
        
[root@lf /]# chown -R oracle:oinstall /backup/

（2）exp全备脚本

[root@lf ~]$ su - oracle   
[oracle@lf backup]$ cd script/
[oracle@lf script]$ vi exp_orcl_full.sh
         
export BAKDATE=`date +%Y%m%d` 
export ORACLE_SID=orcl            
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
       
nohup $ORACLE_HOME/bin/exp \"/ as sysdba\" compress=n buffer=4096000 feedback=100000 full=y file=/backup/full/exp_${ORACLE_SID}_full_${BAKDATE}.dmp log=/backup/logs/exp_${ORACLE_SID}_full_${BAKDATE}.log &
               
find /backup/logs -name "exp_${ORACLE_SID}_full_*.log" -mtime +2 -exec rm -rf {} \;
find /backup/full -name "exp_${ORACLE_SID}_full_*.dmp" -mtime +2 -exec rm -rf {} \;    ###mtime +2 -exec rm -rf {} \; 表示删除目录下2天之前被修改过的文件。写为mtine是因为默认输入ls -l（ll）命令输出的内容中就是最后修改时间 (mtime)。rman 进行crosscheck就会把所有的备份片访问一遍，那么如果写-atime（被访问过的文件）就不是实际之前的文件了，从而导致删除失效。因为计划任务是每天执行，所以find查出2天之前的进行rm删除，也就是保留2天的2份。

（3）在oracle用户下定义exp全备和清理备份的计划任务 

[root@lf ~]$ su - oracle
[oracle@lf script]# chmod 775 /backup/script/*.sh  
[oracle@lf script]# crontab -e
00 05 * * * sh /backup/script/exp_orcl_full.sh         ###每日凌晨5进行一次exp全备

（4）oracle用户下测试脚本可用性，并查看日志

[oracle@lf script]# sh /backup/script/exp_orcl_full.sh
[oracle@lf logs]$ tail -2000f /backup/logs/exp_orcl_full*.log

---

    好啦！今天的内容就分享到这里，那么下一篇文章将详细介绍另一款逻辑迁移工具expdp/impdp。相比于exp/imp，expdp/impdp最大的优势在于支持并行导出和导入，这在效率上比exp/imp要高很多。因此，在Oracle 10g及以后的版本中，expdp/impdp成为了更受欢迎的选择。那么，我们下一篇文章再见！