檢視備份資訊指令碼

檢視某個備份集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;
檢查某個時間段備份失敗的記錄：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;


檢視RAMAN備份工作細節
select start_time,
       end_time,
       input_type,
       output_device_type,
       status,
       round(input_bytes / 1024 / 1024 / 1024) as intput_gb,
       round(output_bytes / 1024 / 1024 / 1024) as output_gb
  from v$rman_backup_job_details
 where end_time > sysdate - 3;
 
 
 RMAN進度查詢
 select case
         when opname like '%aggregate%' then
          'total'
         else
          opname
       end opname,
       trunc(sofar * 100 / totalwork, 2) || '%' progress,
       units
  from v$session_longops
 where opname like 'RMAN%'
   and totalwork > sofar;


關於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;
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_TYPE 
  2  FROM V$RMAN_STATUS 
  3  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 "/"
clip_image001


 


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_TYPE 
  2  FROM V$RMAN_STATUS 
  3  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"


clip_image002


 


也就是說，你可以在這個檢視裡面檢視在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;