- Its one of the feature in Oracle Database Enterprise Edition.
- Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases. Starting with Oracle Database 10g, we can transport tablespaces across platforms . Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data.
- Transportable tablespaces and transportable tables only transports data that resides in user-defined tablespaces. However, full transportable export/import transports data that resides in both user-defined and administrative tablespaces, such as SYSTEM and SYSAUX.
- Full transportable export/import transports metadata for objects contained within the user-defined tablespaces and both the metadata and data for user-defined objects contained within the administrative tablespaces. Specifically, with full transportable export/import, the export dump file includes only the metadata for objects contained within the user-defined tablespaces, but it includes both the metadata and the data for user-defined objects contained within the administrative tablespaces.
Limitations on Transportable Tablespace Use :
- The source and target database must use the same character set and national character set.
- You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
- Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
- If the owner/s of tablespace objects does not exist on target database, the usernames need to be created manually before starting the transportable tablespace import.
- You cannot transport the SYSTEM tablespace or objects owned by the user SYS.
- Advanced Queues Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
- Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.
If we are migrating a database, then make sure there are no invalid objects in the source database before making the export.If invalid exists in source database,remove all the invalid objects by running utlrp.sql.
Cross Platform Transport tablespace from Linux to Windows :
Source Database : Demo (Db name)
Steps for Transporting the tablespace :-
Checking what are all the tablespaces available then Create a new Tablespace which is only am going to do transportation.
I am creating a new table which named as SAMPLE and i am checking the count in that table which i create in the new tablespace as i created before.
- Make Tablespace TEST1 in READ ONLY mode.
- Check the tablespace will be self contained
These violations must be resolved before the tablespaces can be transported.
If there is any object dependencies or referenced objects,violation occurs during exporting tablespace.
If any dependencies are present,move the objects to the target tablespace which is going to transportable.
The transport_set_violations view output has to display empty rows.
RMAN convert Usage :-
- If you see that the endian formats are different for platform and then a conversion using RMAN is necessary for transporting the tablespace set:
- For example, run the below command to convert the tablespace of Source(Linux 64 bit) platform to Target(Microsoft Windows 64 bit) ,
Use CONVERT Tablespace FROM PLATFORM on Source Database
RMAN> convert tablespace TEST1 to platform="Microsoft Windows X86 64-bit" FORMAT '/home/oracle/%U';
- After copy the datafiles as well as the export dump file to the target environment.
Use CONVERT DATAFILE FROM PLATFORM on Target Database
RMAN> CONVERT DATAFILE '/u01/app/oracle/oradata/Demo/datafile/tbs.dbf', FROM PLATFORM="Linux x86 64-bit" TO PLATFORM="Windows TRu64 LINUX" DB_FILE_NAME_CONVERT= "/home/oracle/", "/u01/app/oracle/oradata/Demo/datafile" PARALLELISM=4;
If they are of the same endian format, then no conversion using RMAN is necessary and tablespaces can be transported as if they were on the same platform.
Check Platform Support and Endian format :
Here both of my platform are Little Endian format.
- Export TEST1 tablespace metadata.
Target Database : ORCL (Db name) –Windows Platform
- Copy datafile and dumpfile to target database.
Import transportable tablespace in TARGET Database :
- Before importing the metadata in target database,check SAMPLE table is already present in target database.
- Import the dumpfile and copy of datafile in target database.
- Make the tablespace TEST1 in READ WRITE mode.
- Check the table sample data count.
Thus the SAMPLE table got transported from Linux platform to Windows platform successfully.
Catch Me On:- Hariprasath Rajaram LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page :https://www.facebook.com/dbahariprasath/? Twitter :https://twitter.com/hariprasathdba