11 | 12 | 2017
Latest Articles
Popular Articles

Backup & Recovery

Transportable Tablespaces

User Rating:  / 0
PoorBest 

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


NLS_CHARACTERSET
AL32UTF8
Character set

NLS_NCHAR_CHARACTERSET
AL16UTF16
NCHAR Character set

 

Compatible


Target database

 

 
 

SQL> show parameter compatible


NAME                                 TYPE        VALUE

compatible                           string      10.2.0.3.0


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

 

SQL> grant execute on dbms_tts to system; Grant succeeded.

SQL> grant select on transport_set_violations to system;

Grant succeeded.

 

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

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected


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

 
 
 
 

 

SQL> alter tablespace coconut_data read only;

 
Tablespace altered.

SQL> alter tablespace coconut_indx read only;

Tablespace altered.

 

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

 
oracle@myhost:/home/oracle $ export ORACLE_SID=MYDB
oracle@myhost:/home/oracle $ expdp system dumpfile=tts.dmp directory=data_pump_dir transport_tablespaces=coconut_data,coconut_indx transport_full_check=Y

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 27 March, 2008 13:56:25

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=tts.dmp directory=data_pump_dir transport_tablespaces=coconut_data,coconut_indx transport_full_check=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /mnt/app/oracle/OraHome_2/rdbms/log/tts.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:57:54

 


We use the RMAN' s convert command ...

 

 

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

oracle@myhost:/home/oracle $ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Mar 27 14:01:52 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MY_DB (DBID=4216501149)

RMAN> convert tablespace coconut_data,coconut_indx to platform 'Microsoft Windows IA (32-bit)'  format '/mnt/oradata/MY_DB/%U';

Starting backup at 27-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00029 name=/mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_coconut__3yq39twt_.dbf
RMAN-00571: ===================================================
RMAN-00569:
========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571:
===================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/27/2008 14:21:03
ORA-19699: cannot make copies with compression enabled

 


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

 

 

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

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1


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


RMAN> convert tablespace coconut_data,coconut_indx to platform 'Microsoft Windows IA (32-bit)'  format '/mnt/oradata/MY_DB/%U';

Starting backup at 27-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00029 name=/mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_coconut__3yq39twt_.dbf
converted datafile=/mnt/oradata/MY_DB/data_D-MY_DB_I-4216501149_TS-COCONUT_DATA_FNO-29_fhjcapqv
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00030 name=/mnt/oradata/MY_DB/MY_DB/datafile/o1_mf_coconut__3yq3c55k_.dbf
converted datafile=/mnt/oradata/MY_DB/data_D-MY_DB_I-4216501149_TS-COCONUT_INDX_FNO-30_fijcapt0
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:05
Finished backup at 27-MAR-08


I enable rman compression again

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


old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1


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;

Tablespace altered.

SQL> alter tablespace coconut_indx rename to coconut_indx_03;

Tablespace altered.

 


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 )

C:\oracle\Oradata\LOCALDB\DATAFILE>impdp system/secret_password dumpfile=tts.dmp directory=data_pump_dir transport_datafiles='C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_DATA','C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_INDX'

Import: Release 10.2.0.3.0 - Production on Jeudi, 27 Mars, 2008 15:17:09

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=tts.d
mp directory=data_pump_dir transport_datafiles='C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_DATA','C:\oracle\Oradata\LOCALDB\DATAFILE\COCONUT_INDX'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:17:32

 


We still have to put our new tablespaces read write mode

 

 
SQL> alter tablespace coconut_data read write;

Tablespace altered.

SQL> alter tablespace coconut_indx read write;

Tablespace altered.