Linux/RDBMS/Scripting -- Oracle Hot backup aware Netapp Snapmirror Update script

Here is a script that I have recently written and deployed on a production site.
Ofcourse, it can be written more professionally, but it does the job even in this case.

What this script does is, actually executing storage commands from a database server.
The script first checks, if the database is in hot backup mode.
If it finds the database in hot backup mode, it quits. (designed to do so, it can do any other thing, as well)

Well.. If the database is not in hot backup mode, the scripts takes it into hot backup mode, connects to the storage and executes the snapmirror command, which produces a storage snapshot and do the snapmirror towards to the disaster site. (replication)

I tried to catch all the errors all the way down to the shell. (from OS to SQL, from SQL to shell)
So, the script is a little smart , but can be made smarter as well.
During the execution, the script produces readable log files..
I wrote this script for specific needs of one of my customers, but you can go further and modify it according to your needs.

The thing that made me share this script with you is, actually showing what else can be done using the shell scripts.

Note that, I actually wrote a similar script (probably better than this one) in year 2008 , using Perl. In that script, I was creating snapshot from the code and managing the expired snapshots and so on.

This one, however; is written as a bash script and it uses "sshpass" for password authentication, while connecting to the storage using ssh.  (note that, alternatively, we can build key based authentication between Linux and Netapp)

I have written this script for Linux and the storage that the script is written to connect, is Netapp.

I used WHENEVER OSERROR and WHENEVER SQLERROR for catching sqlplus errors. I used $? for catching shell program errors/returns.

I used bash functions to make the script look nicer. (I must admin that I needed to write a function specifically for logging as well :).

I also designed this script to ignore some of the errors, which are actually good things. (like ORA-01403 while checking the datafiles, which are in backup mode --they must not be in that case..)

You will find an example of the execution (with output) , an example logfile and the script code below.

Example execution and the output:

[oracle@rac01 netapp_snap_script]$ sh snapmirror.sh

Snapmirror script is started , logging to : /home/oracle/netapp_snap_script/log/snapmirror.log.2017_06_05_1496660095
Checking if the database is already in hot backup
Database is not in backup mode, Good.. Now executing alter database begin backup command
Enabling Hot Backup Mode
Succesfully enabled hot backup mode
Connecting to the storage and executing snapmirror command
Disabling Hot Backup Mode
Successfully disabled hot backup mode
Execution successful : date : Mon Jun 5 13:55:08 +03 2017

Example Logfile:

###################################################################################
##Checking if the database is already in hot backup, we will exit if it is so..#####
###################################################################################

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 13:54:55 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SQL> SQL>   2    3    4    5  declare check_variable number(4);
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 3


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
###################################################################################

INFO: If you see ORA-01403 No data found errors above, this is NORMAL!! IGNORE IT .. it means -> there are no files in hot backup mode, which is good
###################################################################################
###################################################################################
##Database is not in backup mode, Good.. Now executing alter database begin backup command #####
###################################################################################

###################################################################################
####Putting the database into Hot Backup Mode#######################################################################################

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 13:54:55 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> 
Database altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

STORAGE WORK !!

###################################################################################
Connecting to the storage and executing snapmirror command###################################################################################
Transfer started.
Monitor progress with 'snapmirror status' or the snapmirror log.

###################################################################################
Disabling Hot Backup Mode
###################################################################################

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 13:55:07 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SQL> SQL> 
Database altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
###################################################################################
Execution Successful !! date : Mon Jun  5 13:55:08 +03 2017
###################################################################################

Script:

storage_ip="10.10.10.1" ## I m not giving the real storage ip here :)
storage_user="snapuser"
storage_password="blablablabla"
oracle_connection_type="/ as sysdba"
oracle_home="/u01/app/oracle/product/11.2.0.4/dbhome_1"
oracle_sid="TEST1"
log_file_dir="/home/oracle/netapp_snap_script/log"
execution_date=`date +%Y_%m_%d_%H_%M`
#####Setting the ORACLE ENVIRONMENT####

export ORACLE_HOME=$oracle_home
export ORACLE_SID=$oracle_sid
export PATH=$ORACLE_HOME/bin:$PATH
echo Snapmirror script is started , logging to : /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

#################FUNCTION DECLERATIONS ####

function check_hot_backup_mode()

{

echo "Checking if the database is already in hot backup"
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "##Checking if the database is already in hot backup, we will exit if it is so..#####" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
sqlplus $oracle_connection_type 2>&1 >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date <<EOF
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT 30;
declare check_variable number(4);
begin
select file# into check_variable from v\$backup where status='ACTIVE' and rownum=1;
end;
/
EOF

sql_return_code=$?
if [ $sql_return_code == 0 ]
then
echo "ERROR!!! error while checking the hot backup status, there is a good chance that: the Database is already in the hot backup mode(at least one file is in backup mode)"
echo "Disabling the hot backup mode, by executing alter database end backup, but you should check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
end_backup
exit;
fi


echo " " >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

echo "INFO: If you see ORA-01403 No data found errors above, this is NORMAL!! IGNORE IT .. it means -> there is no files in hot backup mode, which is good" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

}

function begin_backup()
{
check_hot_backup_mode
echo "Database is not in backup mode, Good.. Now executing alter database begin backup command"
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "##Database is not in backup mode, Good.. Now executing alter database begin backup command #####" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo"">> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "Enabling Hot Backup Mode"
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "####Putting the database into Hot Backup Mode####" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
sqlplus $oracle_connection_type 2>&1 >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date <<EOF
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT 29;
alter database begin backup;
EOF

sql_return_code=$?
if [ $sql_return_code == 29 ]
then
echo "ERROR!!! while enabling hot backup mode (SQL/SQLPLUS level error)"
echo "check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
exit 29;
fi


if [ $sql_return_code == 9 ]
then
echo "ERROR!!! while enabling hot backup mode (OS level error)"
echo "check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
exit 9;
fi

echo "Succesfully enabled hot backup mode"

}


function storage_work()
{
### DO THE STORAGE WORK ##
echo "" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "STORAGE WORK !!" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "Connecting to the storage and executing snapmirror command"
echo"">> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "Connecting to the storage and executing snapmirror command" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

sshpass -p $storage_password ssh $storage_user@$storage_ip "snapmirror update IZMOSRVCLS" 2>&1 >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
sshpass_return=$?

if [ $sshpass_return != 0 ]
then
echo "ERROR!!! while executing SSH!!"
echo "ERROR!!! while execution ssh to storage, the return is $sshpass_return" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
end_backup
exit $sshpass_return;
fi

}


function end_backup()
{
## END HOT BACKUP ##
echo "Disabling Hot Backup Mode"
echo"">> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

echo "Disabling Hot Backup Mode" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date

sqlplus $oracle_connection_type 2>&1 >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date <<EOF

WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT 29;
alter database end backup;
EOF

sql_return_code=$?
if [ $sql_return_code == 29 ]
then
echo "ERROR!!! while disabling hot backup mode (SQL/SQLPLUS level error)"
echo "check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
echo " " >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "!!!!!!!!!!ORA-01260 errors are ignorable if select * from v$backup where status='ACTIVE' returns no rows!!!!!!!!!!!!!!!!!" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "just rerun this script !!!! if you see ORA-01260 here""" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
exit 29;
fi


if [ $sql_return_code == 9 ]
then
echo "ERROR!!! while disabling hot backup mode (OS level error)"
echo "check the logfile /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date"
exit 9;
fi

echo "Successfully disabled hot backup mode"
echo "Execution successful : date : `date`"
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "Execution Successful !! date : `date`" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
echo "###################################################################################" >> /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
}


echo Snapmirror script is started , logging to : /home/oracle/netapp_snap_script/log/snapmirror.log.$execution_date
begin_backup
sleep 2;
storage_work
sleep 2;
end_backup