使用exp/imp 导入11g数据到9i
浏览:1319次 出处信息
现在有个需求,需要使用exp/imp导入11g的数据库数据到9i中,解决这个问题一般来说想到三种方法思路,一个个尝试(其实从高版本服务端支持低版本客户端的原则,可以大概的猜测出使用9i的客户端处理该问题)
方法1:导出导入都使用11g客户端
--11g客户端导出 [oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp >log=/tmp/t_xifenfei.log tables=chf.t_xifenfei Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012 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 - Production With the Partitioning, OLAP, Data Mining 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 T_XIFENFEI 2 rows exported Export terminated successfully without warnings. --11g客户端导入 [oracle@xifenfei ~]$ imp chf/xifenfei@ora9i file=/tmp/t_xifenfei_11g.dmp >log=/tmp/t_xifenfei.log tables=chf.t_xifenfei Import: Release 11.2.0.3.0 - Production on Fri May 18 18:17:24 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. IMP-00058: ORACLE error 6550 encountered ORA-06550: line 1, column 33: PLS-00302: component \'SET_NO_OUTLINES\' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored IMP-00000: Import terminated unsuccessfully
这个错误是版本不兼容导致:PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
方法2:11g客户端导出,9i客户端导入
--11g客户端导出 [oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp >log=/tmp/t_xifenfei.log tables=chf.t_xifenfei Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012 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 - Production With the Partitioning, OLAP, Data Mining 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 T_XIFENFEI 2 rows exported Export terminated successfully without warnings. --传输到9i [oracle@xifenfei tmp]$ scp t_xifenfei.dmp 192.168.1.10:/tmp/ The authenticity of host \'192.168.1.10 (192.168.1.10)\' can\'t be established. RSA key fingerprint is 3d:0c:d1:4b:45:bd:a3:f5:25:eb:4d:52:d2:32:03:69. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added \'192.168.1.10\' (RSA) to the list of known hosts. oracle@192.168.1.10\'s password: t_xifenfei.dmp 100% 56KB 56.0KB/s 00:00 --9i客户端导入 [oracle@xifenfei ~]$ imp chf/xifenfei file=/tmp/t_xifenfei.dmp tables=t_xifenfei Import: Release 9.2.0.4.0 - Production on Thu May 24 23:32:18 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production IMP-00010: not a valid export file, header failed verification IMP-00000: Import terminated unsuccessfully --版本不兼容(高版本的dump文件低版本不能识别)
方法3:9i客户端导出,9i客户端导入
--9i客户端导出 [oracle@xifenfei ~]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp >log=/tmp/t_xifenfei.log tables=chf.t_xifenfei Export: Release 9.2.0.4.0 - Production on Thu May 24 23:37:20 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining 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 T_XIFENFEI 2 rows exported Export terminated successfully without warnings. --9i客户端导入 [oracle@xifenfei log]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp log=/tmp/xifenfei.log full=y Import: Release 9.2.0.4.0 - Production on Fri May 25 03:22:14 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export file created by EXPORT:V09.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character setSegmentation fault --导入数据遇到setSegmentation fault异常终止
解决setSegmentation fault异常终止
--1.修改exu9defpswitches视图 [oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri May 18 22:29:00 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> CREATE OR REPLACE VIEW exu9defpswitches ( 2 compflgs, nlslensem ) AS 3 SELECT a.value, b.value 4 FROM sys.v$parameter a, sys.v$parameter b 5 WHERE a.name = \'plsql_code_type\' AND 6 b.name = \'nls_length_semantics\' ; View created. --2.9i导出11g数据 [oracle@xifenfei tmp]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp >log=/tmp/xifenfei.log tables=t_xifenfei Export: Release 9.2.0.4.0 - Production on Fri May 25 04:08:32 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining 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 T_XIFENFEI 2 rows exported Export terminated successfully without warnings. --9i导入数据 [oracle@xifenfei tmp]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp >log=/tmp/xifenfei.log tables=t_xifenfei Import: Release 9.2.0.4.0 - Production on Fri May 25 04:08:53 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export file created by EXPORT:V09.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF\'s objects into CHF . . importing table "T_XIFENFEI" 2 rows imported Import terminated successfully without warnings. --至此导入成功,完成了11gr2数据导入到9ir2中
通过一系列的实验证明,需要把11g的数据导入到9i中,需要使用9i的客户端进行,其中exu9defpswitches视图需要重建,否则会出现setSegmentation fault异常,导致导入失败.
建议继续学习:
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:sql_id和hash value的部分转换
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: exp imp
- 发布时间:2012-05-28 13:21:29
建议继续学习
近3天十大热文
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [52] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Go Reflect 性能
- [49] Oracle MTS模式下 进程地址与会话信
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [35] 程序员技术练级攻略
- [27] 视觉调整-设计师 vs. 逻辑