自动生成AWR报告的python3脚本

自动生成AWR报告的脚本

闲暇的时候写了个小脚本，估计以后自己会用的上。主要用到了cx_Oracle和paramiko模块，其中切换用户还需要用到channel，不然paramiko会一直卡在那，包括生成AWR报告的交互，也是利用channel来完成的。
报告生成之后，使用到了paramiko里面的SFTP模块来下载到本地磁盘。

'''
数据库巡检脚本
'''
# -*- coding:utf-8 -*-
import shutil,os
import time
import paramiko
import datetime
import cx_Oracle

# 巡检公司名称
company_name = "XXX"

# linux主机配置部分，用户名、密码、IP、端口、数据库用户名、数据库密码、数据库连接地址
host_list = (['root','123456','10.10.100.131',22, 'zsh', 'zshu123', '10.10.100.131:1521/ORCL'],
             ['root','123456','10.10.100.139',22, 'zsh', 'zshu123', '10.10.100.139:1521/ORCL'],
             )

class DatabaseConnect():
    '''
    数据库类，包含数据库连接和数据库语句执行
    '''
    def __init__(self, username, password, host):
        '''
        数据库连接初始化参数
        '''
        self.username = username
        self.password = password
        self.host = host

    def oracleConnect(self):
        db = cx_Oracle.connect(self.username, self.password, self.host)
        return db.cursor()

    def sqlExecute(self, sql):
        # sql = "select name from v$database"
        c = self.oracleConnect()
        c.execute(sql)
        service_name = c.fetchone()[0]
        return service_name

class DbHost():
    '''
    Linux 主机连接配置
    '''
    def __init__(self, username, password, host , port):
        self.username = username
        self.password = password
        self.host = host
        self.port = port

    def connectHost(self):
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(hostname=self.host, port=self.port, username=self.username, password=self.password)
        return ssh

    def commandExecute(self,command):
        ssh = self.connectHost()
        stdin, stdout, stderr = ssh.exec_command(command)
        result = stdout.read().decode()
        return  result

    def oracleExecute(self, channel, command, breakString):
        try:
            time.sleep(0.1)
            channel.send(command)
            # channel.send("\n")
            buff = ''
            while not buff.endswith(breakString):
                resp = channel.recv(9999)
                buff += resp.decode('utf-8')
            # print(buff)
        except paramiko.ssh_exception.AuthenticationException:
            print('Failed to login. ip username or password not correct.')

    def downloadFile(self, server_path, local_path):
        try:
            t = paramiko.Transport((self.host, self.port))
            t.connect(username=self.username, password=self.password)
            sftp = paramiko.SFTPClient.from_transport(t)
            sftp.get(server_path, local_path)
            t.close()
        except Exception as e:
            print(e)

class FileAndPath():
    def __init__(self, company_name, service_name, ipaddr):
        self.company_name = company_name
        self.service_name = service_name
        self.ipaddr = ipaddr

    def checkPath(self):
        file_path = "D:\\"+self.company_name+"\\"+str(datetime.date.today())
        if(os.path.exists(file_path)):
            print("目录存在，不需要新建！")
        else:
            os.makedirs(file_path)
            print("目录不存在，新建目录成功！")
        return file_path

    def checkFile(self):
        file_name = self.service_name + self.ipaddr + '_' + str(datetime.date.today()) + '.txt'
        # 切换目录
        file_path = self.checkPath()
        os.chdir(file_path)
        # 判断文件是否存在
        if os.path.exists(os.path.join(file_path, file_name)):
            print("删除今日已生成的同名文件。。。")
            # 存在同名文件则进行删除，因为是采用append的方式，否则每跑一次内容会重复一次
            os.remove(os.path.join(file_path, file_name))
            print("删除同名文件成功！")
        return file_name

    def outputToFile(self, file_name, command, result):
        with open(file_name, mode='a', encoding='utf-8') as f1:
            f1.write("# " + command + "\n")
            f1.write(result)
        return(print("命令：" + command + " 执行结果输出文件成功！"))

if __name__ == '__main__':
    try:
        # linux主机的连接
        for i in range(len(host_list)):
            # 数据库连接
            oracle_connect = DatabaseConnect(host_list[i][4], host_list[i][5], host_list[i][6])
            # 获取数据库当前的Service_name
            service_name = oracle_connect.sqlExecute("select name from v$database")
            begin_snap_id = oracle_connect.sqlExecute("select min(snap_id) from dba_hist_snapshot where BEGIN_INTERVAL_TIME > to_date('%s', 'yyyy-mm-dd')" % datetime.date.today())
            end_snap_id = oracle_connect.sqlExecute("select max(snap_id) from dba_hist_snapshot where BEGIN_INTERVAL_TIME > to_date('%s', 'yyyy-mm-dd')" % datetime.date.today())

            ssh = DbHost(host_list[i][0], host_list[i][1], host_list[i][2], host_list[i][3])
            my_file = FileAndPath(company_name, service_name, host_list[i][2])
            file_name = my_file.checkFile()
            # 执行命令 iostat -m 1 3
            command = "iostat -m 1 3"
            result = ssh.commandExecute(command)
            my_file.outputToFile(file_name, command, result)

            # 执行命令 uptime
            command1 = "uptime"
            result1 = ssh.commandExecute(command1)
            my_file.outputToFile(file_name, command1, result1)

            # 执行命令 df -h
            command2 = "df -h"
            result2 = ssh.commandExecute(command2)
            my_file.outputToFile(file_name, command2, result2)

            # 执行Oracle的脚本，包含AWR报告和db_check脚本
            channel = ssh.connectHost().invoke_shell()
            ssh.oracleExecute(channel, "su - oracle\n", "$ ")
            ssh.oracleExecute(channel, "sqlplus / as sysdba\n", "SQL> ")
            # ssh.oracleExecute(channel, "@db_check.sql\n", "SQL> ")
            ssh.oracleExecute(channel, "@?/rdbms/admin/awrrpt.sql\n", ": ")
            ssh.oracleExecute(channel, "html\n", ": ")
            ssh.oracleExecute(channel, "1\n", ": ")
            ssh.oracleExecute(channel, str(begin_snap_id) + "\n", ": ")
            ssh.oracleExecute(channel, str(end_snap_id) + "\n", ": ")

            awr_file_name = service_name + "_" + host_list[i][2] + "_" + str(datetime.date.today()) + ".html"
            ssh.oracleExecute(channel, awr_file_name + "\n", "SQL> ")

            ssh.downloadFile("/home/oracle/" + awr_file_name, my_file.checkPath() +"\\" + awr_file_name)
            # 关闭linux主机连接
            ssh.connectHost().close()
    except Exception as e:
        print("Warnning: " + host_list[i][2] + " 执行异常，请检查！")
        print(e)