Create a plug-in tablespace by using the transportable tablespace feature

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] FULL TRANSPORTABLE TABLESPACE feature
1.1. Transport database demonstration
1.2 Transport tablespace demonstration
[2] Create a tablespace on the database in machine1 and create a table on this tablespace
Move this tablespace to the database of the machine2 and query the table.
[3] Create a tablespace on the database running on windows OS and create a table on this tablespace.
Convert the tablespace to the Linux Platform, move the tablespace to the database running on
Linux OS and plug it.
[4] Create two tablespaces on the database running on Linux OS and create a table on this tablespace.
Move it to the machine where the Windows OS is running. Convert it and plug it to the database.

Transporting whole tablespaces is a very good and practical way to move data from one Database to another. Oracle will generate a small dump with the object’s metadata and all you need to do is copy the datafiles and dumps to the new system.

In this topic, practice:

Check if Transport is possible with DBMS_TTS.TRANSPORT_SET_CHECK
Transportable expdp
Convert between different Endians
Transportable impdp.
Try also using Enterprise Manager Grid Control. It can be faster.

In 12c now we have a feature called FULL TRANSPORTABLE TABLESPACE feature. Practice it!

[1] FULL TRANSPORTABLE TABLESPACE feature

Thanks to Franck Pachot for demonstrate this feature.

When 12c came with pluggable database, I said that it was just an extension 
of the TTS feature where the SYSTEM tablespace can be transported as well, 
thanks to multitenant architecture that separates application dictionary.

What’s new in 12c is the ability to transport a whole database, which means 
use TTS for all non-system tablespaces.

1.1. Transport database demonstration

I’ve a PDB pluggable database with some tablespaces and tables, and 
I set the user tablespace read-only;

-bash-4.1$ export ORACLE_SID=cdb2
-bash-4.1$ sql

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO

SQL> create pluggable database pdb2 admin user admin2 identified by admin2
2 file_name_convert=('/u03/app/oracle/oradata/cdb2/pdbseed','/u03/app/oracle/oradata/cdb2/pdb2');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO
4 PDB2 MOUNTED
SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> alter session set container=pdb2;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
SQL>
SQL> grant dba to demo identified by demo;

Grant succeeded.

SQL> create tablespace users datafile '/tmp/userspdb2.dbf' size 10M ;

Tablespace created.

SQL> create table demo.demo tablespace users as select current_timestamp ts from dual;

Table created.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select * from demo.demo;

TS
---------------------------------------------------------------------------
26-AUG-19 11.00.05.369160 AM +05:30

SQL> quit


Then I create a new pluggable database from seed and create a database link 
to transport the tablespace into it:


-bash-4.1$ sqlplus / as sysdba
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MYPDB READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> create pluggable database pdb3 admin user admin3 identified by admin3
2 file_name_convert=('/u03/app/oracle/oradata/cdb2/pdbseed','/u03/app/oracle/oradata/cdb2/pdb3');

Pluggable database created.

SQL> alter session set container=pdb3;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 READ WRITE NO
SQL>
SQL> create database link DBL_PDB2 connect to system identified by oracle_4U 
     using '//racnode2/pdb2';

Database link created.

SQL> select * from dual@dbl_pdb2;

D
-
X

SQL> create directory tmp as '/tmp';

Directory created.

SQL> quit

$ cp /tmp/userspdb2.dbf /tmp/userspdb3.dbf

$ impdp '"sys/oracle_4U@//racnode2/pdb3 as sysdba"' network_link=DBL_PDB2 
directory=tmp full=y transportable=always transport_datafiles=/tmp/userspdb3.dbf

This is database transport. It’s just the possibility to use both 
FULL=Y and TRANSPORTABLE.


-bash-4.1$sql

SQL> alter session set container=pdb2;

Session altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY

SQL> select * from demo.demo;

TS
---------------------------------------------------------------------------
26-AUG-19 11.00.05.369160 AM +05:30

SQL> alter session set container=pdb3;

Session altered.

SQL> select tablespace_name,status from dba_tablespaces;

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

SQL> select * from demo.demo;

TS
---------------------------------------------------------------------------
26-AUG-19 11.00.05.369160 AM +05:30


1.2 Transport tablespace demonstration

If I wanted to transport only the tablespace, I need to have the user created before:


-bash-4.1$ sqlplus / as sysdba

SQL> alter pluggable database pdb3 close;

Pluggable database altered.

SQL> drop pluggable database pdb3 including datafiles;

Pluggable database dropped.

SQL> create pluggable database pdb3 admin user admin3 identified by admin3
2 file_name_convert=('/u03/app/oracle/oradata/cdb2/pdbseed','/u03/app/oracle/oradata/cdb2/pdb3');

Pluggable database created.

SQL> alter session set container=pdb3;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 MOUNTED
SQL>
SQL> startup
Pluggable Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB3 READ WRITE NO
SQL>
SQL> create database link DBL_PDB2 connect to system identified by oracle_4U 
     using '//racnode2/pdb2';

Database link created.

SQL> select * from dual@dbl_pdb3;

D
-
X

SQL> create directory tmp as '/tmp';

Directory created.

SQL> grant dba to demo identified by demo;

Grant succeeded.

SQL> quit

$cp /tmp/userspdb2.dbf /tmp/userspdb3.dbf

$ impdp '"sys/oracle_4U@//racnode2/pdb3 as sysdba"' network_link=DBL_PDB2 
directory=tmp transport_tablespaces=USERS transport_datafiles=/tmp/userspdb3.dbf

Import: Release 12.1.0.2.0 - Production on Mon Aug 26 12:27:38 2019

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

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
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/********@//racnode2/pdb3 AS SYSDBA" network_link=DBL_PDB3 directory=tmp transport_tablespaces=USERS transport_datafiles=/tmp/userspdb3.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Aug 26 12:30:18 2019 elapsed 0 00:02:31

-bash-4.1$ sql
SQL> alter session set container=pdb3;

Session altered.

SQL> select * from demo.demo;

TS
---------------------------------------------------------------------------
26-AUG-19 11.00.05.369160 AM +05:30

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

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

SQL> quit

This is the simple transport tablespace we know from a long time. 
Need to create the user, then all metadata is imported by 
data pump and data comes with the files. Here I used NETWORK_LINK because 
it’s the most simple, but everything is the same 
with a dumpfile. Need to copy datafiles with it to the target.


[2] Create a tablespace on the database in machine1 and create a table on 
this tablespace Move this tablespace to the database of the machine2 and 
query the table.


Machine1 : noncdb(orcl)

#Database-orcl
$ export ORACLE_SID=orcl
SQL> startup

SQL> create tablespace tbs_migrate_01 
datafile '/u01/app/oracle/oradata/orcl/migrate01.dbf' size 10m autoextend on;

SQL> create user tbs_migrate_01 identified by oracle_4U
default tablespace tbs_migrate_01
quota unlimited on tbs_migrate_01;

SQL> grant connect,resource,exp_full_database to tbs_migrate_01;

SQL> create table tbs_migrate_01.usr_table
2 tablespace tbs_migrate_01 as
3 select * from dba_objects where rownum <= 500;

SQL> exec dbms_tts.transport_set_check('tbs_migrate_01',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> create table tbs_migrate_01.tbl_self_contained(id number)
2 partition by range (id)
3 ( partition one values less than (100) tablespace tbs_migrate_01,
4 partition two values less than (1000) tablespace users);

Table created.

SQL> INSERT INTO tbs_migrate_01.tbl_self_contained
2 VALUES (&1);
Enter value for 1: 50
old 2: VALUES (&1)
new 2: VALUES (50)

1 row created.

SQL> /
Enter value for 1: 555
old 2: VALUES (&1)
new 2: VALUES (555)
INSERT INTO tbs_migrate_01.tbl_self_contained
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> ALTER USER tbs_migrate_01 QUOTA 10M ON USERS;


SQL> INSERT INTO tbs_migrate_01.tbl_self_contained
2 VALUES (&1);
Enter value for 1: 55
old 2: VALUES (&1)
new 2: VALUES (55)

1 row created.

SQL> /
Enter value for 1: 200
old 2: VALUES (&1)
new 2: VALUES (200)

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_tts.transport_set_check('tbs_migrate_01',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------
ORA-39901: Partitioned table tbs_migrate_01.TBL_SELF_CONTAINED is partially cont
ained in the transportable set.

ORA-39921: Default Partition (Table) Tablespace USERS for TBL_SELF_CONTAINED not
contained in transportable set.


SQL> ALTER TABLE tbs_migrate_01.tbl_self_contained move partition two 
     tablespace tbs_migrate_01;

Table altered.

SQL> exec dbms_tts.transport_set_check('tbs_migrate_01',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

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

PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------
Linux x86 64-bit Little

SQL> alter tablespace tbs_migrate_01 read only;

Tablespace altered.

SQL> grant read,write on directory DATA_PUMP_DIR to system;

Grant succeeded.

$expdp system/oracle_4U@orcl dumpfile=exp_migrate_01.dmp directory=DATA_PUMP_DIR 
transport_tablespaces=tbs_migrate_01

Export: Release 12.1.0.2.0 - Production on Sun Aug 25 18:57:18 2019

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

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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@orcl dumpfile=exp_migrate_01.dmp directory=DATA_PUMP_DIR transport_tablespaces=tbs_migrate_01
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/exp_migrate_01.dmp
******************************************************************************
Datafiles required for transportable tablespace tbs_migrate_01:
/u01/app/oracle/oradata/orcl/migrate01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Aug 25 18:58:09 2019 elapsed 0 00:00:46

$ cp /u01/app/oracle/oradata/orcl/migrate01.dbf /u03/app/oracle/oradata/cdb1/migrate01.dbf

-bash-4.1$ sqlplus tbs_migrate_01/oracle_4U@orcl
SQL> select count(*) from usr_table;

COUNT(*)
----------
500

SQL> select * from tbl_self_contained;

ID
----------
50
55
200

SQL> quit

-bash-4.1$ sql
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Machine2 CDB(pdb1)

#Database-cdb1
SQL> SQL>
export ORACLE_SID=cdb1
-bash-4.1$ sql
SQL> startup

SQL> alter session set container=pdb1;

SQL> create user usr_migrate_02 identified by oracle_4U
2 quota 10M on users
3 temporary tablespace temp;

SQL> grant connect,resource to usr_migrate_02;

$ cd /u01/app/oracle/admin/orcl/dpdump/
$ mv exp_migrate_01.dmp /u01/app/oracle/admin/cdb1/dpdump/
-bash-4.1$ cd /u01/app/oracle/admin/cdb1/dpdump/
-bash-4.1$ ls
exp_migrate_01.dmp

$ sqlplus sys/oracle_4U@pdb1 as sysdba

SQL> create or replace directory DATA_PUMP_DIR2 AS '/u01/app/oracle/admin/cdb1/dpdump';

SQL> GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR2 TO system;

$ impdp system/oracle_4U@pdb1 remap_schema=tbs_migrate_01:usr_migrate_02 
directory=DATA_PUMP_DIR2 transport_datafiles=/u03/app/oracle/oradata/cdb1/migrate01.dbf 
dumpfile=exp_migrate_01.dmp

Import: Release 12.1.0.2.0 - Production on Sun Aug 25 19:09:36 2019

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

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_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in US7ASCII character set and AL16UTF16 NCHAR character set
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@pdb1 remap_schema=tbs_migrate_01:usr_migrate_02 directory=DATA_PUMP_DIR2 transport_datafiles=/u03/app/oracle/oradata/cdb1/migrate01.dbf dumpfile=exp_migrate_01.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Aug 25 19:10:05 2019 elapsed 0 00:00:27

$ sqlplus sys/oracle_4U@pdb1 as sysdba
SQL> alter tablespace USR_MIGRATE_01 read write;

$ sqlplus usr_migrate_02/oracle_4U@pdb1

SQL> select count(*) from usr_table;

COUNT(*)
----------
500

SQL> select * from tbl_self_contained;

ID
----------
50
55
200

SQL> quit

[3] Create a tablespace on the database running on windows OS and 
create a table on this tablespace. Convert the tablespace to the Linux 
Platform, move the tablespace to the database running on Linux OS and plug it.

[4] Create two tablespaces on the database running on Linux OS and create 
a table on this tablespace. Move it to the machine where the Windows OS 
is running. Convert it and plug it to the database.

My other article related to this topic

Transporting FULL Database from 11g Database to 12c Database

Transporting Tablespaces from 11g Database to 12c Database


======================
Patch to Documentation.
======================
Administrator’s Guide -> Transporting Tablespaces Between Databases

15 Transporting Data
15.1 About Transporting Data
15.1.1 Purpose of Transporting Data
15.1.2 Transporting Data: Scenarios
15.1.2.1 Scenarios for Full Transportable Export/import
15.1.2.1.1 Moving a Non-CDB Into a CDB
15.1.2.1.2 Moving a Database to a New Computer System
15.1.2.1.3 Upgrading to a New Release of Oracle Database
15.1.2.2 Scenarios for Transportable Tablespaces or Transportable Tables
15.1.2.2.1 Scenarios That Apply to Transportable Tablespaces or Transportable Tables
15.1.2.2.2 Transporting and Attaching Partitions for Data Warehousing
15.1.2.2.3 Publishing Structured Data on CDs
15.1.2.2.4 Mounting the Same Tablespace Read-Only on Multiple Databases
15.1.2.2.5 Archiving Historical Data
15.1.2.2.6 Using Transportable Tablespaces to Perform TSPITR
15.1.2.2.7 Copying or Moving Individual Tables

15.1.3 Transporting Data Across Platforms
15.1.4 General Limitations on Transporting Data
15.1.5 Compatibility Considerations for Transporting Data

15.2 Transporting Databases
15.2.1 Introduction to Full Transportable Export/Import
15.2.2 Limitations on Full Transportable Export/import
15.2.3 Transporting a Database Using an Export Dump File
15.2.4 Transporting a Database Over the Network

15.3 Transporting Tablespaces Between Databases
15.3.1 Introduction to Transportable Tablespaces
15.3.2 Limitations on Transportable Tablespaces
15.3.3 Transporting Tablespaces Between Databases

15.4 Transporting Tables, Partitions, or Subpartitions Between Databases
15.4.1 Introduction to Transportable Tables
15.4.2 Limitations on Transportable Tables
15.4.3 Transporting Tables, Partitions, or Subpartitions Using an Export Dump File
15.4.4 Transporting Tables, Partitions, or Subpartitions Over the Network

15.5 Converting Data Between Platforms
15.5.1 Converting Data Between Platforms Using the DBMS_FILE_TRANSFER Package
15.5.2 Converting Data Between Platforms Using RMAN

15.6 Guidelines for Transferring Data Files