Enable block change tracking:
SQL> alter database enable block change tracking using
file 'C:\oracle\product\10.2.0\flash_recovery_area\DBA20\Block Track\block_change_log.log';
Database altered.
The REUSE option tells Oracle to overwrite any existing file with the specified name.
SQL> alter database enable block change tracking using
file 'C:\oracle\product\10.2.0\flash_recovery_area\DBA20\Block Track\block_change_log.log' reuse;
Database altered.
Now that we have enabled block change tracking a new background process called CTWR (change track writer) is started.
To disable block change tracking:
SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> SELECT * FROM V$BLOCK_CHANGE_TRACKING;
Another Procedure:
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------------------------------------------------------------------------
db_create_file_dest string
SQL> alter system set db_create_file_dest='C:\oracle\product\10.2.0\flash_recovery_area\DBA20\Block Track';
System altered.
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string C:\oracle\product\10.2.0\flash_recovery_area\DBA20\Block TraCk
SQL> alter database enable block change tracking;
Database altered.
To determine whether change tracking is enabled:
SQL> COL STATUS FORMAT A8
SQL> COL FILENAME FORMAT A60
SQL> SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME
-------- ------------------------------------------------------------
ENABLED C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DBA20\BLOCK TRACK\BLOCK_CHANGE_LOG.LOG
Size of the Block Change Tracking File:
The size of the change tracking file depends on the size of the database and not the frequency of updates. Oracle states that the size of the block tracking file is 1/30,000 the size of all the database data blocks being tracked by Change Tracking. Oracle also states that the file is created in 10 MB increments. For databases up to one terabyte, the size of the change tracking file will be 10MB, 2 terabyte databases will require 20MB and so on.