Oracle 11g upgrade to 12c - Transportable Database

I have been meaning to document the 12c upgrade process, so decided to use the full transportable export/import. This blog focuses on the use of full transportable export/import to migrate an 11.2.0.3 database to a 12c pluggable database. 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> conn shark/shark@dbmig
Connected.

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.

-- check the tablespaces and make them read only

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
DATA                           ONLINE

7 rows selected.

SQL> alter tablespace data read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
EXAMPLE                        READ ONLY
DATA                           READ ONLY

7 rows selected.

-- Confirm the path of the export

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

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

-- Perform the export using datapump

[oracle@EC2 ~]$ expdp system full=y transportable=always version=12.0 dumpfile=dbmig.dmp logfile=dbmig.log

Export: Release 11.2.0.3.0 - Production on Wed Feb 11 11:54:15 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_FULL_01":  system/******** full=y transportable=always version=12.0 dumpfile=dbmig.dmp logfile=dbmig.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 102.2 MB

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /app/oracle/admin/DBMIG/dpdump/dbmig.dmp
******************************************************************************
Datafiles required for transportable tablespace DATA:
  /data/oradata/DBMIG/data01.dbf
Datafiles required for transportable tablespace EXAMPLE:
  /data/oradata/DBMIG/example01.dbf
Datafiles required for transportable tablespace USERS:
  /data/oradata/DBMIG/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 12:05:34

- Copy dumpfiles and datafiles for data/example/users to target machine/drive

[oracle@EC2 DBMIG]$ ls -lrt /data/oradata/DBMIG
total 2249752
-rw-r-----. 1 oracle oinstall  52429312 Feb 11 11:00 redo01.log
-rw-r-----. 1 oracle oinstall 262152192 Feb 11 11:45 data01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Feb 11 11:45 users01.dbf
-rw-r-----. 1 oracle oinstall 328343552 Feb 11 11:45 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Feb 11 12:01 redo02.log
-rw-r-----. 1 oracle oinstall  59777024 Feb 11 12:04 temp01.dbf
-rw-r-----. 1 oracle oinstall 597696512 Feb 11 12:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Feb 11 12:18 system01.dbf
-rw-r-----. 1 oracle oinstall 120594432 Feb 11 12:18 undotbs01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Feb 11 12:18 redo03.log
-rw-r-----. 1 oracle oinstall  10076160 Feb 11 12:18 control01.ctl

[oracle@EC2 DBMIG]$ ls -lrt /app/oracle/admin/DBMIG/dpdump/
total 165628
-rw-r-----. 1 oracle oinstall       116 Feb  6 09:35 dp.log
-rw-r-----. 1 oracle oinstall 169472000 Feb 11 12:05 dbmig.dmp
-rw-r--r--. 1 oracle oinstall    119131 Feb 11 12:05 dbmig.log

[oracle@EC2 DBMIG]$ cp /app/oracle/admin/DBMIG/dpdump/dbmig.dmp /app/oracle/admin/DB12C/dpdump
[oracle@EC2 DBMIG]$ cp /data/oradata/DBMIG/data01.dbf /data/12c/oradata/DB12C/dbmig12c
[oracle@EC2 DBMIG]$ cp /data/oradata/DBMIG/users01.dbf /data/12c/oradata/DB12C/dbmig12c
[oracle@EC2 DBMIG]$ cp /data/oradata/DBMIG/example01.dbf /data/12c/oradata/DB12C/dbmig12c


## 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 Wed Feb 11 11:03:25 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             675283048 bytes
Database Buffers          390070272 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

- Check existing PDB's

SQL> SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;

PDB_NAME             STATUS
-------------------- ---------
PDB$SEED             NORMAL
PDDB12C              NORMAL

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

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/12c/oradata/DB12C/system01.dbf
/data/12c/oradata/DB12C/sysaux01.dbf
/data/12c/oradata/DB12C/undotbs01.dbf
/data/12c/oradata/DB12C/pdbseed/system01.dbf
/data/12c/oradata/DB12C/users01.dbf
/data/12c/oradata/DB12C/pdbseed/sysaux01.dbf
/data/12c/oradata/DB12C/PDDB12C/system01.dbf
/data/12c/oradata/DB12C/PDDB12C/sysaux01.dbf
/data/12c/oradata/DB12C/PDDB12C/SAMPLE_SCHEMA_users01.dbf
/data/12c/oradata/DB12C/PDDB12C/example01.dbf

10 rows selected.

-- Create a directory for new PDB

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

-- Create new PDB from seed

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

-- Open PDB read/write

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

-- Set session for new 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.

SQL>  select FILE_NAME,TABLESPACE_NAME from cdb_data_files

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/data/12c/oradata/DB12C/dbmig12c/sysaux01.dbf                SYSAUX
/data/12c/oradata/DB12C/dbmig12c/system01.dbf                SYSTEM

SQL> exit

-- Add entry to tnsnames.ora

DBMIG12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = EC2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DBMIG12C)
    )
   )

-- Start Listener

[oracle@EC2 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-FEB-2015 11:37:04

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

Starting /app/oracle/product/12c/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /app/oracle/product/12c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/EC2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EC2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=EC2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                11-FEB-2015 11:37:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/12c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/EC2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EC2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

-- Check new PDB registered

[oracle@EC2 admin]$ lsnrctl status LISTENER | grep dbmig12c
Service "dbmig12c" has 1 instance(s).

-- Test TNSPing

[oracle@EC2 admin]$ tnsping dbmig12c

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-FEB-2015 11:40:25

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

Used parameter files:
/app/oracle/product/12c/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = EC2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBMIG12C)))
OK (10 msec)

-- Perform the datapump import

[oracle@EC2 ~]$ impdp system@dbmig12c full=y directory=dump_dir dumpfile=dbmig.dmp transport_datafiles='/data/12c/oradata/DB12C/dbmig12c/data01.dbf','/data/12c/oradata/DB12C/dbmig12c/users01.dbf','/data/12c/oradata/DB12C/dbmig12c/example01.dbf' logfile=import_dbmig12c.log

Import: Release 12.1.0.2.0 - Production on Wed Feb 11 12:28:44 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
Source time zone is +00:00 and target time zone is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@dbmig12c full=y directory=dump_dir dumpfile=dbmig.dmp transport_datafiles=/data/12c/oradata/DB12C/dbmig12c/data01.dbf,/data/12c/oradata/DB12C/dbmig12c/users01.dbf,/data/12c/oradata/DB12C/dbmig12c/example01.dbf logfile=import_dbmig12c.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"UNDOTBS1" failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file "/data/oradata/DBMIG/undotbs01.dbf"
Failing sql is:
 ALTER DATABASE DATAFILE '/data/oradata/DBMIG/undotbs01.dbf' RESIZE 120586240
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"ORDADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
ORA-31684: Object type ROLE:"APEX_ADMINISTRATOR_ROLE" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
...
..
.

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 684 error(s) at Wed Feb 11 13:08:39 2015 elapsed 0 00:39:43

-- Connect to PDB and check

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

SQL> alter session set container=dbmig12c;

Session altered.

SQL> show con_name

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

SQL> select FILE_NAME,TABLESPACE_NAME from cdb_data_files;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/data/12c/oradata/DB12C/dbmig12c/system01.dbf                SYSTEM
/data/12c/oradata/DB12C/dbmig12c/sysaux01.dbf                SYSAUX
/data/12c/oradata/DB12C/dbmig12c/data01.dbf                  DATA
/data/12c/oradata/DB12C/dbmig12c/example01.dbf               EXAMPLE
/data/12c/oradata/DB12C/dbmig12c/users01.dbf                 USERS

SQL>  select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
DATA                           ONLINE
EXAMPLE                        ONLINE
USERS                          ONLINE


SQL> conn shark/shark@dbmig12c
Connected.

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.