sqlplus連接數據庫命令，oracle sqlplus執行腳本_oracle主、從自動切換方案？我是這樣實現的

oracle主、從自動切換方案

各個部分功能說明：

1、keepalived只用作vip功能；

sqlplus連接數據庫命令？2、check_oracle.sh腳本用于檢測master服務，并執行vip、oracle切換；

通過“select status from v$instance;”獲取master上oracle的數據狀態，由返回值進行判斷。

check_oracle.sh

#!/bin/sh

sqlplus執行sql腳本，monitor_ip=192.168.3.186

ssh_port=22

logfile=/etc/keepalived/check_oracle.log

#check ssh port status

oracle增加字段語句。function check_ssh_port()

{

/usr/bin/nc -z -w 5 -n $monitor_ip $ssh_port 1 > /dev/nul

if [ $? = 0 ]; then

oracle表空間、ssh_status=0

else

ssh_status=1

fi

oracle動態sql拼接？return $ssh_status

}

#oracle_check_status

function check_oracle_helth()

oracle加字段sql。{

source /home/oracle/.bash_profile

source /home/oracle/.bashrc

oracle_check_status=$(su - oracle -c "sqlplus sys/oracle@${monitor_ip}:1521/mydb as sysdba | grep -A 2 STATUS | sed -n '3p'" <

sqlplus常用命令？select status from v$instance;

EOF

)

if [ "$oracle_check_status" == OPEN ];then

oracle常用sql語句、oracle_status=0

else

oracle_status=1

fi

#echo $oracle_check_status

return $oracle_status

}

#update oracle slave to master

function change_oracle_status()

{

source /home/oracle/.bash_profile

source /home/oracle/.bashrc

su - oracle -c 'sqlplus / as sysdba' <

alter database recover managed standby database finish force;

alter database commit to switchover to primary with session shutdown;

alter database open;

EOF

}

check_time=5

#!=0

while [ $check_time -ne 0 ];do

let "check_time -= 1"

check_ssh_port

echo $ssh_status

if [ $ssh_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH ok, success_code:[$ssh_status]

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - start check oracle status......

check_oracle_helth

if [ $oracle_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - ORACLE ok, success_code:[$oracle_status]

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - ORACLE Down, fail_code:[$oracle_status]

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Keepalived stop......

ssh root@$monitor_ip '/etc/init.d/keepalived stop'

wait

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Keepalived Already stop

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Start update oracle slave stauts......

change_oracle_status

wait

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Oracle slave update to master Success

sed 's/.*check_oracle.sh$//g' /var/spool/cron/root

/etc/init.d/crond restart

fi

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH error 1, fail_code:[$ssh_status]

sleep 3

check_ssh_port

if [ $ssh_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH ok, success_code:[$ssh_status]

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH error 2, fail_code:[$ssh_status]

sleep 3

check_ssh_port

if [ $ssh_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH ok, success_code:[$ssh_status]

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH error 3, fail_code:[$ssh_status]

sleep 3

check_ssh_port

if [ $ssh_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH ok, success_code:[$ssh_status]

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH error 4, fail_code:[$ssh_status]

sleep 3

check_ssh_port

if [ $ssh_status = 0 ];then

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH ok, success_code:[$ssh_status]

else

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - SSH error 5, fail_code:[$ssh_status]

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Start update oracle slave stauts......

change_oracle_status

wait

echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - Oracle slave update to master Success

sed 's/.*check_oracle.sh$//g' /var/spool/cron/root

/etc/init.d/crond restart

fi

fi

fi

fi

fi

sleep 5

done

檢測流程圖：

腳本由crond執行，每1分鐘執行一次，每執行一次，腳本內循環檢測5次，首先判斷服務器ssh端口是否正常，正常情況下，再去檢查oracle狀態，根據返回的狀態決定是否進行切換，如果ssh端口異常，則進行5次檢查判斷，5次以內返回正常的話，則重新進行檢測，如果第5次仍異常，則進行切換。