Transportable tablespace from 11gR2 to 12cR1

By | | , ,

Move a tablespace between Oracle database version 11gR2 to 12cR1 with the fastest method between Oracle databases using the transportable tablespace features.

Table of Contents:

  • Transportable tablespace
  • Step by step Transportable tablespace

1. Transportable tablespace:

This method provides broad cross-platform migration support, and limited support for source and destination databases with different character sets. You can also use the transportable tablespace feature to migrate data to a later version of Oracle Database. This method is often chosen when migrating between platforms with different endian formats, or in cases where physical re-organization is not necessary. more

How to check the Endien Format:

SQL> SELECT tp.endian_format FROM v$transportable_platform tp, v$database d WHERE tp.platform_name = d.platform_name;
ENDIAN_FORMAT
Little

2. Step by step Transportable tablespace

a. Specifications of the environments:

b. Steps to export Tablespace on the Source database:

b.1. Creation of a tablespace using OMF:

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 31 10:29:52 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connecte a :
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining  and Real Application Testing options
SQL> 
SQL> create tablespace wadhah_tbs ;
Tablespace cree.

b.2. Creation of a user:

SQL> create user wadhah identified by wadhah default tablespace wadhah_tbs;
Utilisateur cree.
SQL> grant unlimited tablespace,connect,create table to wadhah;
Autorisation de privileges (GRANT) acceptee.
SQL> 
SQL> grant select on hr.employees to wadhah;
Autorisation de privileges (GRANT) acceptee.

SQL> connect wadhah/wadhah
Connecte.
SQL> create table employees as select * from hr.employees;
Table creee.

b.3. Check if possible to transport a tablespace:

The DBMS_TTS package checks if the transportable set is self-contained.

SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'WADHAH_TBS', incl_constraints => TRUE);
Procedure PL/SQL terminee avec succes.
SQL> 
SQL> SELECT * FROM transport_set_violations;
aucune ligne selectionnee

Note: Now we can move the tablespace while with no violations

b.3. Export du tablespace on read only mode:

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
DATA_PUMP_DIR      /u01/app/oracle/admin/orcl/dpdump/

SQL> grant write on directory DATA_PUMP_DIR to system;
Autorisation de privileges (GRANT) acceptee.
SQL> 
SQL> alter tablespace WADHAH_TBS read only;
Tablespace modifie.
SQL> exit
SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='WADHAH_TBS';
FILE_NAME +DATA/orcl/datafile/wadhah_tbs.280.998994683

b.4. Export du metadata of the tablespace using EXPDP: 

$ expdp system directory=DATA_PUMP_DIR dumpfile=wadhah_tbs.dmp logfile=wadhah_tbs.log transport_tablespaces=WADHAH_TBS
Export: Release 11.2.0.4.0 - Production on Thu Jan 31 10:46:54 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining
 and Real Application Testing options
 Demarrage de "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" : system/ directory=DATA_PUMP_DIR dumpfile=wadhah_tbs.dmp logfile=wadhah_tbs.log transport_tablespaces=WADHAH_TBS 
 Traitement du type d'objet TRANSPORTABLE_EXPORT/PLUGTS_BLK
 Traitement du type d'objet TRANSPORTABLE_EXPORT/TABLE 
Traitement du type d'objet TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 Table maitre "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" chargee/dechargee avec succes 
L'ensemble de fichiers de vidage de SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 est :
   /u01/app/oracle/admin/orcl/dpdump/wadhah_tbs.dmp

Fichiers de donnees requis pour le tablespace transportable WADHAH_TBS :
   +DATA/orcl/datafile/wadhah_tbs.280.998994683
 L'execution du travail "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" a abouti a Jeu. Janv. 31 10:48:11 2019 elapsed 0 00:01:09

b.5. Move tablespace files to the destination server: 

In my case, I am using the Oracle grid infrastructure

$ asmcmd cp +DATA/orcl/datafile/wadhah_tbs.280.998994683 /home/oracle/wadhah_tbs.dbf
 copying +DATA/orcl/datafile/wadhah_tbs.280.998994683 -> /home/oracle/wadhah_tbs.dbf

$ ll -h /u01/app/oracle/admin/orcl/dpdump/wadhah_tbs.dmp
 -rw-r----- 1 oracle dba 92K 31 janv. 10:48 /u01/app/oracle/admin/orcl/dpdump/wadhah_tbs.dmp

$ scp /u01/app/oracle/admin/orcl/dpdump/wadhah_tbs.dmp  /home/oracle/wadhah_tbs.dbf oracle@Oracle12c:~/

c. Steps to import Tablespace on the 12c database:

c.1. Create the user: 

$ sqlplus / as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 31 13:49:33 2019
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Connecte a :
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 SQL> create user wadhah identified by wadhah;
 Utilisateur cree.

 SQL> grant unlimited tablespace,connect,create table to wadhah;
 Autorisation de privileges (GRANT) acceptee.

 SQL> grant read,write on directory DATA_PUMP_DIR to system;
 Autorisation de privileges (GRANT) acceptee.

 SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
DIRECTORY_NAME DIRECTORY_PATH 
DATA_PUMP_DIR /u01/app/oracle/admin/db12c/dpdump/

c.2. Copy the files: 

$ cp wadhah_tbs.dmp /u01/app/oracle/admin/db12c/dpdump/

c.3. Import the tablespace: 

$ impdp system directory=DATA_PUMP_DIR dumpfile=wadhah_tbs.dmp logfile=wadhah_tbs.log transport_datafiles='/home/oracle/wadhah_tbs.dbf'
 Import: Release 12.1.0.2.0 - Production on Thu Jan 31 13:52:55 2019
 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
 Table maitre "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" chargee/dechargee avec succes
 La version de fuseau horaire source est 14 et la version de fuseau horaire cible est 18.
 Demarrage de "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" : system/ directory=DATA_PUMP_DIR dumpfile=wadhah_tbs.dmp logfile=wadhah_tbs.log transport_datafiles=/home/oracle/wadhah_tbs.dbf 
 Traitement du type d'objet TRANSPORTABLE_EXPORT/PLUGTS_BLK
 Traitement du type d'objet TRANSPORTABLE_EXPORT/TABLE
 Traitement du type d'objet TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 L'execution du travail "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" a abouti a Jeu. Janv. 31 13:53:08 2019 elapsed 0 00:00:06

c.4. Verification: 

$ sqlplus / as sysdba
 SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 31 13:53:18 2019
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 Connecte a :
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 SQL> select NAME from v$tablespace;
 NAME 
 SYSAUX
 SYSTEM
 UNDOTBS1
 USERS
 TEMP
 EXAMPLE
 WADHAH_TBS
 7 lignes selectionnees.
 SQL> 
 SQL> connect wadhah/wadhah
 Connecte.
 SQL> select count(*) from employees;
  COUNT(*)
   107

3. Conclusion:

Move a tablespace between Oracle database version 11gR2 to 12cR1 with transportable tablespace .

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.