联系：QQ(5163721)
标题：使用exp+pipe的方式直接将数据库导出到目标数据库（数据不落地成dmp）
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
最早在oracle 8，Oracle 8i，Oracle 9i的时候，没有datapump，因此逻辑数据库迁移的工作多依赖于exp/imp，
然而，当磁盘空间有限制，且停机时间短的时候，通常我们需要一种方式是数据不落地。
那时候，我们常用方式就是借助于管道，也就是exp将数据库导出到pipe，目标段通过管道直接将数据imp到目标数据库，这样数据时不落地的，省去了生成dmp文件的时间和空间，O(∩_∩)O哈哈~
当然，类似应用还有很多，比如可以使用管道直接讲exp的数据存储到tape，或者直接exp的数据导出成压缩格式，后面会一次测试。
这里先测试，exp to pipe and imp from pipe:
[oracle@lunar exp-pipe]$ mknod /tmp/exp_pipe p [oracle@lunar exp-pipe]$ [oracle@lunar exp-pipe]$ ifconfig eth0 Link encap:Ethernet HWaddr 08:00:27:43:8D:35 inet addr:192.168.56.66 Bcast:192.168.56.255 Mask:255.255.255.0 inet6 addr: fe80::a00:27ff:fe43:8d35/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:5971 errors:0 dropped:0 overruns:0 frame:0 TX packets:4147 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:555804 (542.7 KiB) TX bytes:550356 (537.4 KiB) [oracle@lunar imp_pipe]$ mknod /tmp/imp_pipe p [oracle@lunar imp_pipe]$ [oracle@lunar imp_pipe]$ ifconfig eth0 Link encap:Ethernet HWaddr 08:00:27:F1:72:D0 inet addr:192.168.56.101 Bcast:192.168.56.255 Mask:255.255.255.0 inet6 addr: fe80::a00:27ff:fef1:72d0/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:5444 errors:0 dropped:0 overruns:0 frame:0 TX packets:4332 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:489891 (478.4 KiB) TX bytes:366032 (357.4 KiB) |
使用oracle安装包的ssh脚本配置两个机器的互信，超简单，这里不赘述
然后在第一个窗口输入：
[oracle@lunar ~]$ dd if=/tmp/exp_pipe | ssh 192.168.56.101 dd of=/tmp/imp_pipe 。。。。 |
这里会停在这个界面，等待管道的输入
然后再开一个session，使用exp讲数据库输入到管道中：
[oracle@lunar ~]$ exp lunar/lunar file=/tmp/exp_pipe log=ff.log tables=ff Export: Release 11.2.0.3.0 - Production on Sun Oct 27 00:48:17 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table FF 。。。。。。。。。 |
这个界面会等待目标段接收。。。。。
然后在目标段开启一个会话：
[oracle@lunar imp_pipe]$ imp lunar/lunar file=/tmp/imp_pipe log=ff.log tables=ff Import: Release 11.2.0.3.0 - Production on Sun Oct 27 16:48:11 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing LUNAR's objects into LUNAR . importing LUNAR's objects into LUNAR . . importing table "FF" 141112 rows imported Import terminated successfully without warnings. [oracle@lunar imp_pipe]$ |
然后可以看到，数据传输完成时，其他会话都完成了：
[oracle@lunar ~]$ dd if=/tmp/exp_pipe | ssh 192.168.56.101 dd of=/tmp/imp_pipe 29856+0 records in 29856+0 records out 15286272 bytes (15 MB) copied, 61.7143 seconds, 248 kB/s 29856+0 records in 29856+0 records out 15286272 bytes (15 MB) copied, 3.78268 seconds, 4.0 MB/s [oracle@lunar ~]$ [oracle@lunar ~]$ exp lunar/lunar file=/tmp/exp_pipe log=ff.log tables=ff Export: Release 11.2.0.3.0 - Production on Sun Oct 27 00:48:17 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table FF 141112 rows exported Export terminated successfully without warnings. [oracle@lunar ~]$ |
现在在目标段查看表是否已经被导入了：
[oracle@lunar imp_pipe]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 27 16:51:13 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options SYS@bb>conn lunar/lunar Connected. LUNAR@bb>select count(*) from ff; COUNT(*) ---------- 141112 LUNAR@bb> |
O(∩_∩)O哈哈~，搞定！
