Checking Ongoing Oracle rman backup and restore progress

  

During Restore we normally  have  to see progress for  big database  .   Posting  script below used  by me  to check progress . 

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 device_type "Device", type, filename, to_char(open_time, 'mm/dd/yyyy hh24:mi:ss') open,

 to_char(close_time,'mm/dd/yyyy hh24:mi:ss') close,elapsed_time ET, effective_bytes_per_second EPS

 from v$backup_async_io;

##### To check Restore Speed 

TTITLE OFF

SET HEAD OFF

SELECT 'Throughput: '||

       ROUND(SUM(v.value/1024/1024/1024),1) || ' Gig so far @ ' ||

       ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)

            FROM v$session_longops

            WHERE opname          LIKE 'RMAN: aggregate input'

              AND sofar           != TOTALWORK

              AND elapsed_seconds IS NOT NULL

       ),SUM(v.value     /1024/1024)),2) || ' Meg/sec'

 FROM gv$sesstat v, v$statname n, gv$session s

WHERE v.statistic# = n.statistic#

  AND n.name       = 'physical write total bytes'

  AND v.sid        = s.sid

  AND v.inst_id    = s.inst_id

  AND s.program LIKE 'rman@%'

GROUP BY n.name

/

########## Shell  Script to Check   backup  or  Restore Status ############# 

After setting db  environment   run below shell 

#!/bin/bash

#Set the environment

_env(){

RESTORE_PROGRESS=/tmp/restore_progress.log

touch dfsize

export SIZELOG=dfsize

} 

#RMAN restore progress monitor in percentage from database

_restore_pct(){

while sleep 0.5;do

date_is=$(date "+%F-%H-%M-%S")

#ela_s=$(date +%s)

#echo "============================================================"+

#echo "         ----->$ORACLE_SID<-----                                |"|tr 'a-z' 'A-Z';echo "    Restore progress ($date_is)                  |"

#echo "============================================================"+

$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOF > dbsz.txt

set feedback off

set lines 200

set pages 1000

set termout off

col INPUT_BYTES/1024/1024 format 9999999

col OUTPUT_BYTES/1024/1024 format 9999999

col OBJECT_TYPE format a10

set serveroutput off

spool dbsz.out

variable s_num number;

BEGIN

  select sum((datafile_blocks)*8/1024) into :s_num from v\$BACKUP_DATAFILE;

  dbms_output.put_line(:s_num);

END;

/

set feedback on

select INPUT_BYTES/1024/1024 as inp_byte,OUTPUT_BYTES/1024/1024 as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/:s_num) as pctdone from v\$rman_status where status like '%RUNNING%';

spool off

EOF

 

#Realtime monitoring of RMAN restore which shows date and percentage of completion

pct="$(cat dbsz.txt|grep -v 'row'|tail -3|grep -v '^$'|awk '{print $5}')"

clear;

echo "$date_is|Current restore progress for $ORACLE_SID:[$pct]"

#cat $SIZELOG|grep -v 'PL'

#cat /dev/null > $SIZELOG

done

}

#ela_e=$(date +%s)

#echo "elapsed_time: $($ela_e - $ela_s)

_env

_restore_pct