Fast Incremental Backups - Active Data Guard

Introduction

 
Fast Incremental backups is possible with Block change tracking, which is initially introduced from version 10.2 onwards, by this tool it's very useful to reduce the RMAN incremental backup duration but it is limited only for Primary database. From 11g Oracle with Active Data Guard feature we can use Block change tracking even on Physical standby database.  This article explains deep dive of fast incremental backups on Physical standby databases

Mechanism of Fast Incremental Backups

 
When this article explains about backups on Standby databases, you may think why should I perform backups on standby databases? Well you have valid point but it won't suits in all environments, Let's suppose If my database size is 500gb or 700gb and for example the duration of backup probably 4 or 5 hours or more or less depends on the CPU resources, Gather stats job and Export jobs (if any) and much... to accommodate we should have window for all of the jobs and for VLDB we have to be more careful. Many of the customers now are opting to perform RMAN backups on standby database to offload the resources of production database.  
 
 RMAN takes Incremental backups based on the changes of the datafiles from previous backup, Previous backups can be either Full backup or Incremental backups. For example..I have FULL backup on Sunday and my next incremental backup is on Monday then RMAN will look for all the changes of datafiles irrespective of whether data has been added/changed or not. This mechanism works in every incremental backups of a week.
 
So with the new Fast Incremental backups after enabling block change tracking(we will see enabling block change tracking in next paragraph) there will be no changes with FULL backup anyhow because we need to have all the base backup once, for Incremental backups RMAN uses the block change tracking file to identify the changed blocks after the last backup and RMAN would not scan whole datafile(s).  The same theory is applicable for either primary database or physical standby database.
 

Block Change Tracking & File

 
Block change tracking allows indeed the highest benefit for databases where the changes are not so high, If the changes are something around 20% then in this situation BCT helps a lot. Because on every day incremental backups RMAN has to take only those changed 20% of blocks but not scan every datafile part of the database. In order to implement Block change tracking it is highly recommend to enable the feature and measure the backup time after implementing it and see how it is going to help to the environment.
Block change tracking maintains bitmap versions of the files for tracking. After we enable block change tracking, the background process CTWR (Change Tracking Writer Process) will be started and CTWR will be knot with the redo apply mechanism. Furthermore Oracle database has integrated with BCT file; hence RMAN uses the change tracking data to determine which blocks to read during incremental backups.  When enabled, the block change tracking file that is 10 MB in size is created and grows as needed.
 
 
You may have small doubt why BCT is not supported in 10gR2 for standby databases?  In order to read the block change tracking file the database should be in OPEN/Read-Only mode and by keep open and no recovery will be possible for 10g. So the standby database should be in Mount status for the media recovery and indeed in mount status we cannot access any datafiles, so there is no concept of BCT in 10g Standby databases. Where from 11g we can have database open and same time recovery will be in progress. Hence BCT is available on standby databases starting from 11g with the Active Data Guard Feature.
 
There is no rule that BCT should be configured on both primary and standby database, we can either configure on both primary and standby or standby or primary. This article explains Fast incremental backups on Physical standby databases (ADG).

Enabling Block Change Tracking

 
Enabling block change tracking is simple. It can be done from SQL prompt and even Oracle Enterprise manager as well.  To enable BCT we need to mention the physical location with file where the BCT we are going to create. If you are using OMF (Oracle Managed Files) then it automatically creates under the DB_CREATE_FILE_DEST.
 
 
SQL> select * from v$block_change_tracking;
 
STATUS     FILENAME                            BYTES     CON_ID
---------- ------------------------------ ---------- ----------
DISABLED
 
SQL> select db_unique_name,database_role,open_mode from v$database;
 
DB_UNIQUE_NAME       DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
india                PHYSICAL STANDBY READ ONLY WITH APPLY
 
SQL> alter database enable block change tracking using file '/u02/app/oracle/oradata/mcdb/Change_Track.f';
 
Database altered.
 
SQL> select filename,status,bytes from v$block_change_tracking;
 
FILENAME                                           STATUS          BYTES
-------------------------------------------------- ---------- ----------
/u02/app/oracle/oradata/mcdb/Change_Track.f        ENABLED      11599872
 
SQL>
 
SQL> !ls /u02/app/oracle/oradata/mcdb/Change_Track.f
/u02/app/oracle/oradata/mcdb/Change_Track.f
 
SQL>
 
After  enabling the block change tracking, now we can see the CTWR background process is started and now it should be ready to write any changed records directly to the BCT file.
 
Completed: alter database disable block change tracking
Wed Mar 18 13:28:22 2015
alter database enable block change tracking using file '/u02/app/oracle/oradata/mcdb/Change_Track.f'
Wed Mar 18 13:28:22 2015
Block change tracking file is current.
Starting background process CTWR
Wed Mar 18 13:28:22 2015
CTWR started with pid=37, OS id=7073
Wed Mar 18 13:28:22 2015
Block change tracking service is active.
Completed: alter database enable block change tracking using file '/u02/app/oracle/oradata/mcdb/Change_Track.f'
 
SQL> select * from v$sgastat where name like '%CTWR%';
 
no rows selected
 
 
If OMF used
 
SQL> alter database enable block change tracking;
 
Database altered.
 
SQL> select filename,status,bytes from v$block_change_tracking;
 
FILENAME                                           STATUS          BYTES
-------------------------------------------------- ---------- ----------
/u02/app/oracle/oradata/INDIA/changetracking/o1_mf ENABLED      11599872
_bjldggbc_.chg
 
Configuring BCT from EM
 
Click on the Standby database instance
 
 
From the page of Database menu  Go through with the navigation--> Availability Menu --> Backup & Recovery Menu --> Backup Settings --> Select Policy
 
 
 
SQL> select filename,status,bytes from v$block_change_tracking;
 
FILENAME                                           STATUS          BYTES
-------------------------------------------------- ---------- ----------
/u02/app/oracle/oradata/mcdb/Change_tracking_EM.f  ENABLED      11599872
 

Interpreting BCT with Test Case

 
So far we have seen what BCT is and how to configure, But it is very important to know how it works with incremental backups. Let's check with a sample test case how BCT works.
 
Created a table
 
SQL> desc c##nassyam.bct_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 QNO                                                NUMBER
 QNAME                                              VARCHAR2(10)
SQL> select * from c##nassyam.bct_tab;
 
no rows selected
 
SQL>
 
Taken Full Backup
 
RMAN> backup database include current controlfile plus archivelog;
 
 
Starting backup at 18-MAR-15
using target database control file instead of recovery catalog
current log archived at primary database
.........
Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u02/app/oracle/fast_recovery_area/INDIA/autobackup/2015_03_18/o1_mf_s_874682272_bjllmh3n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15
 
RMAN>
 
Did some changes in Table of Primary Database
 
SQL> insert into bct_tab values (1010, 'E123923');
 
1 row created.
 
SQL> insert into bct_tab values (1020,'E230739');
 
1 row created.
 
SQL> insert into bct_tab values (1030,'Q937321');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from bct_tab;
 
       QNO QNAME
---------- ----------
      1010 E123923
      1020 E230739
      1030 Q937321
 
Standby
 
SQL> select * from c##nassyam.bct_tab;
 
       QNO QNAME
---------- ----------
      1010 E123923
      1020 E230739
      1030 Q937321
 
SQL>
 
Verify the changed blocks
 
The below query states on which file changes occurred and also it clearly mentions how many bytes, blocks and percentage of changes, this query uses x$ base tables.
 
select file#,
       blocks_changed,
       block_size,
       blocks_changed * block_size bytes_changed,
       round(blocks_changed / blocks * 100, 2) percent_changed
from v$datafile join
     (select fno
             file#,
             sum(bct) blocks_changed
      from (select distinct fno, bno, bct from x$krcbit
            where vertime >= (select curr_vertime from x$krcfde
                              where csno=x$krcbit.csno and fno=x$krcbit.fno))
      group by fno order by 1)
using(file#);
 
     FILE# BLOCKS_CHANGED BLOCK_SIZE BYTES_CHANGED PERCENT_CHANGED
---------- -------------- ---------- ------------- ---------------
         1            140       8192       1146880             .14
         3             72       8192        589824             .05
         4            100       8192        819200             .33
         6             12       8192         98304             .02
So we can see the changes in each datafile.
 
Take Incremental backups
When block change tracking is enabled, as per the behavior it should take backup of only the changed blocks and should not scan or access the datafiles which are not changed, Of course this information RMAN will access from the block change tracking file and that's how it executes the RMAN backup, Let's see now.
 
RMAN> backup incremental level 1 database;
 
Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/app/oracle/oradata/mcdb/sysaux01.dbf
input datafile file number=00001 name=/u02/app/oracle/oradata/mcdb/system01.dbf
input datafile file number=00006 name=/u02/app/oracle/oradata/mcdb/users01.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/mcdb/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u02/app/oracle/fast_recovery_area/INDIA/backupset/2015_03_18/o1_mf_nnnd1_TAG20150318T165317_bjlr1r2q_.bkp tag=TAG20150318T165317 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u02/app/oracle/oradata/mcdb/pdbseed/sysaux01.dbf
skipping datafile 00007 because it has not changed
input datafile file number=00005 name=/u02/app/oracle/oradata/mcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 18-MAR-15
 
Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u02/app/oracle/fast_recovery_area/INDIA/autobackup/2015_03_18/o1_mf_s_874688051_bjlr1w2b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15
 
RMAN>
 
From the above output of RMAN incremental backups, RMAN skipped taking backup for datafiles 5 and 7. Is there any definite reason to skip?
 
SQL> select file#,checkpoint_change# from v$datafile_header;
 
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            4658986
         3            4658986
         4            4658986
         5            1913352
         6            4658986
         7            1913352
 
Yes, RMAN has the reason why the datafiles are skipped and we can assume that BCT is working efficiently.
 
Verify BCT
 
There are few possible reasons, sometimes background process CTWR might terminated and it can hit many bugs, but if you want to verify whether block change tracking is active or not, you may review below things.
 
1) Check status from view
 
SQL> select status from v$block_change_tracking;
 
STATUS
----------
ENABLED
 
2) Check background process from OS
 
SQL> !ps -ef|grep ctwr
oracle   14267     1  0 15:14 ?        00:00:00 ora_ctwr_drmcdb
oracle   21507 20892  0 17:06 pts/1    00:00:00 /bin/bash -c ps -ef|grep ctwr
 
3) Memory Consumption, if memory is consuming then of course process is active.
 
SQL> select * from v$sgastat where name like '%CTWR%';
 
POOL         NAME                            BYTES     CON_ID
------------ -------------------------- ---------- ----------
large pool   CTWR dba buffer               1052672          1
 
Disabling Block Change Tracking
 
Like how easy to configure Block change tracking and it is easier to disable block change tracking with single command.
 
SQL> alter database disable block change tracking;
 
Database altered.
 
SQL> select status from v$block_change_tracking;
 
STATUS
----------
DISABLED
 
SQL> !ps -ef|grep ctwr
oracle   24976 20892  0 17:59 pts/1    00:00:00 /bin/bash -c ps -ef|grep ctwr
 
SQL>
 
 
Whether to implement the BCT or not?
So far we have seen how to implement, interpreting and few validations but the very difficult thing is whether to go with BCT or not?  Well. As I said above this has to be tested and see how it is going to help. Like enable block change tracking and schedule the incremental backups and now it's your job to monitor how many changes per day?
If it is something 10 to 20% of changes in datafiles then considering BCT makes sense. If suppose there are changes of 50% or 70% then no use of using BCT. To confirm the percentage of changes in each datafiles use the below query.
 
select file#,
       blocks_changed,
       block_size,
       blocks_changed * block_size bytes_changed,
       round(blocks_changed / blocks * 100, 2) percent_changed
from v$datafile join
     (select fno
             file#,
             sum(bct) blocks_changed
      from (select distinct fno, bno, bct from x$krcbit
            where vertime >= (select curr_vertime from x$krcfde
                              where csno=x$krcbit.csno and fno=x$krcbit.fno))
      group by fno order by 1)
using(file#);
 

Conclusion

 
We have many advantages with Active Data Guard and one of the good features we have discussed is Block change tracking which improves the performance of backups. We’ve seen how simple is to configure and manage and we have to put few efforts in testing the BCT for collecting the information such as database changes on day to day. If changes are up to 20% then it is good to implement BCT.
 
Reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup004.htm#i1032148