用EXP/IMP从高版本数据库导出至低版本数据库导入实验

简介: 一般来说,从低版本数据库EXP数据,导入至高版本数据库是没什么问题的,因为Oracle数据库在开发设计的时候,考虑了同以前版本的兼容。但是从高本数据库EXP导出数据,导入至低版本数据库,经常会有各种各样的问题。

一般来说,从低版本数据库EXP数据,导入至高版本数据库是没什么问题的,因为Oracle数据库在开发设计的时候,考虑了同以前版本的兼容。但是从高本数据库EXP导出数据,导入至低版本数据库,经常会有各种各样的问题。

在,在Oracle9i之前,不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决:
1)、在高版本数据库上运行底版本的catexp.sql;11G的库里运行10G数据库软件下ORACLE_HOME/rdbms/admin/catexp.sql
2)、使用低版本的EXP来导出高版本的数据;
3)、使用低版本的IMP将数据库导入到低版本数据库中;
4)、在高版本数据库上重新运行高版本的catexp.sql脚本。

而大家目前用的最多的版本是Oracle 10G和11G,以前积累的经验是否还适用,我们在此以实验说明。

首先说明实验环境,笔者的实验环境是一个是10.0.2.0.4,作为导入数据的目标库

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

一个是11.2.0.3,作为实验数据导出的源库

SQL> select * from v$version;     

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


1. 首先在源库上面创建实验对象

SQL> create tablespace test datafile '+zhuxgasm' size 50M;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant dba,connect,resource to test;

Grant succeeded.

SQL> conn test/test;
Connected.
SQL> create table t1 as select * from dba_objects where rownum
Table created.

在这一步我们创建了实验用户test,并且创建了一个表T2,往里面插入了60行的记录。

2.在高版本数据库中生成导出文件,验证直接从高版本数据生成DMP文件,导入低版本数据库的效果。

[oracle@zhuxg ~]$ exp system/kingstar file=/home/oracle/test01.dmp wner=test;

Export: Release 11.2.0.3.0 - Production on Sat May 11 12:00:09 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 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                             T1         60 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@zhuxg ~]$ ls

Desktop  test01.dbf  test01.dmp  tts_test.dmp

从高版本数据库生成了一个DMP文件test01.dmp

3. 将dmp文件从高版本数据库机器拷贝至低版本数据库机器

[oracle@zhuxg ~]$ scp test01.dmp 192.168.192.8:/home/oracle/
The authenticity of host '192.168.192.8 (192.168.192.8)' can't be established.
RSA key fingerprint is 44:bc:69:cd:9d:2b:b3:97:c7:65:55:4f:f3:5b:97:3c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.192.8' (RSA) to the list of known hosts.
oracle@192.168.192.8's password:
Permission denied, please try again.
oracle@192.168.192.8's password:
test01.dmp                                                                                                                                                   100%   16KB  16.0KB/s   00:00    
4. 将数据导入至低版本数据库
[oracle@book ~]$ imp system/kingstar file=test01.dmp fromuser=test touser=test

Import: Release 10.2.0.4.0 - Production on Sat Feb 9 05:05:03 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

IMP-00010: not a valid export file, header failed verification

IMP-00000: Import terminated unsuccessfully

此路不通,直接从高版本数据库导出DMP文件,往低版本数据库插入,跟我们预想的一样,不行。

接下来,我们按照以前大家的经验做法,拷贝低版本$ORACLE_HOME/rdbms/admin下的catexp.sql到高版本客户端(服务端),用sysdba权限执行,然后用EXP工具导出DMP文件,在低版本数据库客户端(服务端)导入。

5. 拷贝10G $ORACLE_HOME/rdbms/admin下的catexp.sql至11G机器,并且用sysdba权限运行

[oracle@book admin]$ scp catexp.sql 192.168.192.2:/home/oracle/
oracle@192.168.192.2's password:
catexp.sql                                                                                                                                                   100%  381KB 381.0KB/s   00:00    
[oracle@zhuxg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 11 12:31:25 2013

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>  @ /home/oracle/catexp.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following PL/SQL block will cause an ORA-20000 error and
DOC>    terminate the current SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.

CREATE ROLE exp_full_database
            *
ERROR at line 1:
ORA-01921: role name 'EXP_FULL_DATABASE' conflicts with another user or role
name



Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

。。。。。。

更新的结果很长,这里只截取了一小部分用于展示。

6. 再次从11数据库导出dmp文件,并传输至10G数据库服务器

[oracle@zhuxg ~]$ exp system/kingstar file=/home/oracle/test02.dmp wner=test;

Export: Release 11.2.0.3.0 - Production on Sat May 11 12:36:35 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 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully

这里导出就有问题了。说明这个方法现在已经不可行了,那么还有没有其他方法呢 ?

我们重新运行高版本数据库的catexp.sql,使高版本数据其恢复回原来的模样。

SQL> @?/rdbms/admin/catexp.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following PL/SQL block will cause an ORA-20000 error and
DOC>    terminate the current SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.


Role created.


Grant succeeded.

。。。。。。。。。后面反馈信息省略。

其他的方法是什么呢,就是在低版本的客户端(服务端)直接用网络服务 连接高版本数据库,然后用EXP工具导出DMP文件。如下:

7. 用低版本的联通高版本的网络服务,然后用EXP导出。

[oracle@book admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB77 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 112.65.228.77)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sgmparts)
    )
  )

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.192.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ASMDB)
    )
  )

[oracle@book ~]$  exp system/kingstar@DB11G file=/home/oracle/test04.dmp wner=test;

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 06:11:13 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                             T1         60 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.


8. 在低版本数据库中导入

[oracle@book admin]$ imp test/test file=/home/oracle/test03.dmp  full=y

Import: Release 10.2.0.4.0 - Production on Sat Feb 9 06:15:12 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYSTEM's objects into TEST
. . importing table                           "T1"         60 rows imported

Import terminated successfully without warnings.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
        60

导入成功。

相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
4天前
|
关系型数据库 MySQL Linux
Linux启动停止重启Mysql数据库针对各个版本的数据库
Linux启动停止重启Mysql数据库针对各个版本的数据库
11 0
|
4天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
104 0
|
4天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
12 0
|
4天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
18 0
|
4天前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
33 0
|
4天前
|
easyexcel Java 关系型数据库
厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中
厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中
10 1
|
4天前
|
前端开发 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
25 0
|
4天前
|
存储 关系型数据库 MySQL
XtraBackup支持哪些MySQL数据库版本?
【5月更文挑战第13天】XtraBackup支持哪些MySQL数据库版本?
17 0
|
4天前
|
存储 Oracle 关系型数据库
实验三 Oracle数据库的创建和管理
实验三 Oracle数据库的创建和管理
13 1
|
4天前
|
SQL Oracle 关系型数据库
实验一 安装和使用Oracle数据库
实验一 安装和使用Oracle数据库
16 1