12c : Transportable Database

Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It  employs

  •  Oracle Data Pump export  to extract  all of the system, user, and application metadata needed for a database migration
  •   transportable tablespaces mechanism to move user and application data i.e. datafiles containing user and application data are physically copied to the target. This results in a migration that is very fast, even for very large volumes of data.

Thus, you can upgrade or migrate to Oracle Database 12c easily and quickly with a single import command. This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3).

This post focuses on the use of full transportable export/import  to migrate an 11.2.0.3 database to a pluggable database , although this feature can be used for migrations to a Oracle Database 12c non-CDB database as well. 

The steps for migrating from an 11.2.0.3 non-CDB  into a pluggable database are as follows:

  • Set the user and application tablespaces in the source database to be READ ONLY
  •  Export from the source database using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS
  • Copy the  dump file and data files for tablespaces containing user/application data to the destination
  • Create a new PDB in the destination CDB using the create pluggable database command
  • Create a directory object in the destination PDB pointing to the folder containing dumpfile
  • Create an entry for the destination PDB in tnsnames.ora
  •   Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target database using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters
  • Restore the user tablespaces in source database to read/write mode

Current scenario:
Source
version : 11.2.0.3
Database
: orcl
Platform 32 bit RHEL 5.4
Tablespaces : system, sysaux, users, example, undotbs1, temp
Target  
container database : cdb1
pluggable database : pdb_orcl
version : 12.1.0.1
Platform 64 bit OEL 5.4
Tablespaces : system, sysaux

Objective :

– Upgrade orcl (11.2.0.3 32 bit linux) to pdb_orcl(12.1.0.1 64 bit linux) using expdp transportable database
- While import rename users tablespace to users_orcl in target db

– Implementation –

— Source Database (ORCL) —

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

– Issue export command 
. TRANSPORTABLE=ALWAYS and FULL=Y : These parameter values tell Data Pump to use full transportable rather than conventional export methods.
.  VERSION=12   : Since source database is  11g Release 2 (11.2.0.3), we  must also specify the Data Pump parameter VERSION=12 to denote the fact that the result of the export will be imported into an Oracle Database 12c Release 1 (12.1) or later database. This is the only time that a version number greater than the current version is allowed by the expdp command. If the source database is Oracle Database 12c, with COMPATIBLE=12.0 or higher, then the VERSION parameter is not required.

Note that command fails as user tablespaces have not been put in read only mode.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Export: Release 11.2.0.3.0 - Production on Mon Mar 24 16:53:13 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y version=12.0 transportable=always
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
 ORA-29335: tablespace 'USERS' is not read only
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 16:53:20

– Set the user and application tablespaces in the source database to be READ ONLY

SQL> alter tablespace example read only;

              alter tablespace users read only;

– Issue expdp command again
After the export command completes, the export log file shows a list of all of the tablespace data files that need to be moved to the target.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log

Export: Release 11.2.0.3.0 - Production on Tue Mar 25 09:38:23 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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=exporcl.dmp logfile=exporcl.log
Estimate in progress using BLOCKS method...

...

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
 /u01/app/oracle/oradata/orcl/example01.dbf
 Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 09:51:17

— TARGET CONTAINER DATABASE (CDB1) –

– Find out names of  the datafiles of   seed  database in cdb1

SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

– create pluggable database pdb_orcl

SQL> create pluggable database pdb_orcl admin user pdbadmin identified by oracle file_name_convert=('pdbseed','pdb_orcl');

SQL> sho pdbs

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

-- check that pdb_orcl has  datafiles for system/sysaux tablespaces only

SQL> alter pluggable database pdb_orcl open;

     select file_name from cdb_data_files where con_id=4;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf

– Add  tnsnames.ora entry for pdb_orcl

PDB_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl)
)
)

– Copy the  export dump file and the data files for example and users tablespaces   from source to target

— copy example01.dbf and users01.dbf belonging to orcl to folder /u01/app/oracle/oradata/cdb1/pdb_orcl  on target machine

— copy exporcl.dmp containing  metadata  to the folder /u01/app/oracle/admin/cdb1 on target machine

– Create a directory object on the destination PDB (PDB_ORCL)  pointing to location of exporcl.dmp i.e. /u01/app/oracle/admin/cdb1

SQL> alter session set container=pdb_orcl;

sho con_name

CON_NAME
------------------------------
PDB_ORCL

SQL> create directory dump_dir as '/u01/app/oracle/admin/cdb1';

– Invoke full transportable import on the destination PDB

Specify following parameters :
- the dumpfile name
– full = Y and
– TRANSPORT_DATAFILES : to secify  the list of user tablespace data files to be transported

[oracle@em12 admin]$ impdp system/oracle@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf', '/u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf' remap_tablespace=users:users_orcl logfile=import_orcl.log

Import: Release 12.1.0.1.0 - Production on Tue Mar 25 11:41:44 2014

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 "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf, /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf remap_tablespace=users:users_orcl logfile=import_orcl.log

....
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 3395 error(s) at Tue Mar 25 12:07:27 2014 elapsed 0 00:25:40

– Check that target PDB’s metadata is updated with datafiles for example and users tablespaces  

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf

–  Check that the application tablespaces are automatically placed in read/write mode on the destination database and users tablespace has been renamed to users_orcl

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
EXAMPLE ONLINE
USERS_ORCL ONLINE

 — Check that user HR has been created on the target PDB also 

SQL> conn hr/hr@pdb_orcl
    select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW

– Restore user tablespaces to read-write mode on the source database

SQL> alter tablespace USERS read write;

alter tablespace example read write;
              select tablespace_name, status from dba_tablespaces;

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

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17636/transport.htm#ADMIN13727
Full Transportable Export / Import – Things to know
Full Transportable Export / Import – Migrating an 11.2.0.4 Database to Oracle Database 12c – Into the Cloud

———————————————————————————————

Related Links:

Home

Database 12c

12c: Transport Database Over Network

 

 

 

 

 

3 thoughts on “12c : Transportable Database

  1. People like makes blogs more interesting and readable. Crystal explanation step by step. Well done buddy. Keep it up. Blogs like this makes Oracle World easy and accessible for novice to this technology.

Leave a Reply to Anju Garg Cancel reply