sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle跨数据库版本 exp导出 imp导入数据测试案例

时间:2016-07-22 22:49   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 使用exp/imp 导入11g数据到9i,运维DBA反映在Oracle数据库跨版本的导入导出时报错,总结一下Oracle数据库使用逻辑导出exp导入imp迁移数据库的测试案例

现在有个需求,需要使用exp/imp导入11g的数据库数据到9i中,解决这个问题一般来说想到三种方法思路,一个个尝试(其实从高版本服务端支持低版本客户端的原则,可以大概的猜测出使用9i的客户端处理该问题)

方法1:导出导入都使用11g客户端

--11g客户端导出
[oracle@oracleplus ~]$ exp chf/oracleplus file=/tmp/t_oracleplus.dmp 
>log=/tmp/t_oracleplus.log tables=chf.t_oracleplus

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_oracleplus          2 rows exported
Export terminated successfully without warnings.

--11g客户端导入
[oracle@oracleplus ~]$ imp chf/oracleplus@ora9i file=/tmp/t_oracleplus_11g.dmp 
>log=/tmp/t_oracleplus.log tables=chf.t_oracleplus

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@oracleplus ~]$ exp chf/oracleplus file=/tmp/t_oracleplus.dmp 
>log=/tmp/t_oracleplus.log tables=chf.t_oracleplus

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_oracleplus          2 rows exported
Export terminated successfully without warnings.

--传输到9i
[oracle@oracleplus tmp]$ scp t_oracleplus.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_oracleplus.dmp                          100%   56KB  56.0KB/s   00:00    

--9i客户端导入
[oracle@oracleplus ~]$ imp chf/oracleplus file=/tmp/t_oracleplus.dmp tables=t_oracleplus

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@oracleplus ~]$ exp chf/oracleplus@ora11g file=/tmp/t_oracleplus_11g.dmp 
>log=/tmp/t_oracleplus.log tables=chf.t_oracleplus

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_oracleplus          2 rows exported
Export terminated successfully without warnings.

--9i客户端导入
[oracle@oracleplus log]$ imp chf/oracleplus file=/tmp/t_oracleplus_11g.dmp log=/tmp/oracleplus.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异常终止

--修改exu9defpswitches视图
[oracle@oracleplus ~]$ 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.

--9i导出11g数据
[oracle@oracleplus tmp]$ exp chf/oracleplus@ora11g file=/tmp/t_oracleplus_11g.dmp 
>log=/tmp/oracleplus.log tables=t_oracleplus

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_oracleplus          2 rows exported
Export terminated successfully without warnings.

--9i导入数据
[oracle@oracleplus tmp]$ imp chf/oracleplus file=/tmp/t_oracleplus_11g.dmp 
>log=/tmp/oracleplus.log tables=t_oracleplus

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_oracleplus"          2 rows imported
Import terminated successfully without warnings.
--至此导入成功,完成了11gr2数据导入到9ir2中

通过一系列的实验证明,需要把11g的数据导入到9i中,需要使用9i的客户端进行,其中exu9defpswitches视图需要重建,否则会出现setSegmentation fault异常,导致导入失败.


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle跨数据库版本 exp导出 imp导入数据测试案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/837.html

Oracle研究中心

关键词:

Oracle跨数据库版本逻辑导入导出测试

Oracle imp导入时报错PLS-00302的解决办法

IMP-00010 IMP-00000产生原因与解决办法

导入数据遇到setSegmentation fault异常终止的解决办法