RMAN essential Queries

Check RMAN running backup status

The below query will show the Historical and current backup processes.

col STATUS format a10

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE,

STATUS,

to_char(START_TIME,'MM/DD/YY HH24:MI') start_time,

to_char(END_TIME,'MM/DD/YY HH24:MI') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

Check backup or restore operation status:

set line 2000;

set pages 3000;

set long 5000;

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) expected

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

Check aggregate backup status:

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 2000;

set pages 3000;

set long 5000;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done, (time_remaining/60/60) Remaining_HRS,

sysdate+(TIME_REMAINING/60/60/24) expected

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sl.opname like '%aggregate%'

and sofar != totalwork

and totalwork > 0

/

Check the percentage completed of RMAN backup / restore

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 order by 6 desc;

Check RMAN backup channels status

select s.inst_id, a.sid, CLIENT_INFO, a.STATUS,

open_time, round(BYTES/1024/1024,2) "SOFAR MB" , round(total_bytes/1024/1024,2)

"TOT MB", io_count,

round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename

from gv$backup_async_io a, gv$session s

where not a.STATUS in ('UNKNOWN') and s.status='ACTIVE' and a.STATUS <> 'FINISHED'

and a.sid=s.sid order by 6 desc,7;

Find the server process with channel

 

COL CLIENT_INFO FORMAT a30

COL SID FORMAT 9999

COL SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO

FROM V$PROCESS p, V$SESSION s

WHERE p.ADDR = s.PADDR

AND CLIENT_INFO LIKE 'rman%';

Find backup / restore status:

set line 190 pages 190

column FILENAME format a70

select sid,

  serial,

  filename,

  status,

  bytes/total_bytes * 100 "Completed",

  EFFECTIVE_BYTES_PER_SECOND/1024/1024 "MB/S"

from V$BACKUP_ASYNC_IO

where type = 'OUTPUT'

  and bytes <> 0

order by status;

Check RMAN JOB Details:

COL START_TIME FOR a15

COL END_TIME FOR a15

COL TIME_TAKEN_DISPLAY FOR a10

COL INPUT_BYTES_DISPLAY HEADING "DATA SIZE" FOR a10

COL OUTPUT_BYTES_DISPLAY HEADING "Backup Size" FOR a11

COL OUTPUT_BYTES_PER_SEC_DISPLAY HEADING "Speed/s" FOR a10

COL output_device_type HEADING "Device_TYPE" FOR a11

SELECT TO_CHAR (start_time, 'DD-MON-YY HH24:MI')     START_TIME,

       TO_CHAR (end_time, 'DD-MON-YY HH24:MI')       END_TIME,

       time_taken_display,

       status,

       input_type,

       output_device_type,

       input_bytes_display,

       output_bytes_display,

       output_bytes_per_sec_display,

       COMPRESSION_RATIO                             COMPRESS_RATIO

  FROM v$rman_backup_job_details

 WHERE status LIKE 'RUNNING%';

Reference:

Script to monitor RMAN Backup and Restore Operations (Doc ID 1487262.1)

Best Wishes!!