ORACLE 11g导入9i dump的问题及解决

因为系统迁移,需要将一部分的9i的数据导入11g的库里, 目标库是11.2.0.3.0 64位的环境。 导入dump的时候,有一个比较大的分区表,需要用导入分区的方式,就写了如下的命令。但是奇怪的是过了一会,抛出来一个imp的错误就完事了。

-bash-4.1$ imp n1/n1@testdb tables=TEST_DB:TEST_DB_PAR_P1        file=TEST_DBP1.dmp     ignore=Y statistics=none grants=n indexes=n buffer=9102000 log=imp_log_TEST_DB_PAR_P1_test
Import: Release 11.2.0.3.0 - Production on Wed Mar 19 17:05:33 2014
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, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via direct path
Warning: the objects were exported by SYSTEM, not by you
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHT16MSWIN950 character set (possible charset conversion)
. importing SYSTEM's objects into N1
IMP-00055: Warning: partition or subpartition "TEST_DB":"TEST_DB_PAR_P1" not found in export file
Import terminated successfully with warnings.

我以为我的表名和分区写的有问题,检查了一下,都在的。 为了继续,然后尝试直接按表导入,但是还是导不进去,不过报错信息倒是不太一样了。

-bash-4.1$ imp n1/n1@testdb tables=TEST_DB       file=TEST_DBP1.dmp     ignore=Y statistics=none grants=n indexes=n buffer=9102000 log=imp_log_TEST_DB_PAR_P1_test
Import: Release 11.2.0.3.0 - Production on Wed Mar 19 17:04:11 2014
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, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via direct path
Warning: the objects were exported by SYSTEM, not by you
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHT16MSWIN950 character set (possible charset conversion)
. importing SYSTEM's objects into N1
IMP-00033: Warning: Table "TEST_DB" not found in export file
Import terminated successfully with warnings.

开始怀疑是不是dump有问题了,确认了下checksum也是一样的。 最后尝试如下的方式,加了 fromuser ,touser选项,竟然可以了。

imp n1/n1@testdb tables=TEST_DB:TEST_DB_PAR_P1      fromuser=TEST_APPO touser=PSCNVDBO1   file=TEST_DBP1.dmp     ignore=Y statistics=none grants=n indexes=n buffer=9102000 log=imp_log_TEST_DB_PAR_P1           &
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via direct path
Warning: the objects were exported by SYSTEM, not by you
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses ZHT16MSWIN950 character set (possible charset conversion)
. importing TEST_APPO's objects into N1
. . importing partition "TEST_DB":"TEST_DB_PAR_P1"   29673834 rows imported
Import terminated successfully without warnings.

查看metalink,也没有发现类似的帖子。也算自己为ORACLE出了一点力吧。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

impdp异常中断导致的问题(r2第8天)

今天查看数据库的负载,发现cpu消耗异常的高。里面有不少dw的进程.但是查看impdp的进程却不存在。 查看datapump的进程情况,发现大量的job,但是状...

38911
来自专栏数据库新发现

Oracle诊断案例-Job任务停止执行[最终版]

Last Updated: Friday, 2004-11-26 9:48 Eygle

1253
来自专栏一个会写诗的程序员的博客

mysql 字符串函数 length(@str) select substring(@str,1,4)

需要注意的,mysql 的 substring(@str,1,4) 位置是从 1 开始的。

1062
来自专栏杨建荣的学习笔记

ORA-00439的解决(笔记93天)

今天用gc配置了stream后,重启数据库后发现有以下的问题 SQL> startup ORA-00439: feature not enabled: Real...

34510
来自专栏杨建荣的学习笔记

10g,11g数据泵的导入问题及解决(19天)

今天在环境上测试expdp/impdp,环境有10.2.0.5.0,11.2.0.2.0的,11g的环境是从10g升级到11gde .是在impdp的时候都报了...

3694
来自专栏杨建荣的学习笔记

10g升级至11g exp的问题解决(23天)

昨天升级数据库,从10.2.0.5.0升级到11.2.0.2.0.按照预定的步骤很快就操作完了。升级完成后,开始跑一些应用和Job.有一个Job开始报错,Job...

4946
来自专栏数据和云

返璞归真:如何判断一个初始化参数是否来自默认设置

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 Oracle初...

2847
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之二

http://www.eygle.com/case/sql_trace_2.htm

1124
来自专栏Netkiller

CentOS7 下 MySQL 5.7 重置root密码

本文节选自《Netkiller MySQL 手札》 CentOS 7.x 添加 skip-grant-tables=1 选项,然后重启mysql # cat /...

3724
来自专栏岑玉海

Hbase 学习(十一)使用hive往hbase当中导入数据

  我们可以有很多方式可以把数据导入到hbase当中,比如说用map-reduce,使用TableOutputFormat这个类,但是这种方式不是最优的方式。 ...

48313

扫码关注云+社区

领取腾讯云代金券