# -*- coding:utf-8 -*-
import cx_Oracle
import codecs
from pprint import pprint
import csv
import time
import re
import binascii
import os
import sys
import logging
import string
import datetime
"""
定时更新系统时间:
# crontab -l
30 * * * * /usr/sbin/ntpdate 192.168.89.200
创建数据库表
create table checkadg (
id number(10) primary key,
dt varchar2(20) not null
);
初始化第一条记录
insert into checkadg1 values (1,'2018-12-18 20:50:14');
创建新用户
SQL> create user checkadg identified by checkadg1234;
User created.
修改用户密码
SQL> alter user checkadg identified by chkeck123456;
User altered.
赋予访问权限
SQL> grant connect,resource to checkadg;
Grant succeeded.
赋予访问权限
SQL> grant create session to checkadg;
Grant succeeded.
赋予访问权限
SQL> grant select,insert,update,delete,all on sys.checkadg1 to checkadg;
Grant succeeded.
write @ 2018.12.18
"""
reload(sys)
sys.setdefaultencoding('utf8')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
def write_logs(wr_logs):
# 写日志文件
get_now = datetime.datetime.now()
fs_name = '/tmp/checkadg_' + datetime.datetime.strftime(get_now, '%Y-%m-%d') + '.log'
fo = open(fs_name, 'a+w')
fo.write(wr_logs)
fo.write("\n")
fo.close()
def send_msg(msg):
# 发短信
con = cx_Oracle.connect('xxk/xxk123456@192.168.100.93/test')
cur = con.cursor()
sql = "SELECT f_send_msg('18639013755', '" + msg + "', 1) from dual"
cur.execute(sql)
cur.close()
con.close()
def check_rac(dt):
# 连接RAC 10.20.0.75,UPDATE
# 记录代码开始时间
start_time = time.time()
con = cx_Oracle.connect('checkadg/chkeck123456@10.20.0.75/orcl')
cur = con.cursor()
# 更新记录
sql = "UPDATE sys.checkadg SET dt = '" + dt + "' where id =1"
cur.execute(sql)
con.commit()
cur.close()
con.close()
# 计算运行时间
end_time = time.time() # 记录代码结束时间
run_time = end_time - start_time # 计算运行时间
if run_time < 2:
log_msg = "RAC数据库正常! 连接耗时:" + str(round(run_time, 3)) + "秒!"
write_logs(log_msg)
else:
log_msg = dt + ":RAC数据库连接不正常,耗时超过:" + str(round(run_time, 3)) + "秒。"
write_logs(log_msg)
send_msg(log_msg)
return 1
def check_adg(dt):
# 连接ADG 10.20.0.209,SELECT
# 记录代码开始时间
start_time = time.time()
con = cx_Oracle.connect('checkadg/chkeck123456@10.20.0.209/orcldg')
cur = con.cursor()
# 从ADG数据库中查询记录
sql = "SELECT dt from sys.checkadg where id =1"
cur.execute(sql)
rs = ""
for result in cur:
rs = result[0]
cur.close()
con.close()
# 计算运行时间
end_time = time.time() # 记录代码结束时间
run_time = end_time - start_time # 计算运行时间
if dt == rs:
log_msg = "ADG同步正常! 耗时" + str(round(run_time, 3)) + "秒。"
temp_dt = "查询结果:" + rs
write_logs(temp_dt)
write_logs(log_msg)
else:
log_msg = dt + ": ADG同步不正常! 耗时超过" + str(round(run_time, 3)) + "秒。"
write_logs(log_msg)
return 1
for num in range(0, 50):
splite_line = "------------------------ " + str(num) + " ------------------------------------"
write_logs(splite_line)
get_dt = datetime.datetime.now()
dt1 = datetime.datetime.strftime(get_dt, '%Y-%m-%d %H:%M:%S')
write_logs(dt1)
if check_rac(dt1) ==1:
exit()
time.sleep(1)
if check_adg(dt1) == 1:
time.sleep(1)
if check_adg(dt1) == 1:
time.sleep(1)
if check_adg(dt1) == 1:
put_msg = dt1 + ":ADG同步不正常! 耗时超过3秒钟!"
send_msg(put_msg)
exit()