Oracle Block Change Tracking

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any kind of damage.

RMAN block change tracking

With Oracle 10gr1 Enterprise Edition Oracle introduce Block change tracking feature which use to FAST / Speed up RMAN Incremental Backup.

What is BLOCK CHANGE TRACKING?

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo. When a commit is issued, the BCT information is copied to a shared area in Large Pool called 'CTWR dba buffer'. At the checkpoint, a new background process, Change Tracking Writer (CTWR), writes the information from the buffer to the change-tracking file. If contention for space in the CTWR dba buffer occurs, a wait event called, 'Block Change Tracking Buffer Space' is recorded. Several causes for this wait event are poor I/O performance on the disk where the change-tracking file resides, or the CTWR dba buffer is too small to record the number of concurrent block changes.
   
There is a hidden parameter that can increase or decrease the size of the CTWR dba buffer. However, you should always check with Oracle Support before using a hidden parameter value as Oracle may not support these types of changes.

_bct_public_dba_buffer_size = total size of all public change tracking dba buffers.
   
    Source: Oracle documentation

    1. Enable Block Change tracking and set location

    Before enabling we must to set/specify block change tracking location.There is two way to specify block change tracking location1. using DB_CREATE_FILE_DEST parameter which use OMF file name.

    SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;

    SQL> alter database enable block change tracking;

    2. Manually specify location for the block change tracking

    SQL>alter database enable block change tracking using file 'location' reuse;

    2. Disable block change tracking

    SQL> alter database disable block change tracking;

    NOTE: BCT files deleted automatically by oracle when BCT is disabled.

    3. Reset the location of Block change tracking file

    There are two options for this

    1. shutdown the database and set the new location

    SQL> shutdown database

    SQL> move BCT file to new location

    SQL> startup mount

    SQL> alter database rename file 'old_location' TO 'new_location';

    SQL> alter database open;

    or


    2. disable the block change tracking / set the new location / enable BCT

    SQL> alter database DISABLE block change tracking;

    SQL> alter database enable block change tracking using FILE 'NEW_LOCATION' REUSE;

    After that RMAN use new location for the BCT.

    4. Checking the information about block change tracking enable or disable

    Check v$BLOCK_CHANGE_TRACKING view
    SELECT status FROM v$block_change_tracking;

    5. BCT file is important for restore and recovery of database.

    Answer: No, it is doesn't require for Database Recovery of database

    6. What happen if BCT file is lost and corrupted.

    Answer: That is very interesting case here, suppose oracle found if BCT file is corrupted or missing then oracle will automatically recreate new BCT file.

    Let see some practial view...

    1. Enable BCT

    SQL> alter database enable block change tracking using file '/ora/database/bct.dbf';
    Database altered.

    2. Check the status and default size.

    SQL> select * from v$block_change_tracking;
    STATUS FILENAME BYTES

    ---------- ---------------------------------------- ----------

    ENABLED /ora/database/bct.dbf 11599872

    NOTE: Filesize created is 11 mb by default.

    3. Now check alertsid log file what entries we found.

    Thu Feb 28 20:06:53 2013

    alter database enable block change tracking using file '/ora/database/bct.dbf'

    Block change tracking file is current.

    Starting background process CTWR

    Thu Feb 28 20:08:13 2013

    CTWR started with pid=20, OS id=464

    Block change tracking service is active.

    Completed: alter database enable block change tracking using file '/ora/database/bct.dbf'


    4. Disable BCT

    SQL> alter database disable block change tracking;
    Database altered.

    SQL> select * from v$block_change_tracking;
    STATUS FILENAME BYTES

    ---------- ---------------------------------------- ----------

    DISABLED

    5. Check alertsid log file

    Thu Feb 28 20:08:51 2013

    alter database disable block change tracking

    Thu Feb 28 20:09:09 2013

    Block change tracking service stopping.

    Stopping background process CTWR

    Deleted file /ora/database/bct.dbf

    Completed: alter database disable block change tracking

    6. Suppose BCT file lost or missing then what oracle will do...

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> host rm /ora/database/bct.dbf

    SQL> startup

    ORACLE instance started.
    [output cut]

    Database mounted.

    Database opened.

    Now check the alertlog file for more information about how oracle create new BCT file when missing or lost.

    Thu Feb 28 20:16:24 2013

    ALTER DATABASE OPEN

    CHANGE TRACKING is enabled for this database, but the

    change tracking file can not be found. Recreating the file.

    Change tracking file recreated.

    Block change tracking file is current.
  

BUG on AIX platform -

Database will become offline when the following error reports in alert log -

ORA-00600: internal error code, arguments: [krccaub_2], [10], [1102], [], [], [], [], []
Sat Jun 01 09:49:49 BST 2013
CTWR: terminating instance due to error 487
Termination issued to instance processes. Waiting for the processes to exit
Instance terminated by CTWR, pid = xxxxxxxxx

Immediate startup of database will resolve it but the cause of issue is because of BUG hit and below are the permanent fix details:

Cause of Issue -

Search String: ora-00600 [krccaub_2]

ORA-600 [krccaub_2] Locator is not for the correct file [DOC ID 1268896.1]

DESCRIPTION:

When trying to apply one update to a buffer it is found that the locator is not for the correct file.

FUNCTIONALITY:
Kernel Recovery block Change tracking Cache manager - Apply one update to the buffer

IMPACT:

INSTANCE FAILURE
NON CORRUPTIVE - No underlying data corruption.

Bug 7312717 - ORA-600 [krccaub_2] can occur with block change tracking [DOC ID 7312717.8]

Versions confirmed as being affected

11.2.0.2
10.2.0.5
10.2.0.3

Workaround :
Disable block change tracking.

Permanent FIX:
To apply the BUG fix patch.
This patchset is one-off patch and is not included in all PSU patches:

The bug is fixed in the following releases/patchset/PSU:
12.1 (Future Release)
11.2.0.3 (Server Patch Set)
11.2.0.2.8 Patch Set Update