A-A+
数据库高版本到低版本跨操作系统位数跨用户的数据导入导出
数据库高版本到低版本跨操作系统位数跨用户的数据导入导出
在这篇文章中，我们将讨论exp/imp以及expdp/impdp从高版本到低版本，从64位操作系统到32位操作系统的数据迁移。当然这个也是之前我的大学同学有一次问我，当时有点忙只告诉了其思路而未实际操作，也不知道其后来是否搞定，现在就模拟一下。模拟场景：将源库（高版本64位）的一个用户移动到目标库（低版本32位）的另一个用户下。而至于从低版本到高版本的迁移，由于oracle的向前兼容性，也是很方便的，在这里就不再讨论了。
关于数据泵的使用，可以见我之前的博文：
Data Pump基本术语和概念：http://www.yallonking.com/database/582.html
Data Pump Export/Impdp操作实例：http://www.yallonking.com/database/591.html
（一）环境：
源库：64位的11.2.0.3.4的数据库版本，如下：
[root@rhl6 ~]# su - oracle [oracle@rhl6 ~]$ which sqlplus /u01/app/oracle/product/11.2.0/db_1/bin/sqlplus [oracle@rhl6 ~]$ file /u01/app/oracle/product/11.2.0/db_1/bin/oracle /u01/app/oracle/product/11.2.0/db_1/bin/oracle: setuid setgid ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped [oracle@rhl6 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE' 14275605 14275605 Tue Dec 25 00:45:11 CST 2012 DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU 13923374 13923374 Tue Dec 25 00:44:15 CST 2012 DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES 13696216 13696216 Tue Dec 25 00:42:27 CST 2012 DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES 13343438 13343438 Tue Dec 25 00:39:18 CST 2012 DATABASE PATCH SET UPDATE 11.2.0.3.1
目标库：32位的10.2.0.1.0的数据库版本，如下：
[root@ora10gr2 ~]# su - oracle [oracle@ora10gr2 ~]$ which oracle /u01/app/oracle/product/10.2.0/db_1/bin/oracle [oracle@ora10gr2 ~]$ file /u01/app/oracle/product/10.2.0/db_1/bin/oracle /u01/app/oracle/product/10.2.0/db_1/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped [oracle@ora10gr2 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE' [oracle@ora10gr2 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 08:05:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 608174080 bytes Fixed Size 1220844 bytes Variable Size 197136148 bytes Database Buffers 406847488 bytes Redo Buffers 2969600 bytes Database mounted. Database opened. SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
（二）模拟场景：将源库（高版本64位）的一个用户移动到目标库（低版本32位）的另一个用户下
2.1 首先使用expdp/impdp工具
2.1.1 首先使用expdp导出用户ora11g
SQL> select * from ora11g.test;
ID NAME INSERTTIME
---------- -------------------- -------------------
1 yallonking 2013/01/15 10:56:30
[oracle@rhl6 ~]$ expdp system/oracle directory=dump_files1 dumpfile=schema_exp.dump schemas=ora11g logfile=schema_exp.log
Export: Release 11.2.0.3.0 - Production on Tue Jan 15 10:59:24 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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_files1 dumpfile=schema_exp.dump schemas=ora11g logfile=schema_exp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ORA11G"."TEST" 5.820 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/dump_dir1/schema_exp.dump
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:01:14
2.1.2 ftp dump到目标端后进行导入操作
[oracle@ora10gr2 ~]$ cd /u01/app/oracle/dump_dir1/ [oracle@ora10gr2 dump_dir1]$ ls -l total 180 -rw-r--r-- 1 oracle oinstall 180224 Jan 14 19:31 schema_exp.dump
2.1.2 常规方式导入
[oracle@ora10gr2 ~]$ impdp system/oracle dumpfile=dump_files1:schema_exp.dump REMAP_SCHEMA=ora11g:test logfile=schema_imp.log Import: Release 10.2.0.1.0 - Production on Monday, 14 January, 2013 19:33:41 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 3.1 in dump file "/u01/app/oracle/dump_dir1/schema_exp.dump"
注意：此时会报版本错误相关信息
2.1.3 现在使用带参数version的expdp在高版本上导出高版本的库
[oracle@rhl6 dump_dir1]$ expdp system/oracle directory=dump_files1 dumpfile=ora11g_exp.dump schemas=ora11g version=10.2.0.1.0 Export: Release 11.2.0.3.0 - Production on Tue Jan 15 11:56:55 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, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_files1 dumpfile=ora11g_exp.dump schemas=ora11g version=10.2.0.1.0 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . exported "ORA11G"."TEST" 5.632 KB 1 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/dump_dir1/ora11g_exp.dump Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:58:30
由于Data Pump是在服务端执行的，所以其产生的dmp文件将会保存在服务端。
2.1.4 将dump文件ftp到目标端后，再正常导入到其他schema
[oracle@ora10gr2 dump_dir1]$ impdp system/oracle dumpfile=dump_files1:ora11g_exp.dump REMAP_SCHEMA=ora11g:test logfile=ora11g_imp.log
Import: Release 10.2.0.1.0 - Production on Monday, 14 January, 2013 20:04:03
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=dump_files1:ora11g_exp.dump REMAP_SCHEMA=ora11g:test logfile=ora11g_imp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST" 5.632 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 20:04:20
SQL> select name from v$database;
NAME
---------
ORA10GR2
SQL> select * from test.test;
ID NAME INSERTTIME
---------- -------------------- -------------------
1 yallonking 2013/01/15 10:56:30
2.2 使用exp/imp工具
使用exp/imp想实现以上过程，需要用低版本的exp/imp工具去处理高版本的exp以及低版本的imp。
2.2.1 首先使用低版本的exp去导出高版本的库的schema
删除目标库的表
SQL> conn test/test Connected. SQL> drop table test; Table dropped.
然后执行导出
[oracle@ora10gr2 ~]$ tnsping ora11gr2x64 TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 14-JAN-2013 20:13:04 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.214)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora11gr2))) OK (20 msec) [oracle@ora10gr2 ~]$ exp userid=system/oracle@ora11gr2x64 file='/u01/app/oracle/dump_dir1/ora11g_exp_2.dmp' DIRECT=y ROWS=y OWNER=ora11g LOG='/u01/app/oracle/dump_dir1/ora11g_exp_2.log' Export: Release 10.2.0.1.0 - Production on Mon Jan 14 20:21:20 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user ORA11G . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user ORA11G About to export ORA11G's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export ORA11G's tables via Direct Path ... . . exporting table TEST 1 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. [oracle@ora10gr2 dump_dir1]$ ls -l total 188 -rw-r--r-- 1 oracle oinstall 16384 Jan 14 20:21 ora11g_exp_2.dmp -rw-r--r-- 1 oracle oinstall 1369 Jan 14 20:21 ora11g_exp_2.log -rw-r--r-- 1 oracle oinstall 167936 Jan 14 20:03 ora11g_exp.dump
由于exp/imp是客户操作，所以导出的文件也就放在客户端了，咱们也不用再ftp了
2.2.2 导入到低版本库的其他用户下
[oracle@ora10gr2 dump_dir1]$ imp userid=system/oracle file='/u01/app/oracle/dump_dir1/ora11g_exp_2.dmp' FROMUSER=ora11g TOUSER=test LOG='/u01/app/oracle/dump_dir1/ora11g_imp_2.log'
Import: Release 10.2.0.1.0 - Production on Mon Jan 14 20:26:17 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ORA11G's objects into TEST
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.
SQL> select * from test.test;
ID NAME INSERTTIME
---------- -------------------- -------------------
1 yallonking 2013/01/15 10:56:30
至此，全部ok。
