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;", '')