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: Release 11.2.0.1.0 Production on Wed Jun 3 21:22:43 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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: Release 11.2.0.1.0 Production on Fri May 29 21:06:27 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> SQL> select name from v$database;NAME------------------pt2SQL>

4，檢查兩者的編碼

到出庫執行個體庫1：

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');PARAMETER                                                        VALUE---------------------------------------------------------------- ----------------------------------------------------------------NLS_CHARACTERSET                                                 ZHS16GBKNLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL> 

到匯入的庫執行個體2庫

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');PARAMETER                                                        VALUE---------------------------------------------------------------- ----------------------------------------------------------------NLS_CHARACTERSET                                                 AL32UTF8NLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL> 

看到匯出匯入庫執行個體1執行個體2的字元集不同，所以問題就在這裡了

5，解決：

[oracle@pttest4 admin]$ export ORACLE_SID=pt2;[oracle@pttest4 admin]$ [oracle@pttest4 admin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:06:27 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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: Release 11.2.0.1.0 Production on Fri May 29 21:32:12 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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-establishedPARAMETER                                                        VALUE---------------------------------------------------------------- ----------------------------------------------------------------NLS_CHARACTERSET                                                 ZHS16GBKNLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL> 

匯出執行個體上：

[oracle@pttest4 expdpimpdp]$ export ORACLE_SID=pt1;[oracle@pttest4 expdpimpdp]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 21:32:50 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name from v$database;NAME---------pt1SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');PARAMETER                                                        VALUE---------------------------------------------------------------- ----------------------------------------------------------------NLS_CHARACTERSET                                                 ZHS16GBKNLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL> 

此時，兩個執行個體上字元集一模一樣了，保持一致。

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上面了。