Question

Upgrade to 12C from 11.2.0.3+ using Transportable parameter via Datapumps

Answer 1

1. Create an empty 12c database.
2. Export the 11.2.0.3+ database using the "FULL=Y TRANSPORTABLE=ALWAYS VERSION=12" parameters.
3. Copy the datafiles into the correct location. If switching between platforms, the datafiles must be converted using the RMAN CONVERT command, or the DBMS_FILE_TRANSFER package.
4. Import into the 12c database

 

Create a new 12c database using the DBCA.

$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ dbca

With the 12c database in place, we must prepare the 11g database for the export. I was testing this on a clean installation of 11.2.0.3, so I had to create a directory object for the export, as well as a little dummy data.

$ export ORACLE_SID=orcl
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES
$ sqlplus / as sysdba

-- Create a directory to export to.
-- This directory should be created in the 12c database too.
CREATE OR REPLACE DIRECTORY TEMP_DIR AS '/tmp/';

-- Create a new tablespace and test user with some dummy data.
CREATE TABLESPACE data_ts 
  DATAFILE '/u01/app/oracle/oradata/orcl/data01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;

CREATE user test IDENTIFIED BY test
  DEFAULT TABLESPACE data_ts
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON data_ts;

GRANT CREATE SESSION, CREATE TABLE TO test;

CREATE TABLE test.t1 AS
SELECT * FROM dba_objects;

-- Make the tablespaces you want to transport read-only.
ALTER TABLESPACE data_ts READ ONLY;
ALTER TABLESPACE example READ ONLY;

EXIT

Export the database, using the "FULL=Y TRANSPORTABLE=ALWAYS VERSION=12" parameters. I excluded the "USER" tablespace as it was already present in the 12c database and there was nothing I wanted to transfer in that tablespace.

$ expdp system full=Y transportable=always version=12 directory=TEMP_DIR \
   dumpfile=orcl.dmp logfile=expdporcl.log exclude=TABLESPACE:\"= \'USERS\'\"

The export contains just metadata for the data tablespaces, but includes the normal dump contents of the SYSTEM and SYSAUX tablespaces.

Copy the datafiles to the correct location for the 12c database.

$ cp /u01/app/oracle/oradata/orcl/data01.dbf /u01/app/oracle/oradata/orcl12c
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl12c

At this point it probably makes sense to switch the tablespaces back to read-write and shutdown the 11g database.

$ sqlplus / as sysdba
ALTER TABLESPACE data_ts READ WRITE;
ALTER TABLESPACE example READ WRITE;

SHUTDOWN IMMEDIATE;
EXIT;

We can now import the dump file into the 12c database. This of course assumes you have already created the directory object in the 12c database, as mentioned previously.

$ export ORACLE_SID=orcl12c
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES

$ impdp system full=Y directory=TEMP_DIR dumpfile=orcl.dmp logfile=impdporcl.log \
   transport_datafiles= \
      '/u01/app/oracle/oradata/orcl12c/data01.dbf', \
      '/u01/app/oracle/oradata/orcl12c/example01.dbf'

Answer 2

The 12c database now contains all the data from the original database.

$ sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 24 12:06:01 2013

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

Last Successful login time: Wed Jul 24 2013 12:05:38 +01: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> SELECT COUNT(*) FROM t1;

  COUNT(*)
----------
     75213

SQL>