Purpose

Use the TRANSPORT TABLESPACE command to create transportable tablespace sets from RMAN backups instead of the live data files of the source database.

Prerequisites

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

The SYSAUX tablespace must not be part of the recovery set, which is the set of tablespaces to be transported. RMAN enforces inclusion of the SYSAUX tablespace in the auxiliary set, which contains data files and other files required for the tablespace transport.

TRANSPORT TABLESPACE does not convert endian formats. If the target platform has a different endian format, then after running TRANSPORT TABLESPACE use the CONVERT command to convert the endian format of the transportable set data files.

If you drop a tablespace, then you cannot later use TRANSPORT TABLESPACE to include this 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 rename a tablespace, then you cannot use TRANSPORT TABLESPACE to create a transportable tablespace set as of a point in time before the tablespace was renamed.

Backups and Backup Metadata

You must have a backup of all needed tablespaces (including those in the auxiliary set) and archived redo log files needed to recover to the target point in time.

If you do not use a recovery catalog, and if the database has re-used control file records containing metadata about required backups, then the command fails because RMAN cannot locate the backups. You may be able to use CATALOG to add backups to the RMAN repository, but if the database is overwriting control file records, you may lose records of other backups.

Data Pump Export and Import

Because the RMAN uses the Data Pump Export and Import utilities, you cannot use TRANSPORT TABLESPACE if the tablespaces to be transported use XMLType. In this case you must use the procedure in Oracle Database Administrator's Guide.

If a file under the name of the export dump file exists in the tablespace destination, then TRANSPORT TABLESPACE fails when it calls Data Pump Export. If you are repeating a previous TRANSPORT TABLESPACE job, then make sure to delete the previous output files, including the export dump file.

Tablespace and Column Encryption

The following database encryption features both use the Oracle software keystore: Transparent Data Encryption (TDE) column encryption, which functions at the column level, and TDE tablespace encryption. Note the following restrictions for tablespaces that are encrypted or contain encrypted columns:

• If you are transporting an encrypted tablespace, then you must manually copy the keystore to the destination database.

• If the destination database has an existing keystore, then you cannot copy the keystore from the source database to the destination database. Thus, you cannot transport encrypted data to a database that already has a keystore. If you encrypt columns with TDE column encryption, then you can export them into an export file that is password-protected and import the data into the destination database.

Usage Notes

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 RMAN is not part of the backup strategy for your database, then you can still use TRANSPORT TABLESPACE if the needed data file copies and archived redo log files are available on disk. Use the CATALOG command to record the data file copies and archived redo log files in the RMAN repository. You can then use TRANSPORT TABLESPACE. You also have the option of using RMAN to back up your database specifically so you can use TRANSPORT TABLESPACE.

Syntax

transpt_tbs::=

(transpt_tbs_optlist::=)

transpt_tbs_optlist::=

(untilClause::=)

Semantics

transpt_tbs

transpt_tbs_optlist

This subclause specifies optional parameters that affect the tablespace transport.

Examples

TRANSPORT TABLESPACE example, tools
  TABLESPACE DESTINATION '/disk1/transportdest'
  AUXILIARY DESTINATION '/disk1/auxdest'
  UNTIL TIME 'SYSDATE-15/1440';

Creating automatic instance, with SID='egnr'
 
initialization parameters used for automatic instance:
db_name=PROD
compatible=11.0.0
db_block_size=8192
.
.
.
starting up automatic instance PROD
.
.
. 
executing Memory Script
 
executing command: SET until clause
 
Starting restore at 07-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=44 device type=DISK
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
.
.
.
output file name=/disk1/auxdest/cntrl_tspitr_PROD_egnr.f
Finished restore at 07-JUN-13
 
sql statement: alter database mount clone database
 
sql statement: alter system archive log current
 
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
 
starting full resync of recovery catalog
full resync complete
.
.
.
executing Memory Script
.
.
.
 
Starting restore at 07-JUN-13
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /disk1/auxdest/TSPITR_PROD_EGNR/datafile/o1_mf_system_%u_.dbf
datafile 1 switched to datafile copy
.
.
.
starting media recovery
.
.
. 
Finished recover at 07-JUN-13
 
database opened
.
.
.
executing Memory Script
.
.
.
sql statement: alter tablespace EXAMPLE read only
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /disk1/auxdest/cntrl_tspitr_PROD_egnr.f deleted
.
.
.

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