A-A+

数据库高版本到低版本跨操作系统位数跨用户的数据导入导出

2013年01月15日 Backup&Recovery, Database 暂无评论 阅读 1,358 次

数据库高版本到低版本跨操作系统位数跨用户的数据导入导出
在这篇文章中,我们将讨论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。

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: