Block Change Tracking setup and overhead

September 9th, 2013


tea plantation

For everything you ever wanted to know about Block Change Tracking (BCT) and were afraid to ask, check out the awesome presentation from Alex Gorbechev

Here are some quick notes, mainly for my own reference, on BCT. BCT can be enabled while the database is up. Turning on BCT requires giving a tracking file location which can also be done while the database is up. Changing the BCT tracking file location after it has already been set requires bouncing the database. Finally, with BCT enabled we can look at the upper bound size for a database incremental backup. The actual size could be the same size as the upper bound or as small as 1/4 of the upper bound size as BCT only tracks groups of 4 blocks at a time for changes.

Screen Shot 2013-09-05 at 9.52.45 PM

select status from v$block_change_tracking;
-->  DISABLED
alter database enable block change tracking;
-->  ERROR at line 1:
-->  ORA-19773: must specify change tracking file name
alter system set db_create_file_dest='/oradata/orcl/' scope=both;
alter database enable block change tracking;
select * from v$block_change_tracking;
-->   STATUS     FILENAME                                                      BYTES
-->   ---------- -------------------------------------------------------- ----------
-->   ENABLED    /oradata/orcl/ORCL/changetracking/o1_mf_6ybbfdjv_.chg     11599872
Screen Shot 2013-09-05 at 9.52.45 PM
The following query is based on original query from Alex Gorbechev to give the number of blocks to read during a level 1 incremental backup. It seems this should also be upper bound on the size of an incremental database level 1 backup. Alex’s original query was based on the reads incurred by one datafile. This query attempts to look at the whole database.
Screen Shot 2013-09-05 at 9.52.45 PM
--  from Alex Gorbechev ( I believe the 32 refers to 4 *8K blocks size so if your block size is different you'll have to change this )
SELECT (count(distinct b.fno||' '||bno) * 32)/1024 MB
  FROM x$krcbit b,
    (SELECT MIN(ver) min_ver, fno
       FROM
         (SELECT curr_vercnt ver,
                 curr_highscn high,
                 curr_lowscn low,
                 fno
          FROM x$krcfde
              UNION ALL
          SELECT
                  vercnt ver,
                  high,
                  low,
                  fno
          FROM x$krcfbh )
          WHERE (SELECT MAX(bd.checkpoint_change#)
                 FROM   v$backup_datafile bd
                 WHERE  bd.incremental_level <= 1)                  between low and high            GROUP BY fno      ) sub  WHERE b.fno = sub.fno AND b.vercnt >= sub.min_ver
/
--> 960
Screen Shot 2013-09-05 at 9.52.45 PM

Alex’s original query:

Screen Shot 2013-09-05 at 9.52.45 PM

SELECT count(distinct bno) * 32
FROM x$krcbit b
WHERE b.fno = 7 AND b.vercnt >=
(SELECT MIN(ver) FROM
(SELECT curr_vercnt ver, curr_highscn high, curr_lowscn low
FROM x$krcfde WHERE fno = 7
UNION ALL
SELECT vercnt ver, high, low
FROM x$krcfbh WHERE fno = 7)
WHERE (SELECT MAX(bd.checkpoint_change#)
FROM v$backup_datafile bd
WHERE bd.file# = 7
AND bd.incremental_level <= 1) between low and high);

Screen Shot 2013-09-05 at 9.52.45 PM

Running incremental backups for a while it’s possible to collect historical ration between number of blocks read and number and size of the backup. This would as well account for compression.
Note that the query above is just an example and it has the following limitations:

  • Chunk size is hard coded to 32K (could it vary on different platforms?)
  • First block overhead is not accounted for
  • No special case when required bitmap version is not available (purged) and the whole datafile must be read
  • No case with backup optimization for level 0 (v$datafile_backup.used_optimization)
  • No case when no data blocks in datafile is changed (no bitmap version but the first block must be backed up anyway)
  • Only single datafile
  • No accounting for unavailable base incremental backup

Screen Shot 2013-09-05 at 9.52.45 PM

I’ve been wondering what kind of impact BCT has on databases and had the opportunity to talk to Vinay Srihari.*

Kyle: Vinay, what kind of impact does BCT have on the performance of an Oracle database?

Vinay: After it was introduced in 10gR1, BCT was put through TPCC-style OLTP benchmarks internally at Oracle. There was no measurable performance overhead on OLTP. However, it took a release to stabilize the feature and fix several fundamental bugs: 10.2.0.4 is all right, not so sure about 10.1.

There was some concern because BCT updates to the in-memory bitmap are done in the critical redo generation path by every foreground. The BCT logging turned out to be negligible in terms of CPU (instruction count) because redo generation was an order of magnitude more work. The main issue was bugs that caused the BCT background (CTWR) to get stuck or slow down, to the point where no more in-memory bitmap buffers were available and impacted foreground activity.

BCT for the Physical Standby was added in 11.1, as part of the Active Data Guard licensed option. There were some critical bugs with this feature that were fully resolved only in 11.2, although patches are available for 11.1.0.7.

I have seen BCT deployed successfully at customers with > 20MB/s (50 GB/hr), Oracle’s internal testing was ~50MB/s.

Bottom line, stability of the feature and not performance is the concern. On the primary: 10.2.0.4, 11.1.0.7, 11.2 are solid. On the standby: 11.1.0.7 (+ BCT patches), 11.2.0.1 (+ BCT patches), 11.2.0.2 are best.

* Vinay is currently an architect at Delphix and spent the past  12 years  at Oracle where he was involved in building High Availability services for the Oracle Database kernel. He was a member of early development teams that delivered RAC Cache Fusion, multi-threaded redo generation, and high performance replication. Since 2005 he was managing the High Availability infrastructure engineering team responsible for the development of the recoverability features of the core database and high-revenue products like Active Data Guard Standby, RAC, and Exadata. Prior to that Vinay also had stints in Oracle’s technical support and server performance organizations.
Concept of Hand with Electronic Fingerprints


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. Biju
    September 10th, 2013 at 03:17 | #1

    Kyle, Thanks and as usual good post. When using BCT with ASM and 11g, we often hit issue during duplicate using RMAN. So, I have seen DBAs disable BCT before RMAN backup for duplicate, and re-enable after the backup. Have you encountered any such issues, and what is the best practice to avoid this error during duplicate?

    Metalink Doc: Bug 10193846 – RMAN duplicate fails with ORA-19755 when BCT file is not accessible [ID 10193846.8]

    The workarounds provided do not seem to work or practical on ASM OMF files.

    Here is the error:
    RMAN-03002: failure of recover command at 04/20/2013 01:41:57
    ORA-00283: recovery session canceled due to errors
    RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘/backup/OLTPPRD/OLTPPRD/archivelog/1_130134_745341360.dbf’
    ORA-00283: recovery session canceled due to errors
    ORA-19755: could not open change tracking file
    ORA-19750: change tracking file: ‘+PROD_CFD1/oltpprd/changetracking/ctf.330.746741277′
    ORA-17503: ksfdopn:2 Failed to open file +PROD_CFD1/oltpprd/changetracking/ctf.330.746741277
    ORA-15012: ASM file ‘+PROD_CFD1/oltpprd/changetracking/ctf.330.746741277′ does not exist


eight − 5 =