Block Chain Tracking in oracle

Hello Everyone

This is my first blog. Through this blog, I will try to share my learning as Oracle DBA. In my first ever blog, I am going to discuss something that is a new concept I learned recently.

The topic is BlockChain Tracking:

We have encountered a problem in our day to day operations, where the application team was facing slowness in some program/PL-SQL code that the program used to finish at 3:00 AM but from past 2 to 3 days, it is not completing at the usual time i.e. 3:00 AM. So we started to dig the problem, in our organization we don't have Tuning and Diagnostics pack so the only way to dig down the issue was through spreports. Application teams gave us a Good time and Bad time.

So after going through the spreports of good as well as a bad time, we came to know that the most time-consuming wait event was Db Sequential file read. This wait event is IO wait, it means there is some issue related to Disk. Upon thorough checking of disks Using OS Watcher, we saw high Disk IO.

In meeting the Storage team was asked to look into the storage i.e. Disks.  They after investigation told us that two rman cron jobs were taking lots of time and consuming disk IO. After comparing old backup time and recent backup time we saw a huge difference in the backup completion time.

Here we have done the troubleshooting part. Now its time for performance tuning. How to tune Rman Backups so that there are fewer IO.

Steps that were taken by us :


1) We rescheduled cron jobs timing, so that no two backups run at the same time.


2) Second, we enabled Blockchain Tracking in the Database.

Now Question arises what is Blockchain tracking and How to enable that?

What Blockchain Tracking does is, it makes a file in oracle ASM, whose work is to store the information of changed block in the datafiles. All the changed block information is stored in this file and rman doesn't have to read all the changed blocks in all the datafiles, now rman has to read a single file and store that information of changed blocks for the Incremental 1 backup.

Once we enable the Blockchain tracking in the database. The first time Level 0 Incremental backup takes the usual time and stores all the blocks information in the blockchain trace file. Once we have the information of blocks from level 0 backup, we will start getting the fruits of the blockchain tracking file.


Here from now onwards whenever any block in any datafile is updated or changed. That changed block information will get stored in the Blockchain track file and during Incremental level 1 rman backup, rman only have to read this blockchain track file resulting in the reduction of Disk IO.


Now How to enable Blockchain tracking on the database :

alter database enable block change tracking;

Query to check whether Blockchain tracking got enabled or not :

select status, filename from v$block_change_tracking;


Thanks and Enjoy Reading!!