V$TABLESPACE

V$TABLESPACE displays tablespace information from the control file.

Column	Datatype	Description
TS#	NUMBER	Tablespace number
NAME	VARCHAR2(30)	Tablespace name
INCLUDED_IN_DATABASE_BACKUP	VARCHAR2(3)	Indicates whether the tablespace is included in full database backups using the BACKUP DATABASE RMAN command (YES) or not (NO); NO only if the CONFIGURE EXCLUDE RMAN command was used for this tablespace
BIGFILE	VARCHAR2(3)	Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO)
FLASHBACK_ON	VARCHAR2(3)	Indicates whether the tablespace participates in FLASHBACK DATABASE operations (YES) or not (NO)
ENCRYPT_IN_BACKUP	VARCHAR2(3)	Indicates whether encryption is turned ON or off at the tablespace level: ON - Encryption is turned ON at the tablespace level OFF - Encryption is turned OFF at the tablespace level NULL - Encryption is neither explicitly turned on nor off at the tablespace level (default or when cleared)

Last updated: Tuesday, September 22, 2009

Oracle data dictionary views

Oracle dynamic performance views

RMAN dynamic views

The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:

·         V$ARCHIVED_LOG: show which archives have been created, backed up, and cleared in the database.

·         V$BACKUP_CORRUPTION: show which blocks have been found to be corrupt during a backup of a backup set.

·         V$BACKUP_DATAFILE: useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile.

·         V$BACKUP_DEVICE: display information about supported backup devices. DISK is not returned because it is always available.

·         V$BACKUP_FILES: display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPY commands.

·         V$BACKUP_PIECE: show backup pieces created for backup sets.

·         V$BACKUP_REDOLOG: show archived logs stored in backup sets.

·         V$BACKUP_SET: show backup sets that have been created.

·         V$BACKUP_SPFILE: display information about server parameter files in backup sets.

·         V$COPY_CORRUPTION: show which blocks have been found to be corrupt during an image copy.

·         V$DATABASE_BLOCK_CORRUPTION: list database blocks marked as corrupt during the most recent RMAN backup.

·         V$RMAN_CONFIGURATION: list information about RMAN persistent configuration settings.

·         V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE: proxy settings for RMAN.

·         V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO: backup performance statistics

V$BACKUP_DEVICE

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.

Column	Datatype	Description
DEVICE_TYPE	VARCHAR2(17)	Type of the backup device
DEVICE_NAME	VARCHAR2(513)	Name of the backup device

Note:

1.       Oracle errors related with V$BACKUP_DEVICE

Error:   ORA-27001 Text:    unsupported device type --------------------------------------------------------------------------- Cause:   the specified device type is NOT supported on this platform Action:  check V$BACKUP_DEVICE for supported device types

Error:   ORA-4350 Text:    unsupported device type --------------------------------------------------------------------------- Cause:   the specified device type is unsupported on this platform Action:  check V$BACKUP_DEVICE for supported device types

Error:   ORA-27020 Text:    named devices not supported --------------------------------------------------------------------------- Cause:   the platform or the specified device type does not support named devices Action:  do not specify device name or use a device type that supports named devices. Use           V$BACKUP_DEVICE view to see what device types and names (if any) are available.

2.       select * from v$backup_device;

SQL>  SELECT * FROM V$BACKUP_DEVICE; DEVICE_TYPE       DEVICE_NAM ----------------- ---------- SBT_TAPE

Oracle data dictionary views

Oracle dynamic performance views

Last updated: Monday, September 14, 2009

V$RECOVERY_AREA_USAGE

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_USAGE in 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):

SELECT * FROM   V$RECOVERY_AREA_USAGE; FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE                   0                         0               0 ONLINELOG                     2                         0              22 ARCHIVELOG                 4.05                      2.01              31 BACKUPPIECE                3.94                      3.86               8 IMAGECOPY                 15.64                     10.43              66 FLASHBACKLOG                .08                         0               1

Oracle data dictionary views

Oracle dynamic performance views

Last updated: Monday, September 14, 2009

V$BACKUP_CORRUPTION

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, 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