共计 6470 个字符，预计需要花费 17 分钟才能阅读完成。
这篇文章主要介绍了数据库中如何查看备份信息脚本，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
查看某个备份集 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;
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“数据库中如何查看备份信息脚本”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
