def stop_mrp(mysql_conn, group_id, s_conn, s_conn_str, sta_id): result = -1 logger.info("Stop the MRP process for databaes %s in progress..." % (sta_id)) # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', '获取数据库角色成功。', 20, 2) logger.info("The current database role is: " + role) # get database version str = """select substr(version, 0, instr(version, '.')-1) from v$instance""" version = oracle.GetSingleValue(s_conn, str) # get mrp process status str = """select count(1) from gv$session where program like '%(MRP0)' """ mrp_process = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', '获取MRP进程状态成功。', 30, 2) if role == "PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', '验证数据库角色成功。', 50, 2) if (mrp_process > 0): logger.info("Now we are going to stop the MRP process... ") sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) #logger.error(err) if err is None: common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', 'MRP进程停止成功。', 90, 2) logger.info("Stop the MRP process successfully.") result = 0 else: common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', '验证MRP进程,已经是停止状态。', 70, 2) logger.info("The MRP process is already stopped!!! ") else: common.log_dg_op_process(mysql_conn, group_id, 'MRP_STOP', '验证数据库角色失败,当前数据库不是PHYSICAL STANDBY,不能停止MRP。', 90) return result
def flashback_table(mysql_conn, server_id, conn, conn_str, restore_str, tab_name): result = -1 logger.info("Check the target database role. server_id is %s" % (server_id)) # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(conn, str) logger.info("The current database role is: " + role) # get instance status str = 'select status from v$instance' ins_status = oracle.GetSingleValue(conn, str) logger.info("The current instance status is: " + ins_status) if role == "PRIMARY" and ins_status == "OPEN": sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes("alter table " + tab_name + " enable row movement;" + os.linesep)) sqlplus.stdin.write(bytes(restore_str + os.linesep)) out, err = sqlplus.communicate() logger.debug(out) #logger.error(err) if err is None: logger.info("Flashback table successfully.") str = """update oracle_fb_process set result='1', reason='' where server_id=%s """ % ( server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) result = 0 else: logger.info("Flashback failed.") str = """update oracle_fb_process set result='0', reason='%s' where server_id=%s """ % ( err, server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) else: msg = "The current database role is not PRIMARY, not allow to flashback table." logger.info(msg) str = """update oracle_fb_process set result='0', reason='%s' where server_id=%s """ % ( msg, server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) return result
def main(): s_conn_str = "wlblazers/[email protected]:1522/orcl" s_conn = oracle.ConnectOracleAsSysdba(s_conn_str) if s_conn is None: print "Connect error" else: print "Connect successfully" str = 'select status from v$instance' status = oracle.GetSingleValue(s_conn, str) print "current status is: %s" % (status) time.sleep(300)
def kill_sessions(mysql_conn, ora_conn, server_id): host_ip = "" host_type = "" host_user = "" host_pwd = "" host_protocol = "" query_str = """select host, host_type, host_user, host_pwd, host_protocol from db_cfg_oracle t where t.id = %s """ % ( server_id) res = mysql.GetMultiValue(mysql_conn, query_str) for row in res: host_ip = row[0] host_type = row[1] host_user = row[2] host_pwd = row[3] host_protocol = row[4] logger.info("The database host type is %s" % (host_type)) # check host username if host_user is None or host_user == "": logger.info("The host user name is None, connect failed.") return # structure the srvctl command to shutdown the other instance when there are more then 1 active instance srvctl_cmd = "" host_list = "" inst_list = "" spid_list = "" # get the process spid which are the python connections str = "select p.spid from v$session s, v$process p where s.paddr = p.addr and s.program like 'python%' and type!='BACKGROUND' " spid_list = oracle.GetMultiValue(ora_conn, str) logger.info("spid list: %s" % (spid_list)) # check if more than one instance str = 'select count(1) from gv$instance' inst_count = oracle.GetSingleValue(ora_conn, str) if inst_count > 1: # get database name str = 'select name from v$database' db_name = oracle.GetSingleValue(ora_conn, str) # get current instance name str = 'select instance_name from v$instance' curr_name = oracle.GetSingleValue(ora_conn, str) # get other instance name list str = """select instance_name from gv$instance where instance_name != '%s' """ % ( curr_name) res = oracle.GetMultiValue(ora_conn, str) for row in res: inst_list = row[0] + "," # get other host name list str = """select host_name from gv$instance where instance_name != '%s' """ % ( curr_name) host_list = oracle.GetMultiValue(ora_conn, str) srvctl_cmd = "srvctl stop instance -d %s -i %s" % (db_name, inst_list) logger.info("srvctl command: %s" % (srvctl_cmd)) else: logger.info("There is only one active instance.") paramiko.util.log_to_file("paramiko.log") if host_type == 0 or host_type == 1 or host_type == 2 or host_type == 3: #host type: 0:Linux; 1:AIX; 2:HP-UX; 3:Solaris if host_protocol == 0: #protocol is ssh2 try: ssh = paramiko.SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh.connect(hostname=host_ip, port=22, username=host_user, password=host_pwd) stdin, stdout, stderr = ssh.exec_command("whoami") stdin.write( "Y" ) # Generally speaking, the first connection, need a simple interaction. print stdout.read() # kill all "(LOCAL=NO)" processes execmd = "ps -ef | grep 'LOCAL=NO' | grep -v grep | awk '{print $2}' " stdin, stdout, stderr = ssh.exec_command(execmd + "\n") pid_str = stdout.read() pid_str = pid_str.replace("\n", " ") #print stdout.read() for spid in spid_list: pid_str = pid_str.replace(spid[0], " ") execmd = "kill -9 %s" % (pid_str) stdin, stdout, stderr = ssh.exec_command(execmd + "\n") logger.info("kill os id list: %s" % (pid_str)) # kill processes on other nodes when there have more than one instance active chan = "" if inst_count > 1: logger.info( "There are more than one active instance, should shutdown the others first." ) #kill "(LOCAL=NO)" processes in other node chan = ssh.get_transport().open_session() chan.settimeout(20) chan.get_pty() chan.invoke_shell() for server in host_list: ssh_cmd = "ssh %s \n" % (server[0]) logger.info("ssh_cmd: %s" % (ssh_cmd)) chan.send(ssh_cmd) chan.send(execmd + "\n") result = "" while True: # 这个循环很重要,保证接受到所有命令执行的返回结果。 time.sleep(0.5) res = chan.recv(1024) result += res if result: sys.stdout.write(result.strip('\n')) if res.endswith('# ') or res.endswith('$ '): break #shutdown oracle instance in other node stdin, stdout, stderr = ssh.exec_command( ". ~/.bash_profile; %s" % (srvctl_cmd)) print stdout.read() print stderr.read() chan.close() except: pass finally: ssh.close() elif host_protocol == 1: #protocol is telnet pass elif host_type == 4: #host type: 4:Windows logger.info("The database host type is Windows, Exit!")
def stop_mrp(mysql_conn, group_id, s_conn, s_conn_str, sta_id): result = -1 logger.info("Stop the MRP process for databaes %s in progress..." % (sta_id)) # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '获取数据库角色成功', 20, 2) logger.info("The current database role is: " + role) # get database version str = """select substr(version, 0, instr(version, '.')-1) from v$instance""" version = oracle.GetSingleValue(s_conn, str) logger.info("The current database version is: " + version) # get instance status str = 'select status from v$instance' status = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '获取数据库角色成功', 20, 2) logger.info("The current instance status is: " + status) if version <= 10: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '退出演练状态失败,当前数据库版本不支持', 90, 2) return result if role == "SNAPSHOT STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '验证数据库角色成功', 50, 2) if status != "MOUNTED": logger.info("Instance is not in MOUNT, startup mount first... ") sqlplus = Popen(["sqlplus", "-S", s_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("Now we are going to convert to physical standby... ") sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes("alter database convert to physical standby;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) #logger.error(err) if 'ORA-' in out: rea_str = '停止快照模式失败,原因是:%s' % (out[out.index("ORA-"):]) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', rea_str, 90, 2) logger.info("Convert to physical standby failed!!! ") else: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '退出演练模式成功', 90, 2) logger.info("Convert to physical standby successfully.") result = 0 common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '正在开启同步进程...', 90, 0) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes("shutdown immediate;" + os.linesep)) sqlplus.stdin.write(bytes("startup mount;" + os.linesep)) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) sqlplus.stdin.write( bytes( "alter database recover managed standby database using current logfile disconnect from session;" + os.linesep)) out, err = sqlplus.communicate() common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '开启同步进程成功', 90, 0) else: common.update_op_reason(mysql_conn, group_id, 'SNAPSHOT_STOP', '验证数据库角色失败,当前数据库不是容灾库,不能退出演练') common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_STOP', '验证数据库角色失败,当前数据库不是容灾库,不能退出演练', 90) return result
def flashback_db(mysql_conn, server_id, conn, conn_str, restore_str): result = -1 logger.info("Check the target database role. server_id is %s" % (server_id)) # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(conn, str) logger.info("The current database role is: " + role) # get mrp process status str = """select count(1) from gv$session where program like '%(MRP0)' """ mrp_process = oracle.GetSingleValue(conn, str) if role == "PHYSICAL STANDBY": if (mrp_process > 0): logger.info( "The mrp process is already active, should to stop it first. ") sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) out, err = sqlplus.communicate() logger.debug(out) if 'ORA-' in out: pass else: str = """update oracle_dg_s_status set mrp_status='1' where server_id=%s """ % ( server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes(restore_str + os.linesep)) out, err = sqlplus.communicate() logger.debug(out) if 'ORA-' in out: rea_str = out[out.index("ORA-"):] logger.info("Flashback failed.") str = """update oracle_fb_process set result='0', reason='%s' where server_id=%s """ % ( rea_str, server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) else: # 开启数据库到OPEN状态 sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) out, err = sqlplus.communicate() logger.info("Flashback successfully.") str = """update oracle_fb_process set result='1', reason='' where server_id=%s """ % ( server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) result = 0 else: sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) logger.info(restore_str) sqlplus.stdin.write(bytes(restore_str + os.linesep)) out, err = sqlplus.communicate() logger.debug(out) #logger.error(err) if 'ORA-' in out: rea_str = out[out.index("ORA-"):] logger.info("Flashback failed.") str = """update oracle_fb_process set result='0', reason='%s' where server_id=%s """ % ( rea_str, server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) else: # 开启数据库到OPEN状态 sqlplus = Popen(["sqlplus", "-S", conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) out, err = sqlplus.communicate() logger.info("Flashback successfully.") str = """update oracle_fb_process set result='1', reason='' where server_id=%s """ % ( server_id) op_status = mysql.ExecuteSQL(mysql_conn, str) result = 0 else: logger.info( "The current database role is not PHYSICAL STANDBY, not allow to flashback databaes." ) return result
def switch2standby(mysql_conn, group_id, p_conn, p_conn_str, pri_id): result = -1 logger.info("Switchover database to physical standby in progress...") # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(p_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '获取数据库角色成功', 15, 2) logger.info("The current database role is: " + role) # get switchover status str = 'select switchover_status from v$database' switch_status = oracle.GetSingleValue(p_conn, str) logger.info("The current database switchover status is: " + switch_status) # get gap count str = 'select count(1) from v$archive_gap' gap_count = oracle.GetSingleValue(p_conn, str) logger.info("The current database gap_count is: %s" % (gap_count)) # get database version str = """select substr(version, 0, instr(version, '.')-1) from v$instance""" version = oracle.GetSingleValue(p_conn, str) # get standby redo log str = 'select count(1) from v$standby_log' log_count = oracle.GetSingleValue(p_conn, str) logger.info("The current database has %s standby log" % (log_count)) recover_str = "" if log_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;" if role == "PRIMARY": common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '验证数据库角色成功', 20, 2) logger.info( "Now we are going to switch database %s to physical standby." % (pri_id)) if switch_status == "TO STANDBY" or switch_status == "SESSIONS ACTIVE" or switch_status == "FAILED DESTINATION" or ( switch_status == "RESOLVABLE GAP" and gap_count == 0): logger.info("Switchover to physical standby... ") common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '正在将主库切换成备库,可能会花费几分钟时间,请耐心等待...', 25, 0) sqlplus = Popen(["sqlplus", "-S", p_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database commit to switchover to physical standby with session shutdown;" + os.linesep)) sqlplus.stdin.write(bytes("shutdown immediate" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.error(err) sqlplus = Popen(["sqlplus", "-S", p_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes("startup mount" + os.linesep)) sqlplus.stdin.write(bytes(recover_str + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.error(err) # 获取oracle连接 p_conn = oracle.ConnectOracleAsSysdba(p_conn_str) if version > '10': logger.info( "Alter standby database to open read only in progress... ") common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '正在将备库启动到open readonly状态...', 40, 0) sqlplus = Popen(["sqlplus", "-S", p_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) sqlplus.stdin.write(bytes(recover_str + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.error(err) str = 'select open_mode from v$database' open_mode = oracle.GetSingleValue(p_conn, str) if open_mode == "READ ONLY" or open_mode == "READ ONLY WITH APPLY": common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '备库已经成功启动到open readonly状态', 45, 2) logger.info("Alter standby database to open successfully.") else: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '备库已经成功启动到open readonly状态', 45, 2) logger.error("Start MRP process failed!") str = 'select database_role from v$database' role = oracle.GetSingleValue(p_conn, str) if role == "PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '主库已经成功切换成备库', 50, 2) logger.info("Switchover to physical standby successfully.") result = 0 else: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '主库切换备库失败', 50, 2) logger.info("Switchover to physical standby failed.") result = -1 else: common.update_op_reason(mysql_conn, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是主库,不能切换到备库') common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是主库,不能切换到备库', 90, 2) logger.error( "You can not switchover a standby database to physical standby!") return result
def start_snapshot(mysql_conn, group_id, s_conn, s_conn_str, sta_id): result = -1 logger.info("Start to activate standby databaes %s in progress..." % (sta_id)) # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '获取数据库角色成功', 20, 2) logger.info("The current database role is: " + role) # get flashback status str = 'select flashback_on from v$database' fb_status = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '获取数据库快照状态成功', 20, 2) logger.info("The current flashback status is: " + fb_status) # get database version str = """select substr(version, 0, instr(version, '.')-1) from v$instance""" version = oracle.GetSingleValue(s_conn, str) # get mrp process status str = """select count(1) from gv$session where program like '%(MRP0)' """ mrp_process = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '获取同步进程状态成功', 30, 2) if version <= 10: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '进入演练模式失败,当前数据库版本不支持', 90, 2) common.update_op_reason(mysql_conn, group_id, 'SNAPSHOT_START', '当前数据库版本不支持') return result if fb_status == "NO": common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '进入演练模式失败,当前数据库没有开启快照', 90, 2) common.update_op_reason(mysql_conn, group_id, 'SNAPSHOT_START', '当前数据库没有开启快照') return result if role == "PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '验证数据库角色成功', 40, 2) if (mrp_process > 0): logger.info( "The mrp process is already active, need stop first... ") common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '检测到同步进程正在运行,需要先停止同步进程', 50, 2) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '正在停止同步进程...', 50, 2) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) if 'ORA-' in out: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '停止同步进程失败', 70, 2) else: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '停止同步进程成功', 70, 2) common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '正在激活数据库快照...', 75, 2) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes("alter database convert to snapshot standby;" + os.linesep)) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) if 'ORA-' in out: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '数据库快照激活失败', 90, 2) else: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '数据库快照激活成功', 90, 2) result = 0 else: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '正在激活数据库快照...', 70, 2) sqlplus.stdin.write( bytes("alter database convert to snapshot standby;" + os.linesep)) sqlplus.stdin.write(bytes("alter database open;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) if 'ORA-' in out: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '数据库快照激活失败', 90, 2) else: common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '数据库快照激活成功', 90, 2) result = 0 else: common.update_op_reason(mysql_conn, group_id, 'SNAPSHOT_START', '验证数据库角色失败,当前数据库不是容灾库,不能激活快照') common.log_dg_op_process(mysql_conn, group_id, 'SNAPSHOT_START', '验证数据库角色失败,当前数据库不是容灾库,不能激活快照', 90) return result
def standby2primary(mysql_conn, group_id, s_conn, s_conn_str, sta_id): result = -1 logger.info("Switchover database to primary in progress...") # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '获取数据库角色成功', 55, 2) logger.info("The current database role is: " + role) # get switchover status str = 'select switchover_status from v$database' switch_status = oracle.GetSingleValue(s_conn, str) logger.info("The current database switchover status is: " + switch_status) if role == "PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '验证数据库角色成功', 70, 2) logger.info("Now we are going to switch database %s to primary." % (sta_id)) if switch_status == "NOT ALLOWED" or switch_status == "SWITCHOVER PENDING": show_str = "数据库状态为 %s,无法进行切换,尝试重启MRP进程" % (switch_status) common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', show_str, 70, 0) logger.info( "The standby database not allowed to switchover, restart the MRP process..." ) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) sqlplus.stdin.write( bytes( "alter database recover managed standby database disconnect from session;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.error(err) # check MRP status str = "select count(1) from gv$session where program like '%(MRP0)' " mrp_status = oracle.GetSingleValue(s_conn, str) if mrp_status > 0: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '重启数据库同步进程成功', 72, 0) logger.info("Restart the MRP process successfully.") else: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '重启数据库同步进程失败', 72, 0) logger.info("Restart the MRP process failed.") # 再次验证切换状态 timeout = 0 str = 'select switchover_status from v$database' switch_status = oracle.GetSingleValue(s_conn, str) while switch_status == "NOT ALLOWED" or switch_status == "SWITCHOVER PENDING": if timeout > 30: break show_str = "数据库状态为 %s,无法进行切换" % (switch_status) common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', show_str, 72, 2) str = 'select switchover_status from v$database' switch_status = oracle.GetSingleValue(s_conn, str) timeout = timeout + 2 if timeout > 30: logger.info("Switchover standby database to primary failed.") return -1 #超时退出 if switch_status == "TO PRIMARY" or switch_status == "SESSIONS ACTIVE": to_primary(mysql_conn, group_id, s_conn_str) if switch_status == "TO PRIMARY" or switch_status == "SESSIONS ACTIVE": to_primary(mysql_conn, group_id, s_conn_str) # 重新切换后数据库角色 s_conn = oracle.ConnectOracleAsSysdba(s_conn_str) str = 'select database_role from v$database' db_role = oracle.GetSingleValue(s_conn, str) if db_role == "PRIMARY": common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '备库已经成功切换成主库', 90, 2) logger.info("Switchover standby database to primary successfully.") result = 0 else: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '备库切换主库失败', 90, 2) logger.info("Switchover standby database to primary failed.") result = -1 else: common.update_op_reason(mysql_conn, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库无法切换到主库') logger.error("You can not switchover primary database to primary!") return result
'连接主库失败') common.update_op_result(mysql_conn, group_id, 'SWITCHOVER', '-1') sys.exit(2) if s_conn is None: common.log_dg_op_process(mysql_conn, group_id, 'SWITCHOVER', '连接备库失败,请根据相应日志查看原因', 10, 5) logger.error("Connect to standby database error, exit!!!") common.update_op_reason(mysql_conn, group_id, 'SWITCHOVER', '连接备库失败') common.update_op_result(mysql_conn, group_id, 'SWITCHOVER', '-1') sys.exit(2) #判断主备库是否是RAC str = "select value from v$option a where a.PARAMETER='Real Application Clusters' " is_p_rac = oracle.GetSingleValue(p_conn, str) is_s_rac = oracle.GetSingleValue(s_conn, str) str = 'select count(1) from gv$instance' p_count = oracle.GetSingleValue(p_conn, str) s_count = oracle.GetSingleValue(s_conn, str) # try to kill all "(LOCAL=NO)" connections in database try: if p_count > 1 or s_count > 1: common.log_dg_op_process( mysql_conn, group_id, 'SWITCHOVER', '正在尝试杀掉"(LOCAL=NO)"的会话,并关闭集群的其他节点可能需要一段时间,请耐心等待...', 5, 0) else: common.log_dg_op_process( mysql_conn, group_id, 'SWITCHOVER',
def failover2primary(mysql_conn, group_id, s_conn, s_conn_str, sta_id): logger.info("Failover database to primary in progress...") result = -1 # get database role str = 'select database_role from v$database' role = oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '获取数据库角色成功', 20, 2) logger.info("The current database role is: " + role) if role == "PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '验证数据库角色成功', 40, 2) logger.info( "Now we are going to failover standby database %s to primary." % (sta_id)) logger.info("Restart the standby database MRP process...") # 判断是否有已经传输过来的归档没有应用 str = "select count(1) from v$archived_log where dest_id = 1 and archived='YES' and applied='NO' " left_arch = oracle.GetSingleValue(s_conn, str) if left_arch > 1: show_str = "还有 %s 个归档等待应用" % (left_arch) common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', show_str, 50, 2) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write( bytes( "alter database recover managed standby database cancel;" + os.linesep)) sqlplus.stdin.write( bytes( "alter database recover managed standby database disconnect from session;" + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.info(err) # check MRP status str = "select count(1) from gv$session where program like '%(MRP0)' " mrp_status = oracle.GetSingleValue(s_conn, str) if mrp_status > 0: common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '重启数据库同步进程成功', 60, 2) logger.info("Restart the MRP process successfully.") else: common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '重启数据库同步进程失败', 60, 2) logger.info("Restart the MRP process failed.") timeout = 0 while left_arch > 1: if timeout > 60: break str = "select count(1) from v$archived_log where dest_id = 1 and archived='YES' and applied='NO' " left_arch = oracle.GetSingleValue(s_conn, str) show_str = "还有 %s 个归档等待应用" % (left_arch) common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', show_str, 65, 2) timeout = timeout + 2 if timeout > 300: common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '归档应用超时,灾难切换失败!', 90, 2) logger.info("Failover standby database to primary failed.") return -1 #超时退出 # 归档应用完毕,开始切换 failover(mysql_conn, group_id, s_conn_str) else: failover(mysql_conn, group_id, s_conn_str) # 重新验证切换后数据库角色 s_conn = oracle.ConnectOracleAsSysdba(s_conn_str) str = 'select database_role from v$database' db_role = oracle.GetSingleValue(s_conn, str) logger.info("Now the database role is: %s" % (db_role)) if db_role == "PRIMARY": common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '数据库灾难切换成功', 90, 2) logger.info("Failover standby database to primary successfully.") result = 0 else: common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '数据库灾难切换失败,请根据相关日志查看原因', 90, 2) logger.info("Failover standby database to primary failed.") result = -1 else: common.update_op_reason( mysql_conn, group_id, 'FAILOVER', '验证数据库角色失败,当前数据库不是PHYSICAL STANDBY,无法切换到Primary') common.log_dg_op_process( mysql_conn, group_id, 'FAILOVER', '验证数据库角色失败,当前数据库不是PHYSICAL STANDBY,无法切换到Primary', 90) logger.error("You can not failover primary database to primary!") return result
try: common.operation_lock(mysql_conn, group_id, 'FAILOVER') common.init_op_instance(mysql_conn, group_id, 'FAILOVER') #初始化切换实例 s_conn = oracle.ConnectOracleAsSysdba(s_conn_str) if s_conn is None: common.log_dg_op_process(mysql_conn, group_id, 'FAILOVER', '连接备库失败,请根据相应日志查看原因', 5, 5) logger.error("Connect to standby database error, exit!!!") common.update_op_reason(mysql_conn, group_id, 'FAILOVER', '连接备库失败') common.update_op_result(mysql_conn, group_id, 'FAILOVER', '-1') sys.exit(2) str = 'select count(1) from gv$instance' s_count = oracle.GetSingleValue(s_conn, str) # try to kill all "(LOCAL=NO)" connections in database try: if s_count > 1: common.log_dg_op_process( mysql_conn, group_id, 'FAILOVER', '正在尝试杀掉"(LOCAL=NO)"的会话,并关闭集群的其他节点可能需要一段时间,请耐心等待...', 5, 0) else: common.log_dg_op_process( mysql_conn, group_id, 'FAILOVER', '正在尝试杀掉"(LOCAL=NO)"的会话可能需要一段时间,请耐心等待...', 5, 0) common.kill_sessions(mysql_conn, s_conn, sta_id) except Exception, e: logger.error("kill sessions error!!!")
def start_mrp(mysql_conn, group_id, s_conn, s_conn_str, sta_id): result=-1 logger.info("Start the MRP process for databaes %s in progress..." %(sta_id)) # get database role str='select database_role from v$database' role=oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '获取数据库角色成功', 20, 2) logger.info("The current database role is: " + role) # get database version str="""select substr(version, 0, instr(version, '.')-1) from v$instance""" version=oracle.GetSingleValue(s_conn, str) # get instance status str="""select status from v$instance""" inst_status=oracle.GetSingleValue(s_conn, str) # get mrp process status str="""select count(1) from gv$session where program like '%(MRP0)' """ mrp_process=oracle.GetSingleValue(s_conn, str) common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '获取同步进程状态成功', 30, 2) # get standby redo log str='select count(1) from v$standby_log' log_count=oracle.GetSingleValue(s_conn, str) logger.info("The current database has %s standby log" %(log_count)) recover_str = "" if log_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;" if role=="PHYSICAL STANDBY": common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '验证数据库角色成功', 50, 2) if(mrp_process > 0): logger.info("The mrp process is already active... ") common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '验证同步进程,已经是激活状态', 70, 2) else: if version> 10 and inst_status=="MOUNTED": common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '检测到当前实例处于MOUNTED状态,正在启动到OPEN...', 70, 2) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes("alter database open;"+os.linesep)) out, err = sqlplus.communicate() if 'ORA-' in out: logger.info("Alter database open failed.") else: common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '启动实例到OPEN状态成功', 70, 2) logger.info("Alter database open successfully.") logger.info("Now we are going to start the mrp process... ") common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '正在开启同步进程...', 70, 2) sqlplus = Popen(["sqlplus", "-S", s_conn_str, "as", "sysdba"], stdout=PIPE, stdin=PIPE) sqlplus.stdin.write(bytes(recover_str + os.linesep)) out, err = sqlplus.communicate() logger.info(out) logger.error(err) # get mrp process status str="""select count(1) from gv$session where program like '%(MRP0)' """ mrp_process_a=oracle.GetSingleValue(s_conn, str) if mrp_process_a > 0: common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '同步进程开启成功', 90, 2) logger.info("Start the MRP process successfully.") result=0 else: common.update_op_reason(mysql_conn, group_id, 'MRP_START', '验证数据库角色失败,当前数据库不是PHYSICAL STANDBY,不能开启同步进程') common.log_dg_op_process(mysql_conn, group_id, 'MRP_START', '验证数据库角色失败,当前数据库不是PHYSICAL STANDBY,不能开启同步进程', 90) return result;