You can perform multiple tests of the procedure for cross-platform transportable tablespaces (XTTS) and incremental backups. You don’t have to restore and recover the data files every time. It makes your testing effort a lot more efficient.
When you perform a Data Pump import of transportable tablespaces, the data files plugs into the target database, and the tablespaces turns into read write mode immediately. It happens in the phase DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK.
Dump Pump must turn the tablespaces into read write mode for several reasons:
- Suppose you have tables with columns to type timestamp with timezone information (TSTZ) and there is a different in the database time zone file version. In that case, all TSTZ columns must be updated to reflect the new timezone conventions.
- The data file bitmap of free space must be updated in case you have segments in your tablespaces that are not imported.
When the tablespaces switches to read write mode the data file headers are updated. Now, the data files changes in such a way that they belong to that database (I imagine it has something to do with DBID, SCN, and so forth).
This also means that you can only import the tablespaces one time. If you want to repeat the process, you need to restore and recover the data files. Not even Flashback Database can save you.
A new option was added to Data Pump in Oracle Database 19c to solve this:
TRANSPORTABLE=KEEP_READ_ONLY. Here is what the documentation says:
If specified, then data files remain in read-only mode. As a result of this setting, the tables containing
TSTZcolumn data cannot be updated, and are dropped from the import. In addition, data file bitmaps cannot be rebuilt.
Keeping the tablespaces read only and leaving the data files untouched sounds good. But there are some restrictions:
- If you have TSTZ columns, they can’t be checked and updated. This means that the entire table with a TSTZ column is skipped during import. You will see this error in the Data Pump log file:
ORA-39339: Table "SCHEMA"."TABLE" was skipped due to transportable import and TSTZ issues. To solve it, you need to manually import the table afterward using a Data Pump table mode export and import.
- If you have any segments in your data files that no longer belong to an object (e.g. if that specific object was not imported), the free space bitmap can’t be updated, and you have free space that can’t be used. You can solve this later on when the tablespaces are in read write mode using
If you can live with these restrictions, you can use this feature.
You can use
TRANSPORTABLE=KEEP_READ_ONLY when testing in the following way:
- Perform the backups on the source database using
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup.
res.txtand restore/recover the data files using
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
- Now you want to test the migration procedure. Set the tablespaces in the source database in read only mode, do an incremental backup and perform the Data Pump export. Optionally, use a temporarily activated standby database to avoid interruptions on the primary production database.
- Set a guaranteed restore point in the target database.
- After recovering the data files on the target system, you perform the Data Pump import. You must set the Data Pump parameter
TRANSPORTABLE=KEEP_READ_ONLYto leave the data files untouched.
- When you are done with your tests, you either flash back to the restore point or delete the data in your database. The latter would be a series of
DROPcommands (schema, roles etc.) followed by
DROP TABLESPACEcommands. To preserve the data files, be sure to use
DROP TABLESPACE ... INCLUDING CONTENTS KEEP DATAFILES. The
KEEPclause is especially vital on ASM and with OMF.
When you specify INCLUDING CONTENTS, the KEEP DATAFILES clause lets you instruct the database to leave untouched the associated operating system files, including Oracle Managed Files. You must specify this clause if you are using Oracle Managed Files and you do not want the associated operating system files removed by the INCLUDING CONTENTS clause.
- You can now continue to recover the data files on your target system with new incremental backups from the source database. You can roll forward the data files even though we had them plugged into a database. This is really cool. This allows you to repeat the test with new fresh production data without the tedious task of completely restoring and recovering the data files.
- Repeat the test cycle as many times as you want. If the data files are left untouched using
TRANSPORTABLE=KEEP_READ_ONLYand you don’t turn them into read write mode manually, you can repeat the process.
Can you use
TRANSPORTABLE=KEEP_READ_ONLY for the production migration? Yes, you can. But eventually, you will need to turn the tablespaces into read write mode.
I would not usually recommend it. But recently, I was involved in a specific case where it was helpful.
During testing, a customer ran into an issue where Data Pump hung during import. They killed the import and tried to repeat the Data Pump import. Unfortunately, Data Pump imports of transportable tablespaces are not resumable until Oracle Database 21c. So they had to start Data Pump all over. But the data files had already been touched by the target database. Now Data Pump refused to progress with the import because the data files were not as expected. The customer was advised on how to avoid the Data Pump hang. But they were still a little concerned about their upcoming production migration. They would like to keep the tablespaces in read only mode. Just in case something similar would happen. In that case, they could easily start all over because the data files were untouched.
During a migration project, you should use the Data Pump feature
TRANSPORTABLE=KEEP_READ_ONLY to ease your testing efforts. In some rare cases, it might also be helpful for production migrations.
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
- Target Database and Data Guard
- ASM Aliases and Why You Should Get Rid of Them
- Pro Tips