Skip to main content

Implement Block Change Tracking in Oracle Database



What is Block Change Tracking?

Block Change Tracking is an Oracle Database feature used with RMAN incremental backups. It tracks the physical locations of changed blocks in the database and stores this information in a separate file called the Block Change Tracking file. Oracle documentation states that this tracking information is maintained in a separate file and is used for tasks such as improving incremental backup performance.

Without BCT, RMAN may need to scan the datafiles to determine which blocks have changed since the previous backup. With BCT enabled, RMAN can directly read the change tracking file and identify changed blocks more efficiently.

-----

Login to SQL

sqlplus / as sysdba


         Set lin 400

SELECT status, filename, bytes

FROM   v$block_change_tracking;


CHECK OMF

SHOW PARAMETER db_create_file_dest; 


ENABLE BCT (BLOCK CHAIN TRACKING)

Common method:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; 

Add to a file path:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/oradata/DBNAME/bct_file.ctf'; 

Add to ASM

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA'; 

DISABLE BCT

I have also encountered issues when RMAN restoring and opening database where i used to occur the error that it is unable to find the file for BCT. in such case senario you can drop the database data file with regards to BCT using below command.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 



Advantage of enabling the BCT.

- Faster RMAN incremental backups

BCT helps RMAN quickly identify changed blocks instead of scanning entire datafiles.

- Reduced backup window

Since RMAN reads only the changed block information, incremental backups complete faster, which is useful for large databases.

- Lower I/O during backups

BCT reduces unnecessary datafile scanning, which helps reduce disk I/O and improves overall backup efficiency. 

Labels:
Location: Colombo, Sri Lanka

Comments

Post a Comment

Popular posts from this blog

Warning: long redo log write elapsed times detected, the LG* process tracefiles have more details

Warning: long redo log write elapsed times detected, the LG* process tracefiles have more details This warning means LGWR / LGnn background processes are taking too long to write redo to the online redo logs. It is usually related to one of these: - Slow storage / high I/O latency on redo log disks - Redo logs placed on busy disks together with datafiles, FRA, archive logs, backups, or OS files - Too many commits from the application, causing frequent LGWR flushes - Redo log size too small, causing frequent log switches - CPU scheduling issue, where LGWR is not getting CPU quickly - Data Guard synchronous transport delay, if using SYNC/AFFIRM - In newer 19c RU versions, especially around 19.28, this warning can appear more visibly because of diagnostic changes, so first confirm whether there is a real performance impact before changing anything. - Oracle’s own wait-event documentation says log file sync is the foreground wait for redo write confirmation after commit, and log file paral...
Post a Comment
Read more

ORACLE AUDIT VAULT - UPDATE FROM 20.14 to 20.17.

 We can update the Oracle Audit Vault using the same configuration and we require to follow the below commands. firstly you need to download the Oracle Audit Vault patches. -- first need to update the new network environment using below commands.   vi /usr/local/dbfw/etc/dbfw.conf    - change GATEWAY to 192.168.128.255 and host ip to 192.168.128.50  - remove syslog ip from both records.  SYSLOGDEST="192.168.10.1" SYSLOGDEST="192.168.10.1:4444"  /usr/local/dbfw/bin/priv/configure-networking you can check the current status of the below command. [oracle@auditvault-testvm ~]$ rpm -qa | grep -i avs  [root@auditvault-testvm bin]# rpm -qa | grep -i avs avs-database-2-19.27.0.0.0-0.x86_64 avs-database-19.27.0.0.0-0.x86_64 avs-bootstrap-20.17.0.0.0-0_260303.0015.noarch bootstrap-avs-dependencies-20.17.0.0.0-0_260303.0015.noarch avs-dbsat-20.14.0.0.0-0_250520.0400.x86_64 bootstrap-avs-application-20.14.0.0.0-0_250520.0400.noarch avs-apex-20.14.0.0.0-0_2505...
Post a Comment
Read more

Convert TIMESTAMP to SCN and SCN to TIMESTAMP in Oracle

  Convert TIMESTAMP to SCN and SCN to TIMESTAMP in Oracle In many recovery scenario we need to know our SCN and timestamps. We can convert this by using the following function SCN_TO_TIMESTAMP TIMESTAMP_TO_SCN We can use this function with help of dual functions. Example of using this function as follows: 1. Convert the SCN to Timestamp SQL> select scn_to_timestamp(2011955) from dual; SCN_TO_TIMESTAMP(2011955) ----------------------------------------------------- 05-SEP-18 12.46.20.000000000 PM 2. Convert the Timestamp to SCN SQL> select timestamp_to_scn(to_timestamp('05-09-2018 12:46:21','dd-mm-yyyy hh24:mi:ss')) scn from dual; SCN ---------- 2011955
Post a Comment
Read more