Oracle 11g在同一台Linux服务器从实例1全库导入到实例2上

Oracle 11g在同一台Linux服务器从实例1全库导入到实例2上

前期导出命令：

[root@powerlong4 ~]# su – Oracle[oracle@powerlong4 ~]$ export ORACLE_SID=pt1;[oracle@powerlong4 ~]$ expdp \‘sys/systestpd as sysdba\’ DIRECTORY=dir_dump_t3 FULL=YES DUMPFILE=expdpfull_pd_20150529_02.dmp……1，开始导入

先在实例2上建立管道目录：

[oracle@pttest4 ~]$ export ORACLE_SID=pt2;[oracle@pttest4 ~]$ sqlplus / as sysdbaSQL*Plus: Production Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 ……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 sysdbaSQL*Plus: Production Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database – 64bit Prod

uctionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> SQL> select name from v$database;NAME——————pt2SQL>4，检查两者的编码

到出库实例库1：

SQLFROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET’, ‘NLS_NCHAR_CHARACTERSET’);PARAMETERVALUE—————————————————————- —————————————————————-NLS_CHARACTERSETZHS16GBKNLS_NCHAR_CHARACTERSETAL16UTF16SQL>

到导入的库实例2库

SQLFROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET’, ‘NLS_NCHAR_CHARACTERSET’);PARAMETERVALUE—————————————————————- —————————————————————-NLS_CHARACTERSETAL32UTF8NLS_NCHAR_CHARACTERSETAL16UTF16SQL>

看到导出导入库实例1实例2的字符集不同，所以问题就在这里了

5，，解决：[oracle@pttest4 admin]$ export ORACLE_SID=pt2;[oracle@pttest4 admin]$ [oracle@pttest4 admin]$ sqlplus / as sysdbaSQL*Plus: Release Production Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 sysdbaSQL*Plus: Production Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name from v$database;NAME———pt2SQL>SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN (‘NLS_CHARACTERSET‘, ‘NLS_NCHAR_CHARACTERSET‘);Warning: connection was lost and re-establishedPARAMETERVALUE—————————————————————- —————————————————————-NLS_CHARACTERSETZHS16GBKNLS_NCHAR_CHARACTERSETAL16UTF16SQL>

喜欢 (0)赏

[搞代码]

分享 (0)