TRANSPORT TABLESPACE

Syntax

transpt_tbs::=

Description of transpt_tbs.gif follows
Description of the illustration transpt_tbs.gif

transpt_tbs_optlist::=

Description of transpt_tbs_optlist.gif follows
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.

See Also:

"CONVERT" to learn how to convert a tablespace for transport to a target platform with a different endian format.

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

Syntax Element Description
tablespace_name Specifies the name of each tablespace to transport.

transpt_tbs_oplist

Syntax Element Description
AUXILIARY DESTINATION Optional argument that specifies the location for files for the auxiliary instance. The SET NEWNAME and CONFIGURE AUXNAME can be used to override this argument for individual files, and the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters can be used instead of AUXILIARY DESTINATION, if using your own initialization parameter file to customize the auxiliary instance. See Oracle Database Backup and Recovery Advanced User's Guide for details on the interactions among the different methods of naming the auxiliary instance files.
DATAPUMP DIRECTORY Optional argument that specifies a database directory object where Data Pump Export outputs are created. If not specified, files will be created in the location specified by TABLESPACE DESTINATION. See Oracle Database Utilities for more details on Data Pump Export and database directory objects.
DUMP FILE Optional argument that specifies where to create the export dump file. If not specified, the export dump file is named dmpfile.dmp and stored in the location specified by the DATAPUMP DIRECTORY clause or in the tablespace destination.
EXPORT LOG Optional argument that specifies the location of the log generated by Data Pump Export. If omitted, the export log is named explog.log and stored in the location specified by the DATAPUMP DIRECTORY clause or in the tablespace destination.
IMPORT SCRIPT Optional argument that specifies the filename for the sample input script generated by RMAN for use in plugging in the transported tablespace at the destination database. If omitted, the import script is named impscript.sql. The script is stored in the tablespace destination.
TABLESPACE DESTINATION Argument that specifies the directory where the datafiles for the transported tablespaces are left at the end of the tablespace transport operation.
untilClause Optional argument that specifies a past point in time. If specified, RMAN restores and recovers the tablespaces at the auxiliary instance to their contents at that past point in time before export.

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';