当前位置：首页 > news >正文

8 个搞定 RMAN 备份核查的实用 SQL 语句

news 2026/6/2 12:13:00

这是一篇 Oracle DBA 日常运维的实用速查笔记，作者整理了 8 个无需进入 RMAN 命令行、直接在 SQL 客户端执行的生产级查询脚本，覆盖了 RMAN 备份管理最常用的核心场景，拿来就能直接用。

内容主要分三块：一是备份集的全维度核查，既能全局看所有有效备份的类型、状态和路径，也能精准查单个备份集里包含的数据文件、控制文件、归档日志和 SPFILE；二是备份任务的实时进度监控，支持查看正在跑的任务，也能查所有已完成和未完成的 RMAN 操作，还适配了 RAC 集群环境；三是历史备份任务的整体统计，可以批量看每次备份的起止时间、耗时和执行状态。

1）查看所有备份集

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 "大小(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' and a.set_count = b.set_count ORDER BY A.COMPLETION_TIME DESC;

2）查找某个备份集中包含数据文件

##根据上面查到的SET_STAMP 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#;

3）查询某个备份集中控制文件

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;

4）查看某个备份集中归档日志

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

5）查看某个备份集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;

6）rman查看未完成的备份进度

set line 9999 col opname for a35 col start_time for a19 set head off select sid, serial#, opname, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, sofar, totalwork, round(sofar / totalwork * 100, 2) "%complete", ceil(elapsed_seconds / 60) elapsed_mi from v$session_longops;

7）rman查看已完成和未完成备份进度

set line 9999 col opname for a35 col start_time for a19 SELECT SID, SERIAL#, opname, to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') start_time, SOFAR, TOTALWORK, ROUND(SOFAR / TOTALWORK * 100, 2) "%COMPLETE", ceil(ELAPSED_SECONDS / 60) ELAPSED_MI FROM V$SESSION_LONGOPS where opname like 'RMAN%' and totalwork <> 0 order by start_time asc; SET LINESIZE 9999 pagesize 9999 COLUMN session_info FORMAT a20 COLUMN start_time FORMAT a20 COLUMN username FORMAT A10 COLUMN opname FORMAT A16 COLUMN module FORMAT A16 COLUMN message FORMAT A40 COLUMN progress_pct FORMAT 99999999.00 /* Formatted on 2021-3-29 10:40:59 (QP5 v5.149.1003.31008) */ SELECT s.inst_id, (SELECT s.sid || ',' || s.SERIAL# || ',' || pr.SPID FROM gv$process pr WHERE s.PADDR = pr.ADDR AND s.inst_id = pr.inst_id) session_info, TO_CHAR (sl.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time, s.username, sl.opname, s.module, MESSAGE MESSAGE, sl.elapsed_seconds elapsed, sl.time_remaining remaining, ROUND (sl.sofar / sl.totalwork * 100, 2) progress_pct FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.inst_id = sl.inst_id AND s.serial# = sl.serial# AND program LIKE '%rman%' AND sl.time_remaining > 0;

8）rman查看备份信息

COL STATUS FORMAT a9 COL hours FORMAT 999.999 SELECT SESSION_KEY, INPUT_TYPE, STATUS, TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time, TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time, ELAPSED_SECONDS/3600 hours FROM V$RMAN_BACKUP_JOB_DETAILS ORDER BY SESSION_KEY;

查看全文

http://www.cnnetsun.cn/news/2584378.html