def gen_alert_sqlserver(server_id, role_switch, db_name):
if g_alert != "1":
return -1
mysql_conn = ''
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sql = """SELECT a.server_id,
b.tags,
a.host,
a.port,
b.send_mail,
b.send_mail_to_list,
b.send_sms,
b.send_sms_to_list,
b.send_wx,
'sqlserver' AS db_type
FROM sqlserver_status a, db_cfg_sqlserver b
WHERE a.server_id = b.id
and a.server_id = %s """ % (server_id)
result = mysql.GetMultiValue(mysql_conn, sql)
if result <> 0:
for line in result:
server_id = line[0]
tags = line[1]
host = line[2]
port = line[3]
send_mail = line[4]
send_mail_to_list = line[5]
send_sms = line[6]
send_sms_to_list = line[7]
send_wx = line[8]
db_type = line[9]
if send_mail_to_list is None or send_mail_to_list.strip() == '':
send_mail_to_list = mail_to_list_common
if send_sms_to_list is None or send_sms_to_list.strip() == '':
send_sms_to_list = sms_to_list_common
create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
if role_switch == 1:
msg = "database mirror of %s has been switched" % (db_name)
send_mail = update_send_mail_status(server_id, db_type,
'role_switch', send_mail,
send_mail_max_count)
send_sms = update_send_sms_status(server_id, db_type,
'role_switch', send_sms,
send_sms_max_count)
send_wx = update_send_wx_status(server_id, db_type,
'role_switch', send_wx)
add_alert(server_id, tags, host, port, create_time, db_type,
'role_switch', 'Principal', 'warning', msg,
send_mail, send_mail_to_list, send_sms,
send_sms_to_list, send_wx)
else:
pass
def kill_sessions(mysql_conn, ora_conn, server_id):
host_ip = ""
host_type = ""
host_user = ""
host_pwd = ""
host_protocol = ""
query_str = """select host, host_type, host_user, host_pwd, host_protocol from db_cfg_oracle t where t.id = %s """ % (
server_id)
res = mysql.GetMultiValue(mysql_conn, query_str)
for row in res:
host_ip = row[0]
host_type = row[1]
host_user = row[2]
host_pwd = row[3]
host_protocol = row[4]
logger.info("The database host type is %s" % (host_type))
# check host username
if host_user is None or host_user == "":
logger.info("The host user name is None, connect failed.")
return
# structure the srvctl command to shutdown the other instance when there are more then 1 active instance
srvctl_cmd = ""
host_list = ""
inst_list = ""
spid_list = ""
# get the process spid which are the python connections
str = "select p.spid from v$session s, v$process p where s.paddr = p.addr and s.program like 'python%' and type!='BACKGROUND' "
spid_list = oracle.GetMultiValue(ora_conn, str)
logger.info("spid list: %s" % (spid_list))
# check if more than one instance
str = 'select count(1) from gv$instance'
inst_count = oracle.GetSingleValue(ora_conn, str)
if inst_count > 1:
# get database name
str = 'select name from v$database'
db_name = oracle.GetSingleValue(ora_conn, str)
# get current instance name
str = 'select instance_name from v$instance'
curr_name = oracle.GetSingleValue(ora_conn, str)
# get other instance name list
str = """select instance_name from gv$instance where instance_name != '%s' """ % (
curr_name)
res = oracle.GetMultiValue(ora_conn, str)
for row in res:
inst_list = row[0] + ","
# get other host name list
str = """select host_name from gv$instance where instance_name != '%s' """ % (
curr_name)
host_list = oracle.GetMultiValue(ora_conn, str)
srvctl_cmd = "srvctl stop instance -d %s -i %s" % (db_name, inst_list)
logger.info("srvctl command: %s" % (srvctl_cmd))
else:
logger.info("There is only one active instance.")
paramiko.util.log_to_file("paramiko.log")
if host_type == 0 or host_type == 1 or host_type == 2 or host_type == 3: #host type: 0:Linux; 1:AIX; 2:HP-UX; 3:Solaris
if host_protocol == 0: #protocol is ssh2
try:
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname=host_ip,
port=22,
username=host_user,
password=host_pwd)
stdin, stdout, stderr = ssh.exec_command("whoami")
stdin.write(
"Y"
) # Generally speaking, the first connection, need a simple interaction.
print stdout.read()
# kill all "(LOCAL=NO)" processes
execmd = "ps -ef | grep 'LOCAL=NO' | grep -v grep | awk '{print $2}' "
stdin, stdout, stderr = ssh.exec_command(execmd + "\n")
pid_str = stdout.read()
pid_str = pid_str.replace("\n", " ")
#print stdout.read()
for spid in spid_list:
pid_str = pid_str.replace(spid[0], " ")
execmd = "kill -9 %s" % (pid_str)
stdin, stdout, stderr = ssh.exec_command(execmd + "\n")
logger.info("kill os id list: %s" % (pid_str))
# kill processes on other nodes when there have more than one instance active
chan = ""
if inst_count > 1:
logger.info(
"There are more than one active instance, should shutdown the others first."
)
#kill "(LOCAL=NO)" processes in other node
chan = ssh.get_transport().open_session()
chan.settimeout(20)
chan.get_pty()
chan.invoke_shell()
for server in host_list:
ssh_cmd = "ssh %s \n" % (server[0])
logger.info("ssh_cmd: %s" % (ssh_cmd))
chan.send(ssh_cmd)
chan.send(execmd + "\n")
result = ""
while True: # 这个循环很重要，保证接受到所有命令执行的返回结果。
time.sleep(0.5)
res = chan.recv(1024)
result += res
if result:
sys.stdout.write(result.strip('\n'))
if res.endswith('# ') or res.endswith('$ '):
break
#shutdown oracle instance in other node
stdin, stdout, stderr = ssh.exec_command(
". ~/.bash_profile; %s" % (srvctl_cmd))
print stdout.read()
print stderr.read()
chan.close()
except:
pass
finally:
ssh.close()
elif host_protocol == 1: #protocol is telnet
pass
elif host_type == 4: #host type: 4:Windows
logger.info("The database host type is Windows, Exit!")
def bind_ip(mysql_conn, group_id, server_id, dg_pid, op_type):
result = 0
host_ip = ""
host_type = ""
host_user = ""
host_pwd = ""
host_protocol = ""
shift_vip = ""
node_vips = ""
network_card = ""
network_card_p = ""
network_card_s = ""
query_str = """select host, host_type, host_user, host_pwd, host_protocol, d.shift_vip, d.node_vips, d.network_card_p, d.network_card_s
from db_cfg_oracle t, db_cfg_oracle_dg d
where d.is_delete = 0
and t.is_delete = 0
and d.id = %s
and t.id = %s """ % (group_id, server_id)
res = mysql.GetMultiValue(mysql_conn, query_str)
for row in res:
host_ip = row[0]
host_type = row[1]
host_user = row[2]
host_pwd = row[3]
host_protocol = row[4]
shift_vip = row[5]
node_vips = row[6]
network_card_p = row[7]
network_card_s = row[8]
logger.info("The database host type is %s" % (host_type))
if server_id == dg_pid:
network_card = network_card_p
else:
network_card = network_card_s
# check host username
if host_user is None or host_user == "":
logger.info("The host user name is None, connect failed.")
return -1
# check shift vip
if shift_vip is None or shift_vip == 0:
logger.info(
"This DG Group have no request for shift vip, no need to unbind ip."
)
return -1
paramiko.util.log_to_file("paramiko.log")
if host_type == 0: #host type: 0:Linux; 1:AIX; 2:HP-UX; 3:Solaris
if host_protocol == 0: #protocol is ssh2
try:
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname=host_ip,
port=22,
username=host_user,
password=host_pwd)
stdin, stdout, stderr = ssh.exec_command("hostname")
stdin.write(
"Y"
) # Generally speaking, the first connection, need a simple interaction.
print stdout.read()
ip_list = node_vips.split(',')
i = 100
for ip in ip_list:
ip_cmd = ""
if op_type == "bind":
#get network mask
mask_cmd = "ifconfig -a %s | grep 'Mask' | awk -F ':' '{print $NF}'" % (
network_card)
stdin, stdout, stderr = ssh.exec_command(mask_cmd +
"\n")
mask = stdout.read()
#get network gateway
gateway_cmd = "route -n | grep %s | awk '{print $2}' | grep -v '0.0.0.0'" % (
network_card)
stdin, stdout, stderr = ssh.exec_command(gateway_cmd +
"\n")
gateway = stdout.read()
ip_cmd = "ifconfig %s:%s %s netmask %s" % (
network_card, i, ip, mask)
ck_cmd = "ifconfig | grep %s:%s | wc -l" % (
network_card, i)
print ip_cmd
stdin, stdout, stderr = ssh.exec_command(ip_cmd + "\n")
out_str = stdout.read()
#arp
arp_cmd = "arping -U -c 1 -I %s -s %s %s" % (
network_card, ip, gateway)
#arping -U -c 1 -I $nic -s $scanip $net_gateway
print arp_cmd
stdin, stdout, stderr = ssh.exec_command(arp_cmd +
"\n")
out_str = stdout.read()
elif op_type == "unbind":
ip_cmd = "ifconfig %s:%s down" % (network_card, i)
ck_cmd = "ifconfig | grep %s:%s | wc -l" % (
network_card, i)
print ip_cmd
stdin, stdout, stderr = ssh.exec_command(ip_cmd + "\n")
out_str = stdout.read()
ck_cmd = "ifconfig | grep %s:%s | wc -l" % (network_card,
i)
stdin, stdout, stderr = ssh.exec_command(ck_cmd + "\n")
out_str = stdout.read().strip("\n")
if out_str.isdigit():
if op_type == "bind" and out_str == "0":
result = -1
if op_type == "unbind" and out_str == "1":
result = -1
else:
result = -1
i = i + 1
except:
pass
finally:
ssh.close()
pass
elif host_protocol == 1: #protocol is telnet
result = -1
pass
elif host_type == 4: #host type: 4:Windows
result = -1
logger.info("The database host type is Windows, Exit!")
return result
def disable_vip(mysql_conn, group_id, server_id, op_type):
result = 0
host_ip = ""
host_type = ""
host_user = ""
host_pwd = ""
host_protocol = ""
shift_vip = ""
node_vips = ""
network_card = ""
query_str = """select host, host_type, host_user, host_pwd, host_protocol, d.shift_vip, d.node_vips, d.network_card_p
from db_cfg_oracle t, db_cfg_oracle_dg d
where d.is_delete = 0
and t.is_delete = 0
and d.id = %s
and t.id = %s """ % (group_id, server_id)
res = mysql.GetMultiValue(mysql_conn, query_str)
for row in res:
host_ip = row[0]
host_type = row[1]
host_user = row[2]
host_pwd = row[3]
host_protocol = row[4]
shift_vip = row[5]
node_vips = row[6]
network_card = row[7]
logger.info("The database host type is %s" % (host_type))
# check host username
if host_user is None or host_user == "":
logger.info("The host user name is None, connect failed.")
return -1
# check shift vip
if shift_vip is None or shift_vip == 0:
logger.info("This DG Group have no request for shift vip.")
return -1
paramiko.util.log_to_file("paramiko.log")
if host_type == 0: #host type: 0:Linux; 1:AIX; 2:HP-UX; 3:Solaris
if host_protocol == 0: #protocol is ssh2
try:
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname=host_ip,
port=22,
username=host_user,
password=host_pwd)
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'which srvctl' ")
stdin.write(
"Y"
) # Generally speaking, the first connection, need a simple interaction.
srvctl_cmd = stdout.read().strip("\n")
print srvctl_cmd
#scan
if op_type == "start":
stdin, stdout, stderr = ssh.exec_command(
"%s enable scan " % (srvctl_cmd))
out_str = stdout.read() # read一定要有，不然可能命令没执行完进程就关了
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl start scan_listener' ")
out_str = stdout.read()
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl start scan' ")
out_str = stdout.read()
elif op_type == "stop":
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl stop scan_listener' ")
out_str = stdout.read()
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl stop scan' ")
out_str = stdout.read()
stdin, stdout, stderr = ssh.exec_command(
"%s disable scan " % (srvctl_cmd))
out_str = stdout.read()
#vip
exec_cmd = """su - grid -c "crs_stat | grep vip | grep NAME | grep -v scan" | awk -F '.' '{print $2}' """
stdin, stdout, stderr = ssh.exec_command(exec_cmd)
host_list = stdout.read().split("\n")
print host_list
for node in host_list:
if node != "":
if op_type == "start":
logger.info("su - root -c '%s enable vip -i %s' " %
(srvctl_cmd, node))
stdin, stdout, stderr = ssh.exec_command(
"su - root -c '%s enable vip -i %s' " %
(srvctl_cmd, node))
out_str = stdout.read()
logger.info(
"su - grid -c 'srvctl start vip -i %s' " %
(node))
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl start vip -i %s' " %
(node))
out_str = stdout.read()
logger.info(
"su - grid -c 'srvctl start listener -n %s' " %
(node))
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl start listener -n %s' " %
(node))
out_str = stdout.read()
elif op_type == "stop":
logger.info(
"su - grid -c 'srvctl stop listener -n %s' " %
(node))
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl stop listener -n %s' " %
(node))
out_str = stdout.read()
logger.info(
"su - grid -c 'srvctl stop vip -i %s' " %
(node))
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'srvctl stop vip -i %s' " %
(node))
out_str = stdout.read()
logger.info(
"su - root -c '%s disable vip -i %s' " %
(srvctl_cmd, node))
stdin, stdout, stderr = ssh.exec_command(
"su - root -c '%s disable vip -i %s' " %
(srvctl_cmd, node))
out_str = stdout.read()
#check vip and listener
if op_type == "start":
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'crs_stat -t | grep vip' | awk '{print $4}' | grep OFFLINE | wc -l "
)
out_str = stdout.read().strip("\n")
logger.info("there still are %s vip OFFLINE" % (out_str))
if out_str.isdigit():
if int(out_str) > 0:
logger.info("start vip error: position 1")
result = -1
else:
logger.info("start vip error: position 2")
result = -1
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'crs_stat -t | grep lsnr' | awk '{print $4}' | grep OFFLINE | wc -l "
)
out_str = stdout.read().strip("\n")
logger.info("there still are %s listener OFFLINE" %
(out_str))
if out_str.isdigit():
if int(out_str) > 0:
logger.info("start listener error: position 1")
result = -1
else:
logger.info("start listener error: position 2")
result = -1
elif op_type == "stop":
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'crs_stat -t | grep vip' | awk '{print $4}' | grep ONLINE | wc -l "
)
out_str = stdout.read().strip("\n")
logger.info("there still are %s vip ONLINE" % (out_str))
if out_str.isdigit():
if int(out_str) > 0:
logger.info("stop vip error: position 1")
result = -1
else:
logger.info("stop vip error: position 2")
result = -1
stdin, stdout, stderr = ssh.exec_command(
"su - grid -c 'crs_stat -t | grep lsnr' | awk '{print $4}' | grep ONLINE | wc -l "
)
out_str = stdout.read().strip("\n")
logger.info("there still are %s listener ONLINE" %
(out_str))
if out_str.isdigit():
if int(out_str) > 0:
logger.info("stop listener error: position 1")
result = -1
else:
logger.info("stop listener error: position 2")
result = -1
except:
pass
finally:
ssh.close()
elif host_protocol == 1: #protocol is telnet
result = -1
pass
elif host_type == 4: #host type: 4:Windows
result = -1
logger.info("The database host type is Windows, Exit!")
return result