Using Block Change Tracking with Oracle 12c Data GuardPublished on: Author: Bastiaan Bak Category: Oracle
What is Block Change Tracking, and why use it?
When doing incremental backups with RMAN, Block Change Tracking (BCT) can be used to bypass the data blocks that have not changed. This means enabling Block Change Tracking will speed up your incremental RMAN backups.
Enabling can be done by an “alter database” command that will create a block change tracking file. The CTWR process, the change track writer, will start to log all the blocks that changed after the last backup.
Scheduling backups in a Data Guard environment
When you use Oracle Data Guard to set up a physical standby database, the standby will be identical to the primary database. Both will also have the same DBID. As a result, you can use backups of the primary to recover the standby, or the other way around. So it’s possible to offload the backups to the standby database.
But in some situations, you might want to make a backup of both the primary and the standby. If your backups are stored in the same location as the database, and you have an outage on the complete primary location, you might lose both your database and all of your backups. If your standby is running on another location, and it has its own backup storage, you will also have a complete backup history after a failover, even if the old primary location is lost completely.
How does Block Change Tracking work in combination with Data Guard?
If you schedule backups on the standby database you have to be very careful when using Block Change Tracking, as BCT on a physical standby is a part of the Active Data Guard option. You are only allowed to use BCT on the standby if you have an Active Data Guard license.
If you are not licensed to use Active Data Guard, you should only activate BCT on the primary. However, if you perform a switchover, your old primary is the new standby. So you shouldn’t forget to disable BCT on the new standby and enable it on the new primary. If you don’t do this, you risk using BCT on the standby and therefore using an unlicensed option. This could have financial consequences, of course.
If you use Fast Start Failover the risk might be even greater, because a failover might be triggered without any interference from the DBA. And if your backup is scheduled just after the failover, you risk starting a backup on the standby using an unlicensed BCT file.
Fixing the backup script
To avoid these problems, I added a few small checks to my backup scripts. First, it checks if a Managed Recovery process (ora_mrp) is running. If so, we are apparently starting a backup of the standby. If not, we are on the primary. Then, it checks if the Change Track Writer (ora_ctwr) is running. If so, BCT is enabled here.
If we are on the standby database and BCT is enabled, we should disable it before starting the backup. If BCT is disabled on the primary, we could enable it. The backup script should look something like this:
- . /home/oracle/.profile
- typeset -i running_mrp=0
- typeset -i running_ctwr=0
- # test what processes are running
- running_mrp=`ps -ef| grep ora_mrp | grep -v grep | wc -l `
- running_ctwr=`ps -ef| grep -i ora_ctwr | grep -v grep | wc -l `
- # disable BCT on standby if needed
- if [ $running_mrp -eq 1 ] && [ $running_ctwr -eq 1 ] ; then
- sqlplus / as sysdba <<EOF
- ALTER DATABASE DISABLE BLOCK CHANGE TRACKING ;
- # enable BCT on primary if needed
- if [ $running_mrp -eq 0 ] && [ $running_ctwr -eq 0 ] ; then
- sqlplus / as sysdba <<EOF
- ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;
- # start the backup
- rman <<EOF
- connect target sys/xx@proddb
- connect catalog rmancat/xx@catdb
- BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE ;
Other queries to use
Another way to check if BCT is enabled:
- SQL> SELECT status FROM v$block_change_tracking;
You could also check if BCT has ever been used for the backups. If you notice this is the case, you should consider recreating the standby database if you are not licensed to use Active Data Guard.
- select trunc(COMPLETION_TIME) backupdate
- , used_change_tracking BCT
- from v$backup_datafile
- group by trunc(COMPLETION_TIME)
- , used_change_tracking
- order by trunc(COMPLETION_TIME)
- BACKUPDAT BCT
- --------- ---
- 10-JAN-19 NO
- 11-JAN-19 NO
- 12-JAN-19 NO
- 13-JAN-19 NO
- 14-JAN-19 NO
- 15-JAN-19 NO
- 16-JAN-19 NO
- 17-JAN-19 NO
- 18-JAN-19 NO
- 9 rows selected.
Be careful not to use Active Data Guard in Oracle 12c when you are not licensed. Although Active Data Guard is mostly associated with the use of Real-time Query to access data on a physical standby database, the license also includes other features.