检测oracle数据库同步和备份

最新推荐文章于 2024-03-06 22:31:35 发布

caixingyunzi

最新推荐文章于 2024-03-06 22:31:35 发布

阅读量1.4k

点赞数

分类专栏： python程序记录（以后回过头来看）文章标签： python 数据库备份和同步检测

本文链接：https://blog.csdn.net/caixingyunzi/article/details/80901008

版权

python程序记录（以后回过头来看）专栏收录该内容

5 篇文章 0 订阅

订阅专栏

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# auther : xiaojinsong(61627515@qq.com)


import paramiko
import logging, configparser
import cx_Oracle, datetime
import re
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import socket


class Mycheck():
    def __init__(self, **kwargs):
        self.user = kwargs['user']
        self.password = kwargs['password']
        self.dbhost = kwargs['dbhost']
        self.dbname = kwargs['dbname']
        self.dghost = kwargs['dghost']
        self.dgname = kwargs['dgname']
        self.backdir = kwargs['backdir']
        self.dbnum = kwargs['dbnum']

    def check_dg(self):
        try:
            conn = cx_Oracle.connect('%s/%s@%s/%s' % (self.user, self.password, self.dghost, self.dgname))
        except Exception as e:
            logging.error(e)
            msg = '%s connect faild' % self.dghost
            return msg
        else:
            cursor = conn.cursor()
            sql = """
            SELECT a.SEQUENCE#,a.APPLIED,a.thread#,a.FIRST_TIME FROM v$archived_log a order by a.FIRST_TIME desc
            """
            try:
                cursor.execute(sql)
            except Exception as e:
                logging.error(e)
            onedata = cursor.fetchone()
            if onedata[1] == 'IN-MEMORY':
                gettime = onedata[3]
                today = datetime.date.today()
                if gettime.year == today.year and gettime.month == today.month and gettime.day == today.day:
                    msg = '%s %s successful sync' % (self.dghost, self.dgname)
                    logging.info(msg)
                else:
                    msg = '%s %s sync faild' % (self.dghost, self.dgname)
                    logging.info(msg)
            else:
                msg = '%s %s sync faild' % (self.dghost, self.dgname)
                logging.info(msg)
            return msg
            cursor.close()
            conn.close()

    def check_bak(self):
        try:
            ssh = paramiko.SSHClient()
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

            ssh.connect(
                hostname=self.dbhost,
                port=22,
                username='root',
            )
            today = datetime.datetime.now().strftime('%Y%m%d')
            shell_command = "for i in `ls %s/backup*%s*.log`;do cat $i | sed -n '$'p;done" % (self.backdir, today)
            stdin, stdout, stderr = ssh.exec_command(shell_command)
            result = stdout.read()
            if len(result) == 0:
                logging.error(stderr.read().decode())
                msg = '%s %s backup faild' % (self.dbhost, self.dbname)
                logging.info(msg)
            else:
                result = str(result, 'utf-8')
                object = re.findall('successfully completed', result)
                if len(object) == int(self.dbnum):
                    msg = '%s %s successful backup' % (self.dbhost, self.dbname)
                    logging.info(msg)
                else:
                    msg = '%s %s backup faild' % (self.dbhost, self.dbname)
                    logging.info(msg)
            return msg
            ssh.close()
        except Exception as e:
            logging.ERROR(e)


def send_email(SMTP_host, from_account, from_passwd, to_account, subject, content):
    '''
    SMTP_host:邮件服务器，
    from_account：邮箱账号/发送者邮箱
    from_passwd：邮箱密码
    to_account：接收者邮箱，是一个list
    subject：邮件主题
    content：邮件内容
    '''
    # create msg
    msg = MIMEText(content, 'plain', 'utf-8')
    msg['Subject'] = Header(subject, 'utf-8')  # subject
    msg['From'] = from_account
    msg['To'] = ';'.join(to_account)

    try:
        email_client = smtplib.SMTP_SSL(SMTP_host, '465')
        email_client.login(from_account, from_passwd)
        email_client.sendmail(from_account, to_account, msg.as_string())
    except smtplib.SMTPException as e:
        print(e)
        print('邮件发送失败！')
    except socket.gaierror as e:
        print(e)
        print('邮件服务器链接失败！')
    else:
        email_client.quit()
        print('成功发送%s封邮件.' % len(to_account))


def main():
    ###定义日志格式
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
        datefmt='%a, %d %b %Y %H:%M:%S',
        filename='/tmp/check_bakup.log',
    )

    ####读取配置文件
    config = configparser.ConfigParser()
    try:
        config.read('/apps/sh/zabbix_api/oracle.ini')
    except Exception as e:
        logging.error(e)

    ###主体
    dgsuc = []
    dbsuc = []
    for section in config.sections():
        tempdict = {}
        for k, v in config.items(section):
            tempdict[k] = v
        tempobj = Mycheck(**tempdict)
        dgmsg = tempobj.check_dg()
        dgsuc.append(dgmsg)
        dbmsg = tempobj.check_bak()
        dbsuc.append(dbmsg)

    ###邮件告警
    from_account = 'xxxx@xxxx.com'
    from_passwd = 'xxxxx'
    to_account = ['xxxx@xxxxx', 'xxxx@xxxxx']
    SMTP_host = 'xxxx.xxxxx.com'
    subject = '公司各大数据库同步备份情况一览'
    content = '''
    ####数据库备份情况######
    %s
    ####dg库同步情况#######
    %s
    ''' % (','.join(dbsuc), ','.join(dgsuc))
    send_email(SMTP_host, from_account, from_passwd, to_account, subject, content)


if __name__ == "__main__":
    main()

oracle.ini

[DEFAULT]
user=xxxx
password=xxxxx

[xxxxx]
dbhost=xx.xx.xx.xx
dbname=xxxdb
dghost=xx.xx.xx.xx
dgname=xxxdg
backdir=/databak/xxxdb
dbnum=1

[xxxx]
dbhost=xx.xx.xx.xx
dbname=xxxdb
dghost=xx.xx.xx.xx
dgname=xxxdg
backdir=/databak/xxxdb
dbnum=1

[xxxx&&xxxx]
dbhost=xx.xx.xx.xx
dbname=xxxdb
dghost=xx.xx.xx.xx
dgname=xxxdg
backdir=/databak/xxxdb
dbnum=2

修改版

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# auther : xiaojinsong(61627515@qq.com)


import paramiko
import logging, configparser
import cx_Oracle, datetime
import re
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import socket


class Mycheck():
    def __init__(self, **kwargs):
        self.user = kwargs['user']
        self.password = kwargs['password']
        self.dbhost = kwargs['dbhost']
        self.dbname = kwargs['dbname']
        self.dghost = kwargs['dghost']
        self.dgname = kwargs['dgname']
        self.backdir = kwargs['backdir']
        self.dbnum = kwargs['dbnum']

    def check_dg(self):
        try:
            conn = cx_Oracle.connect('%s/%s@%s/%s' % (self.user, self.password, self.dghost, self.dgname))
        except Exception as e:
            logging.error(e)
        else:
            cursor = conn.cursor()
            sql = """
            SELECT a.FIRST_TIME FROM v$archived_log a where a.APPLIED='IN-MEMORY'
            """
            try:
                cursor.execute(sql)
            except Exception as e:
                logging.error(e)
            row = cursor.fetchone()
            if row:
                gettime = row[0]
                today = datetime.date.today()
                if gettime.year == today.year and gettime.month == today.month and gettime.day == today.day:
                    msg = '%s %s successful sync' % (self.dghost, self.dgname)
                    logging.info(msg)
                else:
                    msg = '%s %s sync faild' % (self.dghost, self.dgname)
                    logging.info(msg)
            else:
                msg = '%s %s sync faild' % (self.dghost, self.dgname)
                logging.info(msg)
            return msg
            cursor.close()
            conn.close()

    def check_bak(self):
        try:
            ssh = paramiko.SSHClient()
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

            ssh.connect(
                hostname=self.dbhost,
                port=22,
                username='root',
            )
            today = datetime.datetime.now().strftime('%Y%m%d')
            shell_command = "for i in `ls %s/backup*%s*.log`;do cat $i | sed -n '$'p;done" % (self.backdir, today)
            stdin, stdout, stderr = ssh.exec_command(shell_command)
            result = stdout.read()
            if len(result) == 0:
                logging.error(stderr.read().decode())
                msg = '%s %s backup faild' % (self.dbhost, self.dbname)
                logging.info(msg)
            else:
                result = str(result, 'utf-8')
                object = re.findall('successfully completed', result)
                if len(object) == int(self.dbnum):
                    msg = '%s %s successful backup' % (self.dbhost, self.dbname)
                    logging.info(msg)
                else:
                    msg = '%s %s backup faild' % (self.dbhost, self.dbname)
                    logging.info(msg)
            return msg
            ssh.close()
        except Exception as e:
            logging.ERROR(e)


def send_email(SMTP_host, from_account, from_passwd, to_account, subject, content):
    '''
    SMTP_host:邮件服务器，
    from_account：邮箱账号/发送者邮箱
    from_passwd：邮箱密码
    to_account：接收者邮箱，是一个list
    subject：邮件主题
    content：邮件内容
    '''
    # create msg
    msg = MIMEText(content, 'plain', 'utf-8')
    msg['Subject'] = Header(subject, 'utf-8')  # subject
    msg['From'] = from_account
    msg['To'] = ';'.join(to_account)

    try:
        email_client = smtplib.SMTP_SSL(SMTP_host, '465')
        email_client.login(from_account, from_passwd)
        email_client.sendmail(from_account, to_account, msg.as_string())
    except smtplib.SMTPException as e:
        print(e)
        print('邮件发送失败！')
    except socket.gaierror as e:
        print(e)
        print('邮件服务器链接失败！')
    else:
        email_client.quit()
        print('成功发送%s封邮件.' % len(to_account))


def main():
    ###定义日志格式
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
        datefmt='%a, %d %b %Y %H:%M:%S',
        filename='/tmp/check_bakup.log',
    )

    ####读取配置文件
    config = configparser.ConfigParser()
    try:
        config.read('/apps/sh/zabbix_api/oracle.ini')
    except Exception as e:
        logging.error(e)

    ###主体
    dgsuc = []
    dbsuc = []
    for section in config.sections():
        tempdict = {}
        for k, v in config.items(section):
            tempdict[k] = v
        tempobj = Mycheck(**tempdict)
        dgmsg = tempobj.check_dg()
        if dgmsg:
            dgsuc.append(dgmsg)
        else:
            dgsuc.append("{} {} unreachable".format(tempobj.dghost, tempobj.dgname))
        dbmsg = tempobj.check_bak()
        if dbmsg:
            dbsuc.append(dbmsg)
        else:
            dbsuc.append("{} {} unreachable".format(tempobj.dbhost, tempobj.dbname))


    ###邮件告警
    from_account = 'zabbix@yujiahui.com'
    from_passwd = 'Yjh@20181022alter'
    to_account = ['qidian@yujiahui.com', 'baiyang@yujiahui.com']
    SMTP_host = 'mail.yujiahui.com'
    subject = '公司各大数据库同步备份情况一览'
    content = '''
    ####数据库备份情况######
    %s
    ####dg库同步情况#######
    %s
    ''' % (','.join(dbsuc), ','.join(dgsuc))
    send_email(SMTP_host, from_account, from_passwd, to_account, subject, content)


if __name__ == "__main__":
    main()