How to Enable & Disable Block Change Tracking (BCT) in Database Oracle

DBsGuru 11 mins0
()

How to Enable & Disable Block Change Tracking in Database Oracle

In this article, we will demonstrate How to Enable & Disable Block Change Tracking in Database Oracle. Block change tracking was introduced in version 10g. Prior to this feature, RMAN had to scan all those blocks which were not changed since the base incremental backup in another sense incremental backup was high as a full database backup.

Block change tracking provides how to spot the blocks required for backup without scanning the entire datafiles. then RMAN need only read blocks that are really required for this incremental backup i.e RMAN uses this file to spot the changed blocks that require to be backed up. Check Tracking Writer (CTWR) is a background process that is responsible to capture changed blocks and write in the Block Change Tracking File.

To enable Block Change Tracking in the database, It should be either in OPEN or MOUNT state. BCT file can be created option 01 in the defined location of parameter DB_CREATE_FILE_DEST as Oracle Managed Files (OMF) which is similar to database maintains active database files such as datafiles, control files, and online redolog files. In option 02 where you can create a BCT file in a platform-specific location generally in ORACLE HOME or somewhere in the desire location since it’s a lightweight file.


Option 01: Enable BCT Using OMF


1.1: Validate the status of BCT: Validate the status of block change tracking in the database.

COL FILENAME FOR A65

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                     BYTES     CON_ID
---------- ------------------------------------------------------- ---------- ----------
DISABLED

LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$


1.2 Validate the status of OMF: Validate the parameter DB_CREATE_FILE_DEST.

LABDBDUP> SHOW PARAMETER DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string


1.3 Enable OMF: Set the value of parameter DB_CREATE_FILE_DEST.

LABDBDUP> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata/LABDBDUP' SCOPE = BOTH;

System altered.

LABDBDUP> SHOW PARAMETER DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oradata/LABDBDUP


1.4: ENable BCT: To enable BCT in OMF, just need to execute the below command and a BCT file will be created under the location directory DB_CREATE_FILE_DEST and a responsible background process will be started, refer to section 1.5.

LABDBDUP> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.


1.5: Validate the status of BCT: Validate the status of BCT, BCT’s file, and background process.

In Database:

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                               BYTES     CON_ID
---------- ----------------------------------------------------------------- ---------- ----------
ENABLED    /oradata/LABDBDUP/LABDBDUP/changetracking/o1_mf_jo61cdqr_.chg       11599872          0

LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Background Process:

[oracle@DBsGuruN3 changetracking]$ ps -eaf | grep -i ctwr
oracle      6986       1  0 17:37 ?        00:00:00 ora_ctwr_labdbdup

Alert log when enable BCT:

2021-09-28T17:37:08.754872+05:30
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
2021-09-28T17:37:08.779087+05:30
Block change tracking file is current.
starting change tracking :1
Starting background process CTWR
2021-09-28T17:37:08.877673+05:30
CTWR started with pid=69, OS id=6986
2021-09-28T17:37:08.957765+05:30
Block change tracking service is active.
Completed: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING


Option 02: Enable BCT in Desire Location


2.1: Validate the status of BCT: Validate the status of block change tracking in the database.

COL FILENAME FOR A65

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                     BYTES     CON_ID
---------- ------------------------------------------------------- ---------- ----------
DISABLED

LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$


2.2: Create a directory: Create a directory on the desire location for the BCT file.

[oracle@DBsGuruN3 ~]$ mkdir -p /oradata/LABDBDUP/BCT
[oracle@DBsGuruN3 ~]$ ls -ld /oradata/LABDBDUP/BCT
drwxr-xr-x. 2 oracle oinstall 6 Sep 28 17:42 /oradata/LABDBDUP/BCT
[oracle@DBsGuruN3 ~]$ cd /oradata/LABDBDUP/BCT
[oracle@DBsGuruN3 BCT]$ pwd
/oradata/LABDBDUP/BCT


2.3: Enable BCT: To enable BCT using platform-specific directory i.e on desire location. After enabling BCT responsible background process started, refer to next section 2.4.

LABDBDUP> alter database enable block change tracking using file '/oradata/LABDBDUP/BCT/labdbdup_bct.chg';

Database altered.


2.4: Validate the status of BCT: Validate the status of BCT, BCT’s file, and background process.

In Database:

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                               BYTES     CON_ID
---------- ----------------------------------------------------------------- ---------- ----------
ENABLED    /oradata/LABDBDUP/BCT/labdbdup_bct.chg                              11599872          0

LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Alert log when enable BCT:

2021-09-28T17:47:21.291143+05:30
Block change tracking file is current.
starting change tracking :1
Starting background process CTWR
2021-09-28T17:47:21.405388+05:30
CTWR started with pid=69, OS id=7214
2021-09-28T17:47:21.467923+05:30
Block change tracking service is active.
Completed: alter database enable block change tracking using file '/oradata/LABDBDUP/BCT/labdbdup_bct.chg'

Background Process:

[oracle@DBsGuruN3 BCT]$ ps -eaf | grep -i ctw
oracle      7214       1  0 17:47 ?        00:00:00 ora_ctwr_labdbdup


Disable BCT


3.1: Validate the status of BCT: Validate the status of block change tracking in the database.

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                               BYTES     CON_ID
---------- ----------------------------------------------------------------- ---------- ----------
ENABLED    /oradata/LABDBDUP/LABDBDUP/changetracking/o1_mf_jo61cdqr_.chg       11599872          0
LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$


3.2: Disable BCT: After disabling BCT in the database BCT file removed, refer to next section 3.3.

LABDBDUP> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.


3.3: Validate the status of BCT: Validate the status of BCT where no BCT file in below O/P.

In Database:

LABDBDUP> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$
STATUS     FILENAME                                                               BYTES     CON_ID
---------- ----------------------------------------------------------------- ---------- ----------
DISABLED

LABDBDUP>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Alert log after disable BCT:

2021-09-28T17:45:50.785031+05:30
stopping change tracking
2021-09-28T17:45:50.785165+05:30
Block change tracking service stopping.
Stopping background process CTWR
2021-09-28T17:45:51.824648+05:30
Deleted Oracle managed file /oradata/LABDBDUP/LABDBDUP/changetracking/o1_mf_jo61cdqr_.chg
Completed: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING


Important Tips:


**One BCT file per database only.
**If you want to change the location of the BCT file, first disable then enable with desire file/location applicable to enable BCT only.
** in the RAC database, BCT file/location should be on shared storage among all instances/nodes.
**BCT file also can be enabled on Active Dataguard (Standby database).

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, ,




Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!

Related Articles