V$BLOCK_CHANGE_TRACKING

V$BLOCK_CHANGE_TRACKING displays the status of block change tracking for the database.

Column	Datatype	Description
STATUS	VARCHAR2(10)	Status of block change tracking in the database: DISABLED - Block change tracking is disabled TRANSITION - Block change tracking is in the process of transitioning between the enabled and disabled states. The TRANSITION state should usually never be observed, because it only exists while enabling or disabling block change tracking. This state might be observed if the instance crashed while enabling or disabling block change tracking, in which case it will be cleaned up automatically the next time that the database is opened. ENABLED - Block change tracking is enabled
FILENAME	VARCHAR2(513)	Name of the block change tracking file for the database
BYTES	NUMBER	Size of the block change tracking file (in bytes)

Note:

1.       The CTWR process is responsible for updating the Block Change Tracking File which is used to optimize incremental RMAN backups.

2.       Oracle 10g offers a new feature called block change tracking, allowing for faster creation of incremental backups. Changes to the database blocks are tracked by a tracking file. When the block change tracking is enabled, Changes to the blocks are logged into the tracking file. During the incremental backup, RMAN reads the tracking file to determine changed blocks to avoid reading every block in a datafile.  

1. Enabling block change tracking: ALTER DATABASE    SQL> alter database enable block change tracking         using file '/u01/oradata/orcl/change_tracking.dat';    SQL> alter database enable block change tracking         using file '/u01/oradata/orcl/change_tracking.dat' resuse;    SQL> alter database enable block change tracking;         --with DB_CREATE_FILE_DEST set 2. Disabling block change tracking: ALTER DATABASE    SQL> alter database disable block change tracking. 3. Determining if block change tracking is enabled Information about block change tracking and the tracking file is stored in the controlfile and can be accessed using the V$BLOCK_CHANGE_TRACKING view: SQL> select status, filename      from   v$block_change_tracking; --This view always contains ONE record. --If STATUS=ENABLED, then FILENAME=name_of_file, and BYTES=size_of_file. --If STATUS=DISABLED, the other two columns are null. 4. RMAN and V$BACKUP_DATAFILE: The value of column USED_CHANGE_TRACKING of YES for an incremenat backup level > 0 means that RMAN used the tracking file to speed up the incremental backup. select file#,        avg(datafile_blocks),        avg(blocks_read),        avg(blocks_read/datafile_blocks) * 100 as “% read for backup” from   v$backup_datafile where  incremental_level > 0 and    used_change_tracking = 'YES ' group  by file# order  by file#; 5. Other information --Whenever block change tracking is enabled or disabled, a message is logged into   alert.log to indicate the creation or removal of the tracking file. --Tracking file can be renamed using 'alter database rename file ' comamnd. --Tracking file is a binary file. --RMAN does not support the backup and recovery of the tracking file.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-11-10 Tuesday

V$BACKUP_DATAFILE

V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.

Column	Datatype	Description
RECID	NUMBER	Backup datafile record ID
STAMP	NUMBER	Backup datafile record stamp
SET_STAMP	NUMBER	Backup set stamp
SET_COUNT	NUMBER	Backup set count
FILE#	NUMBER	Datafile number; set to 0 for control file
CREATION_CHANGE#	NUMBER	Creation system change number (SCN) of the datafile
CREATION_TIME	DATE	Creation timestamp of the datafile
RESETLOGS_CHANGE#	NUMBER	Resetlogs system change number (SCN) of the datafile when it was backed up
RESETLOGS_TIME	DATE	Resetlogs timestamp of the datafile when it was backed up
INCREMENTAL_LEVEL	NUMBER	(0-4) incremental backup level
INCREMENTAL_CHANGE#	NUMBER	All blocks changed after the incremental change number is included in this backup; set to 0 for a full backup
CHECKPOINT_CHANGE#	NUMBER	All changes up to the checkpoint change number are included in this backup
CHECKPOINT_TIME	DATE	Timestamp of the checkpoint
ABSOLUTE_FUZZY_CHANGE#	NUMBER	Highest change number in this backup
MARKED_CORRUPT	NUMBER	Number of blocks marked corrupt
MEDIA_CORRUPT	NUMBER	Number of blocks media corrupt
LOGICALLY_CORRUPT	NUMBER	Number of blocks logically corrupt
DATAFILE_BLOCKS	NUMBER	Size of the datafile in blocks at backup time. This value is also the number of blocks taken by the datafile restarted from this backup.
BLOCKS	NUMBER	Size of the backup datafile (in blocks). Unused blocks are not copied to the backup.
BLOCK_SIZE	NUMBER	Block size
OLDEST_OFFLINE_RANGE	NUMBER	RECID of the oldest offline range record in this backup control file. 0 for datafile backups.
COMPLETION_TIME	DATE	Time completed
CONTROLFILE_TYPE	VARCHAR2(1)	B - Normal copies S - Standby copies
USED_CHANGE_TRACKING	VARCHAR2(3)	Indicates whether change tracking data was used to accelerate this incremental backup (YES) or whether change tracking data was not used (NO)
BLOCKS_READ	NUMBER	Number of blocks that were scanned while taking this backup. If this was an incremental backup, and change tracking was used to optimize the backup, then the value of this column will be smaller than DATAFILE_BLOCKS. Otherwise, the value of this column will be the same as DATAFILE_BLOCKS. Even when change tracking data is used, the value of this column may be larger than BLOCKS, because the data read by change tracking is further refined during the process of creating an incremental backup.
USED_OPTIMIZATION	VARCHAR2(3)	Whether or not backup optimization was applied
FOREIGN_DBID	NUMBER	Foreign DBID of the database from which this datafile was transported. The value is 0 if the file backed up is not a foreign database file.
PLUGGED_READONLY	VARCHAR2(3)	YES if this is a backup of a transported read-only foreign file; otherwise NO.
PLUGIN_CHANGE#	NUMBER	SCN at which the foreign datafile was transported into the database. The value is 0 if this file is not a foreign database file.
PLUGIN_RESETLOGS_CHANGE#	NUMBER	The SCN of the RESETLOGS operation for the incarnation into which this foreign file was transported. The value is 0 if this file is not a foreign database file.
PLUGIN_RESETLOGS_TIME	DATE	The time of the RESETLOGS operation for the incarnation into which this foreign file was transported. The value is 0 if this file is not a foreign database file.
SECTION_SIZE	NUMBER	Specifies the number of blocks in each section of a multisection backup. Value is 0 for whole file backups.

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-11-10 Tuesday

V$INDEXED_FIXED_COLUMN

V$INDEXED_FIXED_COLUMN displays the columns in dynamic performance tables that are indexed (X$ tables). The X$ tables can change without notice. Use this view only to write queries against fixed views (V$ views) more efficiently.

Column	Datatype	Description
TABLE_NAME	VARCHAR2(30)	Name of the dynamic performance table that is indexed
INDEX_NUMBER	NUMBER	Number that distinguishes to which index a column belongs
COLUMN_NAME	VARCHAR2(30)	Name of the column that is being indexed
COLUMN_POSITION	NUMBER	Position of the column in the index key (this is mostly relevant for multicolumn indexes)

Note:

1.       Scripts using V$INDEXED_FIXED_COLUMN

select table_name, column_name, index_number from   V$INDEXED_FIXED_COLUMN order by table_name;

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-11-10 Tuesday