li_count = func.mysql_single_query(sql) if li_count == 0: sql = "select cntr_value from sqlserver_log where server_id='%s' and snap_id=(select max(snap_id) from sqlserver_log where server_id = '%s');; " % ( server_id, server_id) last_logMbyte = func.mysql_single_query(sql) if last_logMbyte: incr_logMbyte = logMegabyte - last_logMbyte sql = "insert into sqlserver_log(server_id, snap_id, end_time, cntr_value, incr_value) values(%s,%s,%s,%s,%s);" param = (server_id, snap_id, curr_time, logMegabyte, incr_logMbyte) func.mysql_exec(sql, param) func.mysql_exec("commit;", '') #send mail mail.send_alert_mail(server_id, host) except Exception, e: logger.error('traceback.format_exc():\n%s' % traceback.format_exc()) #print 'traceback.print_exc():'; traceback.print_exc() #print 'traceback.format_exc():\n%s' % traceback.format_exc() func.mysql_exec("rollback;", '') sys.exit(1) finally: conn.close() ###################################################################################################### # function get_connect ######################################################################################################
def check_dataguard(dg_id, pri_id, sta_id, is_switch): p_id = "" s_id = "" p_conn = "" s_conn = "" if is_switch == 0: p_id = pri_id s_id = sta_id else: p_id = sta_id s_id = pri_id try: p_conn = get_connect(p_id) s_conn = get_connect(s_id) #check dataguard status dg_p_curr_time = "" dg_s_curr_time = "" func.mysql_exec("begin;", '') func.mysql_exec( "insert into oracle_dg_p_status_his SELECT *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from oracle_dg_p_status where server_id in (%s, %s);" % (pri_id, sta_id), '') func.mysql_exec( 'delete from oracle_dg_p_status where server_id in (%s, %s);' % (pri_id, sta_id), '') func.mysql_exec( "insert into oracle_dg_s_status_his SELECT *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from oracle_dg_s_status where server_id in (%s, %s);" % (pri_id, sta_id), '') func.mysql_exec( 'delete from oracle_dg_s_status where server_id in (%s, %s);' % (pri_id, sta_id), '') if p_conn: # collect primary information # dg_p_info = oracle.get_dg_p_info(p_conn, 1) p_dest = func.mysql_single_query( "select case when t.primary_db_id = %s then t.primary_db_dest else t.standby_db_dest end from db_cfg_oracle_dg t where t.id = %s;" % (p_id, dg_id)) if p_dest is None: p_dest = 2 dg_p_info = oracle.get_dg_p_info_2(p_conn, p_dest) dest_id = -1 transmit_mode = "null" thread = -1 sequence = -1 archived_delay = -1 applied_delay = -1 current_scn = -1 if dg_p_info: # get new check_seq new_check_seq = func.mysql_single_query( "select ifnull(max(check_seq),0)+1 from oracle_dg_p_status where server_id=%s;" % (p_id)) for line in dg_p_info: dest_id = line[0] transmit_mode = line[1] thread = line[2] sequence = line[3] archived = line[4] applied = line[5] current_scn = line[6] dg_p_curr_time = line[7] archived_delay = oracle.get_log_archived_delay( p_conn, dest_id, thread) applied_delay = oracle.get_log_applied_delay( p_conn, dest_id, thread) #print thread, archived_delay, applied_delay ##################### insert data to mysql server############################# #print dest_id, thread, sequence, archived, applied, current_scn, curr_db_time sql = "insert into oracle_dg_p_status(server_id, check_seq, dest_id, transmit_mode, `thread#`, `sequence#`, curr_scn, curr_db_time, archived_delay, applied_delay) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (p_id, new_check_seq, dest_id, transmit_mode, thread, sequence, current_scn, dg_p_curr_time, archived_delay, applied_delay) func.mysql_exec(sql, param) logger.info( "Gather primary database infomation for server: %s" % (p_id)) else: logger.warning("Get no data from primary server: %s" % (p_id)) else: ##################### update data to db_status############################# func.mysql_exec( "update db_status set repl_delay=-1 where server_id = %s;" % (s_id), '') if s_conn and p_conn: dg_s_ms = oracle.get_dg_s_ms(s_conn) dg_s_rate = oracle.get_dg_s_rate(s_conn) dg_s_mrp = oracle.get_dg_s_mrp(s_conn) dg_s_scn = oracle.get_database(s_conn, 'current_scn') dg_s_al = oracle.get_dg_s_al(p_conn, dg_s_scn) logger.info( "Tye to get timestamp by scn(%s) from primary server %s for server %s" % (dg_s_scn, p_id, s_id)) dg_s_curr_time = oracle.get_time_by_scn(p_conn, dg_s_scn) if dg_s_curr_time == None: logger.info( "Try to get timestamp by scn(%s) from v$restorepoint of standby server %s" % (dg_s_scn, s_id)) dg_s_curr_time = oracle.get_time_from_restorepoint( s_conn, dg_s_scn) #logger.info("dg_s_curr_time: %s" %(dg_s_curr_time)) thread = -1 sequence = -1 block = -1 if dg_s_ms: thread = dg_s_ms[0] sequence = dg_s_ms[1] block = dg_s_ms[2] else: if dg_s_ms: thread = dg_s_al[0] sequence = dg_s_al[1] block = 0 dg_delay = -1 if dg_s_curr_time == None or dg_p_curr_time == None or dg_s_curr_time == "" or dg_p_curr_time == "": dg_delay = -1 else: p_time = datetime.datetime.strptime(dg_p_curr_time, '%Y-%m-%d %H:%M:%S') s_time = datetime.datetime.strptime(dg_s_curr_time, '%Y-%m-%d %H:%M:%S') dg_delay_days = (p_time - s_time).days dg_delay_seconds = (p_time - s_time).seconds dg_delay = dg_delay_days * 86400 + dg_delay_seconds #logger.info("p_time: %s" %(p_time)) #logger.info("s_time: %s" %(s_time)) #logger.info("dg_delay_days: %s" %(dg_delay_days)) #logger.info("dg_delay_seconds: %s" %(dg_delay_seconds)) #logger.info("dg_delay: %s" %(dg_delay)) if dg_delay < 0: dg_delay = 0 avg_apply_rate = -1 if dg_s_mrp == 0: avg_apply_rate = 0 elif dg_s_rate: avg_apply_rate = dg_s_rate[0] ##################### insert data to mysql server############################# sql = "insert into oracle_dg_s_status(server_id, `thread#`, `sequence#`, `block#`, delay_mins, avg_apply_rate, curr_scn, curr_db_time, mrp_status) values(%s,%s,%s,%s,%s,%s,%s,%s,%s);" param = (s_id, thread, sequence, block, dg_delay, avg_apply_rate, dg_s_scn, dg_s_curr_time, dg_s_mrp) func.mysql_exec(sql, param) ##################### update data to oracle_status############################# sql = "update oracle_status set dg_stats=%s, dg_delay=%s where server_id = %s;" param = (dg_s_mrp, dg_delay, s_id) func.mysql_exec(sql, param) # generate dataguard alert logger.info("Generate dataguard alert for server: %s begin:" % (s_id)) alert.gen_alert_oracle_dg(s_id) logger.info("Generate dataguard alert for server: %s end." % (s_id)) logger.info("Gather standby database infomation for server: %s" % (s_id)) func.mysql_exec("commit;", '') #send mail host = func.mysql_single_query( "select host from db_cfg_oracle where id = %s;" % (s_id)) mail.send_alert_mail(s_id, host) except Exception, e: logger.error(e) func.mysql_exec("rollback;", '')