!! Oracle 12C: Moving schema from Oracle 11G to 12C. !!
NOTE : As with any code, ensure to test this script in a development environment before attempting to run it in production.
From source server with Oracle 11G
expdp ORAVB/password dumpfile=ORAVBdexport.dmp directory=ORAVB_DIR schemas=ORAVB logfile=ORAVB.log
On the source db - Oracle 12C
Setting up the dump directory.
SQL> !mkdir /u01/backup/ORAVB_DIR
SQL> create or replace directory ORAVB_DIR as '/u01/backup/ORAVB_DIR';
Directory created.
SQL> GRANT read, write ON DIRECTORY ORAVB_DIR TO public;
SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'ORAVB_DIR';
DIRECTORY_PATH
----------------------------------------------------------------------------------------------------
/u01/backup/ORAVB_DIR
Verify if the database is opened.
SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DMPDIR';
DIRECTORY_PATH
------------------------
/u01/backup/ORAVB_DIR
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDBORAVB 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
PDBORAVB NEW
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDBORAVB MOUNTED
SQL> alter session set container=PDBORAVB;
Session altered.
The PDBORAVB is in MOUNTED mode. It needs to be opened.
SQL> alter session set container=PDBORAVB;
Session altered.
SQL> startup;
Pluggable Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBORAVB READ WRITE NO
SQL>
SQL> select count(*) from user_objects;
COUNT(*)
----------
900
SQL> !lsnrctl status|grep PDBORAVB
Service "PDBORAVB" has 1 instance(s).
The datapump import into Oracle 12C
User/Schema already pre-setup
ORAVB
NOTE:Make sure the PDBORAVB service block exist in tnsnames.ora
Here we are using the PDBORAVB 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 ORAVB/password@PDBORAVB DIRECTORY=ORAVB_DIR dumpfile=ORAVBexport.dmp schemas=ORAVB logfile=11g_to_12c_ORAVB_impdp.log
Import: Release 12.1.0.1.0 - Production on Tue Aug 14 16:01:25 2018
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 "ORAVB"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ORAVB"."SYS_IMPORT_SCHEMA_01": ORAVB/********@PDBORAVB DIRECTORY=ORAVB_DIR dumpfile=ORAVBexport.dmp schemas=ORAVB logfile=11g_to_12c_ORAVB_impdp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ORAVB" 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 "ORAVB"."IMP_DATA" 3.418 GB 1167153 rows
. . imported "ORAVB"."SCHOOL_DATA" 239.8 MB 2891749 rows
......
..................... and so on ...................
[oracle@oracle12c ORAVB_DIR]$ sqlplus ORAVB/password@PDBORAVB
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 14 16:11:25 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Tue Aug 14 16:13:25 2018 -09: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 "ORAVB"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBORAVB READ WRITE NO
SQL> select count(*) from user_objects;
COUNT(*)
----------
900
SQL>
