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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 |
#!/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 |
