通过sql查询rman备份信息

通过sql查询rman备份信息

查看所有备份集

 1 SELECT A.RECID "BACKUP SET",
 2          A.SET_STAMP,
 3          DECODE (B.INCREMENTAL_LEVEL,
 4                  '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
 5                  1, 'Incr-1级',
 6                  0, 'Incr-0级',
 7                  B.INCREMENTAL_LEVEL)
 8             "Type LV",
 9          B.CONTROLFILE_INCLUDED "包含CTL",
10          DECODE (A.STATUS,
11                  'A', 'AVAILABLE',
12                  'D', 'DELETED',
13                  'X', 'EXPIRED',
14                  'ERROR')
15             "STATUS",
16          A.DEVICE_TYPE "Device Type",
17          A.START_TIME "Start Time",
18          A.COMPLETION_TIME "Completion Time",
19          A.ELAPSED_SECONDS "Elapsed Seconds",
20          --a.BYTES/1024/1024/1024 "大小(G)",
21          --a.COMPRESSED,
22          A.TAG "Tag",
23          A.HANDLE "Path"
24     FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
25    WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' and  a.set_count = b.set_count
26 ORDER BY A.COMPLETION_TIME DESC;

查找某个备份集中包含数据文件

1 SELECT distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
2   FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
3  WHERE A.SET_STAMP = C.SET_STAMP
4 AND D.FILE# = C.FILE#
5 AND A.DELETED='NO'
6 AND c.set_stamp=&set_stamp
7 ORDER BY C.FILE#;

查询某个备份集中控制文件

1 SELECT DISTINCT A.SET_STAMP,
2                 D.NAME,
3                 C.CHECKPOINT_CHANGE#,
4                 C.CHECKPOINT_TIME
5   FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
6  WHERE A.SET_STAMP = C.SET_STAMP
7    AND C.FILE# = 0
8    AND A.DELETED = 'NO'
9    AND C.SET_STAMP = &SET_STAMP;

查看某个备份集中归档日志

 1 SELECT DISTINCT B.SET_STAMP,
 2                 B.THREAD#,
 3                 B.SEQUENCE#,
 4                 B.FIRST_TIME,
 5                 B.FIRST_CHANGE#,
 6                 B.NEXT_TIME,
 7                 B.NEXT_CHANGE#
 8   FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 9  WHERE A.SET_STAMP = B.SET_STAMP
10    AND A.DELETED = 'NO'
11    AND B.SET_STAMP = &SET_STAMP
12  ORDER BY THREAD#, SEQUENCE#;

查看某个备份集SPFILE

1 SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
2   FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A
3  WHERE A.SET_STAMP = B.SET_STAMP
4    AND A.DELETED = 'NO'
5    AND B.SET_STAMP = &SET_STAMP;

rman配置信息

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

来检查某个时间段备份失败的记录

1 SELECT * FROM V$RMAN_STATUS 
2 WHERE START_TIME >= TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS')
3   AND END_TIME   <= TO_DATE(&END_TIME  ,'YYYY-MM-DD HH24:MI:SS')
4   AND OPERATION ='BACKUP'
5   AND STATUS !='COMPLETED' 
6   AND STATUS NOT LIKE 'RUNNING%'

查看备份成功的历史记录

1 SELECT * FROM V$RMAN_STATUS 
2 WHERE START_TIME >= TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS')
3   AND END_TIME   <= TO_DATE(&END_TIME  ,'YYYY-MM-DD HH24:MI:SS')
4   AND OPERATION ='BACKUP'
5   AND STATUS ='COMPLETED'

 

posted @ 2018-08-20 16:49  Anan_Zou  阅读(348)  评论(0)  编辑  收藏  举报