Backup & Recovery

Transportable Tablespaces

• 
• 

User Rating:  / 0

PoorBest 

Details

Parent Category: Articles

Created on Thursday, 18 March 2010 20:34

Last Updated on Tuesday, 08 May 2012 13:22

Published on Thursday, 18 March 2010 20:34

Written by Guy Lambregts

Hits: 8305

Transportable Tablespaces

Today I will better my life and will use the transportable tablespace feature to copy tablespaces from our HP UX 64 bit Itanium to a Windows 32 bit box.

This assumes the compatible parameter must be at least 10.0 on both target and source database since the cross platform transportable tablespace requires 10G datafileheader format.
When we query v$transportable_platform we list the endian format per platform. Since in my example the endianess of source and target database are not identical I need to do an additional step.

1. If the endian format is identical for both source and target database then tablespaces can be transported without any conversion requirement.

2. If there is a difference between the endian format of source and target database then tablespaces can still be transported, but an additional conversion is required.


There are some restrictions with respect to data types - like XML - which I do not handle here.

Characterset

The target and source db characterset and national characterset should be identical

SQL> select * from database_properties where property_name like '%CHARACTERSET%';


 

Compatible

Target database

 

SQL> show parameter compatible


Source database

 

SQL> show parameter compatible


NAME                                 TYPE        VALUE

---

compatible                           string      10.2.0

 

Block size

From 9i onwards we can transport tablespaces accross databases whith different standard blocksize.

We grant to additional privs to system, hence we avoid we have to connect with sys

 

 

Assume we wanna transport the tablespace COCONUT_DATA from our HP Itanium 64 bit platform to our Windows 32 bit platform.
We first validate whether the tablespace COCONUT_DATA is self contained, and we notice it is not. There is a primary index built in another tablespace.

 

SQL> exec sys.dbms_tts.transport_set_check('COCONUT_DATA',true,true);
PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

VIOLATIONS

---


Index COCONUT.PK_T01 in tablespace COCONUT_INDX enforces primary constriants  of  table COCONUT.TABLE_T01 in tablespace COCONUT_DATA

 

We decide to transport the tablespace COCONUT_INDX as well, let us check whether these 2 tablespaces are self contained. They apparently are.


SQL> exec sys.dbms_tts.transport_set_check('COCONUT_DATA,COCONUT_INDX',true,true);


Ok, let 's go for it, we put the 2 tablespaces in read only mode

 

SQL> alter tablespace coconut_data read only;

 

We export with the datapump, notice we use the transport_tablespaces and the transport_full_check attribute

 

We use the RMAN' s convert command ...

 

oracle@myhost:/home/oracle $ export ORACLE_SID=MYDB


 

There always goes something wrong, I will temporary disable rman compression

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;


We run the convert command again, this time it runs succesfully

I enable rman compression again

Belief it or not but in the target database I already have a coconut_data and a  coconut_indx tablespace ( I use coconut language whenever I' m testing )
So I rename these tablespaces. The rename tablespace feature is new in 10G and

1. is easy
2. is sometimes great
3. is sometimes really wonderfull

SQL> alter tablespace coconut_data rename to coconut_data_03;


 

I move both the tts.dmp ( metadata ) and the converted datafiles towards the target platform. I decide to rename the converted datafiles to something readable
( C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_DATA, C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_INDX )

 

We still have to put our new tablespaces read write mode