Import in Oracle 12c PDB from Oracle 11g Export Dump:

I have taken backup dumpfile(scott.dmp) of SCOTT schema from Oracle 11g database and will be importing that dumpfile to Oracle 12c Pluggable database inside TEST schema in a pluggable database "pdbtestdb".

In order to import in pluggable database inside Oracle 12c, we will have to create a service and in my case pdbtestdb service is created for my PDB.

Connected to pluggable database pdbtestdb and created a directory "TEST_DIR"(as usual we have been doing in previous releases of oracle database) where I have kept the export dumpfile which was exported from oracle 11g database and tried usual method to import inside Oracle 12c PDB.


Received below error while import.

[oracle@localhost bin]$ impdp system/xxx@pdbtestdb directory=TEST_DIR dumpfile=scott.dmp  remap_schema=scott:test logfile=test.log

Import: Release 12.1.0.1.0 - Production on Wed May 18 13:50:00 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name TEST_DIR is invalid



Note : In Oracle database 12c, it looks for directory naming convention $TNS_DATA_PUMP_DIR format so we need to create directory in oracle 12c pluggable database as PDBTESTDB_DATA_PUMP_DIR as illustrated below.

SQL> conn /as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Now, set the appropriate container(pluggable) database in which directory will be created to import the exported dumpfile.

SQL> alter session set container = pdbtestdb;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
PDBTESTDB

Now we are connected to PDBTESTDB pluggable database so let's create directory in there.


SQL> create directory PDBTESTDB_DATA_PUMP_DIR AS '/u01/app/11g_exp';

Directory created.



SQL> GRANT READ, WRITE ON DIRECTORY PDBTESTDB_DATA_PUMP_DIR TO SYSTEM;

Grant succeeded.



Finally, let's try to import the dumpfile inside Oracle 12c pluggable database using new directory created above.


[oracle@localhost bin]$ impdp system/xxx@pdbtestdb directory=PDBTESTDB_DATA_PUMP_DIR dumpfile=scott.dmp  remap_schema=scott:test logfile=test.log

Import: Release 12.1.0.1.0 - Production on Wed May 18 13:58:20 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@pdbtestdb directory=PDBTESTDB_DATA_PUMP_DIR dumpfile=scott.dmp remap_schema=scott:test logfile=test.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists  -- This can be ignored as I pre-created TEST user in the pluggable database.
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                               5.937 KB       4 rows
. . imported "TEST"."EMP"                                8.570 KB      14 rows
. . imported "TEST"."EMPBK"                              8.570 KB      14 rows
. . imported "TEST"."SALGRADE"                           5.867 KB       5 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed May 18 13:58:31 2016 elapsed 0 00:00:09


Import went successful.