Transportable Tablespaces (TTS) in Oracle

Oracle Transportable Tablespaces (TTS)

We can use the transportable tablespaces feature to copy/move subset of data (set of user tablespaces), from an Oracle database and plug it in to another Oracle database. The tablespaces being transported can be either dictionary managed or locally managed.

With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.

With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.

With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).

From 10g Release2 we can transport whole database, this is called Transportable Database (TDB).

From Oracle 11g, we can transport single partition of a tablespace between databases.

You can also query the V$TRANSPORTABLE_PLATFORM view to see all the platforms that are supported, and to determine their platform names and IDs and their endian format.

SQL> select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit)                  Big
2 Solaris[tm] OE (64-bit)                  Big
3 HP-UX (64-bit)                           Big
4 HP-UX IA (64-bit)                        Big
5 HP Tru64 UNIX                            Little
6 AIX-Based Systems (64-bit)               Big
7 Microsoft Windows IA (32-bit)            Little
8 Microsoft Windows IA (64-bit)            Little
9 IBM zSeries Based Linux                  Big
10 Linux IA (32-bit)                        Little
11 Linux IA (64-bit)                        Little
12 Microsoft Windows x86 64-bit             Little
13 Linux x86 64-bit                         Little
15 HP Open VMS                              Little
16 Apple Mac OS                             Big
17 Solaris Operating System (x86)           Little
18 IBM Power Based Linux                    Big
19 HP IA Open VMS                           Little
20 Solaris Operating System (x86-64)        Little
21 Apple Mac OS (x86-64)                    Little  (from Oracle 11g R2)

To find out your platform name and it's endian format

SQL> select tp.PLATFORM_NAME, tp.ENDIAN_FORMAT from v$database d, v$transportable_platform tp where d.platform_name=tp.platform_name;

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit)                  Big

Transporting tablespaces is particularly useful for

(i) Updating data from production to development and test instances.

(ii) Updating data from OLTP systems to data warehouse systems.

(iii) Transportable Tablespace (TTS) is used to take out of the database pieces of data for various reasons (Archiving, Moving to other databases etc).

(iv) Performing tablespace point-in-time-recovery (TS PITR).

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

In Oracle 8i, there were three restrictions with TTS. First, both the databases must have same block size. Second, both platforms must be the same OS. Third, you cannot rename the tablespace. Oracle 9i removes the first restriction. Oracle 10g removes the second restriction. Oracle 10g also makes available a command to rename tablespaces.

Limitations/Restrictions

Following are limitations/restrictions of transportable tablespace:

System, undo, sysaux and temporary tablespaces cannot be transported.

The source and target database must be on the same hardware platform. We can transport tablespaces between Sun Solaris databases, or we can transport tablespaces between Windows NT databases. However, you cannot transport a tablespace from a Sun Solaris database to a Windows NT database.

The source and target database must use the same character set and national character set.

If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.

You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

Transportable tablespaces do not support: Materialized views/replication Function-based indexes.

We can't do TTS when we are using TDE concept for our database.

Binary_Float and Binary_Double datatypes (new in Oracle 10g) are not supported.

At Source:

Validating Self Containing Property

TTS requires all the tablespaces, which we are moving, must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('tbs', TRUE);

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('tbs1, tbs2, tbs3', FALSE, TRUE);

SQL> SELECT * FROM transport_set_violations;

No rows should be displayed

If it were not self contained you should either remove the dependencies by dropping/moving them or include the tablespaces of segments into TTS set to which migration set is depended.

Put Tablespaces in READ ONLY Mode

We will perform physical file system copy of tablespace datafiles. In order those datafiles to not to require recovery, they need to be in consistent during the copy activity. So put all the tablespaces in READ ONLY mode.

SQL> alter tablespace tbs-name read only;

Export the Metadata

Export the metadata of the tablespace set.

exp FILE=/path/dump-file.dmp LOG=/path/tts_exp.log TABLESPACES=tbs-names TRANSPORT_TABLESPACE=y STATISTICS=none

(or)

expdp DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=exp_dir TRANSPORT_TABLESPACES=tts TRANSPORT_FULL_CHECK=y

for transporting partition,
expdp DUMPFILE=tts_partition.dmp LOGFILE=tts_partition_exp.log DIRECTORY=exp_dir TRANSPORTABLE=always TABLES=trans_table:partition_Q1

If the tablespace set being transported is not self-contained, then the export will fail.

You can drop the tablespaces at source, if you don’t want them.

SQL> drop tablespace tbs-name including contents;

Otherwise, make all the tablespaces as READ WRITE

SQL> alter tablespace tbs-name read write;

Copying Datafiles and export file

Copy the datafiles and export file to target server.

At Target:

Import the export file.

imp FILE=/path/dump-file.dmp LOG=/path/tts_imp.log TTS_OWNERS=user-name FROMUSER=user-name TOUSER=user-name TABLESPACES=tbs-name TRANSPORT_TABLESPACE=y DATAFILES=/path/tbs-name.dbf

(or)

impdp DUMPFILE=tts.dmp LOGFILE=tts_imp.log DIRECTORY=exp_dir

REMAP_SCHEMA=master:scott TRANSPORT_DATAFILES='/path/tts.dbf'

for transporting partition,

impdp DUMPFILE=tts_partition.dmp LOGFILE=tts_partition_imp.log DIRECTORY=exp_dir TRANSPORT_DATAFILES='/path/tts_part.dbf' PARTITION_OPTIONS=departition

Finally we have to switch the new tablespaces into read write mode:

SQL> alter tablespace tbs-name read write;

TRANSPORT TABLESPACE Using RMAN
Create transportable tablespace sets from backup for one or more tablespaces.
RMAN> TRANSPORT TABLESPACE example, tools
TABLESPACE DESTINATION '/disk1/trans' AUXILIARY DESTINATION '/disk1/aux' UNTIL TIME 'SYSDATE-15/1440';
RMAN> TRANSPORT TABLESPACE exam
TABLESPACE DESTINATION '/disk1/trans' AUXILIARY DESTINATION '/disk1/aux' DATAPUMP DIRECTORY dpdir DUMP FILE 'dmpfile.dmp' IMPORT SCRIPT 'impscript.sql' EXPORT LOG 'explog.log';

Using Transportable Tablespaces with a Physical Standby Database

We can use the Oracle transportable tablespaces feature to move a subset of an Oracle database and plug it in to another Oracle database, essentially moving tablespaces between the databases.

To move or copy a set of tablespaces into a primary database when a physical standby is being used, perform the following steps:

1. Generate a transportable tablespace set that consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.

2. Transport the tablespace set:
  a. Copy the datafiles and the export file to the primary database.
  b. Copy the datafiles to the standby database.

The datafiles must be copied in a directory defined by the DB_FILE_NAME_CONVERT initialization parameter. If DB_FILE_NAME_CONVERT is not defined, then issue the ALTER DATABASE RENAME FILE statement to modify the standby control file after the redo data containing the transportable tablespace has been applied and has failed. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO.

3. Plug in the tablespace.
Invoke the Data Pump utility to plug the set of tablespaces into the primary database. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database.

Related Packages

DBMS_TTS

DBMS_EXTENDED_TTS_CHECKS

Other Oracle Articles:  Export and Import (exp & imp)     Data Pump in Oracle