RMAN筆記之查詢rman備份資訊狀態

查詢rman備份狀態主要是透過檢視V$RMAN_STATUS來進行，這個檢視可以讓你看見你在RMAN裡面執行的操作。
在V$RMAN_STATUS中，最主要的列是OPERATION和status。

Operation的值有rman、backup、list、report。

Status的值有running、running with warnings、running with errors、completed、completed with warnings、completed with errors、failed等幾種狀態。

如果要檢視RMAN的輸出，可以檢視V$RMAN_OUTPUT。V$RMAN_OUTPUT 檢視記錄了RMAN生成的資訊，這是在記憶體中的檢視，不會記錄到控制檔案上。最多有37278條記錄。

還可以從V$RMAN_BACKUP_JOB_DETAILS中檢視RMAN備份更詳細的資訊。

檢視備份失敗的記錄：

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