ORACLE 查看RMAN的备份信息总结

转载

钱若梨花落 2022-12-14 09:06:29 博主文章分类：oracle

某个时间段备份失败的记录
SELECT * FROM V$RMAN_STATUSWHERE 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_STATUSWHERE 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 ~]$ dateTue 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 2016Copyright (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;SQL> COL OPERATION FOR A10;SQL> COL COMMAND_ID FOR A20;SQL> COL STATUS FOR A30;SQL> COL OBJECT_TYPE FOR A16;SQL> SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE2  FROM V$RMAN_STATUS3  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 2016Copyright (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 commandsRMAN-01006: error signalled during parseRMAN-02001: unrecognized punctuation symbol "/"SQL> COL ROW_TYPE FOR A10;SQL> COL OPERATION FOR A10;SQL> COL COMMAND_ID FOR A20;SQL> COL STATUS FOR A30;SQL> COL OBJECT_TYPE FOR A16;SQL> SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE2  FROM V$RMAN_STATUS3  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_DISPLAYFROM V$RMAN_BACKUP_JOB_DETAILSORDER 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 BWHERE 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_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE DWHERE A.SET_STAMP = C.SET_STAMPAND D.FILE# = C.FILE#AND A.DELETED='NO'AND c.set_stamp=&set_stampORDER BY C.FILE#; 
 
 
查询某个备份集中控制文件
 
SELECT DISTINCT A.SET_STAMP,D.NAME,C.CHECKPOINT_CHANGE#,C.CHECKPOINT_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE DWHERE A.SET_STAMP = C.SET_STAMPAND C.FILE# = 0AND 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 AWHERE A.SET_STAMP = B.SET_STAMPAND A.DELETED = 'NO'AND B.SET_STAMP = &SET_STAMPORDER BY THREAD#, SEQUENCE#; 
 
 
查看某个备份集SPFILE
 
SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLEFROM V$BACKUP_SPFILE B, V$BACKUP_PIECE AWHERE A.SET_STAMP = B.SET_STAMPAND A.DELETED = 'NO'AND B.SET_STAMP = &SET_STAMP; 
 
 
查看RMAN的配置信息
 
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION; 

    日积月累