ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6133字,纯文字阅读大概需要9分钟。
内容图文
![ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上](/upload/InfoBanner/zyjiaocheng/449/bc1935f6e7c241d7b874c3b92f21152b.jpg)
1,开始导入
先在实例2上建立管道目录:
[oracle@pttest4 ~]$ export ORACLE_SID=pt2;
[oracle@pttest4 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 3 21:22:43 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE OR REPLACE DIRECTORY dir_dump_t3 AS ‘/home/oracle/expdpimpdp/‘;
Directory created.
SQL>
开始导入全库备份集:
impdp \‘sys/syspddev@pt2 as sysdba\‘ directory=dir_dump_t3 dumpfile=expdpfull_pd_20150529_02.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE
......
<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址: http://blog.csdn.net/mchdba/article/details/46335861
原作者:黄杉 (mchdba)
2,导入报错:
ORA-02374: conversion error loading table "puser"."RES_APPROVE_CONTENT"
ORA-12899: value too large for column CREATED_POSITION_CD (actual: 24, maximum: 20)
3,怀疑导入目标错误,验证:
[oracle@pttest4 admin]$ export ORACLE_SID=pt2;
[oracle@pttest4 admin]$
[oracle@pttest4 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:06:27 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select name from v$database;
NAME
------------------
pt2
SQL>
4,检查两者的编码
到出库实例库1:
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET‘, ‘NLS_NCHAR_CHARACTERSET‘);
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
到导入的库实例2库
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET‘, ‘NLS_NCHAR_CHARACTERSET‘);
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
看到导出导入库实例1实例2的字符集不同,所以问题就在这里了
5,解决:
[oracle@pttest4 admin]$ export ORACLE_SID=pt2;
[oracle@pttest4 admin]$
[oracle@pttest4 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:06:27 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>connect username/password as SYSDBA;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK ;
ALTER DATABASE CHARACTER SET ZHS16GBK;
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
报字符集不兼容,此时下INTERNAL_USE指令不对字符集超集进行检查:
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
6,再去检查两个实例的编码
[oracle@pttest4 expdpimpdp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:32:12 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
pt2
SQL>
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET‘, ‘NLS_NCHAR_CHARACTERSET‘);
Warning: connection was lost and re-established
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
导出实例上:
[oracle@pttest4 expdpimpdp]$ export ORACLE_SID=pt1;
[oracle@pttest4 expdpimpdp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:32:50 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
pt1
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET‘, ‘NLS_NCHAR_CHARACTERSET‘);
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
此时,两个实例上字符集一模一样了,保持一致。
7,重新导入impdp
[oracle@pttest4 admin]$ export ORACLE_SID=pt2;
[oracle@pttest4 admin]$ impdp \‘sys/sysdevpd@pt2 as sysdba\‘ directory=dir_dump_t3 dumpfile=expdpfull_pd_20150529_02.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE
OK,一切正常,可以导入到实例2上面了。
ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上
标签:oracle 多实例 导入导出 expdp impdp
本文系统来源:http://blog.csdn.net/mchdba/article/details/46351687
内容总结
以上是互联网集市为您收集整理的ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上全部内容,希望文章能够帮你解决ORACLE 11G在同一台linux服务器从实例1全库导入到实例2上所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。