Oracle 11gR2 RAC/ADG状态监测

# -*- 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()