Oracle查看RMAN的备份信息总结
http://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等几种状态。另外，如果你在命令窗口输入
[oracle@MyDB ~]$ date
Tue Jul 19 10:52:02 CST 2016
[oracle@MyDB ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jul 19 10:52:13 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: SCM2 (DBID=3990839260)
RMAN>
在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;
SQL> 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
此时如果在RMAN中随意执行一个错误命令，如下所示
[oracle@MyDB ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jul 19 10:52:13 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: SCM2 (DBID=3990839260)
RMAN> /
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signalled during parse
RMAN-02001: unrecognized punctuation symbol "/"
SQL> COL ROW_TYPE FOR A10;
COL OPERATION FOR A10;
COL COMMAND_ID FOR A20;
COL STATUS FOR A30;
COL OBJECT_TYPE FOR A16;
SQL> 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语句。
http://www.xifenfei.com/2012/02/%E9%80%9A%E8%BF%87sql%E6%9F%A5%E8%AF%A2rman%E5%A4%87%E4%BB%BD%E4%BF%A1%E6%81%AF.html
查看所有备份集详细信息：
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 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;
