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"?
"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 ?
- The BCT has a structure that contains the starting SCN of changes captured.
- The BCT file creates version records associated with block changes before the backup begins.
- If no BCT changes are captured for a datafile, it is assumed that no changes occured since the starting SCN of the BCT file.
- The BCT file keeps a bit map of changes between 2 SCN numbers
- The BCT file keeps changes on a datafile level.
- 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.
- 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.
Love this. Was bothering me for a while.
ReplyDelete