TRANSPORT TABLESPACE

Syntax

transpt_tbs::=

Description of the illustration transpt_tbs.gif

transpt_tbs_optlist::=

Description of the illustration transpt_tbs_optlist.gif

Purpose

To create transportable tablespace sets from RMAN backups, instead of the live datafiles of the source database. Also, to create transportable tablespace sets that are recovered to a point in time in the past instead of the present time.

Restrictions and Usage Notes

The limitations on creating transportable tablespace sets described in Oracle Database Administrator's Guide apply to transporting tablespaces from backup, with the exception of the requirement to make the tablespaces read-only.

TRANSPORT TABLESPACE does not perform endian format conversion. If the target platform has a different endian format, then you must use the RMAN CONVERT command to perform the separate step of converting the endian format of the datafiles in the transportable set.

There are also limitations specific to creating a transportable tablespace set using RMAN:

• You must have a backup of all needed tablespaces (including those in the auxiliary set) and archived redo log files available for use by RMAN that can be recovered to the target point in time for the TRANSPORT TABLESPACE operation.

  
  

  Note: If RMAN is not part of the backup strategy for your database, you can still use RMAN TRANSPORT TABLESPACE, as long as the needed datafile copies and archived redo logs are available on disk. Use the RMAN CATALOG command to record the datafile copies and archived logs in the RMAN repository. You can then use TRANSPORT TABLESPACE. See Oracle Database Backup and Recovery Basics for details on using CATALOG. You also have the option of using RMAN to back up your database specifically to create backups for use in creating a transportable tablespace set from backup.

  
  

• Because the RMAN process for creating transportable tablespaces from backup uses the Data Pump Export and Import utilities, you cannot use this process if the tablespaces to be transported use XMLTypes. In such a case, you must use the process documented in Oracle Database Administrator's Guide.

• Because RMAN creates the automatic auxiliary instance used for restore and recovery on the same node as the source instance, there is some performance overhead during the operation of the TRANSPORT TABLESPACE command.

• If you drop a tablespace, then you cannot later use TRANSPORT TABLESPACE to include that tablespace in a transportable tablespace set, even if the SCN for TRANSPORT TABLESPACE is earlier than the SCN at which the table was dropped.

• If you renname a tablespace, you cannot use TRANSPORT TABLESPACE to create a transportable tablespace set as of a point in time before the tablespace was renamed. (RMAN has no information about the previous name of the tablespace.)

• You cannot TRANSPORT tables without their associated constraints, or constraints without their associated tables.

• Neither the transportable set nor the auxiliary set datafiles can contain any of the following:

  • Replicated master tables

  • Partial tables

  • Tables with VARRAY columns, nested tables, or external files

  • Snapshot logs and snapshot tables

  • Tablespaces containing undo or rollback segments

  • Tablespaces that contain objects owned by SYS, including rollback segments

If you are performing TRANSPORT TABLESPACE without a recovery catalog, the following additional restrictions apply:

• If not using a recovery catalog and transporting tablespaces as of a point in time in the past, then the set of tablespaces with undo segments at the time TRANSPORT TABLESPACE is executed must be the same as the set of tablespaces with undo segments at the time selected for transport.

  Tablespaces including undo segments as of the target SCN for TRASNPORT TABLESPACE must be part of the auxiliary set. The RMAN repository in the control file only contains a record of tablespaces that include undo segments at the current time. If the set of tablespaces with undo segments was different at the target SCN, then TRANSPORT TABLESPACE fails.

• If the database has re-used the control file records for the RMAN repository that contained information about backups required for the TRANSPORT TABLESPACE process, then the process fails because RMAN cannot locate the required backups. You may be able to use CATALOG to add the needed backups to the RMAN repository if they are still available, but if the database is already overwriting control file records you may lose records of other needed backups.

Keywords and Parameters

transpt_tbs_oplist

Examples

Creating Transporable Tablespaces from Backup with TRANSPORT TABLESPACE: Example In this example, the tablespaces for the transportable set are tbs_2 and tbs_3, the transportable set files are to be stored at /disk1/transport_dest, and the transportable tablespaces are to be recovered to the present time:

transport tablespace tbs_2, tbs_3
   tablespace destination '/disk1/transportdest'
   auxiliary destination '/disk1/auxdest'
   ;
 

Using TRANSPORT TABLESPACE with a Past Point in Time: Example In this example, the tablespaces for the transportable set are tbs_2 and tbs_3, the transportable set files are to be stored at /disk1/transport_dest, and the transportable tablespaces are to be recovered to a specific SCN:

transport tablespace tbs_2, tbs_3
   tablespace destination '/disk1/transportdest'
   auxiliary destination '/disk1/auxdest'
   UNTIL SCN 251982;
   ;
 

Using TRANSPORT TABLESPACE with Custom File Locations: Example This example illustrates the use of the optional arguments of TRANSPORT TABLESPACE that control the locations of the Data Pump-related files such as the dump file, as well as the DATAPUMP DIRECTORY which in this case references a directory object mypumpdir:

TRANSPORT TABLESPACE tbs_2
   TABLESPACE DESTINATION '/disk1/transportdest'
   AUXILIARY DESTINATION '/disk1/auxdest'
   DATAPUMP DIRECTORY mypumpdir
   DUMP FILE 'mydumpfile.dmp'
   IMPORT SCRIPT 'myimportscript.sql'
   EXPORT LOG 'myexportlog.log';