12c Database Upgrade/Migrate using Full Transportable Export/Import

Traditional methods (Original export/import, Datapump export/import, Transportable tablespaces) can be used to upgrade/migrate database to 12c. In addition to that, 12c provides new feature called Full transportable export/import method to make migration/upgrade to easier, faster and more efficient.

Traditional export/import is slower method. For large database size systems it is not preferred method. Though it is a reliable migration technique, it should be used only for Oracle 9i and earlier databases.

Datapump export/import was introduced in Oracle 10g offers more features. It is designed to handle large volume of data.  But Datapump is overtaken by Transportable tablespace in terms of performance.

Transportable tablespaces achieves upgrade/migrate faster by copying database files to target system. Database file conversion will be required in case source and target has different Endian format. Since database file is directly copied it is faster than Datapump export/import. But it requires additional complicated set of steps to move user and system metadata to destination database. User objects associated with non user tablespaces (system and sysaux tablespaces) needs to be taken care manually.

Oracle 12c has introduced new feature “Full Transportable Export/Import”. It uses both Transportable tablespaces mechanism and Datapump features.  Transportable tablespace mechanism used to move database files to target system which results in migration in a faster even for large volume of data. Also it takes advantage of Datapump to take advantage of option to move metadata to target database and it is able to complete migration with single import command. At the end Full transportable export/import moves all of user data and metadata needed for database migration.

Prerequisites

1)      Create new target 12c database. It can non-CDB(Container database) or PDB (Pluggable database)

2)      Grant DATAPUMP_IMP_FULL_DATABASE role to user going to perform migration

3)      Set tablespaces read-only before performing upgrade/migrate

Steps

1) Create new 12c database:

The newly create database includes set of administrative tablespaces, Oracle supplied components and packages

2) Check Endianness of source and target platform

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_ID = d.PLATFORM_ID;

3) Verify the tablespaces to be transported is self-contained

We must verify that there are no logical or physical dependencies between objects stored in tablespaces being transported and those that are not being transported

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(' User Tablespace names separated by comma operator', TRUE);

The results can be checked at

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

If query has returned rows then we need to address the issues before proceeding to full transportable operation

4) Create directory object

Create Directory object at source database to store dump

SQL> CREATE DIRECTORY dp_dir AS ’<Physical location>’;

5) Place user tablespaces which is to be transported in read only mode

SQL> ALTER TABLESPACE <tablespace_name> READ ONLY;  -- Downtime starts from here

6) Invoke full transportable export on the source database

expdp system/<password> full=y transportable=always version=12 \

directory=<database directory> dumpfile=<dump file name> \

metrics=y exclude=statistics \

logfile=<log file name>

 Version parameter is required only when source database is 11.2.0.3 or 11.2.0.4

7) Copy database datafiles to target site

Physically copy datafiles of tablespaces to target site

8) Create directory object at destination site

SQL> CREATE DIRECTORY dp_dir AS ’<Physical location>’;

9) Invoke full transport import at destination database

impdp system/<password>@<db name> directory=<directory name> \

dumpfile=<dump file name> logfile=<log file name> \

metrics=y \

transport_datafiles='<datafile names with full path>',

10) Convert tablespaces to read-write at source and target database - Downtime is completed

SQL> alter tablespace <tablespace-name> read write;