Transportable tablespace :-
- Transportable tablespace is the feature of 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 Windows to Linux :
Check Platform Support and Endian format :
Source Database :
Target Database :
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(Microsoft Windows 64 bit) to Target(Linux 64 bit) platform,
Use CONVERT Tablespace FROM PLATFORM on Source Database
RMAN> convert tablespace TESTTBS1 to platform="Linux IA (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/TEST18C/datafile/testtbs.dbf', TO PLATFORM="Linux x86 64-bit" FROM PLATFORM="Windows TRu64 LINUX" DB_FILE_NAME_CONVERT= "/home/oracle/", "/u01/app/oracle/oradata/TEST18C/datafile" PARALLELISM=5;
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.
Steps for Transporting the tablespace :-
- Create Tablespace and User
- Create sample table
- Check the tablespace will be self contained
Note: 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.
- Make Tablespace TESTTBS in READ ONLY mode.
- Export TESTTBS tablespace metadata.
- Copy datafile and dumpfile to target database.
Import transportable tablespace in TARGET Database :
- Before importing the metadata in target database,check user dbwrora is present in target database or create and grant access to the user.
- Import the dumpfile and copy of datafile in target database.
- Make the tablespace TESTTBS in READ WRITE mode.
- Check the table objects data count.
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
