【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。
RECORDLENGTHIO记录的长度(最大为 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使用
Touserdmp 文件要导入到什么目标用户中(目标用户)。配合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成为了更受欢迎的选择。那么,我们下一篇文章再见!

  • 40
    点赞
  • 35
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

奈斯DBA

打赏到账,我飘啦~

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值