Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
===================================================================
V$BACKUP_PIECE displays information about backup pieces from the control file. Each backup set consists of one or more backup pieces.
Column
|
Datatype
|
Description
|
RECID
|
NUMBER
|
Backup piece record ID
|
STAMP
|
NUMBER
|
Backup piece record stamp
|
SET_STAMP
|
NUMBER
|
Backup set stamp
|
SET_COUNT
|
NUMBER
|
Backup set count
|
PIECE#
|
NUMBER
|
Backup piece number (1-N)
|
COPY#
|
NUMBER
|
Indicates the copy number for backup pieces created with duplex enabled. 1 if the backup piece is not duplexed.
|
DEVICE_TYPE
|
VARCHAR2(17)
|
Type of the device on which the backup piece resides. Set to DISK for backup sets on disk.
See Also: V$BACKUP_DEVICE
|
HANDLE
|
VARCHAR2(513)
|
Backup piece handle identifies the backup piece on restore
|
COMMENTS
|
VARCHAR2(64)
|
Comment returned by the operating system or storage subsystem. Set to NULL for backup pieces on disk. This value is informational only; not needed for restore.
|
MEDIA
|
VARCHAR2(65)
|
Name of the media on which the backup piece resides. This value is informational only; not needed for restore.
|
MEDIA_POOL
|
NUMBER
|
The media pool in which the copy resides. This is the same value that was entered in the POOL operand of the Recovery Manager BACKUP command.
|
CONCUR
|
VARCHAR2(3)
|
(YES | NO) Indicates whether the piece on a media that can be accessed concurrently
|
TAG
|
VARCHAR2(32)
|
Backup piece tag. The tag is specified at backup set level, but stored at piece level.
|
STATUS
|
VARCHAR2(1)
|
Indicates the status of the piece: A (available), D (deleted), or X (expired)
|
START_TIME
|
DATE
|
Starting time
|
COMPLETION_TIME
|
DATE
|
Completion time
|
ELAPSED_SECONDS
|
NUMBER
|
Number of elapsed seconds
|
DELETED
|
VARCHAR2(3)
|
(YES/NO) NO indicates that the file still exists. YES indicates the file no longer exists because it has been deleted.
|
BYTES
|
NUMBER
|
Size of the backup piece (in bytes)
|
IS_RECOVERY_DEST_FILE
|
VARCHAR2(3)
|
Indicates whether the file was created in the flash recovery area (YES) or not (NO)
|
RMAN_STATUS_RECID
|
NUMBER
|
Owning V$RMAN_STATUS record ID
|
RMAN_STATUS_STAMP
|
NUMBER
|
Owning V$RMAN_STATUS record stamp
|
COMPRESSED
|
VARCHAR2(3)
|
Indicates whether the backup piece is compressed (YES) or not (NO)
|
BACKED_BY_VSS
|
VARCHAR2(3)
|
Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is reserved for internal use.
|
Note:
1. Click to view more RMAN related dynamic views.
2. Scripts using V$BACKUP_PIECE
select RECID,SET_STAMP, TAG, STATUS, HANDLE
from v$backup_piece
order by recid desc;
|
select recid, status, device_type, handle, completion_time
from v$backup_piece;
|
|
SELECT bs.recid, bp.piece#, bp.copy#, bp.recid bp_key
, DECODE(bs.controlfile_included
, 'NO', '-'
, bs.controlfile_included) controlfile_included
, TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS') completion_time
, DECODE( status
, 'A', 'Available'
, 'D', 'Deleted'
, 'X', 'Expired') status
, handle handle
FROM v$backup_set bs, v$backup_piece bp
WHERE bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
AND bs.controlfile_included != 'NO'
ORDER BY bs.recid, piece#;
|
-- Available backup pieces contained in the control file
SELECT
bs.recid bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, DECODE( status
, 'A', 'Available'
, 'D', 'Deleted'
, 'X', 'Expired') status
, handle handle
, TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS') start_time
, TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS') completion_time
, bp.elapsed_seconds elapsed_seconds
FROM
v$backup_set bs
, v$backup_piece bp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
ORDER BY
bs.recid
, piece#;
|
-- Available automatic SPFILE files within all available (and expired) backup sets.
SELECT
bs.recid bs_key
, bp.piece# piece#
, bp.copy# copy#
, bp.recid bp_key
, sp.spfile_included spfile_included
, TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS') completion_time
, DECODE( status
, 'A', 'Available'
, 'D', 'Deleted'
, 'X', 'Expired') status
, handle handle
FROM
v$backup_set bs
, v$backup_piece bp
, (select distinct
set_stamp
, set_count
, 'YES' spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bp.status IN ('A', 'X')
AND bs.set_stamp = sp.set_stamp
AND bs.set_count = sp.set_count
ORDER BY
bs.recid
, piece#;
|
Oracle data dictionary views
Last updated: 2009-10-26 Monday
|
