Oracle EE is quite more expensive than SE, however sometimes you need a standby replica “just in case”.
This is how you get a standby replica on a different server, without breaking any Oracle licensing rules:
1) the following script has to be on both servers. It calls itself on remote.
2) set up a cronjob to launch the script on the primary server.
3) Check the log.
I must say that this script needs some big cleaning and ordering, but it works.
#!/bin/bash +x
#joanmarcrieraATgmailDOCcom
#TODO:: fix the output processing when DEBUG=1
#TODO:: too many paths that must become variables
STB_SERVER=oranode2 #the Standby database server # needs passwordless authentication to user oracle
LOG_FILE=/home/oracle/scripts/db_status.log #
#load the environment variables
. ~/.bash_profile
# When debug=1 the scripts must be executed by hand , and most functions will not work as expected. Processing the functions output must be fixed
DEBUG=0
# is the local database OK ?
function check_status ()
{
[[ $DEBUG -eq 1 ]] && echo "DEBUG=check_Status"
#expected status "ok=1 ko=0"
INSTANCE_STATUS=$(echo "select STATUS from v\$instance; " | sqlplus -S / as sysdba|grep -E "MOUNTED|OPEN"|wc -l )
INSTANCE_ARCHIVER=$(echo "select ARCHIVER from v\$instance; " | sqlplus -S / as sysdba|grep "STARTED"| wc -l)
INSTANCE_DB_STATUS=$(echo "select DATABASE_STATUS from v\$instance; " | sqlplus -S / as sysdba|grep "ACTIVE" |wc -l)
INSTANCE_ACTIVE_STATE=$(echo "select ACTIVE_STATE from v\$instance; " | sqlplus -S / as sysdba|grep "NORMAL"|wc -l)
[[ $INSTANCE_STATUS -eq 1 ]] && [[ $INSTANCE_ARCHIVER -eq 1 ]] && [[ $INSTANCE_DB_STATUS -eq 1 ]] && [[ $INSTANCE_ACTIVE_STATE -eq 1 ]] && echo "STATUS = OK" || echo "STATUS = KO"
}
#check tns ping .
function tnsping_status ()
{
[[ $DEBUG -eq 1 ]] && echo "DEBUG=tnsping_status"
TNSPING=$(tnsping eypesa|tail -n1 |cut -d' ' -f1 |grep "OK"|wc -l)
[[ $TNSPING -eq 1 ]] && echo "STATUS = OK" || echo "STATUS = KO"
}
# get value for logcount on local database (open mode)
function archive_log_count ()
{
[[ $DEBUG -eq 1 ]] && echo "DEBUG=archive_log_count"
#don't return current, because is not finished. so return the last already switched.
#select max(SEQUENCE#) from V$ARCHIVED_LOG;
ARCHIVE_LOG=$(echo "select max(SEQUENCE#) from V\$ARCHIVED_LOG;" |sqlplus -S / as sysdba|tail -n2|head -n1|tr -d ' \t\r\f')
echo "LAST = $ARCHIVE_LOG"
}
#ask database to check for its local logcount (standby mode)
function archive_log_count_stb ()
{
#select max(recid) from v$log_history;"
ARCHIVE_LOG_STB=$(echo "select max(recid) from v\$log_history;"|sqlplus -S / as sysdba |tail -n2 |head -n1|tr -d ' \t\r\f')
echo "LAST_STB = $ARCHIVE_LOG_STB"
}
#ask for remote logcount (standby mode)
function archive_log_count_stb_remote ()
{
ssh $STB_SERVER ~/scripts/db_status.sh archive_log_stb
}
#send new archive files to STB_SERVER
function send_files ()
{
#seq 452 $(expr 454 - 1)
FROM_FILE=$(ssh $STB_SERVER ~/scripts/db_status.sh archive_log_stb |cut -d'=' -f2)
TO_FILE=$(~/scripts/db_status.sh archive_log |cut -d'=' -f2)
[[ $FROM_FILE -eq $TO_FILE ]] && echo "SAME STATUS ON BOTH SIDES. " && exit 0
#files to send FROM TO -1
for FILE in $(seq $FROM_FILE $TO_FILE)
do
rsync -v /u01/eypesa/ORADATA/DB_ARCHIVE/1_${FILE}_* $STB_SERVER:/u01/eypesa/ORADATA/DB_ARCHIVE
done
}
#set remote database to recover automatically with archive logs already sent
function recover_standby_remote ()
{
#start recovery on standby
ssh $STB_SERVER ~/scripts/db_status.sh recover_local_standby
}
#recover with local archive logs
function recover_standby_local ()
{
sqlplus -S / as sysdba @/home/oracle/scripts/recover_standby_database.sql
}
#switch logfile (debug purposes)
function switch_local ()
{
echo "alter system switch logfile;" |sqlplus -S / as sysdba
}
#delete old archive log files, this is used on STB_DATABASE because it does not have rman to clean it.
function clean_old_files_remote ()
{
ssh $STB_SERVER "find /u01/eypesa/ORADATA/DB_ARCHIVE -mtime +7 -exec rm -rf {} \;"
}
#procedure when launched by cron.
function cron_mode ()
{
exec 1>> $LOG_FILE
exec 2>> $LOG_FILE
echo "CRON EXECUTION : $(date +%Y%m%d%H%M)"
#check status
check_status
tnsping_status
archive_log_count
archive_log_count_stb_remote
#send files
send_files
#recover
recover_standby_remote
clean_old_files_remote
echo "###### END CRON EXECUTION : $(date +%Y%m%d%H%M)"
}
case $1 in
status)
check_status
;;
tnsping)
tnsping_status
;;
archive_log)
archive_log_count
;;
archive_log_stb)
archive_log_count_stb
;;
send_files)
send_files
;;
recover_remote_standby)
recover_standby_remote
;;
recover_local_standby)
recover_standby_local
;;
switch_logfile_local)
switch_local
;;
cron_mode)
cron_mode
;;
check_all)
check_status
tnsping_status
archive_log_count
archive_log_stb
;;
*)
echo "Usage: $0 {cron_mode|status|tnsping|archive_log|archive_log_stb|send_files|recover_remote_standby|recover_local_standby|switch_logfile_local|check_all}"
exit 1
esac
