Oracle RMAN备份相关信息查询 |
您所在的位置:网站首页 › rman查看归档备份 › Oracle RMAN备份相关信息查询 |
查询 RMAN 备份状态主要是通过视图V$RMAN_STATUS 来进行,这个视图可以查询到 RMAN 执行的操作。 我们主要查看两列:OPERATION 和 STATUS。 OPERATION 的值有:RMAN、BACKUP、DELETE、CROSSCHECK、DELETE OBSOLETE等。 STATUS的值有:RUNNING、FAILED、COMPLETED、COMPLETED WITH ERRORS、COMPLETED WITH WARNINGS等。 如果要查看RMAN的输出,可以查看V$RMAN_OUTPUT。该视图记录了RMAN生成的信息,这是在内存中的视图,不会记录到控制文件上。最多有37278条记录。 如果要查看RMAN备份更详细的信息,可以查看V$RMAN_BACKUP_JOB_DETAILS中。 查看备份失败的记录: SELECT * FROM V$RMAN_STATUS WHERE START_TIME >= TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS') AND END_TIME = TO_DATE(&START_TIME,'YYYY-MM-DD HH24:MI:SS') AND END_TIME SYSDATE - 10 ORDER BY close_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;查看RMAN备份速度: select * from V$rman_backup_job_details查看RMAN备份进度: SELECT SID, SERIAL#, CONTEXT, SOFAR,TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR TOTALWORK; SELECT s.sid, s.serial#, s.machine,sl.opname,sl.target,sl.message,s.program,sl.sql_hash_value, TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed, TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND sl.totalwork>0 AND sl.sofarsl.totalwork看某SID的具体信息: SELECT SID,SADDR,PADDR,PROGRAM,CLIENT_INFO FROM V$SESSION WHERE SID=XXX;参考文章: https://www.cnblogs.com/yaoyangding/p/12091482.html https://zhuanlan.zhihu.com/p/79435060 https://www.iteye.com/blog/myaerie-1221036 https://www.cnblogs.com/kerrycode/p/5684768.html 拓展阅读: RMAN详细教程(一) —— 基本命令代码 RMAN详细教程(二) —— 备份、检查、维护、恢复 Oracle 中止 RMAN 任务 RMAN删除7天前的备份归档日志 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |