ORA-39013: Remapping the SYSTEM schema is not supported.

Scenario : Took Data Pump Export of SYSTEM schema in Oracle 12c CDB container database and tried to import that backup dumpfile data inside a Pluggable database PDBTEST but it ends with an error saying "Remapping SYSTEM is not supported."


Let's take a look.
----------------

1 - Export the Oracle 12c CDB SYSTEM schemas as below.


E:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN>expdp system/redhat directory=system_dir dumpfile=system.dmp logfile=system.log

Export: Release 12.1.0.1.0 - Production on Thu May 19 13:53:56 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

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=system_dir dumpfile=system.dmp logfile=system.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\SYSTEM_DIR\SYSTEM.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 19 13:54:32 2016 elapsed 0 00:00:30





SQL> conn system/redhat@pdbtest
Connected.


SQL> show con_name

CON_NAME
------------------------------
PDBTEST


Now, let's try to import the exported SYSTEM schema from Oracle 12c CDB inside one of its PDB pluggable database names PDBTEST in schema TEST as illustrated below.
--------------------------------------------------------------------------------------------------------------

E:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN>impdp system/redhat@pdbtest directory=PDBTEST_DATA_PUMP_DIR dumpfile=system.dmp logfile=test.log remap_schema=system:test

Import: Release 12.1.0.1.0 - Production on Thu May 19 14:00:02 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-39001: invalid argument value
ORA-39013: Remapping the SYSTEM schema is not supported.



-- Here you got the error saying Remapping the SYSTEM schema is not supported.



Solution : You can re-try the import without REMAP_SCHEMA clause as illustrated below.


E:\app\oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN>impdp system/redhat@pdbtest directory=PDBTEST_DATA_PUMP_DIR dumpfile=system.dmp logfile=test.log

Import: Release 12.1.0.1.0 - Production on Thu May 19 14:13:19 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/********@pdbtest directory=PDBTEST_DATA_PUMP_DIR dumpfile=system.dmp logfile=test.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu May 19 14:13:22 2016 elapsed 0 00:00:03


Import went successful now.

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.