V$COPY_CORRUPTION

V$COPY_CORRUPTION displays information about datafile copy corruptions from the control file.

Column	Datatype	Description
RECID	NUMBER	Copy corruption record ID
STAMP	NUMBER	Copy corruption record stamp
COPY_RECID	NUMBER	Datafile copy record ID
COPY_STAMP	NUMBER	Datafile copy record stamp
FILE#	NUMBER	Datafile number
BLOCK#	NUMBER	First block of the corrupted range
BLOCKS	NUMBER	Number of contiguous blocks in the corrupted range
CORRUPTION_CHANGE#	NUMBER	Change number at which the logical corruption was detected. Set to 0 to indicate media corruption.
MARKED_CORRUPT	VARCHAR2(3)	(YES | NO) If set to YES the blocks were not marked corrupted in the datafile, but were detected and marked as corrupted while making the datafile copy
CORRUPTION_TYPE	VARCHAR2(9)	Type of block corruption in the datafile: ALL ZERO - Block header on disk contained only zeros. The block may be valid if it was never filled and if it is in an Oracle7 file. The buffer will be reformatted to the Oracle8 standard for an empty block. FRACTURED - Block header looks reasonable, but the front and back of the block are different versions. CHECKSUM - optional check value shows that the block is not self-consistent. It is impossible to determine exactly why the check value fails, but it probably fails because sectors in the middle of the block are from different versions. CORRUPT - Block is wrongly identified or is not a data block (for example, the data block address is missing) LOGICAL - Specifies the range is for logically corrupt blocks. CORRUPTION_CHANGE# will have a nonzero value.

Note:

1.       RMAN is able to detect block corruption. Three views server this purpose: V$BACKUP_CORRUPTION, V$COPY_CORRUPTION, and V$DATABASE_BLOCK_CORRUPTION.

2.       RMAN identifies corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION. If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions. You can repair corruptions using block media recovery.  After a corrupt block is repaired, the row identifying this block is deleted from the view.

3.       The V$BACKUP_CORRUPTION view shows corrupted blocks discovered during an RMAN backup. But once the blocks have been fixed, this view is not updated. The corrupt backup sets information in V$BACKUP_CORRUPTION are represented by SET_STAMP, and SET_COUNT columns.

4.       Useful Join Columns: FILE# - join to V$DATAFILE.FILE#.

5.        In 10.2.0.4, the v$database_block_corruption view is based on v$copy_corruption and v$backup_corruption. The rows for dropped datafile doesn't go away until the datafile# is reused by database and a backup of that file# is taken. This issue was fixed in 11g. To workaround the problem in 10.2.0.4, you will have to clear the v$backup_corruption and v$copy_corruption view on target database.

SQL> execute dbms_backup_restore.resetCfileSection(17); /** clear v$backup_corruption
SQL> execute dbms_backup_restore.resetCfileSection(18); /**clear v$copy_corruption

Oracle data dictionary views

Oracle dynamic performance views

Last updated: September 9, 2009