oracle 查看rman进度,查看RMAN备份进度的两条SQL

近期在生产系统重新部署DG时网上收集发现如下两条SQL还是挺好用的，在此作为记录分享一下。

SELECT SID,

SERIAL#,

CONTEXT,

SOFAR,

TOTALWORK,

ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"

FROM gV$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND

TOTALWORK != 0 AND SOFAR <> TOTALWORK;

或

col opname for a30

set linesize 500

select inst_id,sid,serial#,opname,COMPLETE,

trunc(((to_char(last_update_time,'dd')-to_char(start_time,'dd'))*60*24+(to_char(last_update_time,'hh24')-to_char(start_time,'hh24'))*60 +(to_char(last_update_time,'mi')-to_char(start_time,'mi')))*(100-complete)/complete) min from

(

SELECT inst_id,

sid,

serial#,

opname,

ROUND(SOFAR / TOTALWORK * 100, 2) COMPLETE,

LAST_UPDATE_TIME,

START_TIME

FROM gV$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

--AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK

) t ;

如下为在系统查询的结果：

SQL> SELECT SID,

2         SERIAL#,

3         CONTEXT,

4         SOFAR,

5         TOTALWORK,

6         ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"

7    FROM gV$SESSION_LONGOPS

8  WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND

9         TOTALWORK != 0 AND SOFAR <> TOTALWORK;

SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK %_COMPLETE

---------- ---------- ---------- ---------- ---------- ----------

892      10655          1   23280622   53423998      43.58

SQL> col opname for a30

SQL> set linesize 500

SQL>

SQL> select inst_id,sid,serial#,opname,COMPLETE,

2  trunc(((to_char(last_update_time,'dd')-to_char(start_time,'dd'))*60*24+(to_char(last_update_time,'hh24')-to_char(start_time,'hh24'))*60 +(to_char(last_update_time,'mi')-to_char(start_time,'mi')))*(100-complete)/complete) min from

3  (

4  SELECT inst_id,

5  sid,

6  serial#,

7  opname,

8  ROUND(SOFAR / TOTALWORK * 100, 2) COMPLETE,

9  LAST_UPDATE_TIME,

10  START_TIME

11  FROM gV$SESSION_LONGOPS

12  WHERE OPNAME LIKE 'RMAN%'

13  --AND OPNAME NOT LIKE '%aggregate%'

14  AND TOTALWORK != 0

15  AND SOFAR <> TOTALWORK

16  ) t ;

INST_ID        SID    SERIAL# OPNAME                           COMPLETE        MIN

---------- ---------- ---------- ------------------------------ ---------- ----------

2       1057      37067 RMAN: aggregate input               78.34        139

2        892      10655 RMAN: full datafile backup          43.65        156

SQL>

第二条SQL语句还显示剩余时间(估计时间)。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1792151/，如需转载，请注明出处，否则将追究法律责任。