When doing a cross-platform migration with transportable tablespaces and incremental backup, is it possible to perform backups on a standby database? If so, you could offload the work from a primary database.

The short answer is yes. You can use a physical standby database but not a snapshot standby database.

Using a standby database for the backups is an advantage in some situations:

• The primary database does not have the capacity to perform the extra backups (CPU, disk space, or I/O).
• You want the primary database to be completely unaffected by the migration.

How To

You follow the regular procedure described in the MOS note V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1) with a few changes. The changes are described in Using XTTs in a Data Guard Environment.

Be Careful

You must have an Active Data Guard license, if you:

• Open a physical standby database and enable redo apply.
• Enable block change tracking on the standby database (for faster incremental backups).

Procedure

The primary database is called SRCPRI and runs on the host src-pri. The standby is called SRCSTDBY and runs on the host src-stdby.

• Ensure allowstandby=1 in xtt.properties.

• Whenever you need to perform a backup on the physical standby database – level 0 or incremental:

  • Cancel redo apply and open the physical standby database:

  SRCSTDBY SQL> alter database recover managed standby database cancel;
  SRCSTDBY SQL> alter database open;
  

  • Perform the backup

  [oracle@src-stdby]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
  

  • Bring the standby database back in MOUNT mode and re-enable redo apply:

  SRCSTDBY SQL> shutdown immediate
  SRCSTDBY SQL> startup mount
  SRCSTDBY SQL> alter database recover managed standby database disconnect from session;
  

• When it is time to perform the final incremental backup:

  • Set tablespaces in READ ONLY mode on the primary database:

  SRCPRI SQL> alter tablespace ... read only;
  

  • Archive current log and ensure it is applied on the standby database:

  SRCPRI SQL> alter system archive log current;
  

  • When you have confirmed the redo is applied on the standby database, cancel redo apply and open it:

  SRCSTDBY SQL> alter database recover managed standby database cancel;
  SRCSTDBY SQL> alter database open;
  

  • Verify tablespaces are READ ONLY:

  SRCSTDBY SQL> select tablespace_name, status from dba_tablespaces;
  

  • Perform the final incremental backup

  [oracle@src-stdby]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
  

  • Perform the Data Pump export on the primary database:

  [oracle@src-pri]$ expdp system ... full=y transportable=always ...
  

Active Data Guard

If you have a license for Active Data Guard, you can simply keep the physical standby database in OPEN WITH REDO APPLY mode. You don’t need to switch from OPEN mode to MOUNT mode.

Conclusion

Being able to perform the backups on a standby database is a huge advantage. But it makes the process slightly more complicated, so I would recommend it only if really needed. In addition, if you don’t follow the procedure strictly, you might accidentally use a feature that requires Active Data Guard.

The MOS note V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1) has some additional details about using a standby database. You can get those details by opening a Service Request and asking for it.

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
• Pro Tips