Set up a friendly environment to share my understanding and ideas about Oracle / Oracle Spatial database administration, ESRI ArcSDE Geodatabase administration and UNIX (Solaris) operating system.
V$BACKUP_DEVICE displays information about supported backup devices. If a device type does not support named devices, then one row with the device type and a null device name is returned for that device type. If a device type supports named devices then one row is returned for each available device of that type. The special device type DISK is not returned by this view because it is always available.
V$RECOVERY_AREA_USAGE displays usage information about recovery areas.
Column
Datatype
Description
FILE_TYPE
VARCHAR2(20)
File type:
CONTROL FILE
REDO LOG
ARCHIVED LOG
BACKUP PIECE
IMAGE COPY
FLASHBACK LOG
REMOTE ARCHIVED LOG
PERCENT_SPACE_USED
NUMBER
Percent of the recovery area that is in use
PERCENT_SPACE_RECLAIMABLE
NUMBER
Percent of the recovery area that is reclaimable
NUMBER_OF_FILES
NUMBER
Number of files in the recovery area
Note:
1.Replace V$FLASH_RECOVERY_AREA_USAGEin 11g.
2.Query the V$RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files. Also, you can determine how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape. For example, enter the following query (sample output included):
V$BACKUP_CORRUPTION displays information about corrupt block ranges in datafile backups from the control file. Note that corruptions are not tolerated in the control file and archived redo log backups.
Column
Datatype
Description
RECID
NUMBER
Backup corruption record ID
STAMP
NUMBER
Backup corruption record stamp
SET_STAMP
NUMBER
Backup set stamp
SET_COUNT
NUMBER
Backup set count
PIECE#
NUMBER
backup piece that contains this corrupt block
FILE#
NUMBER
Absolute file number of the datafile that contains the corrupt blocks
BLOCK#
NUMBER
Block number of the first corrupt block in the range of corrupted blocks
BLOCKS
NUMBER
Number of corrupted blocks found starting with BLOCK#
CORRUPTION_CHANGE#
NUMBER
Change number at which the logical corruption was detected. Set to 0 to indicate media corruption.
MARKED_CORRUPT
VARCHAR2(3)
Indicates whether this corruption was not previously detected by the Oracle Database (YES) or the Oracle Database had already discovered this corrupt block and marked it as corrupt (NO). Note that when a corrupt block is encountered in a backup, and was not already marked corrupt by the Oracle Database, then the backup process does not mark the block as corrupt in the production datafile. Thus, this field may be YES for the same block in more than one backup set.
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, andV$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_CORRUPTIONview 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_CORRUPTIONview 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.
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.
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, andV$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_CORRUPTIONview 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_CORRUPTIONview 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.
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.
I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this BLOG are provided "as is" with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.
