現在有個需求,需要使用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異常終止

 

--修改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.

--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異常,導致導入失敗.

 

創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(1) 人氣()