Transportable Tablespaces (TTS)

Transportable tablespaces can be used to copy a set of tables from one Oracle database to another. This approach to replication has been used for feeding data from OLTP systems to data warehouse systems, updating data warehouses and data marts from staging systems, loading data marts from central data warehouses, archiving OLTP and data warehouse systems efficiently and data publishing to internal and external customers.

You can move large amounts of data between databases just simply by moving data files from one database to another. You copy all the data files from the source database to the target database and import the data

dictionary information about the tablespaces from the source database to the target database.

Transportable Tablespace mechanism consist of combination of 2 things:

·         An export of metadata(instead of data) of tablespace

·         A copy of files belonging to tablespace 

You use transportable tablespaces mainly in the context of a data warehouse, some of the important features are

·         Moving data from the source database (OLTP) into a data warehouse database

·         Moving data from a staging database into a data warehouse database

·         Moving data from a data warehouse to a data mart

·         Performing tablespace point-in-time recovery

·         Archiving historical data

BENEFITS  OF TRANSPOTABLE TABLESPACES

·         Move  entire tablespace data

·         Supports media recovery  

·         Reduces Server Burden - When transportable tablespaces replace large export/import or other loading,a significant processing burden disappears.

·         Higher Reliability - Fewer processing steps and quicker processing reduce the opportunities for human Errors and system problems.

Limitations:

·         The source and target database must be on the same hardware platform.

·         The source and target database must have the same database block size.

·         A tablespace of the name being copied must not exist in the target database.

·         The current implementation does not support :

o    snapshot/replication

o    function-based indexes

o    scoped REF's

o    domain indexes

o    8.0-compatible advanced queues with multiple recipients.

There are 4 steps to transport a tablespace

1.     Grant the necessary privileges

2.     Make the tablespace is transportable

3.     Generate the transportable tablespace set (data dictionary information)

4.     Copy the data files to the target server

5.     Perform the tablespace import

Transporting tablespaces on different platforms :

You can transport tablespace between different server architectures, there is only one requirement both platform must have the same endian format. endian format refers to the byte ordering of the file system, they can be one of two types big or small, if they differ you must convert the data files to the format you require.

To check what format you have

Check endian format

select t.endian_format from v$transportable_platform t, v$database d where t.platform_name = d.platform_name;

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 11g R2)

The steps to transport a tablespace that are a different endian format are

1.     Ensure that the tablespaces are self-contained

2.     Make the tablespaces read-only

3.     Export the metadata using Data Pump Export

4.     Convert the data files to match the endian format

5.     Copy the converted data files to the target system

6.     Use the Data Pump Import utility to import the metadata

Steps for Migration

1-Verify Tablespace is self-contained:

      You can optionally choose whether to include referential integrity constraints as part of the transportable tablespace set. If you choose to do so, the tables required to maintain the key relationships will increase the size of the transportable tablespace set. Including referential integrity constraints is optional, because you may refer to the same tables in multiple databases. Say you plan to move a tablespace from a test database that contains a table called COUNTRY to a production database that already contains a COUNTRY table of identical country codes. Because the two databases have identical tables, you do not need to transport the referential integrity constraints. You could transport the tablespace and then reenable the referential integrity constraints in the target database once you've moved the tablespace, simplifying the creation of the transportable tablespace set. You must always export primary-key constraints.

     To find out if a tablespace set is self-contained, execute the TRANSPORT_SET_CHECK procedure as SYS User, which is in the DBMS_TTS package. This procedure takes two input parameters: the tablespace set and a Boolean flag set to TRUE if you want to include referential integrity constraints.
In this example, constraints aren't included in the AGG_DATA and AGG_INDEXES tablespace checks:
    execute DBMS_TTS.TRANSPORT_SET_CHECK ('AGG_DATA,AGG_INDEXES','FALSE');

Then execute the following to report violations:

        SELECT * FROM transport_set_violations;

2- Put Tablespace/s in Read Mode

Once you have verified that the selected tablespace set is self-contained, make the tablespaces read only, as follows:

     alter tablespace AGG_DATA read only;
alter tablespace AGG_INDEXES read only;

3- Export MetaData of Tablespace

     Next, export the metadata for the tablespaces, using the TRANSPORT_TABLESPACES and TABLESPACES parameters of the Oracle Export utility:

exp user/pwd TRANSPORT_TABLESPACE=Y TABLESPACES=(AGG_DATA,AGG_INDEXES) CONSTRAINTS=N GRANTS=Y TRIGGERS=N
 

4- Copy and Plugg in the Transportable Tablespace Set

Once you have moved the transportable tablespace set to an area the target database can access, you can plug the set into the target database. First, use the Oracle Import utility to import the exported metadata:

imp user/pwd TRANSPORT_TABLESPACE=Y DATAFILES=(agg_data.dbf,agg_indexes.dbf)

      In the import command, you specify the datafiles that are part of the transportable tablespace set. When the import has finished, all of the new tablespaces in the transportable tablespace set are in read-only mode. 

5- Modify Tablespaces to read-write Mode

      To change them to read-write mode, you issue the alter tablespace read write command in the target database:

      alter tablespace AGG_DATA read write;
 alter tablespace AGG_INDEXES read write;

      After you've made these changes, you should back up the control files of both the target and the source database:

           alter database backup controlfile to trace;

Ref:   http://www.mbjconsulting.com/oracle_links/Transportable_Tablespaces.html

Read more :  Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)