Oracle 11g upgrade to 12c - Datapump

I have been meaning to document the 12c upgrade process, so decided to use the standard datapump export/import to move a schema. My setup was an amazon EC2 instance running redhat linux with 11.2.0.3 binaries installed and a running 11g database. In another home was the 12c binaries installed and a running 12c database.

 

## SOURCE - DBMIG - 11.2.0.3 ##

[oracle@EC2 ~]$ . ./set_11g.env
[oracle@EC2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 11 11:43:25 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

-- check schema objects

SQL>  select object_type, count(1) from dba_objects where owner = 'SHARK' group by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
LOB                          4
TYPE BODY                    1
TRIGGER                      1
TABLE                       49
INDEX                       22
TYPE                         6

6 rows selected.

SQL> select owner, object_name, object_type from dba_objects where status !='VALID'

OWNER      OBJECT_NAME     OBJECT_TYPE
---------- --------------- -------------------
SHARK      TOOL_TY         TYPE
SHARK      ANIMAL_TY       TYPE BODY

SQL> select tname from tab;

TNAME
-----------------------------------------------------
COMFORT_TEST
CONTINENT
ADDRESS
AREAS
AUTHOR
BIRTHDAY
BOOK_ORDER
BOOK_REVIEW_CONTEXT
BOOK_REVIEW_CTXCAT
CATEGORY
BOOKSHELF
BOOKSHELF_AUDIT
BOOKSHELF_AUTHOR
BOOKSHELF_CHECKOUT
BREEDING
CD
COMFORT
COMFORT2
COUNTRY
EMPTY
HOLIDAY
LOCATION
MAGAZINE
MATH
NAME
NEWSPAPER
NUMBER_TEST
PAYDAY
RADIUS_VALS
RATING
ROSE
SHIPPING
STOCK
STOCK_ACCOUNT
STOCK_TRX
TROUBLE
TWONAME
WEATHER
BORROWER
BREEDER
ANIMALS_NT_TAB
CUSTOMER
PROPOSAL
ANIMAL

44 rows selected.

--make tablespace read only

SQL> alter tablespace data read only;

Tablespace altered.

SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/app/oracle/admin/DBMIG/dpdump/

--do datapump export

[oracle@EC2 dpdump]$ expdp system dumpfile=shark.dmp logfile=shark.log directory=data_pump_dir schemas=shark

Export: Release 11.2.0.3.0 - Production on Fri Feb 13 09:23:42 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=shark.dmp logfile=shark.log directory=data_pump_dir schemas=shark
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.062 MB
Processing object type SCHEMA_EXPORT/USER
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/TABLE/TABLE
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/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
. . exported "SHARK"."ADDRESS"                           8.359 KB      17 rows
. . exported "SHARK"."ANIMAL"                            7.078 KB       1 rows
. . exported "SHARK"."AUTHOR"                            6.640 KB      31 rows
. . exported "SHARK"."BIRTHDAY"                          6.335 KB       5 rows
. . exported "SHARK"."BOOKSHELF"                         7.617 KB      31 rows
. . exported "SHARK"."BOOKSHELF_AUTHOR"                  6.835 KB      37 rows
. . exported "SHARK"."BOOKSHELF_CHECKOUT"                7.148 KB      19 rows
. . exported "SHARK"."BOOK_ORDER"                        6.023 KB       6 rows
. . exported "SHARK"."BOOK_REVIEW_CONTEXT"               6.585 KB       1 rows
. . exported "SHARK"."BOOK_REVIEW_CTXCAT"                6.585 KB       1 rows
. . exported "SHARK"."BORROWER"                          5.718 KB       1 rows
. . exported "SHARK"."BREEDING"                          7.015 KB      16 rows
. . exported "SHARK"."CATEGORY"                          6.007 KB       6 rows
. . exported "SHARK"."CD"                                5.937 KB       7 rows
. . exported "SHARK"."COMFORT"                           6.953 KB       8 rows
. . exported "SHARK"."CUSTOMER"                          8.156 KB       2 rows
. . exported "SHARK"."HOLIDAY"                           6.171 KB      10 rows
. . exported "SHARK"."LOCATION"                          7.929 KB      14 rows
. . exported "SHARK"."MAGAZINE"                          6.984 KB       5 rows
. . exported "SHARK"."MATH"                              6.289 KB       4 rows
. . exported "SHARK"."NAME"                              5.101 KB       5 rows
. . exported "SHARK"."NEWSPAPER"                         6.054 KB      14 rows
. . exported "SHARK"."NUMBER_TEST"                       5.976 KB       8 rows
. . exported "SHARK"."PAYDAY"                            5.148 KB      12 rows
. . exported "SHARK"."RADIUS_VALS"                       5.062 KB       1 rows
. . exported "SHARK"."RATING"                            5.531 KB       5 rows
. . exported "SHARK"."ROSE"                              5.054 KB       4 rows
. . exported "SHARK"."SHIPPING"                          5.460 KB       3 rows
. . exported "SHARK"."STOCK"                             8.312 KB      26 rows
. . exported "SHARK"."STOCK_ACCOUNT"                     5.507 KB       2 rows
. . exported "SHARK"."STOCK_TRX"                         6.820 KB       4 rows
. . exported "SHARK"."TROUBLE"                           6.804 KB       3 rows
. . exported "SHARK"."WEATHER"                           6.367 KB       7 rows
. . exported "SHARK"."AREAS"                                 0 KB       0 rows
. . exported "SHARK"."BOOKSHELF_AUDIT"                       0 KB       0 rows
. . exported "SHARK"."BREEDER"                               0 KB       0 rows
. . exported "SHARK"."ANIMALS_NT_TAB"                        0 KB       0 rows
. . exported "SHARK"."COMFORT2"                              0 KB       0 rows
. . exported "SHARK"."COMFORT_TEST"                          0 KB       0 rows
. . exported "SHARK"."CONTINENT"                             0 KB       0 rows
. . exported "SHARK"."COUNTRY"                               0 KB       0 rows
. . exported "SHARK"."EMPTY"                                 0 KB       0 rows
. . exported "SHARK"."PROPOSAL"                              0 KB       0 rows
. . exported "SHARK"."TWONAME"                               0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /app/oracle/admin/DBMIG/dpdump/shark.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:25:15

## TARGET - DB12C - 12.1.0.2 ##

[oracle@EC2 ~]$ . ./set_12c.env
[oracle@EC2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 13 09:27:37 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--check pdbs

SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDDB12C                        MOUNTED

--create pdb

SQL> !mkdir /data/12c/oradata/DB12C/dbmig12c

SQL> create pluggable database dbmig12c admin user pdbadmin identified by pdbadmin file_name_convert=('/data/12c/oradata/DB12C/pdbseed','/data/12c/oradata/DB12C/dbmig12c');

Pluggable database created.

SQL> show pdbs

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

SQL> alter pluggable database DBMIG12C open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDDB12C                        MOUNTED
         4 DBMIG12C                       READ WRITE NO

--connect to pdb

SQL> alter session set container=dbmig12c;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DBMIG12C

SQL> create directory dump_dir as '/app/oracle/admin/DB12C/dpdump' ;

Directory created.

--create user and tablespace

SQL> create user shark identified by shark container=current;

User created.

SQL> grant connect, resource, create any view, unlimited tablespace to shark;

Grant succeeded.

SQL> create tablespace data datafile '/data/12c/oradata/DB12C/dbmig12c/data01.dbf' size 250M;

Tablespace created.

SQL> alter user shark default tablespace data;

User altered.

SQL> exit

-copy the dumpfile

[oracle@EC2 ~]$ cp /app/oracle/admin/DBMIG/dpdump/shark.dmp /app/oracle/admin/DB12C/dpdump

--perform the datapump import

[oracle@EC2 ~]$ impdp system@dbmig12c dumpfile=shark.dmp logfile=shark_imp.log directory=dump_dir

Import: Release 12.1.0.2.0 - Production on Fri Feb 13 09:39:06 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.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/********@dbmig12c dumpfile=shark.dmp logfile=shark_imp.log directory=dump_dir
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHARK" 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
ORA-39082: Object type TYPE:"SHARK"."TOOL_TY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHARK"."ADDRESS"                           8.359 KB      17 rows
. . imported "SHARK"."ANIMAL"                            7.078 KB       1 rows
. . imported "SHARK"."AUTHOR"                            6.640 KB      31 rows
. . imported "SHARK"."BIRTHDAY"                          6.335 KB       5 rows
. . imported "SHARK"."BOOKSHELF"                         7.617 KB      31 rows
. . imported "SHARK"."BOOKSHELF_AUTHOR"                  6.835 KB      37 rows
. . imported "SHARK"."BOOKSHELF_CHECKOUT"                7.148 KB      19 rows
. . imported "SHARK"."BOOK_ORDER"                        6.023 KB       6 rows
. . imported "SHARK"."BOOK_REVIEW_CONTEXT"               6.585 KB       1 rows
. . imported "SHARK"."BOOK_REVIEW_CTXCAT"                6.585 KB       1 rows
. . imported "SHARK"."BORROWER"                          5.718 KB       1 rows
. . imported "SHARK"."BREEDING"                          7.015 KB      16 rows
. . imported "SHARK"."CATEGORY"                          6.007 KB       6 rows
. . imported "SHARK"."CD"                                5.937 KB       7 rows
. . imported "SHARK"."COMFORT"                           6.953 KB       8 rows
. . imported "SHARK"."CUSTOMER"                          8.156 KB       2 rows
. . imported "SHARK"."HOLIDAY"                           6.171 KB      10 rows
. . imported "SHARK"."LOCATION"                          7.929 KB      14 rows
. . imported "SHARK"."MAGAZINE"                          6.984 KB       5 rows
. . imported "SHARK"."MATH"                              6.289 KB       4 rows
. . imported "SHARK"."NAME"                              5.101 KB       5 rows
. . imported "SHARK"."NEWSPAPER"                         6.054 KB      14 rows
. . imported "SHARK"."NUMBER_TEST"                       5.976 KB       8 rows
. . imported "SHARK"."PAYDAY"                            5.148 KB      12 rows
. . imported "SHARK"."RADIUS_VALS"                       5.062 KB       1 rows
. . imported "SHARK"."RATING"                            5.531 KB       5 rows
. . imported "SHARK"."ROSE"                              5.054 KB       4 rows
. . imported "SHARK"."SHIPPING"                          5.460 KB       3 rows
. . imported "SHARK"."STOCK"                             8.312 KB      26 rows
. . imported "SHARK"."STOCK_ACCOUNT"                     5.507 KB       2 rows
. . imported "SHARK"."STOCK_TRX"                         6.820 KB       4 rows
. . imported "SHARK"."TROUBLE"                           6.804 KB       3 rows
. . imported "SHARK"."WEATHER"                           6.367 KB       7 rows
. . imported "SHARK"."AREAS"                                 0 KB       0 rows
. . imported "SHARK"."BOOKSHELF_AUDIT"                       0 KB       0 rows
. . imported "SHARK"."BREEDER"                               0 KB       0 rows
. . imported "SHARK"."ANIMALS_NT_TAB"                        0 KB       0 rows
. . imported "SHARK"."COMFORT2"                              0 KB       0 rows
. . imported "SHARK"."COMFORT_TEST"                          0 KB       0 rows
. . imported "SHARK"."CONTINENT"                             0 KB       0 rows
. . imported "SHARK"."COUNTRY"                               0 KB       0 rows
. . imported "SHARK"."EMPTY"                                 0 KB       0 rows
. . imported "SHARK"."PROPOSAL"                              0 KB       0 rows
. . imported "SHARK"."TWONAME"                               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/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
ORA-39082: Object type TYPE_BODY:"SHARK"."ANIMAL_TY" created with compilation warnings
ORA-39082: Object type TYPE_BODY:"SHARK"."ANIMAL_TY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39082: Object type TYPE:"SHARK"."TOOL_TY" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 5 error(s) at Fri Feb 13 09:41:35 2015 elapsed 0 00:02:18

[oracle@EC2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 13 09:43:21 2015

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--check schema objects

SQL> alter session set container=dbmig12c;

Session altered.

SQL>  select object_type, count(1) from dba_objects where owner = 'SHARK' group by object_type;

OBJECT_TYPE               COUNT(1)
----------------------- ----------
LOB                              4
TYPE BODY                        1
TRIGGER                          1
TABLE                           49
INDEX                           23
TYPE                             6

6 rows selected.

SQL> select owner, object_name, object_type from dba_objects where status !='VALID'

OWNER      OBJECT_NAME     OBJECT_TYPE
---------- --------------- -------------------
SHARK      TOOL_TY         TYPE
SHARK      ANIMAL_TY       TYPE BODY

SQL> conn shark/shark@dbmig12c
Connected.

SQL>  select tname from tab;

TNAME
--------------------------------------------------------------------------------
DR$REVIEW_CTXCAT_INDEX$I
DR$REVIEW_CONTEXT_INDEX$N
DR$REVIEW_CONTEXT_INDEX$R
DR$REVIEW_CONTEXT_INDEX$K
DR$REVIEW_CONTEXT_INDEX$I
ANIMAL
PROPOSAL
CUSTOMER
ANIMALS_NT_TAB
BREEDER
BORROWER
WEATHER
TWONAME
TROUBLE
STOCK_TRX
STOCK_ACCOUNT
STOCK
SHIPPING
ROSE
RATING
RADIUS_VALS
PAYDAY
NUMBER_TEST
NEWSPAPER
NAME
MATH
MAGAZINE
LOCATION
HOLIDAY
EMPTY
COUNTRY
COMFORT2
COMFORT
CD
BREEDING
BOOKSHELF_CHECKOUT
BOOKSHELF_AUTHOR
BOOKSHELF_AUDIT
BOOKSHELF
CATEGORY
BOOK_REVIEW_CTXCAT
BOOK_REVIEW_CONTEXT
BOOK_ORDER
BIRTHDAY
AUTHOR
AREAS
ADDRESS
CONTINENT
COMFORT_TEST

49 rows selected.