SQL QUERY TO MONITOR BACKUP,RESTORE AND RECOVERY PROGRESS IN ORACLE
Check for these similar restore scripts as well
http://alphaoragroup.com/2021/08/01/sql-query-to-monitor-rman-restore-in-oracle/
http://alphaoragroup.com/2021/07/31/shell-script-to-monitor-restore-progress-from-rman-in-oracle/
http://alphaoragroup.com/2021/07/31/shell-script-to-monitor-backup-and-restore-in-oracle/
Calculate the database size
SQL> select sum(bytes)/1024/1024 as DATAFILESIZE from v$datafile;
DATAFILESIZE
------------
4438.9375
Substitute the database size in below query
set lines 200
set pages 1000
col INPUT_BYTES/1024/1024 format 9999999
col OUTPUT_BYTES/1024/1024 format 9999999
col OBJECT_TYPE format a10
col 100*(MBYTES_PROCESSED/4438.9375) format 99999999
select INPUT_BYTES/1024/1024 as inp_byte,OUTPUT_BYTES/1024/1024 as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/4438.9375) as pctcomplete from v$rman_status where status like '%RUNNING%';
Sample restore
RMAN> restore database;
Starting restore at 30-JUL-21
Starting implicit crosscheck backup at 30-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 30-JUL-21
Starting implicit crosscheck copy at 30-JUL-21
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 30-JUL-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data01/ORCL19X/autobackup/2021_07_29/o1_mf_s_1079221656_jj5w60qn_.bkp
File Name: /data01/ORCL19X/autobackup/2021_07_30/o1_mf_s_1079223357_jj5xvb7f_.bkp
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_4_jj5xt8o1_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_2_jj5xt8p3_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_3_jj5xt9db_.arc
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /apps01/oradata/orcl19xsystem01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /apps01/oradata/orcl19x/EXAMPLE6345.dbf
channel ORA_DISK_1: restoring datafile 00003 to /apps01/oradata/orcl19xsysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /apps01/oradata/orcl19xundotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /apps01/oradata/orcl19xexample01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /apps01/oradata/orcl19xusers01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /apps01/oradata/orcl19x/EXAMPLE12625.dbf
channel ORA_DISK_1: restoring datafile 00008 to /apps01/oradata/orcl19x/SYSAUX18237.dbf
channel ORA_DISK_1: restoring datafile 00009 to /apps01/oradata/orcl19x/SYSTEM9302.dbf
channel ORA_DISK_1: restoring datafile 00010 to /apps01/oradata/orcl19x/EXAMPLE7187.dbf
channel ORA_DISK_1: reading from backup piece /data01/ORCL19X/backupset/2021_07_29/o1_mf_nnndf_TAG20210729T234343_jj5vytcy_.bkp
channel ORA_DISK_1: piece handle=/data01/ORCL19X/backupset/2021_07_29/o1_mf_nnndf_TAG20210729T234343_jj5vytcy_.bkp tag=TAG20210729T234343
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:57
Finished restore at 30-JUL-21
Sample restore output
SQL> set lines 200
SQL> set pages 1000
col INPUT_BYTES/1024/1024 format 9999999
col OUTPUT_BYTES/1024/1024 format 9999999
col OBJECT_TYPE format a10
col 100*(MBYTES_PROCESSED/4438.9375) format 99999999
select INPUT_BYTES/1024/1024 as inp_byte,OUTPUT_BYTES/1024/1024 as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/4438.9375) as pctcomplete from v$rman_status where status like '%RUNNING%';
SQL> SQL> SQL> SQL> SQL>
INP_BYTE OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
0 0 0
236.992188 2048.41406 DB FULL 46.1464948
SQL> SQL> /
INP_BYTE OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
0 0 0
246.992188 2098.41406 DB FULL 47.2728905
SQL> /
INP_BYTE OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
0 0 0
250.992188 2123.41406 DB FULL 47.8360883
SQL> /
INP_BYTE OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
0 0 0
253.992188 2133.41406 DB FULL 48.0613674
.....................
SQL> /
INP_BYTE OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
0 0 0
489.070313 4438.9375 DB FULL 100