--检查某个时间段备份情况
SELECT
START_TIME,
END_TIME,
OUTPUT_DEVICE_TYPE,
STATUS,
ELAPSED_SECONDS,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY
FROM
V$RMAN_BACKUP_JOB_DETAILS
WHERE
START_TIME >= trunc( SYSDATE ) - 1
ORDER BY
START_TIME DESC;
--查看所有备份集详细信息
SELECT
A.RECID "BACKUP SET",
A.SET_STAMP,
DECODE(
B.INCREMENTAL_LEVEL,
'',
DECODE( BACKUP_TYPE, 'L', 'Archivelog', 'Full' ),
1,
'Incr-1级',
0,
'Incr-0级',
B.INCREMENTAL_LEVEL
) "Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE( A.STATUS, 'A', 'AVAILABLE', 'D', 'DELETED', 'X', 'EXPIRED', 'ERROR' ) "STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES / 1024 / 1024 / 1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM
GV$BACKUP_PIECE A,
GV$BACKUP_SET B
WHERE
A.SET_STAMP = B.SET_STAMP
AND A.DELETED = 'NO'
ORDER BY
A.COMPLETION_TIME DESC;
--查看所有备份集详细信息
SELECT A.RECID "BACKUP SET",A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1级',
0, 'Incr-0级',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', 'EXPIRED',
'ERROR')
"STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES/1024/1024/1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
--查找某个备份集中包含数据文件
SELECT DISTINCT c.file#,
A.SET_STAMP,
D.NAME,
C.CHECKPOINT_CHANGE#,
C.CHECKPOINT_TIME
FROM V$BACKUP_DATAFILE C,
V$BACKUP_PIECE A,
V$DATAFILE D
WHERE A.SET_STAMP = C.SET_STAMP
AND D.FILE# = C.FILE#
AND A.DELETED='NO'
and a.START_TIME>trunc(sysdate)
-- AND c.set_stamp='999651695'
ORDER BY C.FILE#;
--查询某个备份集中控制文件
SELECT DISTINCT
A.SET_STAMP,
D.NAME,
C.CHECKPOINT_CHANGE#,
C.CHECKPOINT_TIME
FROM
V$BACKUP_DATAFILE C,
V$BACKUP_PIECE A,
V$CONTROLFILE D
WHERE
A.SET_STAMP = C.SET_STAMP
AND C.FILE# = 0
AND A.DELETED = 'NO'
AND a."START_TIME" > trunc( SYSDATE )
-- AND C.SET_STAMP = '999651780';
--查看某个备份集中归档日志
SELECT DISTINCT
B.SET_STAMP,
B.THREAD#,
B.SEQUENCE#,
B.FIRST_TIME,
B.FIRST_CHANGE#,
B.NEXT_TIME,
B.NEXT_CHANGE#
FROM
V$BACKUP_REDOLOG B,
V$BACKUP_PIECE A
WHERE
A.SET_STAMP = B.SET_STAMP
AND A.DELETED = 'NO'
AND a.START_TIME > trunc( SYSDATE )
--AND B.SET_STAMP = '999651783'
ORDER BY
THREAD#,
SEQUENCE#;
