這篇文章主要介紹了數(shù)據(jù)庫中如何查看備份信息腳本，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供阿圖什網(wǎng)站建設(shè)、阿圖什做網(wǎng)站、阿圖什網(wǎng)站設(shè)計(jì)、阿圖什網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、阿圖什企業(yè)網(wǎng)站模板建站服務(wù)，10年阿圖什做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
查看某個(gè)備份集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;
檢查某個(gè)時(shí)間段備份失敗的記錄：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'
查看所有備份集詳細(xì)信息：
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備份工作細(xì)節(jié)
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進(jìn)度查詢
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;
關(guān)于Oracle數(shù)據(jù)庫的RMAN備份，除了郵件外，是否能通過其它方式檢查RMAN備份的成功與失敗呢？其實(shí)我們可以通過下面SQL腳本來檢查某個(gè)時(shí)間段備份失敗的記錄：
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等幾種狀態(tài)。另外，如果你在命令窗口輸入
[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狀態(tài)
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
此時(shí)如果在RMAN中隨意執(zhí)行一個(gè)錯(cuò)誤命令，如下所示
[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中退出，此時(shí)你會看到STAUS記錄從"RUNNING WITH ERRORS"變成了"COMPLETED WITH ERRORS"
clip_image002
也就是說，你可以在這個(gè)視圖里面查看在RMAN里面執(zhí)行的一些操作,例如刪除歸檔日志等，另外，如果要查看RMAN的輸出，可以查看V$RMAN_OUTPUT，V$RMAN_OUTPUT 視圖記錄了RMAN生成的信息，這是在內(nèi)存中額視圖，不會記錄到控制文件上。最多有37278條記錄.
當(dāng)然也可以從V$RMAN_BACKUP_JOB_DETAILS中查看RMAN備份更詳細(xì)的信息。
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備份的一些詳細(xì)記錄，在惜分飛的通過sql查詢r(jià)man備份信息博客里面分享了下面一些經(jīng)典的SQL語句。收錄在此。
查看所有備份集詳細(xì)信息：
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;
查找某個(gè)備份集中包含數(shù)據(jù)文件
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#;
查詢某個(gè)備份集中控制文件
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;
查看某個(gè)備份集中歸檔日志:
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#;
查看某個(gè)備份集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;
感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“數(shù)據(jù)庫中如何查看備份信息腳本”這篇文章對大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!
重慶分公司
重慶分公司
