Rman Transportable Tablespace and Plugin into RAC and ASM (Alejandro Vargas' Blog)

This post describes the Rman Transportable Tablespace Command, how to use it to plug a set of tablespaces from a single instance into a RAC database based on ASM.

The purpose of the Rman 'Transportable Tablespace' command is to generate a Transportable Tablespace Set (TTS), in addition it generates a Metadata export and an Import Metadata Script. They are to be used lately to plug in the TTS on another database.

The Rman 'Transportable Tablespace' command has the advantage that tablespaces to be transported do not need to be on read only mode on the siurce database; instead the datafiles are restored from a backupset into an auxiliary instance to create the TTS.

The exercise described on this post includes:

Create the Transportable Tablespace Set with Rman
Plug it into a RAC database and
Moving these tablespaces datafiles into ASM

The steps required to complete the task are:

1. Create an Rman Backup of the source database
2. Prepare an auxiliary directory for storing the Rman generated export and import files and a data directory for the resulting TTS
3. Prepare an Rman script to generate the TTS
4. Execute the Rman Script
5. Plug the TTS on the target database
6. Convert the transported tablespaces to ASM
7. Offline the transported tablespaces
8. Rename the datafiles to it's new ASM destination
9. Online the transported tablespaces

On this example I'm using a small database named 'whiteowl' that has three test tablespaces that I will transport to a RAC database within ASM.

SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'TEST%';

TABLESPACE_NAME
------------------------------
TEST2
TEST3
TEST4

SQL> select tablespace_name||' '||
2 segment_name
3 from dba_segments
4 where tablespace_name like 'TEST%';

TABLESPACE_NAME||''||SEGMENT_NAME
--------------------------------------------------------------------------------
TEST2 TEST1
TEST3 TEST2
TEST4 TEST3

Execution Steps:

1. Create an Rman Backup of the source database

This is the backup script: Rman Single Instance Backup Script

2. Prepare an
auxiliary directory for storing the Rman generated export and import
files and a data directory for the resulting TTS

mkdir /asmtest/whiteowl/auxiliar/

This directory will be used to create on it the TTS, the export data pump and the import data pump scripts.

3. Prepare an Rman script to generate the TTS

Look at the time of your last backup to setup the until time clause.

run {
TRANSPORT TABLESPACE 'TEST2','TEST3','TEST4'
AUXILIARY DESTINATION '/asmtest/whiteowl/auxiliar'
DUMP FILE 'whiteowl_exp.dmp'
EXPORT LOG 'whiteowl_tts.log'
IMPORT SCRIPT 'whiteowl_imp.sql'
TABLESPACE DESTINATION '/asmtest/whiteowl/auxiliar'
UNTIL TIME="to_date('29-03-2007:16:57:00', 'dd-mm-yyyy:hh24:mi:ss')";
}

4. Execute the Script

{oracle} /oradisk/app01/oracle/scripts/av/rman [ractest1] > rman target / @rman_tts

While the script run it creates an auxiliar instance, to restore and recover the TTS:

On the auxiliar destination we got a directory containing the TTS and the import script:

{oracle} /asmtest/whiteowl/auxiliar [ractest1] > ls -ltr
total 12
drwxr-x---    4 oracle   dba          4096 Apr 1 15:08 TSPITR_WHITEOWL_FQWD
-rw-r--r--    1 oracle   dba          1428 Apr 1 15:12 whiteowl_tts.log
-rw-r--r--    1 oracle   dba          2473 Apr 1 15:12 whiteowl_imp.sql

{oracle} /asmtest/whiteowl/auxiliar [ractest1] > ls -l TSPITR_WHITEOWL_FQWD/datafile/
total 307536
-rw-r-----    1 oracle   dba      104865792 Apr 1 15:11 o1_mf_test2_30z87qw6_.dbf
-rw-r-----    1 oracle   dba      104865792 Apr 1 15:11 o1_mf_test3_30z86w4w_.dbf
-rw-r-----    1 oracle   dba      104865792 Apr 1 15:11 o1_mf_test4_30z87r52_.dbf

5. Plug the TTS on the target database

The final steps are to plug the tablespaces into the target database and move them to ASM, to plug the tablespaces in this case I did use the generated import data pump script:

{oracle} /vmasmtest/whiteowl/auxiliar [ractest1] > ./expscr
setenv ORACLE_HOME /oradisk/app01/oracle/product/10gDB
setenv PATH /oradisk/app01/oracle/product/10gDB/bin:/oradisk/app01/oracle/scripts/general:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/oradisk/app01/oracle/scripts:/usr/local/maint/oracle:/crmdb/app01/oracle/product/db_scripts/RAC:/crmdb/app01/oracle/product/db_scripts
setenv ORACLE_SID racdbtst1
impdp avargas/avargas directory=imp_dp dumpfile= whiteowl_exp.dmp transport_datafiles= /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf, /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf, /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf

Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:08:47
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Master table "AVARGAS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "AVARGAS"."SYS_IMPORT_TRANSPORTABLE_01": avargas/******** directory=imp_dp dumpfile= whiteowl_exp.dmp transport_datafiles= /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf, /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf, /vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "AVARGAS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:09:14

6. Convert the pluged tablespaces to ASM

{oracle} /oradisk/app01/oracle/scripts/av/rman [ractest1] > rman target / nocatalog
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 2 18:56:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RACDBTST (DBID=519338572)
using target database control file instead of recovery catalog

RMAN> convert datafile '/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf'
2> format '+DATADG/racdbtst/datafile/o1_mf_test4_3119t5p7_.dbf';
Starting backup at 02/04/2007 18:57:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 instance=racdbtst1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=130 instance=racdbtst1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf
converted datafile=+DATADG/racdbtst/datafile/o1_mf_test4_3119t5p7_.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
Finished backup at 02/04/2007 18:58:16

RMAN> convert datafile '/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf'
2> format '+DATADG/racdbtst/datafile/o1_mf_test3_3119sn54_.dbf';
Starting backup at 02/04/2007 19:01:46
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile conversion
input filename=/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf
converted datafile=+DATADG/racdbtst/datafile/o1_mf_test3_3119sn54_.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
Finished backup at 02/04/2007 19:02:02

RMAN> convert datafile '/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf'
2> format '+DATADG/racdbtst/datafile/o1_mf_test2_3119t5fs_.dbf';
Starting backup at 02/04/2007 19:03:50
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile conversion
input filename=/vmasmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf
converted datafile=+DATADG/racdbtst/datafile/o1_mf_test2_3119t5fs_.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 02/04/2007 19:03:58
RMAN>

After the convert command we can see the datafiles inside ASM:

{oracle} /home/oracle [ractest1] > 10asm
{oracle} /home/oracle [ractest1] > sts +ASM1
{oracle} /home/oracle [ractest1] > asmcmd
ASMCMD> cd datadg/racdbtst/datafile
ASMCMD> ls
ON_ASM.274.617123537
ON_ASM.280.617292277
ON_ASM.283.617125585
RECOP1.273.617292261
RECOP1.276.617125571
RECOP1.279.617123521
SYSAUX.261.606653697
SYSTEM.259.606653665
TBS_AUTO.275.617292277
TBS_AUTO.287.617123539
TBS_AUTO.288.617125577
TBS_MANUAL.272.617125575
TBS_MANUAL.284.617123519
TBS_MANUAL.286.617292261
TEST2.282.618865431
TEST3.264.618865309
TEST4.289.618865083
UNDOTBS1.260.606653693
UNDOTBS2.263.606653713
USERS.278.617292245
USERS.281.617125569
USERS.285.617123513
o1_mf_test2_3119t5fs_.dbf
o1_mf_test3_3119sn54_.dbf
o1_mf_test4_3119t5p7_.dbf
ASMCMD>

7. Offline the plugged tablespaces

Once the datafiles are converted to ASM using Rman we need to rename them to it's new location, the first step for doing this is to made the tablespaces offline:

SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------
+DATADG/racdbtst/datafile/system.259.606653665
+DATADG/racdbtst/datafile/undotbs1.260.606653693
+DATADG/racdbtst/datafile/sysaux.261.606653697
+DATADG/racdbtst/datafile/undotbs2.263.606653713
+DATADG/racdbtst/datafile/users.278.617292245
+DATADG/racdbtst/datafile/recop1.273.617292261
+DATADG/racdbtst/datafile/tbs_manual.286.617292261
+DATADG/racdbtst/datafile/tbs_auto.275.617292277
+DATADG/racdbtst/datafile/on_asm.280.617292277
/asmtest/oradata/on_fs01.dbf
/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf
/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf
/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf
13 rows selected.

SQL> alter tablespace test2 offline;
Tablespace altered.

SQL> c/2/3/
1* alter tablespace test3 offline
SQL> /
Tablespace altered.

SQL> c/3/4/
1* alter tablespace test4 offline
SQL> /
Tablespace altered.

8. Rename the datafiles to it's new ASM destination

SQL> alter database rename file '/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test4_3119t5p7_.dbf'
2 to '+DATADG/racdbtst/datafile/o1_mf_test4_3119t5p7_.dbf';
Database altered.

SQL> alter database rename file '/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test3_3119sn54_.dbf'
2 to '+DATADG/racdbtst/datafile/o1_mf_test3_3119sn54_.dbf';
Database altered.

SQL> alter database rename datafile '/asmtest/whiteowl/auxiliar/TSPITR_WHITEOWL_FNOU/datafile/o1_mf_test2_3119t5fs_.dbf'
2 to '+DATADG/racdbtst/datafile/o1_mf_test2_3119t5fs_.dbf';
Database altered.

9. Online the plugged tablespaces

SQL> alter tablespace test2 online;
Tablespace altered.
SQL> alter tablespace test3 online;

Tablespace altered.
SQL> alter tablespace test4 online;

Tablespace altered.

Now the tablespaces are available on the RAC Database.

Comments (4)

Mike:

Can something like this be done when moving between platforms? We are in a situation where we need to move from Oracle 10g on Windows/AMD to Oracle 10g on Sparc.

Posted by Mike | April 12, 2007 10:32 AM

Posted on April 12, 2007 10:32

Satish:

Do we have a solution to import a transportable tablespace into Oracle ASM storage from an NTFS based exported transportable tablespace? We have a situation where we need to restore old tablespaces which were exported using transportable tablespace process on NTFS system on th enew Oracle RAC ASM based storage.

Posted by Satish | August 28, 2007 12:20 PM

Posted on August 28, 2007 12:20

Vladimir Przyjalkowski:

Nice and pretty ! This text only helped me to manage things after vain starring at Oracle documentation and metalink. Thank you much.

Posted by Vladimir Przyjalkowski | September 13, 2007 11:58 AM

Posted on September 13, 2007 11:58

Deepak:

Hi,

Thanks for this article, this was the only information i could get in the whole of (www) for migrating Oracle9i database to Oracle10g RAC -ASM.

Posted by Deepak | November 5, 2008 12:46 PM

Posted on November 5, 2008 12:46