Oracle 12C: Moving schema from Oracle 11G to 12C.

The steps are pretty straightforward except user needs to pay attention to where the target database the datapump export going into.

From source server with Oracle 11G

expdp RNVCLOUD/password dumpfile=mycloudexport.dmp  directory=kdump schemas=RNVCLOUD logfile=export.log

On the source db - Oracle 12C

Setting up the dump directory. 

SQL> !mkdir /u01/backup

SQL> create or replace directory kdump as '/u01/backup';

Directory created.

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DMPDIR';

DIRECTORY_PATH
----------------------------------------------------------------------------------------------------
/u01/backup

Verify if the database is opened. 

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DMPDIR';

DIRECTORY_PATH
------------------------
/u01/backup

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 PDBVCDB                        MOUNTED

SQL> col pdb_name format a20
SQL> col status format a20
SQL> select pdb_name, status from dba_pdbs;

PDB_NAME             STATUS
-------------------- --------------------
PDBORCL              NORMAL
PDB$SEED             NORMAL
PDBVCDB              NEW

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 PDBVCDB                        MOUNTED

SQL> alter session set container=PDBVCDB;

Session altered.

The PDBVCDB is in MOUNTED mode. It needs to be opened.

SQL> alter session set container=PDBVCDB;

Session altered.

SQL> startup;

Pluggable Database opened.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDBVCDB                        READ WRITE NO
SQL>

SQL> !lsnrctl status|grep pdbvcdb

Service "pdbvcdb" has 1 instance(s).

The datapump import into Oracle 12C

User/Schema already pre-setup for this experiment.

RNVCLOUD

NOTE:Make sure the PDBVCDB service block exist in tnsnames.ora

Here we are using the pdbvcdb service for the impdp. Since,we know that there can be many databases in a single instance and we could have many of the same schemas exist in many of the databases from within.

Note: No schema mapping or tablespace mapping needed .. moving a schema to schema in Oracle 12C.

impdp RNVCLOUD/password@pdbvcdb DIRECTORY=kdump dumpfile=mycloudexport.dmp schemas=RNVCLOUD logfile=11g212c_rnvcloud_impdp.log

Import: Release 12.1.0.1.0 - Production on Tue Dec 2 12:20:42 2014

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 "RNVCLOUD"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "RNVCLOUD"."SYS_IMPORT_SCHEMA_01":  RNVCLOUD/********@pdbvcdb DIRECTORY=kdump dumpfile=mycloudexport.dmp schemas=RNVCLOUD logfile=11g212c_rnvcloud_impdp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"RNVCLOUD" already exists
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RNVCLOUD"."AUDIT_EVENT"                    3.578 GB 1167153 rows
. . imported "RNVCLOUD"."LICENSING_VM_DATA"              229.1 MB 2891749 rows

......

..................... and so on ...................

[oracle@oracle12c backup]$ sqlplus rnvcloud/password@pdbvcdb

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 2 12:36:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Dec 02 2014 12:34:42 -07:00

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

SQL> show user
USER is "RNVCLOUD"

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDBVCDB                        READ WRITE NO

SQL> select count(*) from user_objects;

  COUNT(*)
----------
      1148

SQL>