Migrating databases from different operating system versions with Oracle’s XTTS utility can be a slow and painful process. But we figured out an innovative way to help accelerate that process.
Centric Consulting’s EAS team helped a very large healthcare company migrate their enterprise data warehouse from an AIX environment to an Oracle Exadata Linux environment. This database was in excess of 200 terra bytes and the client wanted the migration to be completed with minimal downtime.
Enter Oracle’s Cross-Platform Transportable Tablespace (XTTS) Utility
In our case, we used Oracle’s version 2 of the XTTS tool. This tool has some specific limitations on how much data is transferred as part of the migration. In addition, there are limitations on adding tablespaces or data files once the “Prepare Phase” has begun.
What our EAS team found is this: For very large databases, using the delivered XTTS process isn’t very practical. With the delivered tool, you only get one tablespace processed at a time.
In our testing, the PARALLEL=2 option in the xtt.properties configuration file allowed us to process about 100 megabytes of data per second. On our client’s network, this would have meant 23 days of transferring data until our 200+ terra byte database completed the Prepare Phase alone. With the limitation of not being able to add data files or tablespaces during that process, this further reduced the usefulness of this tool for very large databases.
So we modified the existing XTTS process, by creating forty identical directories. Each of these included a complete XTTS utility set of scripts. We then wrote custom code that allowed each of the 40 xtt.properties files to hold a subset of the total number of tablespaces to be transferred. This allowed us to process the 200+ terra byte database to our target system in six days – instead of the estimated 23 days – with a standard delivered code.
We also implemented a number of other changes throughout the entire process to speed up our ability to migrate, convert, and roll forward this data into our new target system. Below is a visual representation of the timeline to complete the migration:
Leading Major Data Warehouse Migration Projects
If you are interested in all of the changes we implemented as part of our major data warehouse migration, download Centric’s whitepaper on: “How To Migrate A 200+ TB Database In Under A Day Using Oracle’s Cross-Platform Transportable Tablespaces Utilizing RMAN Incremental Roll-Forward Backups.”
The whitepaper will include details on how we changed the delivered processing to speed up transfers and ensure consistency.
During our project, Centric worked extensively with the Oracle’s product development team, which manages that XTTS utility. Oracle subsequently released version 3 of the XTTS utility in June 2017, which now allows for tablespaces and data files to be created at any time during the migration process. This is a definite improvement, but more is still needed.
The ability to process data faster through the Prepare Phase is critical, and robust restart capabilities are just some of the major limitations to this utility. However, these can be overcome with some planning and assistance.