rman备份_八条SQL助你检查RMAN备份是否成功

概述

关于Oracle数据库的RMAN备份，除了邮件外，是否能通过其它方式检查RMAN备份的成功与失败呢？其实我们可以通过下面SQL脚本来检查

---

1、查看某个时间段RMAN备份失败的记录

可以在视图v$rman_status查看在RMAN里面执行的一些操作,例如删除归档日志等。如果要查看RMAN的输出，可以查看V$RMAN_OUTPUT，V$RMAN_OUTPUT 视图记录了RMAN生成的信息，这是在内存中额视图，不会记录到控制文件上。最多有37278条记录。

SELECT * FROM V$RMAN_STATUS WHERE START_TIME >= TO_DATE('2020-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS')  AND END_TIME   <= TO_DATE('2020-04-29 00:00:00' ,'YYYY-MM-DD HH24:MI:SS')  AND OPERATION ='BACKUP'  AND STATUS !='COMPLETED'   AND STATUS NOT LIKE 'RUNNING%'

---

2、查看某个时间段RMAN备份成功的历史记录

SELECT * FROM V$RMAN_STATUS WHERE START_TIME >= TO_DATE('2020-04-10 00:00:00','YYYY-MM-DD HH24:MI:SS')  AND END_TIME   <= TO_DATE('2020-04-29 00:00:00','YYYY-MM-DD HH24:MI:SS')  AND OPERATION ='BACKUP'  AND STATUS ='COMPLETED'

其中STATUS主要有RUNNING、RUNNING WITH WARNINGS、RUNNING WITH ERRORS、COMPLETED、COMPLETED WITH WARNINGS、COMPLETED WITH ERRORS、FAILED等几种状态。

---

3、查询RMAN备份更详细的信息

可以在视图V$RMAN_BACKUP_JOB_DETAILS中查看RMAN备份更详细的信息。

SELECT START_TIME,      END_TIME,      OUTPUT_DEVICE_TYPE,      STATUS,      ELAPSED_SECONDS,      COMPRESSION_RATIO,      INPUT_BYTES_DISPLAY,      OUTPUT_BYTES_DISPLAYFROM V$RMAN_BACKUP_JOB_DETAILSORDER BY START_TIME DESC ;

---

4、查看所有备份集详细信息

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'RDER BY A.COMPLETION_TIME DESC;

---

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

SELECT DISTINCT c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE DWHERE A.SET_STAMP = C.SET_STAMP    AND D.FILE# = C.FILE#    AND A.DELETED='NO'    AND c.set_stamp=&set_stamp ORDER BY C.FILE#;

---

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

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 C.SET_STAMP = &SET_STAMP;

---

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

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 B.SET_STAMP = &SET_STAMP ORDER BY THREAD#, SEQUENCE#;

---

8、查看某个备份集SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLEFROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A WHERE A.SET_STAMP = B.SET_STAMP   AND A.DELETED = 'NO'   AND B.SET_STAMP = &SET_STAMP;

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~