SQL QUERY TO MONITOR BACKUP,RESTORE AND RECOVERY PROGRESS IN ORACLE

Check for these similar restore scripts as well

https://alphaoragroup.com/2021/08/01/sql-query-to-monitor-rman-restore-in-oracle/

https://alphaoragroup.com/2021/07/31/shell-script-to-monitor-restore-progress-from-rman-in-oracle/

https://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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s