Oracle数据库RMAN备份脚本

#!/usr/local/bin/python3

#coding=utf-8

import binascii

import logging

import time

from logging import handlers

import pyDes

import sys

from paramiko.client import SSHClient, AutoAddPolicy

from email.header import Header

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

from email.utils import parseaddr,formataddr

import smtplib

import os

# 邮件推送接收人,多个接收人用逗号隔开

MAILTO="XXXX@qq.com,2XXX@qq.ccom"

'''

日志文件配置

LOG_NAME 日志文件名

'''

LOG_NAME = f'{sys.path[0]}/logs/rmanbak_full_main.log'

'''

des密钥key和iv配置，长度8位字符

'''

DES_KEY = 'MouTai11'

DES_IV = 'mouTai11'

'''

远程数据库连接信息配置文件名

'''

BAK_DB_CONFIG = "dbConfig.txt"

# 判断备份级别0 或 1，默认0级

BAK_LEVEL = 0

BAK_TYPE="full"

if len(sys.argv) > 1:

    if sys.argv[1] == "1":

        BAK_LEVEL = 1

        BAK_TYPE="incr"

# adg主库rman需要额外多配置条策略

# CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

# rman 脚本生成

def RMAN_FULL_SCRIPT(bkpath):

    # format '/dbbak/rman/10.211.55.5/ctl_%d_%F.bak'

    RMAN_FULL_SCRIPT1="""source /home/oracle/.bash_profile;rman target / << EOF

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

allocate channel c4 device type disk;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 32 DAYS;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

"""

    RMAN_FULL_SCRIPT2=f"""

backup incremental level {BAK_LEVEL} as compressed backupset database format '{bkpath}/{BAK_TYPE}_level{BAK_LEVEL}_%d_%I_%T_%U.bak' tag='{dbSid}_level_{BAK_LEVEL}_{nowdate()}';

sql 'alter system archive log current';

backup archivelog all format '{bkpath}/arch_%d_%I_%T_%U.bak' delete all input;

backup as compressed backupset format='{bkpath}/spfile_%d_%I_%T_%U.bak' spfile tag='spfile_{nowdate()}';

backup as compressed backupset format='{bkpath}/ctl_%d_%I_%T_%U.bak' current controlfile tag='ctl_{nowdate()}';

"""

    RMAN_FULL_SCRIPT3="""

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

crosscheck backup;

delete noprompt expired backup;

list backup summary;

EOF

"""

    return RMAN_FULL_SCRIPT1 + RMAN_FULL_SCRIPT2 + RMAN_FULL_SCRIPT3

'''

日志操作类

'''

class LOG(object):

    level_relations = {

        'debug':logging.DEBUG,

        'info':logging.INFO,

        'warning':logging.WARNING,

        'error':logging.ERROR,

        'crit':logging.CRITICAL

    }#日志级别关系映射

    def __init__(self,filename,level='debug',when='D',backCount=3,fmt='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s'):

        self.logger = logging.getLogger(filename)

        formatStr = logging.Formatter(fmt)#设置日志格式

        self.logger.setLevel(self.level_relations.get(level))#设置日志级别

        sh = logging.StreamHandler()#往屏幕上输出

        sh.setFormatter(formatStr) #设置屏幕上显示的格式

        trfh = handlers.TimedRotatingFileHandler(filename=filename,when=when,backupCount=backCount,encoding='utf-8')

        trfh.setFormatter(formatStr)#设置文件里写入的格式

        self.logger.addHandler(sh) #把对象加到logger里

        self.logger.addHandler(trfh)

'''加密'''

def des_encrypt(s):

    try:

        ds = pyDes.des(DES_KEY, pyDes.CBC, DES_IV)

        e = ds.encrypt(s.strip(), padmode=pyDes.PAD_PKCS5)

        return binascii.b2a_hex(e).decode()

    except Exception as ex:

        return None

'''解密'''

def des_descrypt(s):

    try:

        ds = pyDes.des(DES_KEY, pyDes.CBC, DES_IV)

        d = ds.decrypt(binascii.a2b_hex(s), padmode=pyDes.PAD_PKCS5)

        return d.decode()

    except Exception as ex:

        return None

# 取出当前时间

def now():

    return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())

# 取出当前时间

def nowdate():

    return time.strftime('%Y-%m-%d-%H%M',time.localtime())

# 计算备份文件大小

def getDirSize(path):

    file_size = 0

    file_list=os.listdir(path)

    for file in file_list:

        if os.path.isdir(os.path.join(path,file)):

            getDirSize(os.path.join(path,file))

        else:

            file_size+=os.stat(os.path.join(path,file)).st_size

    return round(file_size/1024/1024/1024,2)

# ss = des_encrypt("abcd")

'''从配置文件中取出所有数据库连接字符串'''

def getDbConnStr(dcfn):

    try:

        with open(dcfn, 'r', encoding='utf-8') as f:

            return True,f.readlines()[1:]

    except Exception as ex:

        log.logger.error(f"读取备份列表失败，请检查dbConfig.txt文件，错误：{ex}")

        return False,ex

class SSH():

    def __init__(self):

        self.sshcli = SSHClient()

    # appName | osIp | osPort | osUser | osPwd | dbSid

    def conn(self,osIp,osPort,osUser,osPwd):

        try:

            # 设置允许连接known_hosts文件中的主机（默认连接不在known_hosts文件中的主机会拒绝连接抛出SSHException）

            self.sshcli.set_missing_host_key_policy(AutoAddPolicy())

            self.sshcli.connect(osIp,port=osPort,username=osUser,password=osPwd,timeout=1000)

            self.invshell = self.sshcli.invoke_shell()

            self.invshell.settimeout(1000)

            return True,"ssh初始化成功"

        except Exception as ex:

            return False,ex

    # 此函数用于执行command参数中的命令并打印命令执行结果

    def oracle(self,command):

        try:

            stdin, stdout, stderr = self.sshcli.exec_command(command)

            # log.logger.info(stderr.read().decode())

            return True,stdout.read().decode()

        except Exception as ex:

            return False,ex

    def oraclecmd(self,command):

        try:

            self.invshell.send(command)

            self.invshell.send('\n')

            buff = ''

            while not buff.endswith(('$', '$ ')):

                resp = self.invshell.recv(9999)

                buff += resp.decode('utf8', errors='ignore')

                log.logger.debug(buff)

            return True,buff.strip()

        except Exception as ex:

            return False,ex

    # 此函数用于退出登录

    def close(self):

        try:

            self.sshcli.close()

            return True,"ssh关闭成功"

        except Exception as ex:

            return False,ex

# 最终结果通过Email发送

def _format_addr(s):

    name, addr = parseaddr(s)

    return formataddr((Header(name,'utf-8').encode(),addr))

def Me_email(from_addr,password,to_addr,smtp_server,title,mail_body,f):

    msg = MIMEMultipart()

    message = MIMEText(mail_body, _subtype='plain', _charset='utf-8')

    msg.attach(message)

    msg['From'] = _format_addr('RMAN <%s>' % from_addr)                # 邮件发送人

    msg['To']=MAILTO

    msg['Subject'] = Header('%s' % (title),'utf-8').encode()        # 邮件title

    if os.path.isfile(f):

        att = MIMEText(open(f, 'rb').read(), 'base64', 'utf-8')

        att["Content-Type"] = 'application/octet-stream'

        att["Content-Disposition"] = 'attachment;filename=' + os.path.basename(f)

        msg.attach(att)

    server = smtplib.SMTP_SSL(smtp_server,465)

    # server.set_debuglevel(1)

    server.login(from_addr,password)

    lst =MAILTO.split(',')

    server.sendmail(from_addr, lst, msg.as_string())

    server.quit()

# 发送邮件

def sendmain(mail_title,mail_body,fileName):

    # 输入Email地址和口令

    from_addr = 'xxxxxx@139.com'    # 发送邮件的邮箱账号

    password = 'xxxxxx'                # 发送邮件的邮箱密码,无需提供

    to_addr = 'xxxxxx@qq.com'        # 接收邮件的邮箱账号

    smtp_server = 'smtp.139.com'        # 发送邮件邮箱的STMP服务器地址

    Me_email(from_addr,password,to_addr,smtp_server,mail_title,mail_body,fileName)

'''程序入口'''

if __name__ == '__main__':

        # 初始化日志

        log = LOG(LOG_NAME)

        # log.logger.error("123")

        _bool,dbConn = getDbConnStr(f"{sys.path[0]}/{BAK_DB_CONFIG}")

        if _bool and len(dbConn) > 0:

            # log.logger.info(dbConn)

            for dbconn in dbConn:

                try:

                    if "#" not in dbconn:

                        dbconnlist = dbconn.split('|')

                        # log.logger.info(dbconnlist[0])

                        # appName|osIp|osPort|osUser|osPwd|dbSid

                        appName = dbconnlist[0]

                        osIp = dbconnlist[1]

                        osPort = dbconnlist[2]

                        osUser = dbconnlist[3]

                        osPwd = des_descrypt(dbconnlist[4])

                        dbSid = dbconnlist[5]

                        ssh = SSH()

                        _bool,sshstr = ssh.conn(osIp,osPort,osUser,osPwd)

                        # 检查ssh是否连接成功，否者跳过

                        if _bool:

                            log.logger.info(f"{appName} - {osIp} - ssh连接成功")

                        else:

                            log.logger.error(f"{appName} - {osIp} - ssh连接失败：{sshstr}")

                            # break

                        # test

                        # 检查nas是否存在,NAS_backup

                        _bool,sshstr = ssh.oracle("df | grep NAS_backup | grep -v grep")

                        if _bool and "NAS_backup" in sshstr:

                            log.logger.info(f"{osIp} - NAS_backup 挂载成功。")

                        else:

                            log.logger.error(f"NAS_backup 挂载失败,请到目标服务器执行：mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,nolock,proto=tcp,actimeo=0,vers=3,timeo=600 10.0.15.248:/NAS_backup /dbbak/")

                            # break

                        # 开始执行远程备份到nas

                        ST = time.time()

                        ST1 = now()

                        log.logger.info(f"[{appName}] - {osIp} - 开始备份 - {BAK_TYPE}")

                        BAKPATH=f"/dbbak/rman/{osIp}/{nowdate()}-{BAK_TYPE}"

                        log.logger.info(f"备份目录：{BAKPATH}")

                        ssh.oracle(f"mkdir -p {BAKPATH}")

                        log.logger.info(f"生成rman备份脚本：\r\n {RMAN_FULL_SCRIPT(BAKPATH)}")

                        _bool,sshstr = ssh.oracle(RMAN_FULL_SCRIPT(BAKPATH))

                        log.logger.info(f"备份结果输出：\r\n {sshstr}")

                        # 计算备份文件大小

                        _bool, baksize = ssh.oracle(f"du -sh {BAKPATH}")

                        ssh.close()

                        # 单独写一份备份日志，用于邮件发送

                        BAK_LOGFILE = f"{sys.path[0]}/logs/{osIp}_{nowdate()}.txt"

                        with open(BAK_LOGFILE,'w') as f:

                            f.write(sshstr)

                        # 计算耗时

                        DT = time.time()

                        DT1 = now()

                        USED_TIME = round(DT-ST)

                        # 判断备份是否成功

                       if "Recovery Manager complete" in sshstr and "ERROR" not in sshstr:

                            # 发送邮件body

                            MAIL_TITLE = f"备份成功 | {appName}"

                            MAIL_BODY = f"备份级别：[level {BAK_LEVEL}] \n\n 备份结果：[备份成功] \n\n 业务系统名称：[{appName}] \n\n IP地址：[{osIp}] \n\n 备份开始时间：[{ST1}] \n\n 备份结束时间：[{DT1}] \n\n 耗时：[{USED_TIME}秒] \n\n 备份文件大小：[{baksize}]"

                            log.logger.info(f"[{appName}] - 备份成功 [{BAK_LEVEL}级] - 耗时：{USED_TIME} 秒")

                        else:

                            # 发送邮件body

                            MAIL_TITLE = f"备份失败 | {appName}"

                            MAIL_BODY = f"备份级别：[level {BAK_LEVEL}] \n\n 备份结果：[备份失败] \n\n 业务系统名称：[{appName}] \n\n IP地址：[{osIp}] \n\n 备份开始时间：[{ST1}] \n\n 备份结束时间：[{DT1}] \n\n 耗时：[{USED_TIME}秒]"

                            log.logger.error(f"[{appName}] - 备份失败")

                        sendmain(MAIL_TITLE,MAIL_BODY, BAK_LOGFILE)

                except Exception as ex:

                    # 发送邮件body

                    MAIL_TITLE = f"备份失败 | {appName}"

                    MAIL_BODY = f"备份级别：[level {BAK_LEVEL}] \n\n 备份结果：[备份异常] \n\n 业务系统名称：[{appName}] \n\n IP地址：[{osIp}] \n\n 异常信息：[{ex}]"

                    sendmain(MAIL_TITLE, MAIL_BODY, "")

                    log.logger.error(f"[{appName}] - {osIp}- 备份失败 错误：{ex}")

            log.logger.info("备份全部结束")

        else:

            log.logger.error(f"读取配置文件出错了：{dbConn}")

            sendmain("备份失败 | 读取配置文件出错了", dbConn, "")