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;
