ORACLE 查看RMAN的备份信息总结

原文链接:https://www.cnblogs.com/kerrycode/p/5684768.html

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

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'

其中STATUS主要有RUNNING、RUNNING WITH WARNINGS、RUNNING WITH ERRORS、COMPLETED、COMPLETED WITH WARNINGS、COMPLETED WITH ERRORS、FAILED等几种状态。另外，如果你在命令窗口输入

在V$RMAN_STATUS里面，你会看到插入了一条记录STATUS为RUNNING状态
SQL> COL ROW_TYPE FOR A10;
COL OPERATION FOR A10;
COL COMMAND_ID FOR A20;
COL STATUS FOR A30;
COL OBJECT_TYPE FOR A16;
SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE 
FROM V$RMAN_STATUS 
WHERE START_TIME >= TO_DATE('2016-07-19 10:52:00', 'YYYY-MM-DD HH24:MI:SS');


此时如果在RMAN中随意执行一个错误命令，如下所示
SQL> COL ROW_TYPE FOR A10;
COL OPERATION FOR A10;
COL COMMAND_ID FOR A20;
COL STATUS FOR A30;
COL OBJECT_TYPE FOR A16;
SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE 
FROM V$RMAN_STATUS 
WHERE START_TIME >= TO_DATE('2016-07-19 10:52:00', 'YYYY-MM-DD HH24:MI:SS');
 
ROW_TYPE   COMMAND_ID           OPERATION  STATUS                  OBJECT_TYPE
---------- -------------------- ---------- --------------------- ----------------
SESSION    2016-07-19T10:52:13  RMAN       RUNNING WITH ERRORS

在RMAN中退出，此时你会看到STAUS记录从"RUNNING WITH ERRORS"变成了"COMPLETED WITH ERRORS"

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

当然也可以从V$RMAN_BACKUP_JOB_DETAILS中查看RMAN备份更详细的信息。
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
ORDER BY START_TIME DESC ;

另外，如果我们需要查看RMAN备份的一些详细记录，在惜分飞的通过sql查询rman备份信息博客里面分享了下面一些经典的SQL语句。收录在此。

查看所有备份集详细信息：
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;

查找某个备份集中包含数据文件
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;

通过动态性能视图v$backup_set_details或v$backup_piece都可以查询RMAN每天的备份大小信息：

查询v$backup_set_details
select 
to_char(start_time,'yyyy-mm-dd') start_time,
to_char(start_time,'day') day, 
round(sum(OUTPUT_BYTES)/1024/1024/1024,2) SIZE_GB 
from v$backup_set_details
group by to_char(start_time,'yyyy-mm-dd'),to_char(start_time,'day') 
order by start_time desc;

查询v$backup_piece
select 
to_char(start_time,'yyyy-mm-dd') start_time,
to_char(start_time,'day') day, 
round(sum(BYTES)/1024/1024/1024,2) SIZE_GB 
from v$backup_piece where handle is not null
group by to_char(start_time,'yyyy-mm-dd'),to_char(start_time,'day') 
order by start_time asc;

Oracle 9i 查询方法
查看数据文件的备份大小
set line 120
select to_char(completion_time,'yyyymmdd') d_time ,ceil(sum(blocks)/128)||'M' d_M 
from v$backup_datafile GROUP BY 
to_char(completion_time,'yyyymmdd') ORDER BY d_time ASC ;

查看日志文件的备份大小
SELECT to_char(first_time,'yyyymmdd') l_time,sum(ceil(blocks/2/1024)) l_M FROM v$backup_redolog
GROUP BY to_char(first_time,'yyyymmdd') ORDER BY l_time  ASC;

http://blog.chinaunix.net/uid-23284114-id-3977925.html
http://blog.itpub.net/23650854/viewspace-695047/