Transport Tablespace from RMAN backupsets

Transportable Tablespace from RMAN backupsets:

Transportable tablespace supports the fastest method of copying set of tablespaces from one Oracle database to another.

In general, tablespaces must be kept in read only mode before being transported. It will be difficult task to put bigger size tablespaces and 24x7 accessed high availability tablespaces in the read only mode for longer time. This could prove a potential problem with the high availability databases.

Starting with version 10g Release 2, Transportable tablespace can be created from RMAN backupsets. In contrast, when RMAN creates transportable tablespace set from backupsets, it doesn’t touch the live datafiles from the tablespace to be transported and this elements the need of putting the tablespace in read only mode. Hence, it improves the availability.

Depending on the backup retention policy, transportable tablespace cal also be enables until point in time, SCN or restore points.

Step-by-step procedure for creating Transportable Tablespace from RMAN backup

RMAN uses three following steps to create Transpor Tablespace:

1. RMAN constructs an auxiliary instance.

• Creates an initialization parameter file.
• Brings the instance in NOMOUNT state and then restores production database controlfile from RMAN backup to server auxiliary instance.
• Then, mounts the database.

2. RMAN restores auxiliary and transportable set of datafiles and Perform Point-in-Time Recovery.

• Restore system related tablespaces(system,sysaux,undo & temp)
• Restore transportable set of datafiles to the specified auxiliary destination.
• Performs SWITCH operations at auxiliary instance.
• Performs point-in-time recovery at the auxiliary instance.
• Archived logs will be applied on the demand and will be deleted upon apply.
• Upon completion of recovery, RMAN performs OPEN RESETLOGS on the auxiliaryinstance.

3. Auxiliary tablespace are put into read only mode, and Data Pump export mode invokes to construct the dump file with set of transportable tablespace.

Upon successfully completion of above operations, RMAN shutdown the auxiliary instance and deletes all the files generated during the above operations. It also generates the loading and unloading scripts.

Creating Transport Tablespace from RMAN backupsets

Start the RMAN and use the following syntax to create transportable tablespace from the RMAN backup.

RMAN> transport tablespace amicus
tablespace destination ‘d:\tts_rman’
auxiliary destination ‘d:\temp_dest’;

Description:

TRANSPORT TABLESPACE is the command used to create transportable tablespace from RMAN backup.

TABLESPACE DESTINATION: is the location where RMAN left the set transportable datafiles, and Data Pump loading, unloading scripts names, dumpfile.dmp, export.log and impscrpt.sql.

AUXILIARY DESTINATION : is the temporary location where RMAN restores system, sysaux, undo and temp tablespace from the source database, and also redo logs. These files will be deleted automatically upon successfully completion of transport tablespace operation.

It is possible to divert the Data Pump files (import script, dump file and logfile) generated by RMAN to the Data Pump directory using the following syntax:

RMAN> transport tablespace amicus
Tablespace destination ‘d:\tts_rman’
Auxiliary destination ‘d:\temp_dest’
Datapump directory dump_dir
Dump file ‘amicus.dmp’
Import script ‘amicus_tbs.sql’
Export log ‘amicus_tbs.log’;

RMAN Transport Tablespace also can be used with SCN, UNTIL and RESTORE POINTS options.

Examples:

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL SCN 11379;

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL RESTORE POINT 'before_change';

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL TIME 'SYSDATE-1';

Auxiliary Instance Parameter:

Following are the initialization parameters are used to construct the auxiliary instance.

Db_name
Compatible
Db_unique_name
Db_block_size
Db_files
Shared_pool_size – Set to 110M
Large_pool_size – set to 1M

Customizing Auxiliary File Location:

Its also possible to relocate the auxiliary instance files to a specific location for various reasons. Using SET NEWNAME FOR DATAFILE is used in the RUN block to achieve the above.

Common Errors triggers during RMAN transportable tablespace process:

Failing due to the insufficient value for shared_pool_size.
Filename conflict.

Limitations:

There are limitation applies specific to this feature.

There must be valid backup of all the datafiles required for this operations. In case there is no backup of the tablespace used for this operation, following error will be thrown on the RMAN prompt:

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed

RMAN-00571: ========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================

RMAN-03002: failure of tranport tablespace command at 05/22/2007 21:59:01
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "JAFFAR"

Automatic auxiliary instance be created on the same host as the source instance.
Dropped and renamed tablespace can’t be used later for RMAN Transport Tablespace.
Tablespace must be self contained.