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