SHELL SCRIPT TO MONITOR RESTORE PROGRESS FROM RMAN IN ORACLE

Use this shell script to get the restore progress of database from rman and get an approximate value of the progress

Here if you know the size of the database then in place of variable ‘ dbsz ‘ use the database size . This is because during restore, the output of ‘report schema’ command will be reset to 0 MB of size. This step can be followed if the restore percentage is unapproximate value exceeding the total percentage (100)

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 -x
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 dbsize dbsize1
export SIZELOG=dbsize
export DBSIZELOG=dbsize1
while true;do
rman cmdfile=rman_schema.cmd msglog=$SIZELOG >/dev/null 2>&1;grep -v 'TEMP' dbsize|awk '{print $2}'|grep -Eo '[0-9]{1,9}'|awk '{sum+=$1;}END{print sum;}'|tee -a $DBSIZELOG >/dev/null 2>&1
break
done
dbsz="$(cat $DBSIZELOG|grep -Eo '[0-9]{1,9}')" #this variable can be replaced by database size 
cat /dev/null > $DBSIZELOG
cat /dev/null > $SIZELOG
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOF
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/${dbsz}) format 99999999
select trunc(INPUT_BYTES/1024/1024) as inp_byte,trunc(OUTPUT_BYTES/1024/1024) as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/${dbsz}) as pctcomplete from v\$rman_status where status like '%RUNNING%';
EOF
exit;

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.

[oracle@orcl19x ~]$ cat dbsz.sh
#!/bin/bash
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
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;
/
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 status 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)

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

ORA-01081: cannot start already-running ORACLE – shut it down first

This error is observed when you try to start already started instance

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size            1157631712 bytes
Database Buffers          402653184 bytes
Redo Buffers               13848576 bytes

Already the database instance is up

SQL> startup nomount;
ORA-01081: cannot start already-running ORACLE - shut it down first

Shutdown the database and then try –Warning – use this command with caution. Think twice before execute the statement in production

SQL> shutdown immediate;

ORA-02288: invalid OPEN mode

The error below is syntactical error

RMAN> alter database open resetlog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/30/2021 21:40:13
ORA-02288: invalid OPEN mode

Give proper syntax while database open

RMAN> alter database open resetlogs;

Statement processed

SHELL SCRIPT TO GET DATABASE SIZE IN ORACLE

Its often a requirement for DBA to get the size for a database for determining various plans or activity prechecks to conclude the fastness,capacity,criticality feature of performing a critical change.

I was researching a way to find out the size of database when the database is in mount stage. As we cannot access any dba_* views during mount stage like dba_data_files …etc, we will not be able to get accurate or even approximate size of datafiles using any dynamic v$ views like v$datafile,v$datafile_header etc.. during a database restore. If we forget to note the approximate database size before restore, then it would be tedious to predict or guess the remaining time taken or percentage of time to complete the database restore.

You can use the below script to get the database size from rman when the database is in mount state.

Use report schema to get the size of each datafiles

[oracle@orcl19x ~]$ cat rman_schema.cmd
connect target /
run
{
report schema;
}

From the report schema information, we can grab the database size by redirecting the information to output file

Create a shell script ~] vi dbsize.sh

#!/bin/bash
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 dbsize dbsize1
export SIZELOG=dbsize
export DBSIZELOG=dbsize1
while true;do
rman cmdfile=rman_schema.cmd msglog=$SIZELOG >/dev/null 2>&1;grep -v 'TEMP' dbsize|awk '{print $2}'|grep -Eo '[0-9]{1,9}'|awk '{sum+=$1;}END{print sum;}'|tee -a $DBSIZELOG >/dev/null 2>&1
break
done
echo "the database size is $(cat $DBSIZELOG|grep -Eo '[0-9]{1,9}')"
cat /dev/null > $DBSIZELOG
cat /dev/null > $SIZELOG

Display the database size output

[oracle@orcl19x ~]$ ./dbsize.sh
the database size is 4437

RMAN-06004: Oracle error from recovery catalog database: ORA-02296: cannot enable (RCAT.) – null values found

Multiple databases were upgraded to a new version in our project and our catalog version was older ( RCAT.DBMS_RCVCAT version 11.02.00.04 ) than database version. So we tried upgrade the catalog database

[oracle@orcl19x apps01]$ rman target / catalog rcat/password@orcl11x

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 29 20:45:26 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19X1 (DBID=2012336792)
connected to recovery catalog database
PL/SQL package RCAT.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

error creating modify_grsp_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: Oracle error from recovery catalog database: ORA-02296: cannot enable (RCAT.) - null values found

RMAN> exit

And this weird error was thrown

recovery catalog is partially upgraded to 19.03.00.00
error creating modify_grsp_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: Oracle error from recovery catalog database: ORA-02296: cannot enable (RCAT.) - null values found

After deeper analysis for long time, we come to know that we enabled guarantee restore point on the catalog database itself for rollback purpose which was causing this issue. So we dropped the existing GRP

SQL> drop restore point GUA;

Restore point dropped.

We created a normal restore point

SQL> create restore point A;

Restore point created.

After that, catalog was upgraded successfully

[oracle@orcl19x apps01]$ rman target / catalog rcat/password@orcl11x

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 29 21:17:30 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19X1 (DBID=2012336792, not open)
connected to recovery catalog database
PL/SQL package RCAT.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 19.03.00.00.00
DBMS_RCVMAN package upgraded to version 19.03.00.00
DBMS_RCVCAT package upgraded to version 19.03.00.00.

This also seems to be bug in 12c versions with different errors. But the above error is different w.r.t GRP

Bug 20861957
Bug 19677999 - related to container databases(CDB/PDB)

RMAN-03014: implicit resync of recovery catalog failed RMAN-06004: Oracle error from recovery catalog database: RMAN-20001: target database not found in recovery catalog 

I connected to rman catalog and run controlfile backup in a new database. Below error was thrown

[oracle@orcl19x apps01]$ rman target / catalog rcat/password@orcl11x

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 29 21:21:39 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19X1 (DBID=2012336792, not open)
connected to recovery catalog database

RMAN> backup current controlfile;

Starting backup at 29-JUL-21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/29/2021 21:21:47
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: Oracle error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

I forgot to register the new database to catalog. So register it my friend

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


Backup happen normally after registration of database to catalog

RMAN> backup current controlfile;

Starting backup at 29-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-21
channel ORA_DISK_1: finished piece 1 at 29-JUL-21
piece handle=/data01/FRA/ORCL19X1/backupset/2021_07_29/o1_mf_ncnnf_TAG20210729T212303_jj5mq3ct_.bkp tag=TAG20210729T212303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 29-JUL-21

Starting Control File and SPFILE Autobackup at 29-JUL-21
piece handle=/data01/FRA/ORCL19X1/autobackup/2021_07_29/o1_mf_s_1079211956_jj5mq9vh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUL-21

SQL QUERY TO CHECK STATUS OF BACKUP AND DATABASES REGISTERED TO RMAN CATALOG

If you use rman catalog and need to check the status of backups in catalog for different database or need a daily report , use this script

col OUTPUT_DEVICE_TYPE format a6
col OUTPUT_DEVICE_TYPE format a10
col status format a10
col TIME_TAKEN_DISPLAY format a10
col DAYOFENDTIME format a10
col OUTPUT_BYTES_DISPLAY format a10
select db_name,input_type,
output_device_type,status,
output_bytes_display,time_taken_display,
to_char(start_time,'DD/MM/YYYY HH24:MI') start_time,
to_char(end_time,'DD/MM/YYYY HH24:MI') end_time,
to_char(end_time,'DAY') dayofendtime
from RC_RMAN_BACKUP_JOB_DETAILS 
order by db_name,input_type;

Output:

SQL> col OUTPUT_DEVICE_TYPE format a6
col OUTPUT_DEVICE_TYPE format a10
col status format a10
col TIME_TAKEN_DISPLAY format a10
col DAYOFENDTIME format a10
col OUTPUT_BYTES_DISPLAY format a10
select db_name,input_type,
output_device_type,status,
output_bytes_display,time_taken_display,
to_char(start_time,'DD/MM/YYYY HH24:MI') start_time,
to_char(end_time,'DD/MM/YYYY HH24:MI') end_time,
to_char(end_time,'DAY') dayofendtime
from RC_RMAN_BACKUP_JOB_DETAILS
order by db_name,input_type;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8

DB_NAME  INPUT_TYPE    OUTPUT_DEV STATUS     OUTPUT_BYT TIME_TAKEN START_TIME       END_TIME         DAYOFENDTI
-------- ------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
ORCL11X  CONTROLFILE   DISK       COMPLETED      9.33M  00:00:06   28/07/2021 21:06 28/07/2021 21:06 WEDNESDAY
ORCL11X  DB FULL                  FAILED         0.00K             28/07/2021 19:22
ORCL11X  DB FULL       DISK       COMPLETED    285.10M  00:01:07   28/07/2021 20:44 28/07/2021 20:45 WEDNESDAY

DROP A DATABASE IN ORACLE

High level steps of database drop in oracle

Shutdown the database instance

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup database in exclusive restrict to block incoming client connections to the database

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1169149952 bytes
Fixed Size                  2252624 bytes
Variable Size             385876144 bytes
Database Buffers          771751936 bytes
Redo Buffers                9269248 bytes
Database mounted.

Drop the database

SQL> drop database;

Database dropped.

Then you can use dbca to delete the SID and other entries from the server

ORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: ‘/apps01/oradata/orcl19xsystem01.dbf’

Cause:

Due to inconsistent state of the datafile, i was not able to open the database without recover of archivelogs apply

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/28/2021 23:55:21
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/apps01/oradata/orcl19xsystem01.dbf'

Workaround:

After recover of the database, i was able to open the database

RMAN> recover database;

Starting recover at 28-JUL-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 152 is already on disk as file /data01/ORCL19X/archivelog/2021_07_28/o1_mf_1_152_jj2w0xl1_.arc
archived log for thread 1 with sequence 153 is already on disk as file /apps01/oradata/orcl19xredo03.log
archived log file name=/data01/ORCL19X/archivelog/2021_07_28/o1_mf_1_152_jj2w0xl1_.arc thread=1 sequence=152
archived log file name=/apps01/oradata/orcl19xredo03.log thread=1 sequence=153
media recovery complete, elapsed time: 00:00:03
Finished recover at 28-JUL-21

Open the database

RMAN> alter database open resetlogs;

Statement processed