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 auxiliary instance.

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.

Happy Reading

Jaffar

Oracle 10g upgrade.

As part of upgrading all our production databases from 8i & 9i to 10g, last Friday, early morning, we had successfully upgraded our core banking business critical database from 9i to 10gR2.
Prior to this upgrade, we had done the upgrade with other business critical databases, but, this database is very specially, critical, thus our management wanted a special concern while upgrading this database and looked-for an immediate fallback option.
We had one week of time to plan everything and simulate the upgrade on development server to measure the exact downtime and fall back procedure and application testing. Our several attempted simulations took maximum 1 hr of time and we have requested for 1 hr downtime and fortunately we have got 1 hr down time for the upgrade.

Following methods have came to our mind:

Transportable Tablespaces, Rolling Upgrade.
Streams
Standby

Streams : Since we require the fallback option, we tested using streams where we need to install 10g software on a different machine, create database, export and import and configure Streams to replicate changes from 9i to 10g and vice versa. On a granted cut off time, divert all connection from 9i to 10g and still keep the stream replication between 9i and 10g as the fallback option.
We didn’t like this method, because it involved export and import and we don’t want to use export and import facility for such an important upgrade.

And we finally zeroed the Standby method idea. In this method, what we did was, first create a standby database on the same server where primary up and running, keep primary and standby in sync, disconnect all session from the primary database, stop primary database, switchover standby database to primary database. Once the standby became primary, fulfilling all perquisites, like creating SYSAUX tablespace and other stuff, we have started the manual upgrade. The ‘catupgrade’ took 12 minutes for 64 gb database size.
We were very much glad as the upgrade activity finished in 50 minutes and we still had 10 minutes to do all our pre-checks.
In case we couldn’t finish the upgrade in the given time, we simple stopped the standby which turn primary, and started the old primary database as fall back option.
After an upgrade, there were couple of issues which took less time to resolve.