Transportable Tablespaces

 
This feature has given new dimension to DBA'S in managing the databases effectively .This feature bypasses usual data extract and load processes and enables fast bulk data transport between databases. Its mechanism consist of combination of 2 things:

• An export of metadata (instead of data) of tablespace
  
• A copy of files belonging to tablespace 

These two parts can be transferred to another compatible system  in order to plug in tablespaces with its files on another database Restore files to new location . Import metadata of tablespace specifying new location of files by transporting tablespaces between databases data movement can be fast as a simple  transfer of files between  machines which greatly improves the performance and provides operational simplicity.
A transportable tablespace set contains all the datafiles for the tablespaces you are moving along with an export of the metadata for those tablespaces. The tablespaces you wish to transport should be self-contained - they should not contain any objects that depend on objects outside the tablespaces in the set. For example, if you want to move a table, you must also transport the tablespace that contains the table's indexes. The better you organize and distribute your objects among tablespaces, the easier it is to generate a self-contained set of tablespaces for transport.

LIMITATIONS OF TRANSPORTABLE TABLESPACES

• Tablespaces need to be self contained: All dependent objects must be included in tablespace set
• Tablespaces need to be read only for duration of export and datafile copying.
• Not restartable
• Must be privileged account
• Notes:
• Use RMAN CONVERT to change endianness (if needed)
• Data Pump moves only metadata
• Can use network link
• For a full list of what is exported:
  select unique seq_num, full_pathfrom datapump_pathswhere het_type = 'TRANSPORTABLE_EXPORT' order by seq_num;
  

Steps for Transportable Tablespaces

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 re-enable 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 are included in the AGG_DATA and AGG_INDEXES tablespace checks:
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'AGG_DATA,AGG_INDEXES', incl_constraints => TRUE);

Then execute the following to report violations:
SELECT * FROM SYS.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:

expdp userid=system directory=temp_dir transport_tablespaces=AGG_DATA,AGG_INDEXES dumpfile=agg_data.dmp logfile=agg_exp.log transport_full_check=y

or

exp system TRANSPORT_TABLESPACE=Y TABLESPACES=(AGG_DATA,AGG_INDEXES) CONSTRAINTS=N GRANTS=Y TRIGGERS=N file=agg_data.dmp log=agg_exp.log

The advantage of Oracle data pump IMPDP utility over Oracle EXP and IMP utilities is one need not create dump file for meta data extraction. Data Pump Import fetches the necessary metadata from the source across the database link and re-creates it in the target  database.


4- Copy and Plugg in the Transportable Tablespace Set
In PROD, you can put back the tablespace in WRITE mode:
alter tablespace AGG_DATA read write;
alter tablespace AGG_INDEXES read write;

Now copy the dmp file and the datafiles of the transportable tablespace set to an area that the target database can access, you can plug the set into the target data.

Then import the tablespace metadata:
impdp userid=system directory=temp_dir dumpfile=agg_data.dmp logfile=agg_imp.log transport_datafiles='/export/home/oracle/DIE/datafile/agg_data.dbf','/export/home/oracle/DIE/datafile/agg_indexes.dbf' keep_master=y

or

imp system TRANSPORT_TABLESPACE=Y DATAFILES=(agg_data.dbf,agg_indexes.dbf) file=agg_data.dmp log=add_imp.log

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;

Note that instead of importing the table's data, you have imported only the tablespaces' metadata - a significant performance savings. When you import the data into the target database, the schema owner must already exist - the import does not create a user
 

What About Partitions?
When you move a table, you have to move the entire table. If you have partitioned a table, you can't transport just one partition - it's all or nothing. If you have a large table with many partitions and you want to use the transportable tablespace feature to move one partition's worth, you can follow these steps:

1. Create a new tablespace to store the data you want to move.
   
2. In the new tablespace, create a table for the data you want to move. For example, you can use a create table as select command with a where clause that selects only the current month's data and puts it into the new table. Optionally, you can create indexes for the new table, in either the new tablespace or in a separate isolated one.
3. Prepare the new tablespace for the move by placing it in read-only mode.
   
4. Check the transport set by using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
   
5. Export the metadata for the new tablespace.
   
6. At the operating-system level, move the new tablespace's datafiles and the export dump file to the target location.
   
7. Once you have completed and verified step 6, drop the tablespace from the source database.
   
8. Import the metadata for the new tablespace into the target database.
   
9. Insert the data into the tables in the target database. You can do this via insert as select or, if the target table is partitioned, by using the exchange partition option to make the newly plugged-in table a partition within the target table.
   
10. Commit the inserted data, and verify that everything worked.
    
11. Back up the control files for the source and the target database.