ORACLE 11G on the same Linux server imported from instance 1 full library to instance 2

Pre-Export command:

[root@powerlong4 ~]# su - oracle[oracle@powerlong4 ~]$ 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

Set up the pipeline directory on instance 2 first:

[[email protected] ~]$ export oracle_sid=pt2; [Email protected] ~]$ Sqlplus/as sysdbasql*plus:Release 11.2. 0. 1. 0Production onWed June3  +: A: +  -Copyright (c)1982, the, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 1. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing optionssql> CREATEORREPLACE DIRECTORY dir_dump_t3 as '/home/oracle/expdpimpdp/';D irectory created. Sql>

To start importing a full library backup set:

\‘sys/[email protected] as sysdba\‘ directory=dir_dump_t3 dumpfile=expdpfull_pd_20150529_02.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE ......

< copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal liability!>
Original Blog Address: http://blog.csdn.net/mchdba/article/details/46335861
Hara Douglas Fir (MCHDBA)

2, import Error:

ORA-02374errortable"puser"."RES_APPROVE_CONTENT"ORA-12899for2420)

3, suspect import target error, verify:

[[email protected] admin]$ export oracle_sid=pt2; [Email protected] admin]$ [[email protected] admin]$ Sqlplus/as sysdbasql*plus:Release 11.2. 0. 1. 0Production onFri May in  +: .: -  -Copyright (c)1982, the, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 1. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing optionssql> sql> sql>SelectName from V$database;name------------------Pt2sql>

4, check the code for both

To the out of Library instance library 1:

SQL>SELECT* FROM V$NLS_PARAMETERSWHEREIN (‘NLS_CHARACTERSET‘‘NLS_NCHAR_CHARACTERSET‘);PARAMETER                                                        VALUE--------------------------------------------------------------------------------------------------------------------------------NLS_CHARACTERSET                                                 ZHS16GBKNLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL>

To an imported library instance 2 Library

SQL>SELECT* FROM V$NLS_PARAMETERSWHEREIN (‘NLS_CHARACTERSET‘‘NLS_NCHAR_CHARACTERSET‘);PARAMETER                                                        VALUE--------------------------------------------------------------------------------------------------------------------------------NLS_CHARACTERSET                                                 AL32UTF8NLS_NCHAR_CHARACTERSET                                           AL16UTF16SQL>

See Export Import Library Instance 1 Instance 2 has a different character set, so the problem is here.

5, Solve:

[[email protected] admin]$ export oracle_sid=pt2; [Email protected] admin]$ [[email protected] admin]$ Sqlplus/ asSysdbasql*plus:release11.2. 0. 1. 0Production onFri May in  +: .: -  -Copyright (c)1982, the, Oracle. All rights reserved. Connected to: Oracle Database OneG Enterprise Edition Release11.2. 0. 1. 0- -Bit Production withThe partitioning, OLAP, Data Mining andReal Application Testing optionssql> sql>connect Username/password asSYSDBA; Sql>shutdown IMMEDIATE; Sql>startup MOUNT; Sql>alter SYSTEM ENABLE RESTRICTED SESSION; Sql>alter SYSTEMSETjob_queue_processes=0; Sql>alter SYSTEMSETaq_tm_processes=0; Sql>alter DATABASE OPEN; sql> ALTER DATABASE CHARACTERSETZHS16GBK; ALTER DATABASE CHARACTERSETzhs16gbk;*ERRORAt line1: ora-12712:NewCharacterSetMust be a superset ofOld characterSetCharacter set is not compatible, the internal_use instruction does not check the character set superset at this time: Sql>alter DATABASE CHARACTERSETInternal_use ZHS16GBK; Sql>shutdown IMMEDIATE; sql>startup;

6, then check the encoding of the two instances

[Email protected] expdpimpdp]$ Sqlplus/as sysdbasql*plus:Release 11.2. 0. 1. 0Production onFri May in  +: +: A  -Copyright (c)1982, the, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 1. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>SelectName from V$database;name---------Pt2sql> sql>SELECT* FROM V$nls_parameters WHERE PARAMETERinch(' Nls_characterset‘,' Nls_nchar_characterset‘); Warning:connection was lost andRe-establishedparameter VALUE---------------------------------------------------------------- ------------------------------------------- ---------------------Nls_characterset Zhs16gbknls_nchar_characterset Al16utf16sql>

On the export instance:

[[email protected] expdpimpdp]$ export oracle_sid=pt1; [Email protected] expdpimpdp]$ Sqlplus/as sysdbasql*plus:Release 11.2. 0. 1. 0Production onFri May in  +: +: -  -Copyright (c)1982, the, Oracle. AllRights reserved. Connected to: Oracle Database OneG Enterprise EditionRelease 11.2. 0. 1. 0- -bitProduction withThe partitioning, OLAP, Data Mining andReal Application Testing Optionssql>SelectName from V$database;name---------Pt1sql>SELECT* FROM V$nls_parameters WHERE PARAMETERinch(' Nls_characterset‘,' Nls_nchar_characterset‘); PARAMETER VALUE---------------------------------------------------------------- ------------------------------------------- ---------------------Nls_characterset Zhs16gbknls_nchar_characterset Al16utf16sql>

at this point, the character set on the two instances is identical and consistent.

7, re-import IMPDP

[oracle@pttest4 admin]$ ORACLE_SID=pt2;[oracle@pttest4 admin]$ impdp \‘sys/[email protected] as sysdba\‘ directory=dir_dump_t3 dumpfile=expdpfull_pd_20150529_02.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE

OK, everything is normal, can be imported into instance 2 above.

ORACLE 11G on the same Linux server imported from instance 1 full library to instance 2