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))