Oracle 11 GB is imported from instance 1 full database to instance 2 on the same Linux Server

Oracle 11 GB is imported from instance 1 full database to instance 2 on the same Linux Server

Export command in the early stage:

[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. Start importing

First, create the MPs queue directory on instance 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> 

Start importing the full-Database Backup set:

impdp \'sys/syspddev@pt2 as sysdba\' directory=dir_dump_t3 dumpfile=expdpfull_pd_20150529_02.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE ......

2. Import error:

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. Check whether the import target is incorrect:

 [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. Check the encoding of the two.

To warehouse instance Library 1:

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

To the imported library instance 2

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

We can see that the character set of instance 1, instance 2, is different, so the problem is here.

5. solution:

[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 2015 Copyright (c) 1982,200 9, 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 indicates incompatible CHARACTER sets. At this time, the INTERNAL_USE command does not check character SET supersets: SQL> alter database character set INTERNAL_USE ZHS16GBK; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;

6. Check the encoding of the two instances.

[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> 

Export instance:

[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> 

In this case, the character sets on the two instances are identical.

7. Import impdp again.

[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. Everything is normal. You can import it to instance 2.