Monday, July 29, 2019

Block change tracking and the ZDLRA (part 1)

Oracle Block Change Tracking(BCT) and the ZDLRA



One of the most commonly asked question I get when explaining how the ZDLRA works has to do with Block Change tracking.

The question is usually something like...

"If the the Block Change Tracking file only keeps 7 days of history by default, how does it work with the ZDLRA where you only perform a full backup once"?

And the second question is

"If I only perform incremental cumulative backups, how does it use the BCT if it get's recreated/invalidated ?"

How does the BCT work ?

Well let's walk through what the block change does from a high level.

If you want the detail of the internals, Alex Gorbachev wrote a great paper and MOS note explaining it all.  ORACLE 10G BLOCK CHANGE TRACKING INSIDE OUT (Doc ID 1528510.1)

I'm going to start with a simple example.
First I'm going to turn on block change tracking, and then check the File# for my database files.


SQL> alter database enable block change tracking using file '/home/oracle/app/oracle/oradata/BSG18/bct.dbf';

SQL>
TABLESPACE_NAME                   FILE_ID
------------------------------ ----------
USERS                                   7
UNDOTBS1                                4
SYSTEM                                  1
SYSAUX                                  3




I am going to use 2 internal structures to walk through the BCT information.

X$KRCFH - This structure contains the lowscn of the BCT file.  It is used by RMAN to determine
                      the beginning SCN of the BCT file.  If no changes have been captured for a datafile,
                      then RMAN knows at which point it can make that assumption.

X$KRCFBH - This structure contains the versions of block changes available to use for backups.
                         This structure contains a row for each datafile, and each version.
                         This row points to a bitmap containing the block changes associated with the version.
                          The key columns I am interested in to explain how it works are

                                FNO          - File number
                                VERCNT - Version number identifying the change records.
                                                    This starts at 1 and increases over the life of the BCT file.
                                 LOW        - Low SCN of the block changes. It is 0 for the first record
                                                     indicating that it not complete
                                 HIGH        - High SCN of the block changes. The last SCN number
                                                      identifying the block changes.

I am going to look the internal structure X$KRCFBH to view what's in the block change tracking.

select (select tablespace_name from dba_data_files where file_id=fno) tablespace_name,vercnt,to_char(vertime,'mm/dd/yy hh24:mi:ss') vertime,low, high from x$krcfbh
   where fno in (select file_id from dba_data_files);
SQL> SQL>   2
TABLESPACE     VERCNT VERTIME                  LOW       HIGH
---------- ---------- ----------------- ---------- ----------
SYSTEM              1 07/29/19 09:21:17          0          0
SYSAUX              1 07/29/19 09:21:18          0          0
UNDOTBS1            1 07/29/19 09:21:18          0          0
USERS               1 07/29/19 09:21:18          0          0



I can see that there are entries for all my tablespaces, with a "version count" of 1 and low/high time of 0.  This is telling me that that no backups have been executed yet usingthe BCT.
I am also going to look at X$KRCFH to see what the starting SCN is for the BCT file.


select lowscn from X$KRCFH;

LOWSCN
----------
8361930


Now I am going to execute a full backup and see what happens.



TABLESPACE     VERCNT VERTIME                  LOW       HIGH
---------- ---------- ----------------- ---------- ----------
SYSTEM              1 07/29/19 09:21:17          0    8362001
SYSAUX              1 07/29/19 09:21:18          0    8362001
UNDOTBS1            1 07/29/19 09:21:18          0    8362001
USERS               1 07/29/19 09:21:18          0    8362001




You can see that the high SCN set to 8362001.
Now I am going to look at the RMAN backup (USERS tablespace) to see what the Checkpoint SCN number was for the backup.

  File LV Type Ckp SCN    Ckp Time          Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ----------------- ----------- ------ ----
  7    0  Incr 8362002    07/29/19 09:46:59              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf


Ahhh.. Now I can see how it fits together. The high SCN for the BCT file is SCN number right before the checkpoint taken with the backup.

Now let's execute an incremental backup and see what happens.

TABLESPACE     VERCNT VERTIME                  LOW       HIGH
---------- ---------- ----------------- ---------- ----------
SYSAUX              1 07/29/19 09:21:18          0    8362001
SYSTEM              1 07/29/19 09:21:17          0    8362001
UNDOTBS1            1 07/29/19 09:21:18          0    8362001
USERS               1 07/29/19 09:21:18          0    8362001
SYSAUX              2 07/29/19 09:46:59    8362001    8363961
SYSTEM              2 07/29/19 09:46:59    8362001    8363961
UNDOTBS1            2 07/29/19 09:46:59    8362001    8363961


1    0  Incr 8362002    07/29/19 09:46:59              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
1    1  Incr 8363962    07/29/19 10:04:00              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  


By looking at the SYSTEM tablespace (file 1) I can see exactly what is happening with the BCT file.
The first version marks the starting SCN prior to the first backup after creating the file.
The second version marks the checkpoint SCN of the first backup (low), and the SCN prior to the second backup(high).
These marks, LOW/HIGH SCN, can be used to identify the blocks that changed between the backups.
Now I am going to perform a few more incremental backups with a few changes to the USERS tablespace and see what happens to the SYSTEM and USERS tablespaces versions.

First here is my query.

col tablespace_name format a10

select (select tablespace_name from dba_data_files where file_id=fno) tablespace_name,vercnt,to_char(vertime,'mm/dd/yy hh24:mi:ss') vertime,low, high from x$krcfbh
   where fno in (select file_id from dba_data_files)
    order by 1,2;

Now let's see what what my backups look for File 1 (SYSTEM tablespace).

List of Backup Sets
===================
  File LV Type Ckp SCN    Ckp Time          Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ----------------- ----------- ------ ----
  1    0  Incr 8362002    07/29/19 09:46:59              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8363962    07/29/19 10:04:00              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8364371    07/29/19 10:13:21              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8364482    07/29/19 10:15:19              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8364637    07/29/19 10:16:38              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8365075    07/29/19 10:26:23              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8365373    07/29/19 10:29:13              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8365578    07/29/19 10:30:21              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf
  1    1  Incr 8365763    07/29/19 10:30:58              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_system_fz01xnqo_.dbf


Let's see what my backups look like for File 7 (USERS tablespace)

List of Backup Sets
===================
  File LV Type Ckp SCN    Ckp Time          Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ----------------- ----------- ------ ----
    7    0  Incr 8362002    07/29/19 09:46:59              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8363962    07/29/19 10:04:00              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8364371    07/29/19 10:13:21              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8364482    07/29/19 10:15:19              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8364637    07/29/19 10:16:38              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8365075    07/29/19 10:26:23              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8365373    07/29/19 10:29:13              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8365578    07/29/19 10:30:21              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf
    7    1  Incr 8365763    07/29/19 10:30:58              NO    /home/oracle/app/oracle/oradata/BSG/datafile/o1_mf_users_fz01zl49_.dbf




My backups for both of these match. They have the same Ckp SCN.
Notice that I performed an Incremental level 0 backup, and then 8 Incremental level 1 backups.
I actually performed both differential backups and cumulative backups but it didn't matter.

Now let's look at the block change tracking file for these 2 tablespaces (system and users)

TABLESPACE     VERCNT VERTIME                  LOW       HIGH
---------- ---------- ----------------- ---------- ----------
SYSTEM              3 07/29/19 10:04:00    8363961    8364370
SYSTEM              4 07/29/19 10:13:21    8364370    8364481
SYSTEM              5 07/29/19 10:15:19    8364481    8364636
SYSTEM              6 07/29/19 10:16:38    8364636    8365074
SYSTEM              7 07/29/19 10:26:23    8365074    8365372
SYSTEM              8 07/29/19 10:29:13    8365372    8365577
SYSTEM              9 07/29/19 10:30:20    8365577    8365762
USERS               1 07/29/19 09:21:18          0    8362001
USERS               2 07/29/19 09:46:59    8362001    8364481
USERS               3 07/29/19 10:15:19    8364481    8364636


Very interesting.. Since I only made few changes to the users tablespace it has 3 versions, the oldest of which is the full backup.
The system tablespace has gone over 7 versions and it no longer has the original version from the level 0 backup.

Now let's see if it used the BCT files for the backups.

     FILE# Creation Time     INCREMENTAL_LEVEL INCREMENTAL_CHANGE# CHECKPOINT_CHANGE# USED BCT
---------- ----------------- ----------------- ------------------- ------------------ ---
         1 07/29/19 09:47:24                 0                   0            8362002 YES
         1 07/29/19 10:04:02                 1             8362002            8363962 YES
         1 07/29/19 10:13:21                 1             8363962            8364371 YES
         1 07/29/19 10:15:19                 1             8364371            8364482 YES
         1 07/29/19 10:16:38                 1             8364482            8364637 YES
         1 07/29/19 10:26:24                 1             8364637            8365075 YES
         1 07/29/19 10:29:14                 1             8362002            8365373 YES
         1 07/29/19 10:30:22                 1             8362002            8365578 YES
         1 07/29/19 10:31:10                 1             8362002            8365763 NO

         7 07/29/19 09:47:00                 0                   0            8362002 YES
         7 07/29/19 10:04:02                 1             8362002            8363962 YES
         7 07/29/19 10:13:21                 1             8364300            8364371 YES
         7 07/29/19 10:15:19                 1             8364371            8364482 YES
         7 07/29/19 10:16:38                 1             8364482            8364637 YES
         7 07/29/19 10:26:24                 1             8364637            8365075 YES
         7 07/29/19 10:29:14                 1             8362002            8365373 YES
         7 07/29/19 10:30:22                 1             8362002            8365578 YES
         7 07/29/19 10:30:59                 1             8362002            8365763 YES



WOW...  Notice that the system tablespace (FILE #1) could not use the BCT file for the last backup, but the backup of the user tablespace (FILE #7) could because there were no changes between a few of the backups.

I also noticed the creation time of the RMAN backup, and the creation time of the BCT file record.  The new BCT file record (if changes occured) is created BEFORE the backup begins.

Finally, I can also see that the high SCN for users, 8364636,is older than the high SCN for system.


I am going to change users, and perform an incremental to see what happens to the LOW/HIGH scn in the next version.

TABLESPACE     VERCNT VERTIME                  LOW       HIGH
---------- ---------- ----------------- ---------- ----------
USERS               1 07/29/19 09:21:18          0    8362001
USERS               2 07/29/19 09:46:59    8362001    8364481
USERS               3 07/29/19 10:15:19    8364481    8364636
USERS               4 07/29/19 10:16:38    8364636    8366975

After the backup, there is no gap in the SCN numbers.  It created a new version that contained the changes between the previous version HIGH and the SCN of the incremental backup.


So what have we learned about the BCT ?



  1. The BCT has a structure that contains the starting SCN of changes captured.
  2. The BCT file creates version records associated with block changes before the backup begins.
  3. If no BCT changes are captured for a datafile, it is assumed that no changes occured since the starting SCN of the BCT file. 
  4. The BCT file keeps a bit map of changes between 2 SCN numbers
  5. The BCT file keeps changes on a datafile level.
  6. If a datafile didn't change between backups, it doesn't create a new record in the BCT file.  It doesn't matter if the backup is an incremental or full backup.
  7. By default 7 versions are kept for EACH DATAFILE. After 7 days of backups, some datafiles may still use the BCT if they haven't changed.

Below is a flow chart of what happens.



I explain how it is used in part 2 found here.

1 comment: