def update_switch_flag(mysql_conn, group_id):
logger.info(
"Update switch flag in db_cfg_oracle_dg for group %s in progress..." %
(group_id))
# get current switch flag
str = 'select is_switch from db_cfg_oracle_dg where id= %s' % (group_id)
is_switch = mysql.GetSingleValue(mysql_conn, str)
logger.info("The current switch flag is: %s" % (is_switch))
if is_switch == 0:
str = """update db_cfg_oracle_dg set is_switch = 1 where id = %s""" % (
group_id)
else:
str = """update db_cfg_oracle_dg set is_switch = 0 where id = %s""" % (
group_id)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
if is_succ == 1:
logger.info(
"Update switch flag in db_cfg_oracle_dg for group %s successfully."
% (group_id))
else:
logger.info(
"Update switch flag in db_cfg_oracle_dg for group %s failed." %
(group_id))
def update_mrp_status(mysql_conn, sta_id):
logger.info(
"Update MRP status in oracle_dg_s_status for server %s in progress..."
% (sta_id))
# get current switch flag
str = 'select mrp_status from oracle_dg_s_status where server_id= %s' % (
sta_id)
mrp_status = mysql.GetSingleValue(mysql_conn, str)
logger.info("debug the mrp_status: %s" % (mrp_status))
if mrp_status == '0':
logger.info("The current MRP status is inactive.")
str = """update oracle_dg_s_status s set s.mrp_status = 1 where s.id in (select * from (select max(t.id) from oracle_dg_s_status t where t.server_id = %s) m) """ % (
sta_id)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
if is_succ == 1:
logger.info(
"Update MRP status to active in oracle_dg_s_status for server %s successfully."
% (sta_id))
else:
logger.info(
"Update MRP status to active in oracle_dg_s_status for server %s failed."
% (sta_id))
def update_switch_flag(mysql_conn, group_id):
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s in progress..." %
(group_id))
# get current switch flag
str = 'select is_switch from db_cfg_mysql_dr where id= %s' % (group_id)
is_switch = mysql.GetSingleValue(mysql_conn, str)
logger.info("The current switch flag is: %s" % (is_switch))
if is_switch == 0:
str = """update db_cfg_mysql_dr set is_switch = 1 where id = %s""" % (
group_id)
else:
str = """update db_cfg_mysql_dr set is_switch = 0 where id = %s""" % (
group_id)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
if is_succ == 1:
common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER',
'容灾组更新状态成功', 100, 2)
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s successfully."
% (group_id))
else:
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s failed." %
(group_id))
def update_db_op_reason(mysql_conn, db_type, group_id, op_type, reason):
logger.info("update opration reason for group %s." % (group_id))
# get max inst id
str = """select max(id) from db_opration where group_id= %s and op_type = '%s' """ % (
group_id, op_type)
max_id = mysql.GetSingleValue(mysql_conn, str)
str = """update db_opration set reason = '%s' where id = %s and op_type = '%s' """ % (
reason, max_id, op_type)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
def get_option(key):
try:
mysql_conn = mysql.ConnectMysql()
sql = "select value from options where name='%s'; " % (key)
option_value = mysql.GetSingleValue(mysql_conn, sql)
return option_value
except Exception, e:
print 'traceback.print_exc():'
traceback.print_exc()
def update_op_result(mysql_conn, group_id, op_type, result):
logger.info("update switch result for group %s." % (group_id))
# get max inst id
str = """select max(id) from oracle_dg_opration where group_id= %s and op_type = '%s' """ % (
group_id, op_type)
max_id = mysql.GetSingleValue(mysql_conn, str)
str = """update oracle_dg_opration set result = '%s' where id = %s and op_type = '%s' """ % (
result, max_id, op_type)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
def pre_flashback(mysql_conn, server_id, fb_type, fb_object):
logger.info("Check the flashback process status for server: %s" %
(server_id))
# get flashback process
str = """select count(1) from oracle_fb_process where server_id=%s """ % (
server_id)
fb_record = mysql.GetSingleValue(mysql_conn, str)
if fb_record == 0:
logger.info("Generate a flashback process for server: %s" %
(server_id))
str = """insert into oracle_fb_process(server_id, fb_type, fb_object, on_process) values('%s', '%s', '%s', 1) """ % (
server_id, fb_type, fb_object)
op_status = mysql.ExecuteSQL(mysql_conn, str)
return 0
else:
str = """select on_process from oracle_fb_process where server_id=%s """ % (
server_id)
fb_process = mysql.GetSingleValue(mysql_conn, str)
if fb_process == 1:
logger.error("Blocked, another flashback process is running.")
str = """update oracle_fb_process set blocked = 1 where server_id=%s """ % (
server_id)
op_status = mysql.ExecuteSQL(mysql_conn, str)
sys.exit(2)
else:
logger.info(
"Update the flashback process infomation for server: %s" %
(server_id))
str = """update oracle_fb_process set fb_type = '%s', fb_object='%s', on_process=1, result='-1', reason='', blocked=0, create_time=CURRENT_TIMESTAMP where server_id=%s """ % (
fb_type, fb_object, server_id)
op_status = mysql.ExecuteSQL(mysql_conn, str)
logger.info(
"The flashback process is already prepared for server: %s" %
(server_id))
return 0
def update_send_sms_status(server, db_type, alert_item, send_sms,
send_sms_max_count):
try:
mysql_conn = mysql.ConnectMysql()
sql = ""
if db_type == "os":
sql = "select count(1) from alerts_temp where ip='%s' and db_type='%s' and alert_item='%s' and alert_type='sms' and create_time > date_add(sysdate(), interval -%s second);" % (
server, db_type, alert_item, send_sms_sleep_time)
else:
sql = "select count(1) from alerts_temp where server_id=%s and db_type='%s' and alert_item='%s' and alert_type='sms' and create_time > date_add(sysdate(), interval -%s second);" % (
server, db_type, alert_item, send_sms_sleep_time)
alert_count = mysql.GetSingleValue(mysql_conn, sql)
if int(alert_count) > 0:
send_sms = 0
else:
send_sms = send_sms
return send_sms
except Exception, e:
print 'traceback.print_exc():'
traceback.print_exc()
def add_alert(server_id, tags, db_host, db_port, create_time, db_type,
alert_item, alert_value, level, message, send_mail,
send_mail_to_list, send_sms, send_sms_to_list):
try:
mysql_conn = mysql.ConnectMysql()
if db_type == 'os':
count_str = "select id from alerts where host='%s' and alert_item='%s';" % (
db_host, alert_item)
alert_count = mysql.GetSingleValue(mysql_conn, count_str)
if int(alert_count) > 0:
sql = "insert into alerts_his select *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from alerts where host='%s' and alert_item='%s';" % (
db_host, alert_item)
try:
mysql.ExecuteSQL(mysql_conn, sql)
except Exception, e:
print "Move alert to history: " + str(e)
sql = "delete from alerts where host='%s' and alert_item='%s' ;" % (
db_host, alert_item)
mysql.ExecuteSQL(mysql_conn, sql)
else:
p_username = res1[2]
p_password = res1[3]
s_str = """select host, port, username, password from db_cfg_mysql where id=%s; """ %(sta_id)
res2 = mysql.GetSingleRow(mysql_conn, s_str)
if res2:
s_host = res2[0]
s_port = res2[1]
s_username = res2[2]
s_password = res2[3]
#print s_host,s_port,s_username,s_password
p_str = """select concat(host, ':', port) from db_cfg_mysql where id=%s; """ %(pri_id)
p_nopass_str = mysql.GetSingleValue(mysql_conn, p_str)
s_str = """select concat(host, ':', port) from db_cfg_mysql where id=%s; """ %(sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
logger.info("The master database is: " + p_nopass_str + ", the id is: " + str(pri_id))
logger.info("The slave database is: " + s_nopass_str + ", the id is: " + str(sta_id))
db_type = "mysql"
try:
common.db_op_lock(mysql_conn, db_type, group_id, 'SWITCHOVER') # 加锁
common.init_db_op_instance(mysql_conn, db_type, group_id, 'SWITCHOVER') #初始化切换实例
# connect to mysql
p_conn = mysql.ConnectMysql_T(p_host,p_port,p_username,p_password)
if int(alert_count) > 0:
sql = "insert into alerts_his select *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from alerts where host='%s' and alert_item='%s';" % (
db_host, alert_item)
try:
mysql.ExecuteSQL(mysql_conn, sql)
except Exception, e:
print "Move alert to history: " + str(e)
sql = "delete from alerts where host='%s' and alert_item='%s' ;" % (
db_host, alert_item)
mysql.ExecuteSQL(mysql_conn, sql)
else:
count_str = "select count(1) from alerts where server_id=%s and db_type='%s' and alert_item='%s';" % (
server_id, db_type, alert_item)
alert_count = mysql.GetSingleValue(mysql_conn, count_str)
if int(alert_count) > 0:
sql = "insert into alerts_his select *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from alerts where server_id=%s and db_type='%s' and alert_item='%s';" % (
server_id, db_type, alert_item)
try:
mysql.ExecuteSQL(mysql_conn, sql)
except Exception, e:
print "Move alert to history: " + str(e)
sql = "delete from alerts where server_id=%s and db_type='%s' and alert_item='%s' ;" % (
server_id, db_type, alert_item)
mysql.ExecuteSQL(mysql_conn, sql)
sql = "insert into alerts(server_id,tags,host,port,create_time,db_type,alert_item,alert_value,level,message,send_mail,send_mail_to_list,send_sms,send_sms_to_list) values(%s,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (
server_id, tags, db_host, db_port, create_time, db_type,
fb_value = arg
elif opt == '-n':
tab_name = arg
###########################################################################
# connect to mysql
mysql_conn = ''
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sys.exit(2)
str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
server_id)
conn_str = mysql.GetSingleValue(mysql_conn, str)
str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
server_id)
nopass_str = mysql.GetSingleValue(mysql_conn, str)
logger.info("The flashback database is: %s, the id is: %s" %
(nopass_str, server_id))
conn = oracle.ConnectOracleAsSysdba(conn_str)
if conn is None:
logger.error("Connect to flashback database error, exit!!!")
sys.exit(2)
else:
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sys.exit(2)
# get infomation from mysql
db_name = ""
s_host = ""
s_port = ""
s_username = ""
s_password = ""
name_str = """select db_name from db_cfg_sqlserver_mirror where id=%s; """ %(group_id)
db_name = mysql.GetSingleValue(mysql_conn, name_str)
s_str = """select host, port, username, password from db_cfg_sqlserver where id=%s; """ %(sta_id)
res2 = mysql.GetSingleRow(mysql_conn, s_str)
if res2:
s_host = res2[0]
s_port = res2[1]
s_username = res2[2]
s_password = res2[3]
#print s_host,s_port,s_username,s_password
s_str = """select concat(host, ':', port) from db_cfg_sqlserver where id=%s; """ %(sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
logger.info("The standby database is: " + s_nopass_str + ", the id is: " + str(sta_id))
elif opt == '-g':
group_id = arg
###########################################################################
# connect to mysql
mysql_conn = ''
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sys.exit(2)
# get infomation from mysql
p_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
pri_id)
p_conn_str = mysql.GetSingleValue(mysql_conn, p_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_conn_str = mysql.GetSingleValue(mysql_conn, s_str)
p_str = """select concat(username, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
pri_id)
p_nopass_str = mysql.GetSingleValue(mysql_conn, p_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
logger.info("The primary database is: " + p_nopass_str + ", the id is: " +
str(pri_id))
logger.info("The standby database is: " + s_nopass_str + ", the id is: " +
str(sta_id))
sta_id = arg
elif opt == '-g':
group_id = arg
###########################################################################
# connect to mysql
mysql_conn = ''
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sys.exit(2)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_conn_str = mysql.GetSingleValue(mysql_conn, s_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
logger.info("The standby database is: " + s_nopass_str + ", the id is: " +
str(sta_id))
dg_pid_str = """select t.primary_db_id from db_cfg_oracle_dg t where id = %s """ % (
group_id)
dg_pid = mysql.GetSingleValue(mysql_conn, dg_pid_str)
try:
common.operation_lock(mysql_conn, group_id, 'FAILOVER')
elif opt == '-g':
group_id = arg
###########################################################################
# connect to mysql
mysql_conn = ''
try:
mysql_conn = mysql.ConnectMysql()
except Exception as e:
logger.error(e)
sys.exit(2)
p_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ %(pri_id)
p_conn_str = mysql.GetSingleValue(mysql_conn, p_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ %(sta_id)
s_conn_str = mysql.GetSingleValue(mysql_conn, s_str)
p_str = """select concat(username, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ %(pri_id)
p_nopass_str = mysql.GetSingleValue(mysql_conn, p_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ %(sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
p_dest_str = """select (case when t.primary_db_id = %s then t.primary_db_dest else t.standby_db_dest end) as dest_id from db_cfg_oracle_dg t where t.id = %s """ %(pri_id, group_id)
p_dest_id = mysql.GetSingleValue(mysql_conn, p_dest_str)
logger.info("The primary database is: " + p_nopass_str + ", the id is: " + str(pri_id))
logger.info("The standby database is: " + s_nopass_str + ", the id is: " + str(sta_id))
