While preparing for the production migration of your Oracle Database using cross-platform transportable tablespaces (XTTS) and incremental backups, you should test the procedure.
It is often impossible to get a full copy of the production database or a comparable test system. If you still want to test the migration procedure, you need to take the backups on the production database. That might have an impact on your production database. You could offload the backups to a standby database but periodically must set tablespaces in read only mode to perform the Data Pump exports. This means downtime for your application.
But there is a way to do all the work on the standby database. This will allow you to test the migration procedure without affecting the primary production database.
You can also read about the procedure in the MOS note Using XTTs in a Data Guard Environment.
How To
You should use the procedure described in a previous blog post about backups on a standby database. When you reach the final incremental backups you need to follow the information below.
I assume you have conducted a level 0 and a number of incremental backups on the standby database. Now it is time for the final incremental backup. We will do this on the standby database as well. You don’t need to touch the primary database except for a few minor changes (archive current log and defer redo log transport).
- Temporarily activate the standby database following the procedure described in MOS note How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1).
- Connect to the activated standby database, which is now in read write mode, and set the tablespaces read only.
STDBY SQL> alter tablespace ... read only; - Do the final incremental backup on the standby database using the Perl script:
$ perl xttdriver.pl --restore - Perform the Data Pump export from the standby database
$ expdp system@stdby transportable=always full=y ... - Re-instate the activated standby database as a proper standby database following the procedure described in MOS note How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1).
Now you have a consistent backup of the data files and a corresponding Data Pump export; both components were taken from a standby database. Transfer the files to your target database and test the procedure.
Production Migration
The heading says you can use this procedure for testing. How about the production migration?
Well, it could work, but I would not recommend it.
When you use this approach, you leave the primary database open for business. At the same time, you take the final incremental backup. This opens for a potential catastrophe. Users entering data into the open, primary database while you have taken the last incremental backup. That would mean data loss!
Using the other standby database approach, you are sure that no more data is entered into the source database, when you perform the final migration. You ensure this by setting the tablespaces read only in the primary database. This is why I do not recommend that you use the approach with an activated standby database for your production migration.
Conclusion
You should test your migration procedure and get comfortable with your runbook. You can use a temporarily activated standby database if you don’t have a dedicated test environment. This allows you to perform realistic testing without interrupting the primary database and your users.
Other Blog Posts in This Series
- Introduction
- Prerequisites
- 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
- How to use Transportable Tablespace with TDE Tablespace Encryption
- Pro Tips
Databases Are Fun 
Hi Daniel,
Is it possible to do Primary , incremental etc on primary but only do final incremental & expdp/impdp on standby. I have to do over 120 TB XTTS migration from Solaris to Linux and I want to do mock test to guage timing and issues etc. I have already done few migrations but data were less than 20 TB so this one is huge and want to minimize downtime for production
LikeLike
Hi Sanjay,
I actually don’t know whether it will work. You need to try it out to find out. However, according to the MOS note only three options are supported:
1) Do everything on primary
2) Do all backups on standby, set tablespaces read only and export from primary
3) Use the procedure with temporarily activated standby database
Other approaches might work, but if you run into problems, I don’t think you should expect much help from support.
Regards,
Daniel
LikeLike
Hi Daniel,
I am also applying the similar approach as below. but the extra steps that includes is GG after the final restore. So here which SCN we should consider for GG replicat start.
our goal is to use OGG after final restore until cutover. pls recommend/suggest on this
LikeLike
Hi,
When you set the tablespaces read-only on the source database, you should get the SCN. Start extract from this point, and start replicat as of that SCN. That is step 5 in the above.
You shouldn’t activate the standby database. This is not necessary. You can do it from a snapshot standby database. There is a properties file setting for doing it on the standby database.
I suggest that you manually recover the standby database to a certain SCN. Then you switch to snapshot standby and perform steps 5-7. The SCN that you recover to on the standby should be the SCN from where you start replicat.
Regards,
Daniel
Regards,
Daniel
LikeLike