Creating a Transportable Tablespace Set with RMAN: Procedure

Using RMAN TRANSPORT TABLESPACE: Basic Scenario

This section describes the use of TRANSPORT TABLESPACE in the most automated case.

In this scenario, the AUXILIARY DESTINATION clause is specified, which causes RMAN to use default values that work for most cases in managing the auxiliary instance. Only required options are specified. Oracle recommends that you use an auxiliary destination with TRANSPORT TABLESPACE to simplify management of auxiliary instance files.

To use RMAN TRANSPORT TABLESPACE:

Start the RMAN client, connecting to the source database and, if used, the recovery catalog. Then enter the TRANSPORT TABLESPACE command, specifying the required arguments. For example, to transport the tablespaces tbs_2 and tbs_3, use the TRANSPORT TABLESPACE command as follows:

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

When the TRANSPORT TABLESPACE command completes, the following outputs result:

• The transportable set datafiles are left in the location /disk1/transportdest with their original names.

• The Data Pump Export dump file for the transportable set is named dmpfile.dmp, the export log is named explog.log and the sample import script is named impscrpt.sql. All are created in the tablespace destination /disk1/transportdest.

  
  

  Note: If there is already a file under the name of the export dump file in the tablespace destination, then TRANSPORT TABLESPACE fails when it calls Data Pump Export. If repeating a previous TRANSPORT TABLESPACE operation, make sure you delete the previous output files, including the export dump file.

  
  

• The auxiliary set files are removed from /disk1/auxdest.

You can now return to the process for transporting tablespaces described in Oracle Database Administrator's Guide.

RMAN TRANSPORT TABLESPACE with UNTIL Time or SCN

If you specify a target time with the TRANSPORT TABLESPACE command, then during the tablespace transport operation, RMAN will restore the tablespace at the auxiliary instance using backups from prior to the target time and perform point-in-time recovery on the auxiliary database to the specified target time. (Backups and redo logs needed for this point-in-time recovery must be available.)

The target time can be specified using an SCN (in the current incarnation or its ancestors), log sequence number, restore point or time expression. For example:

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'
   UNTIL RESTORE POINT 'before_upgrade';

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

RMAN TRANSPORT TABLESPACE: Specifying Locations for Data Pump Files

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, and the log file generated by Data Pump Export, and the directory to which they are written.

By default, these files are stored in the tablespace destination and named as follows:

• The Data Pump Export dump file is named dmpfile.dmp

• The export log file is named explog.log

• The sample import script is called impscrpt.sql

You can place the dump file and the export log in a different directory by using the DATAPUMP DIRECTORY clause, passing in the name of a database directory object.

These files can be renamed using the DUMP FILE, EXPORT LOG and IMPORT SCRIPT clauses of TRANSPORT TABLESPACE.

The following example illustrates the use of TRANSPORT TABLESPACE with the DATAPUMP DIRECTORY, DUMP FILE, EXPORT LOG and IMPORT SCRIPT filenames specified.

Assume that a database directory object has been created as follows, for use with Data Pump Export:

SQL>  CREATE OR REPLACE DIRECTORY mypumpdir as '/datapumpdest';

The following TRANSPORT TABLESPACE command illustrates the use of the optional arguments that control file output locations:

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

After a successful run, the auxiliary destination is cleaned up, the Data Pump Export Dump file and the export log are located in the directory referenced by DATAPUMP DIRECTORY (/datapumpdest/mydumpfile.dmp and /datapumpdest/myexportlog.log) and the transportable set datafiles are stored in /transportdest.

RMAN TRANSPORT TABLESPACE with Customized Initialization Parameters

When RMAN creates the auxiliary instance, it creates an initialization parameter file with default parameters that should work for nearly all TRANSPORT TABLESPACE cases, especially if you provide an AUXILIARY DESTINATION argument to TRANSPORT TABLESPACE.

RMAN TRANSPORT TABLESPACE can also use an auxiliary instance parameter file, which can contain values for additional initialization parameters, and values that override the values of those parameters defined in the default initialization parameter file.

Reasons for using an auxiliary instance parameter file include:

• To manage locations for auxiliary instance datafiles (if, for instance, you do not want all auxiliary instance datafiles stored in the same location on disk, but you do not want to specify the location of every file individually).

• To specify control naming for online redo logs using LOG_FILE_NAME_CONVERT

• To increase SHARED_POOL_SIZE if needed for Data Pump Export.

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

Customize Shared Pool Size in RMAN TRANSPORT TABLESPACE

If the default shared pool size of 100MB is not sufficient for Data Pump Export to run successfully, then specify a larger value. Use an auxiliary instance parameter file as described in "RMAN TRANSPORT TABLESPACE with Customized Initialization Parameters". Include the SHARED_POOL_SIZE initialization parameter in your auxiliary instance parameter file, listing the control file locations desired.

For more on the use of the SHARED_POOL_SIZE initialization parameter, see Oracle Database Reference.

Customize Auxiliary Control File Location in TRANSPORT TABLESPACE

By default, one control file is created for the auxiliary instance, in an operating system dependent location. (In Unix, the default location is ?/dbs/cntrl_@.dbf, where '?' stands for ORACLE_HOME and '@' stands for ORACLE_SID. For an automatic auxiliary instance, ORACLE_SID is randomly generated by RMAN).

To change the location of the auxiliary instance control file, use an auxiliary instance parameter file as described in "RMAN TRANSPORT TABLESPACE with Customized Initialization Parameters". Include the CONTROL_FILES initialization parameter in your auxiliary instance parameter file, listing the control file locations desired.

For more on the use of the CONTROL_FILES initialization parameter, see Oracle Database Reference.

Customize Other Auxiliary File Locations in TRANSPORT TABLESPACE

Several different rules affect the location of auxiliary instance files such as datafiles and online logs, created during TRANSPORT TABLESPACE. While the simplest method is to use AUXILIARY DESTINATION and let RMAN manage all file locations automatically, you may need to relocate some or all auxiliary instance files. The options for specifying locations for these files are described here in order of precedence, so that, if several are used, then for each file, the first rule in the list that applies determines the filename.

• Transport Tablespace with SET NEWNAME for Auxiliary Datafiles

• Transport Tablespace with CONFIGURE AUXNAME for Auxiliary Datafiles

• Transport Tablespace with AUXILIARY DESTINATION Parameter

• Transport Tablespace Naming Auxiliary Files with Initialization Parameters

RUN {
   SET NEWNAME FOR DATAFILE '/oracle/dbs/tbs_12.f' 
      TO '/bigdrive/auxdest/tbs_12.f';
   SET NEWNAME FOR DATAFILE '/oracle/dbs/tbs_11.f'
      TO '/bigdrive/auxdest/tbs_11.f';
   TRANSPORT TABLESPACE tbs_2 
         TABLESPACE DESTINATION '/disk1/transportdest' 
         AUXILIARY DESTINATION '/disk1/auxdest';
   }

CONFIGURE AUXNAME FOR '/oracle/dbs/tbs_12.f' TO '/disk1/auxdest/tbs_12.f';
 

TRANSPORT TABLESPACE tbs_1 AUXILIARY DESTINATION '/myauxdest';