There’s a requirement for cross platform database migration from Solaris Operating System (SPARC) (64-bit) to Solaris Operating System (x86-64) and ideas started flying around.
CONVERT DATABASE cannot be used because of endian change.
Dataguard was considered and there are limitations as well.
Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]
Because of source and target, neither options were available.
Two other options were explored: Cross Platform Transportable Tablespace (XTTS) and Data Pump.
Here’s a brief overview for XTTS:
- Verify tablespace is self-contained.
- Set the tablespace to READ ONLY.
- Export metadata at source
- Endian Conversion – Convert at SOURCE: convert tablespace / Convert at TARGET: convert datafile
- Transfer datafiles and export dump file to target.
- Set the source tablespace to READ WRITE.
- Import metadata at target
- Set the imported tablespace to READ WRITE
For the test case, endian conversion was performed at source using convert tablespace because it’s much simpler with 3 tablespaces and 45 data files.
The platform name can be determined using the SQL:
SELECT tp.platform_id, tp.PLATFORM_NAME, ENDIAN_FORMAT,d.name FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME(+) ORDER BY name, endian_format, platform_id;
Using RMAN for Conversion:
configure device type disk parallelism 6 backup type to backupset; configure archivelog deletion policy to none; convert tablespace tbs1,tbs2,tbs3 to platform="Solaris Operating System (x86-64)" db_file_name_convert '/oracle/oradata/source','/oracle/oradata/target';
Caveat – TRANSPORT_TABLESPACES do not contain: db_link,function,package,procedure,sequence,synonym,view, to name a few.
Summary of results:
XTTS took 593 minutes and Data Pump took 663 minutes.
However, REF_CONSTRAINT took 337 minutes.
Is it necessary to enable REF_CONSTRAINT if the tablesspace is in READ ONLY or the export is consistent???
Without enabling REF_CONSTRAINT, the total Data Pump time is reduced to 326 minutes.
RMAN CONVERT tablespace total 704GB for all data files and total USED tablespace is 692GB.
Of interest, the total USED tablespace is 445GB when imported at target.
That’s a difference 0f 247GB !!!
|
Action |
Mins |
| expdp schema parallel(18) | 68 |
| impdp table parallel(21) | 68 |
| index parallel(16) | 159 |
| constraint | 15 |
| stats parallel(24) | 16 |
| subtotal | 326 |
| ref_constraint | 337 |
| total | 663 |
| expdp_tts | 183 |
| dbf conversion parallel(6) | 274 |
| impdp_tts | 136 |
| total | 593 |
References:
Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
Best Practices for Using Transportable Tablespaces (TTS) [ID 1457876.1]
Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups [ID 1389592.1]
Thinking Out Loud 