SHELL SCRIPT TO MONITOR BACKUP AND RESTORE IN ORACLE

SHELL SCRIPT TO MONITOR BACKUP AND RESTORE IN ORACLE

One of junior dba corrupted the ASM disk header by adding an exisiting candidate disk secured under vormetric due to space constraint in tablespace.

This lead to a huge escalation from the customers and we would had to wipe out the ASM from OS and were restoring the whole database.

The customer was screaming for the restore progress and estimated time of completion of restore due to revenue bleed. Under pressure, we were not able to find a suitable script anywhere which display an approximate percentage of restore completion.

I researched a lot on this to prepare a shell script which is convenient to display timely report of the restore progress with not accurate but approximate progress.

Hope this script helps you in your database restore

Note – you can also redirect the output of script to a logfile if you are not interactively at the screen

Tips: If the database size is in GB or TB, then you have to convert the values accordingly in the script. This script has size format in “MB”. So remember it.

#!/bin/bash
#set environment variables
_env(){
RESTORE_PROGRESS=/tmp/restore_progress.log
export PATH=/apps01/product/12.1.0/dbhome_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:                         /usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/                         oracle/bin
export ORACLE_HOME=/apps01/product/12.1.0/dbhome_1
export ORACLE_SID=orcl19x
touch dfsize
export SIZELOG=dfsize
}
#check the restore status
_restore_pct(){
while true;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
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
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 pctcomplete from v\$rman_status where s                         tatus like '%RUNNING%';
EOF
#cat $SIZELOG|grep -v 'PL'
#cat /dev/null > $SIZELOG
sleep 6
done
}
#ela_e=$(date +%s)
#echo "elapsed_time: $($ela_e - $ela_s)
_env
_restore_pct

Sample output:

============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-47-41)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
         0          0 DB FULL              0
============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-47-47)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
 9.9921875 327.703125 DB FULL     7.32543031
============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-47-53)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
22.9921875 409.203125 DB FULL     9.14727003
============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-47-59)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
41.9921875  535.15625 DB FULL     11.9628088

.......................

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
489.304688  4438.9375 DB FULL     99.2273947
============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-50-55)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
489.304688  4438.9375 DB FULL     99.2273947
============================================================
     ----->ORCL19X<-----
Restore progress (2021-07-30-23-51-02)
============================================================

  INP_BYTE   OUT_BYTE OBJECT_TYP PCTCOMPLETE
---------- ---------- ---------- -----------
         0          0                      0
============================================================

Restore output:

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 8 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_30/o1_mf_s_1079300772_jj89gdcb_.bkp
File Name: /data01/ORCL19X/autobackup/2021_07_30/o1_mf_s_1079301579_jj8b7q05_.bkp
File Name: /data01/ORCL19X/autobackup/2021_07_30/o1_mf_s_1079302004_jj8bnz4v_.bkp
File Name: /data01/ORCL19X/autobackup/2021_07_30/o1_mf_s_1079302354_jj8bzxnx_.bkp
File Name: /data01/ORCL19X/autobackup/2021_07_30/o1_mf_s_1079306707_jj8h7w5t_.bkp
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_1_jj8b79y1_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_2_jj8b7b1c_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_3_jj8b7b49_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_1_jj8bnfd9_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_1_jj8bz918_.arc
File Name: /data01/ORCL19X/archivelog/2021_07_30/o1_mf_1_1_jj8h7gm5_.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_30/o1_mf_nnndf_TAG20210730T214320_jj8991b5_.bkp
channel ORA_DISK_1: piece handle=/data01/ORCL19X/backupset/2021_07_30/o1_mf_nnndf_TAG20210730T214320_jj8991b5_.bkp tag=TAG20210730T214320
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:16
Finished restore at 30-JUL-21