生成oracle database awr 邮件报告 python脚本
import cx_Oracle import smtplib from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.mime.base import MIMEBase from email import encoders import os import datetime # Oracle数据库连接信息 oracle_user = 'your_oracle_user' oracle_password = 'your_oracle_password' oracle_dsn = 'your_oracle_dsn' # 例如：'localhost:1521/orcl' 或 RAC的TNS名称 # 邮件服务器和账户信息 smtp_server = 'your_smtp_server' smtp_port = 25 # 或其他端口，如587（TLS）或465（SSL） email_from = 'your_email@example.com' email_to = 'recipient@example.com' email_subject = 'Oracle AWR Report - Last 8 Hours' # 其他配置 report_type = 'html' report_filename = f'awr_report_{datetime.datetime.now().strftime("%Y%m%d_%H%M%S")}.html' def generate_awr_report(): try: # 连接到Oracle数据库 connection = cx_Oracle.connect(oracle_user, oracle_password, oracle_dsn) cursor = connection.cursor() # 获取开始和结束的快照ID cursor.execute(""" SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot WHERE begin_interval_time BETWEEN SYSDATE - INTERVAL '8' HOUR AND SYSDATE """) snap_ids = cursor.fetchone() if not snap_ids: raise Exception("No snapshots found in the last 8 hours.") begin_snap, end_snap = snap_ids # 生成AWR报告的SQL*Plus命令 awr_cmd = f""" SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 1000 TRIMSPOOL ON SPOOL {report_filename} @?/rdbms/admin/awrrpt.sql {report_type} {begin_snap} {end_snap} / SPOOL OFF EXIT; """ # 执行生成AWR报告的命令 cursor.execute("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;") cursor.execute(f"SPOOL {report_filename};") for line in awr_cmd.splitlines(): cursor.execute(line) cursor.execute("SPOOL OFF;") # 关闭数据库连接 cursor.close() connection.close() print(f"AWR report generated successfully: {report_filename}") except Exception as e: print(f"Error generating AWR report: {e}") os.remove(report_filename) # 清理未完成的报告文件 raise def send_email_with_attachment(filename): try: # 构建邮件内容 msg = MIMEMultipart() msg['From'] = email_from msg['To'] = email_to msg['Subject'] = email_subject # 附加AWR报告作为邮件附件 with open(filename, 'rb') as attachment: part = MIMEBase('application', 'octet-stream') part.set_payload(attachment.read()) encoders.encode_base64(part) part.add_header('Content-Disposition', f'attachment; filename={os.path.basename(filename)}') msg.attach(part) # 发送邮件 with smtplib.SMTP(smtp_server, smtp_port) as server: server.sendmail(email_from, email_to, msg.as_string()) print("Email sent successfully with AWR report attachment.") except Exception as e: print(f"Error sending email: {e}") if name == 'main': try: # 生成AWR报告 generate_awr_report() # 发送带有AWR报告的邮件 send_email_with_attachment(report_filename) # 清理生成的报告文件（如果需要） # os.remove(report_filename) except Exception as e: print(f"An error occurred: {e}")
