这是一篇 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查看某个备份集SPFILESELECT 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;8rman查看备份信息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;