A transportable tablespace set contains data files for a set of tablespaces and an export file containing structural metadata for the set of tablespaces. The export file is generated by Data Pump Export.

One use of transportable tablespace sets is to create a tablespace repository. For example, if you have a database with some tablespaces used for quarterly reporting, you can create transportable sets for these tablespaces for storage in a tablespace repository. Subsequently, versions of the tablespace can be requested from the repository and attached to another database for use in generating reports.

A key benefit of the RMAN TRANSPORT TABLESPACE command is that it does not need access to the live data files from the tablespaces to be transported. In contrast, the transportable tablespace technique described in Oracle Database Administrator’s Guide requires that the tablespaces to be transported are open read-only during the transport. Thus, transporting from backups improves database availability, especially for large tablespaces, because the tablespaces to be transported can remain open for writes during the operation. Also, placing a tablespace in read-only mode can take a long time, depending on current database activity.

The RMAN TRANSPORT TABLESPACE command also enables you to specify a target point in time, SCN, or restore point during your recovery window and transport tablespace data as it existed at that time. For example, if your backup retention policy guarantees a 1 week recovery window, and if you want to create transportable tablespaces based on the contents of the database on the last day of the month, then RMAN can perform this task at any time during the first week of the next month.

You create a transportable tablespace set by connecting RMAN to a source database as TARGET and then executing the TRANSPORT TABLESPACE command. The source database contains the tablespaces to be transported.

You must have a backup of all needed tablespaces 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. Figure 27-1 illustrates the basic process of transportable tablespace creation.

Figure 27-1 RMAN Transportable Tablespace from Backup: Architecture

Description of "Figure 27-1 RMAN Transportable Tablespace from Backup: Architecture"

The process shown in Figure 27-1 occurs in the following phases:

1. RMAN starts an auxiliary instance.

   An auxiliary instance is created by RMAN on the same host as the source database to perform the restore and recovery of the tablespaces. RMAN automatically creates an initialization parameter file for the auxiliary instance and starts it NOMOUNT.

2. RMAN restores a backup of the source database control file to serve as the auxiliary instance control file and mounts this control file.

3. RMAN restores auxiliary set and transportable set data files from the backups of the source database.

   The auxiliary set includes data files and other files required for the tablespace transport but which are not themselves part of the transportable tablespace set. The auxiliary set typically includes the SYSTEM and SYSAUX tablespaces, temp files, and data files containing rollback or undo segments. The auxiliary instance has other files associated with it, such as its own control file, parameter file, and online logs, but they are not part of the auxiliary set.

   RMAN stores the auxiliary data files in the selected auxiliary destination. The auxiliary destination is a disk location where RMAN can store auxiliary set files such as the parameter file, data files (other than those in the transportable set), control files, and online logs of the auxiliary instance during the transport. If the transport succeeds, then RMAN deletes these files.

   RMAN stores the transportable set files in the tablespace destination. The tablespace destination is a disk location that by default contains the data file copies and other output files when the tablespace transport command completes.

4. RMAN performs database point-in-time recovery (DBPITR) at the auxiliary instance.

   The recovery updates auxiliary and transportable set data files to their contents as of the target time specified for the TRANSPORT TABLESPACE command. If no target time is specified, then RMAN recovers with all available redo. RMAN restores archived redo logs from backup as necessary at the auxiliary destination (or other location) and deletes them after they are applied.

5. RMAN opens the auxiliary database with the RESETLOGS option.

   The data files now reflect the tablespace contents as of the target SCN for the tablespace transport operation.

6. RMAN places the transportable set tablespaces of the auxiliary instance into read-only mode. RMAN also invokes Data Pump Export in transportable tablespace mode to create the export dump file for the transportable set.

   By default, the dump file is located in the tablespace destination. You can specify an alternate location for the dump file.

   RMAN also generates the sample Data Pump import script for use when plugging in the transported tablespaces at a target database. The contents of this script are written to a file named impscript.sql in the tablespace destination. The commands for the script are also included in the RMAN command output.

7. If the preceding steps are successful, then RMAN shuts down the auxiliary instance and deletes all files created during the TRANSPORT TABLESPACE operation except for the transportable set files, the Data Pump Export file, and the sample import script.

To create transportable tablespace sets, you set the required parameters in the auxiliary instance initialization parameter file and then use the TRANSPORT TABLESPACE command.

1. Start the RMAN client and connect to the source database and, if used, the recovery catalog.
2. Ensure that the prerequisites for the TRANSPORT TABLESPACE command are met.
3. If necessary, set additional parameters in the auxiliary instance parameter file.

   This task is described in "Customizing Initialization Parameters for the Auxiliary Instance".

4. Execute the TRANSPORT TABLESPACE command.

   This basic technique is described in "Creating a Transportable Tablespace Set". Variations on this technique are described in "Transportable Tablespace Set Scenarios".

5. If the TRANSPORT TABLESPACE command fails, troubleshoot the problem and then retry the command until it succeeds.

   This technique is described in "Troubleshooting the Creation of Transportable Tablespace Sets".

6. Return to the procedure for transporting tablespaces described in Oracle Database Administrator’s Guide.

RMAN defines certain basic initialization parameters for the automatic auxiliary instance that is used with transportable tablespaces.

You might use an auxiliary instance parameter file to include additional parameters for the following reasons:

• To increase STREAMS_POOL_SIZE and SHARED_POOL_SIZE if needed for Data Pump Export.

• To manage locations for auxiliary instance data files. For example, you do not want all auxiliary instance data files stored in the same location on disk, but you do not want to specify the location of every file individually.

• To specify names for online redo logs with LOG_FILE_NAME_CONVERT .

The auxiliary instance parameter file is not intended to be a complete initialization parameter file for the auxiliary instance. Any parameters specified are added to or override the default parameters for the auxiliary instance. It is not necessary to specify parameters in the initialization file that you do not intend to override.

The following table describes the initialization parameters for the auxiliary instance.

Overriding a basic initialization parameter with an inappropriate value in the auxiliary instance parameter file can cause TRANSPORT TABLESPACE to fail. If you encounter a problem, then try returning the initialization parameter to its default value.

By default, RMAN looks for the auxiliary initialization parameter file at an operating system-dependent location on the host running the RMAN client. This location may not be on the host running the auxiliary instance. For UNIX systems, this location is ?/rdbms/admin/params_auxint.ora, where the question mark (?) stands for ORACLE_HOME on the host running RMAN. If no file is found in the default location, then RMAN does not generate an error.

SHARED_POOL_SIZE=150M;

RUN
{
  SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/auxinstparams.ora';
  TRANSPORT TABLESPACE tbs_2 
    TABLESPACE DESTINATION '/disk1/transportdest' 
    AUXILIARY DESTINATION '/disk1/auxdest';
}

You can specify a target time or SCN with the TRANSPORT TABLESPACE command. During the tablespace transport operation, RMAN restores the tablespace at the auxiliary instance with backups from before the target time and performs point-in-time recovery on the auxiliary database to the specified target time. Backups and archived redo logs needed for this point-in-time recovery must be available.

TRANSPORT TABLESPACE tbs_2 
   TABLESPACE DESTINATION '/disk1/transportdest' 
   AUXILIARY DESTINATION '/disk1/auxdest'
   UNTIL SCN 11379;

TRANSPORT TABLESPACE tbs_2 
   TABLESPACE DESTINATION '/disk1/transportdest' 
   AUXILIARY DESTINATION '/disk1/auxdest'
   TO RESTORE POINT 'before_upgrade';

TRANSPORT TABLESPACE tbs_2 
   TABLESPACE DESTINATION '/disk1/transportdest' 
   AUXILIARY DESTINATION '/disk1/auxdest'
   UNTIL TIME 'SYSDATE-1';

You can change the names of the Data Pump export dump file for the transportable set, the sample import script for use at the target database, the log file generated by Data Pump Export, and the directory to which they are written.

CREATE OR REPLACE DIRECTORY mypumpdir as '/datapumpdest';

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

Several rules are applicable to the location of auxiliary instance files created during the transport.