When migrating massive amounts of data—ranging from several GBs to multiple TBs—between Oracle databases, standard expdp/impdp (logical export/import) can often take too long to fit within a maintenance window.
In such cases, the ultimate solution is Transportable Tablespaces (TTS), a technology that re-uses the physical files (data files) themselves. This article explains the differences and use cases for the two primary methods: Data Pump and RMAN.
🚀 What is Transportable Tablespace?
While a normal export logically reads data and writes it to a dump file, Transportable Tablespace is a technology that copies the data file (.dbf) itself and “mounts” it into another database.
Because the process primarily involves physical copying, the processing speed depends on the storage I/O performance. This allows it to maintain overwhelming speed even as data volumes increase.
✅ Key Use Cases
- Large-scale Data Migration: Migrating data in the tens of GB to TB class.
- Environment Replication: Building test or development environments using production data.
- Version Upgrades/Platform Migration: Moving data between different operating systems or versions.
1. Transportable Tablespace (TTS) via Data Pump
Characteristics
This method uses the standard Oracle expdp/impdp utilities. It exports only the metadata (structural information like table definitions), while the data files containing the actual data are copied manually via OS commands.
Example: Exporting with Data Pump
$ expdp user/pass TRANSPORT_TABLESPACES=USERS DIRECTORY=dir DUMPFILE=meta.dmp
Advantages
- Intuitive: Operates using the familiar Data Pump command syntax.
- Flexibility: Easily perform modifications during import, such as changing schema names using
REMAP_SCHEMA. - Full Tablespace Migration: Since 11g, using
FULL=YwithTRANSPORTABLE=ALWAYSallows for the migration of the entire database except for the system tablespaces.
2. Transportable Tablespace via RMAN
Characteristics
This method utilizes Recovery Manager (RMAN) features to create a set of transportable tablespaces from backup sets. RMAN automates everything from metadata creation and file copying to endian conversion.
Example: Execution in RMAN
RMAN> TRANSPORT TABLESPACE users
TABLESPACE DESTINATION '/tmp/transport'
AUXILIARY DESTINATION '/tmp/aux';
Advantages
- End-to-End Automation: Processes file conversion and metadata extraction in a single batch.
- Strong Cross-Platform Support: For migrations between different endians (e.g., Linux to Windows), RMAN automatically handles the conversion process.
- Scripting Friendly: Easy to incorporate into automated batches for periodic environment refreshes.
❓ FAQ: Frequently Asked Questions
Q: Is it okay if the block size differs between the source and destination?
A: No. The destination database must support tablespaces with the same block size. If the standard block sizes differ, you must configure the DB_nK_CACHE_SIZE parameter on the destination side in advance.
Q: Can I migrate if the character sets are different?
A: Generally, the destination character set must be a superset of (or compatible with) the source character set. Moving between AL32UTF8 environments is fine, but if they differ, a compatibility check is required beforehand.
Q: Are indexes migrated as well?
A: Yes. If the indexes are stored within the same tablespace, they are migrated along with the data files. If they reside in a different tablespace, those tablespaces must also be included in the transport operation.
Q: Is there a way to check if migration is possible before execution?
A: Use the DBMS_TTS.TRANSPORT_SET_CHECK procedure. After execution, you can check the TRANSPORT_SET_VIOLATIONS view to verify if the set is self-contained (i.e., no dependencies on other tablespaces).
📊 Comparison of Methods
| Item | Data Pump Method | RMAN Method |
| Operational Feel | Intuitive and simple commands | Highly automatable (Script-oriented) |
| File Movement | Manual copy via OS commands | Automatically generated and placed by RMAN |
| Cross-platform Conversion | Requires separate commands | Automatically converted by RMAN |
| Execution Speed | Very fast (Physical copy) | Very fast (Efficient including conversion) |
| Recommended Level | Beginner to Intermediate | Intermediate to Advanced |
💡 Which one should I choose?
Choose the Data Pump method when:
- One-time simple migration: When you want to get it done quickly and easily.
- Changing schema names: When you want to change the owner (username) during import.
Choose the RMAN method when:
- Migrations across different OS: When endian conversion is required (e.g., Linux ↔ Windows).
- Operational Automation: When creating a system to copy data from production to verification every weekend.
🏁 Conclusion
Transportable Tablespace is the ultimate “time-saving” weapon for an Oracle engineer.
- The Data Pump method is attractive for its ease of use and flexibility.
- The RMAN method is powerful for its automation, handling conversion and movement for you.
Select the optimal method based on your migration requirements and skill set. Once you master these, even Terabyte-class migrations are nothing to fear!
This article is based on Oracle Database 19c (screens and default values may differ in other versions).
[reference]
Oracle Database Utilities, 19c
コメント