Oracle RMAN备份相关信息查询

查询 RMAN 备份状态主要是通过视图V$RMAN_STATUS 来进行，这个视图可以查询到 RMAN 执行的操作。
我们主要查看两列：OPERATION 和 STATUS。

OPERATION 的值有：RMAN、BACKUP、DELETE、CROSSCHECK、DELETE OBSOLETE等。

STATUS的值有：RUNNING、FAILED、COMPLETED、COMPLETED WITH ERRORS、COMPLETED WITH WARNINGS等。

如果要查看RMAN的输出，可以查看V$RMAN_OUTPUT。该视图记录了RMAN生成的信息，这是在内存中的视图，不会记录到控制文件上。最多有37278条记录。

如果要查看RMAN备份更详细的信息，可以查看V$RMAN_BACKUP_JOB_DETAILS中。

查看备份失败的记录：

SELECT * FROM V$RMAN_STATUS

WHERE START_TIME >= TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS')

  AND END_TIME   <= TO_DATE(&END_TIME  ,'YYYY-MM-DD HH24:MI:SS')

  AND OPERATION ='BACKUP'

  AND STATUS !='COMPLETED'

  AND STATUS NOT LIKE 'RUNNING%';

查看备份成功的记录：

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

查看所有备份集详细信息：

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   device_type "Device",
           TYPE,
           filename,
           TO_CHAR (open_time, 'yyyy-mm-dd hh24:mi:ss') open,
           TO_CHAR (close_time, 'yyyy-mm-dd hh24:mi:ss') close,
           elapsed_time et,
           effective_bytes_per_second EPS
FROM    v$backup_async_io
WHERE close_time > SYSDATE - 10
ORDER BY  close_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 c.set_stamp=&set_stamp

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

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

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#;

查看某个备份集spfile：

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE

FROM 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;

查看RMAN的配置信息：

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

查看RMAN备份速度：

select * from V$rman_backup_job_details

查看RMAN备份进度：

SELECT SID, SERIAL#, CONTEXT, SOFAR,TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK;

SELECT s.sid,

s.serial#,

s.machine,sl.opname,sl.target,sl.message,s.program,sl.sql_hash_value,

TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,

TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,

ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct

FROM v$session s,

v$session_longops sl

WHERE s.sid = sl.sid

AND s.serial# = sl.serial#

AND sl.totalwork>0

AND sl.sofar<>sl.totalwork

看某SID的具体信息：

SELECT SID,SADDR,PADDR,PROGRAM,CLIENT_INFO FROM V$SESSION WHERE SID=XXX;

参考文章：
https://www.cnblogs.com/yaoyangding/p/12091482.html
https://zhuanlan.zhihu.com/p/79435060
https://www.iteye.com/blog/myaerie-1221036
https://www.cnblogs.com/kerrycode/p/5684768.html

拓展阅读：

RMAN详细教程(一) —— 基本命令代码

RMAN详细教程(二) —— 备份、检查、维护、恢复

Oracle 中止 RMAN 任务

RMAN删除7天前的备份归档日志