DB - Transportable Tablespaces

EnglishVersion

Oracle allows a nice way to copy aplication / data between databases, is known as TTS ( Transportable Tablespace). Basically is to export a dump with tablespace's metadata and copy the datafile(s). With that dump + data file (s) associated to the tablespace is possible to attach the tablespace to another database.

All this using oracle commands expdp + impdp and standard commands to copy the dump and datafile(s) : cp,scp, ftp,etc

Ref : https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11394

Preparation

1 - Create a test tablespace : xpto : Data File : xpto.bdf
2 - Create a test user : xpto
3 - Create a test data/table : xpto.users

SQL> create tablespace xpto datafile 'xpto.bdf' size 10m autoextend on next 10m maxsize 100m;
Tablespace created.

SQL> create user xpto identified by xpto default tablespace xpto quota unlimited on xpto;
User created.

SQL> create table xpto.users as select * from dba_users;
Table created.

expdp

1 - create a folder for export
2 - force an error ( that is not an error, is a requirement the tablespace need to be as "read only")
3 - make a copy od the datafile 'xpto.bdf'

SQL> create directory DIR_TTS as '/home/oracle';
Directory created.

SQL> !expdp DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES='XPTO' DUMPFILE=tts_xpto.dmp
Export: Release 11.2.0.1.0 - Production on Fri Feb 16 16:32:15 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES=XPTO DUMPFILE=tts_xpto.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'XPTO' is not read only
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:32:22

SQL> alter tablespace xpto read only;
Tablespace altered.

SQL> !expdp DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES='XPTO' DUMPFILE=tts_xpto.dmp

Export: Release 11.2.0.1.0 - Production on Fri Feb 16 16:32:50 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES=XPTO DUMPFILE=tts_xpto.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts_xpto.dmp
******************************************************************************
Datafiles required for transportable tablespace XPTO:
/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:33:36

SQL> !cp /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle

SQL> !ls /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle/*xpto*
/home/oracle/tts_xpto.dmp /home/oracle/xpto.bdf /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

SQL> !ls -l /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle/*xpto*
-rw-r----- 1 oracle oracle 90112 Feb 16 16:33 /home/oracle/tts_xpto.dmp
-rw-r----- 1 oracle oinstall 10493952 Feb 16 16:34 /home/oracle/xpto.bdf
-rw-r----- 1 oracle oracle 10493952 Feb 16 16:32 /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

Test - Drop the tablespace

To simulate other database / even as backup/recover test , I will remove the tablespace

SQL> drop tablespace xpto including contents and datafiles;
Tablespace dropped.

impdp

Now is time to bring the data/tablespace back for that lets do the next steps
1 - put data files in the right path
2 - run the impdp
3 - Check thetable xpto.users's data

SQL> !cp /home/oracle/xpto.bdf /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

SQL> !impdp DIRECTORY=DIR_TTS DUMPFILE='tts_xpto.dmp' TRANSPORT_DATAFILES='/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf'

Import: Release 11.2.0.1.0 - Production on Fri Feb 16 16:44:45 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS DUMPFILE=tts_xpto.dmp TRANSPORT_DATAFILES=/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:44:50

SQL> select count(0) from xpto.users;

COUNT(0)
----------
37

Thank you for reading, hope this post was helpful.
Rogerio