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 exe_muitl_cmd(hostname, port, username, password):
conn_str = "wlblazers/[email protected]:1522/orcl"
conn = oracle.ConnectOracleAsSysdba(conn_str)
paramiko.util.log_to_file("paramiko.log")
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname=hostname,
port=port,
username=username,
password=password)
#cmd_list="""["whoami", "pwd", "ls -al"]"""
#stdin, stdout, stderr = ssh.exec_command(cmd_list)
#stdin.write("Y") # Generally speaking, the first connection, need a simple interaction.
#print stdout.read()
cmd_list = "ps -ef | grep '(LOCAL=NO)' | grep -v grep | awk '{print $2}'"
stdin, stdout, stderr = ssh.exec_command(cmd_list + "\n")
stdin.write(
"Y"
) # Generally speaking, the first connection, need a simple interaction.
spid_str = stdout.read()
spid_str = spid_str.replace("\n", " ")
#spid_list=spid_str.split("\n")
#spid_list.pop()
print type(spid_str)
print spid_str
print stderr.read()
ssh.close()
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 = oracle.GetMultiValue(conn, str)
for line in spid:
print type(line[0])
print "line: %s" % (line)
spid_str = spid_str.replace(line[0], " ")
print spid_str
except Exception as e:
logger.error(e)
sys.exit(2)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_conn_str = mysql.GetSingleValue(mysql_conn, s_str)
s_str = """select concat(username, '/', password, '@', host, ':', port, '/', dsn) from db_cfg_oracle where id=%s """ % (
sta_id)
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str)
logger.info("The standby database is: " + s_nopass_str + ", the id is: " +
str(sta_id))
s_conn = oracle.ConnectOracleAsSysdba(s_conn_str)
try:
common.operation_lock(mysql_conn, group_id, 'SNAPSHOT_STOP')
common.init_op_instance(mysql_conn, group_id,
'SNAPSHOT_STOP') #初始化切换实例
if s_conn is None:
logger.error("Connect to standby database error, exit!!!")
common.update_op_reason(mysql_conn, group_id, 'SNAPSHOT_STOP',
'连接数据库失败')
common.update_op_result(mysql_conn, group_id, 'SNAPSHOT_STOP',
'-1')
sys.exit(2)
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 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
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
