If you need to migrate a database to the cloud or anywhere else for that matter, you should consider using cross-platform transportable tablespaces and incremental backup (XTTS). Even for really large databases – 10s or 100s of TB – you can still migrate with minimal downtime. And it works across different endian formats. In fact, for the majority of cross-endian projects this method is used.
In addition to minimal downtime, XTTS has the following benefits:
- You can implicitly upgrade the database by migrating directly into a higher release
- You can migrate from a non-CDB and into a PDB
- You can keep downtime at a minimum by using frequent incremental backups
- You can migrate across endianness – e.g. from AIX or Solaris to Oracle Linux
Endianness is determined by the operating system. Simplified, it determines in which order bytes are stored in memory:
- Big endian: stores the most significant byte of a word at the smallest memory address.
- Little endian: stores the least-significant byte at the smallest address.
Wikipedia has an article for the interested reader.
Which platform uses which endian format? There is a query for that:
SQL> select platform_name, endian_format from v$transportable_platform;
If your source and target platform does not use the same endian format, then you need a cross-endian migration.
How Does It Work
To concept is explained in this video on our YouTube Channel:
Basically, you need to migrate two things:
The data itself is stored in data files and you will be using transportable tablespaces for this. Since the source and target platform are not the same, the data files must be converted to the new format. Only the data files that make up user tablespaces are transported. The system tablespaces, like SYSTEM and SYSAUX, are not transported.
If you have a big database, it will take a lot of time to copy the data files. Often this is a problem because the downtime window is short. To overcome this you can use a combination of RMAN full backups (backup set or image copies) and incremental backups.
There are a few ways to do this which is covered in the following MOS notes:
- Version 3: 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 1389592.1)
- Version 3: 12C – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2005729.1)
- Version 4, preferred: Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)
The first two methods are using version 3 of a Perl script (
xttdriver.pl), whereas the last method uses version 4 of the same Perl script. Version 4 offers a much simplified method and I will use that version for this blog post series.
Version 4 of the Perl script has a list of requirements. If your project can’t meet these requirements, check if the previous version 3 can be used.
Transferring the data files is just part of the project. Information on what is inside the data files, the metadata, is missing because the system tablespaces were not transferred. The metadata is needed by the target database, otherwise, the data files are useless. The Transportable Tablespace concept as a whole does not work for system tablespaces, but instead we can use Data Pump.
You can use either:
- Traditional transportable tablespace
- Or, the newer full transportable export/import (FTEX)
For this blog post series, I am only focusing on FTEX. But if you run into problems with FTEX, or if you can’t meet any of the FTEX requirements, you should look into the traditional transportable tablespace method.
Here are a few examples of metadata that Data Pump must transfer:
- Packages, procedudes and functions
- Table and index defintions (the actual rows and index blocks are in the data files)
- Temporary tables
- Database links
- And so forth
By using a combination of cross-platform transportable tablespaces and incremental backups, you can migrate even huge databases to the cloud. And it even works for cross-endian migrations, like AIX or Solaris to Oracle Linux.
You can watch our YouTube playlist and watch videos on cross-platform transportable tablespaces.
- Transporting Data Across Platforms, Backup and Recovery User’s Guide 19c
- My Oracle Support: Master Note for Transportable Tablespaces (TTS) — Common Questions and Issues (Doc ID 1166564.1)
- My Oracle Support: Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (Doc ID 1454872.1)
- Mike Dietrich: Different MOS Notes for xTTS PERL scripts – Use V4 scripts
Other Blog Posts in This Series
- Full Transportable Export/Import
- What Is a Self-contained Transportable Tablespace Set
- How to Migrate a Database Using Full Transportable Export/Import and Incremental Backups
- Make It Fast
- Backup on Standby Database
- Slow Network Between Source and Target
- Bigfile Tablespaces
- Testing the Procedure On Activated Standby Database
- Testing the Procedure Multiple Times Using Read-Only Tablespaces
- Target Database and Data Guard
- ASM Aliases and Why You Should Get Rid of Them
- Pro Tips
4 thoughts on “XTTS: Introduction – Minimal Downtime Migration with Full Transportable Export Import and Incremental Backups”
A major issue with the xTTS Perl scripts V4 approach is that the process starts with RMAN taking file image copies of NONCDB – hence the need to have storage space available to copy your NONCDB application data. If an organisation needs to use incremental backups to migrate – i.e. it has huge business critical database that can only afford a brief window of downtime, then it almost certainly is already backing up using Oracle recommended incremental strategy (e.g. Level 0 followed by Level 1, usually in weekly cycles).
To avoid taking and transporting another copy of the application’s data files, it’s much faster and more efficient to RESTORE FOREIGN and RECOVER FOREIGN the existing Level 0 and 1 into the target PDB file directory until the desired migration cut-over.
Maybe also worth mentioning that if it takes a week to roll-forward a huge database in this way, then in the interim period before migration, some applications like DWH will have added / dropped tablespaces – hence, use of the NETWORK_LINK option for the DATAPUMP FULL=YES TRANSPORTABLE=ALWAYS is preferred since you can then easily generate the required TRANSPORT_DATAFILES clauses.
LikeLiked by 2 people
Thanks for your comments. And you are right about the disk space requirements, and that it is a good idea to uses a database link to extract the metadata for the full transportable export import. Once time allows I will update the post. Thanks.
Also, the idea of using RESTORE FOREIGN is put on my list for a future blog post.
LikeLiked by 1 person
I am performing a migration with V4 XTTS from AIX to linux, and I have encountered an error that I did not expect, when performing the backup command:
$ORACLE_HOME/perl/bin/perl xttdriver.pl –backup
everything went correctly but when I saw the res.txt file, it did not match the backups pieces with the datafiles. Here is an example:
[oracle@targetserver tempxtt]$ cat res.txt
And you should see the following done:
[oracle@targetserver tempxtt]$ cat res.txt
Any thoughts on this.
Thank you very much for everything.
Have you tried to restore the backup? I have never taken a look into that file, so I don’t know what you should expect of the content. Try to restore and post the error to the blog, if any.