def drop_expire_restore_point(host, port, dsn, username, password, server_id,
tags):
try:
conn = get_connect(server_id)
cur = conn.cursor()
db_time = oracle.get_sysdate(conn)
open_mode = oracle.get_database(conn, 'open_mode')
stb_redo_count = oracle.get_standby_redo_count(conn)
parameters = oracle.get_parameters(conn)
flashback_retention = parameters['db_flashback_retention_target']
p_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
server_id)
p_conn_str = func.mysql_single_query(p_str)
recover_str = ""
if stb_redo_count > 0:
recover_str = "alter database recover managed standby database using current logfile disconnect from session;"
else:
recover_str = "alter database recover managed standby database disconnect from session;"
# 每天0点，删除过期的闪回点
if db_time[8:10] == "00":
r_name_list = oracle.get_expire_restore_list(
conn, flashback_retention)
if r_name_list:
logger.info("begin drop expire restore point for server: %s" %
(server_id))
if open_mode == "MOUNTED" or open_mode == "READ WRITE":
for r_name in r_name_list:
str = 'drop restore point %s' % (r_name[0])
cur.execute(str)
logger.info('drop expire restore point: %s for %s' %
(r_name[0], server_id))
elif open_mode == "READ ONLY" or open_mode == "READ ONLY WITH APPLY":
sqlplus = Popen(
["sqlplus", "-S", p_conn_str, "as", "sysdba"],
stdout=PIPE,
stdin=PIPE)
sqlplus.stdin.write(
bytes("shutdown immediate" + os.linesep))
sqlplus.stdin.write(bytes("startup mount" + os.linesep))
out, err = sqlplus.communicate()
logger.info(out)
logger.error(err)
try:
conn = get_connect(server_id)
cur = conn.cursor()
for r_name in r_name_list:
str = 'drop restore point %s' % (r_name[0])
cur.execute(str)
logger.info(
'drop expire restore point: %s for %s' %
(r_name[0], server_id))
except Exception, e:
logger.error(e)
finally:
def create_restore_point(conn, flashback_retention):
cur = None
try:
last_restore_time = oracle.get_last_fbtime(conn)
db_time = oracle.get_sysdate(conn)
time_def = -1
if last_restore_time <> 'null':
time_def = (datetime.datetime.strptime(db_time, '%Y%m%d%H%M%S') -
datetime.datetime.strptime(last_restore_time,
'%Y%m%d%H%M%S')).seconds
# 没有闪回点，或者当前数据库时间和最后的闪回点时间相差1小时以上，创建闪回点
logger.info('last_restore_time: %s' % (last_restore_time))
logger.info('db_time: %s' % (db_time))
logger.info('time_def: %s' % (time_def))
if last_restore_time == 'null' or time_def > 3600:
db_unique_name = oracle.get_database(conn, 'db_unique_name')
cur = conn.cursor()
try:
# 关闭MRP进程
stb_redo_count = oracle.get_standby_redo_count(conn)
#logger.info("type stb_redo_count : %s" %(type(stb_redo_count)))
mrp_status = oracle.get_dg_s_mrp(conn)
#logger.info('mrp_status: %s' %(mrp_status))
if mrp_status == 1:
str = 'alter database recover managed standby database cancel'
cur.execute(str)
#生成闪回点
restore_name = db_unique_name + db_time
str = 'create restore point %s' % (restore_name)
cur.execute(str)
finally:
# 如果一开始MRP进程是开启状态，则创建完成后，再次开启MRP进程
if mrp_status == 1:
if stb_redo_count == 0:
str = 'alter database recover managed standby database disconnect from session'
else:
str = 'alter database recover managed standby database using current logfile disconnect from session'
cur.execute(str)
except Exception, e:
logger.error(e)
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;", '')
logger.error(str(e).strip('\n'))
sys.exit(1)
finally:
sys.exit(1)
finally:
func.check_db_status(server_id, host, port, tags, 'oracle')
try:
##func.mysql_exec('delete from oracle_redo where server_id = %s;' %(server_id),'')
#get info by v$instance
connect = 1
instance_name = oracle.get_instance(conn, 'instance_name')
instance_role = oracle.get_instance(conn, 'instance_role')
database_role = oracle.get_database(conn, 'database_role')
db_name = oracle.get_database(conn, 'name')
open_mode = oracle.get_database(conn, 'open_mode')
protection_mode = oracle.get_database(conn, 'protection_mode')
if database_role == 'PRIMARY':
database_role_new = 'm'
dg_stats = '-1'
dg_delay = '-1'
else:
database_role_new = 's'
#dg_stats = oracle.get_stats(conn)
#dg_delay = oracle.get_delay(conn)
dg_stats = '1'
dg_delay = '1'
instance_status = oracle.get_instance(conn, 'status')