AWR 报告脚本实现

感谢花生土豆粉

 

Script to generate AWR report from remote sql client

oraclesql
发布于 2014-12-27

通常我们都是通过登录服务器，然后执行$ORACLE_HOME/rdbms/admin/awrrpt.sql脚本来生成awr报告，同时Oracle还提供了DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML存储过程，方便我们在不方便登录服务器的情况下获取AWR报告。
通过如下脚本，可以简单的在dbms_output中获取到AWR报告的内容：

 
DECLARE
  DBID           NUMBER;
  INST_ID        NUMBER;
  BID            NUMBER;
  EID            NUMBER;
  DB_UNIQUE_NAME VARCHAR2(30);
  STARTTIME      DATE;
  ENDTIME        DATE;
BEGIN
  STARTTIME := TO_DATE('2014-12-15 18:00', 'YYYY-MM-DD HH24:MI') - 1 / 24;
  ENDTIME   := TO_DATE('2014-12-15 19:00', 'YYYY-MM-DD HH24:MI');

  SELECT MIN(SNAP_ID), MAX(SNAP_ID)
    INTO BID, EID
    FROM DBA_HIST_SNAPSHOT DHS
   WHERE TRUNC(DHS.BEGIN_INTERVAL_TIME, 'HH24') >= TRUNC(STARTTIME, 'HH24')
     AND TRUNC(DHS.END_INTERVAL_TIME, 'HH24') <= TRUNC(ENDTIME, 'HH24');
  SELECT DBID, INST_ID, DB_UNIQUE_NAME
    INTO DBID, INST_ID, DB_UNIQUE_NAME
    FROM GV$DATABASE;

  FOR C1_REC IN (SELECT OUTPUT
                   FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(DBID,
                                                                       INST_ID,
                                                                       BID,
                                                                       EID))) LOOP
    DBMS_OUTPUT.PUT_LINE(C1_REC.OUTPUT);
  END LOOP;

END;
/


python实现自动生成oracle awr报告

https://s1.51cto.com/images/20180613/1528857464916528.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

 

https://www.pianshen.com/article/3658505290/

https://www.pythonf.cn/read/116913


def generate_oracle_awr(database_info,first_id,second_id,output_path)

sql_conn_str='sqlplus {0}/{1}
sql_path_get_db_id='os.path.join(root_path,"congfig")
sql_patch_generate_awr='os.path.join(root_patch,"config\\generate_oracle_awr.sql")

try
ret os.popen(sql_conn_str + sql_path_get_bdid)
line_info=ret.readlines()
for line_num line in enumerate(line_info);
if 'DBID INSTAN_NUMBER' in line
dbid,instance_num=line_info
break

 

 

 

Pyhton初学之生成awr报告

https://blog.csdn.net/feng_vs_sunzhilong/article/details/81408170

 


########sample 3

感谢Vyacheslav Rasskazov
https://blog.pythian.com/creating-ansible-custom-module-for-awr-reports-generation/

CREATING ANSIBLE CUSTOM MODULE FOR AWR REPORT GENERATION

by Vyacheslav Rasskazov
 
August 2, 2017
Posted in: Technical Track
Tags: ansible, AWR, reporting

Creating AWR reports is a common task for many DBAs. At times, you may need to generate multiple AWR reports from multiple databases for performance audit; Ansible allows you to automate these types of tasks. You can, for example, generate reports from all databases and gather them in a single location. In this post, I will demonstrate how it can be achieved by a custom Ansible module.

Custom module provides more flexibility than standard shell and command modules (which also can be used).  The final playbook looks both simple and neat if custom module is being used.

Although there is a lot of room for improvement, here an oversimplified example of custom Ansible module, just for demonstration purposes.
Example: It supports Oracle connections only by using wallet.
cx_Oracle Python module is required.
Module accepts seven parameters:

  • TNS alias for database connectivity
  • AWR report begin interval time
  • AWR report end interval time
  • Database instance
  • Directory for report file
  • Report type (text / html)
  • Date format used in interval range

Module returns path to report file for future processing.

Let’s check the code:

  • Lines 13-23: Instantiating the module class
  • Lines 26-32: Define arguments passed from Ansible playbook
  • Lines 38-42: Connect to database using wallet
  • Lines 47-52: Select from dbid from v$database
  • Lines 55-62: Select from dba_hist_snapshot for ‘Begin Snapshot’ ID
  • Lines 65-72: Select from dba_hist_snapshot for ‘End Snapshot’ ID
  • Lines 83-98: Generate AWR report and save it into the file
#!/usr/bin/python
import os
try:
import cx_Oracle
cx_oracle_found = True
except ImportError:
cx_oracle_found = False
def main():
module = AnsibleModule(
argument_spec = dict(
tns_name = dict(required=True),
begin_interval = dict(required=True),
end_interval = dict(required=True),
instance = dict(default = 1),
out_directory = dict(default='/tmp'),
date_format = dict(default='yyyy-mm-dd hh24:mi'),
report_type = dict(default='text', choices=["text", "html"])
)
)
# Define arguments passed from ansible playbook.
tns_name = module.params["tns_name"]
begin_interval = module.params["begin_interval"]
end_interval = module.params["end_interval"]
instance = module.params["instance"]
out_directory = module.params["out_directory"]
report_type = module.params["report_type"]
date_format = module.params["date_format"]
if not cx_oracle_found:
module.fail_json(msg="Can't import cx_Oracle module")
# Connect to database
try:
con = cx_Oracle.connect('/@%s' % tns_name)
except cx_Oracle.DatabaseError, exception:
error, = exception.args
module.fail_json(msg='Database connection error: %s, tns_name: %s' % (error.message, tns_name), changed=False)
cursor = con.cursor()
# Get dbid
try:
cursor.execute ("select dbid from v$database")
dbid = cursor.fetchone ()[0]
except cx_Oracle.DatabaseError, exception:
error, = exception.args
module.fail_json(msg= 'Error selecting v$database for dbid: %s' % (error.message), changed=False)
# Get the 'Begin Snapshot' ID
try:
cursor.execute ("select max(snap_id) from dba_hist_snapshot where END_INTERVAL_TIME < to_date(:1,:2)",[begin_interval, date_format])
begin_snap = cursor.fetchone ()[0]
if begin_snap is None:
module.fail_json(msg = 'Fist snapshot is not found. Begin_interval: %s' % (begin_interval), changed=False)
except cx_Oracle.DatabaseError, exception:
error, = exception.args
module.fail_json(msg='Error selecting dba_hist_snapshot for interval begin: %s' % (error.message), changed=False)
# Get the 'End Snapshot' ID
try:
cursor.execute ("select min(snap_id) from dba_hist_snapshot where END_INTERVAL_TIME > to_date(:1,:2)",[end_interval, date_format])
end_snap = cursor.fetchone ()[0]
if end_snap is None:
module.fail_json(msg = 'Last snapshot is not found. End_interval: %s' % (end_interval), changed=False)
except cx_Oracle.DatabaseError, exception:
error, = exception.args
module.fail_json(msg = 'Error selecting dba_hist_snapshot for interval end: %s' % (error.message), changed=False)
if report_type=='text':
sql = 'SELECT output FROM TABLE(dbms_workload_repository.awr_report_text (:1,:2,:3,:4))'
file_ext=".txt"
else:
sql = 'SELECT output FROM TABLE(dbms_workload_repository.awr_report_html (:1,:2,:3,:4))'
file_ext=".html"
file_name=out_directory + str(dbid) + '_' + str(instance) + '_' + str(begin_snap) + '_' + str(end_snap) + file_ext
# Generate the AWR report and save it into the file
try:
cursor.execute(sql,[dbid, instance, begin_snap, end_snap])
try:
f = open(file_name,'w')
for row in cursor.fetchall():
if row[0] is not None:
f.write('\n' + row[0])
else:
f.write('\n')
f.close
except IOError as e:
module.fail_json( 'Couldn\'t open file: %s' % (file_name), changed=False)
except cx_Oracle.DatabaseError, exc:
error, = exc.args
module.fail_json( msg='Error executing dbms_workload_repository: %s, begin_snap %s, end_snap %s' % (error.message,begin_snap, end_snap), changed=False)
module.exit_json(fname=file_name, changed=True)
from ansible.module_utils.basic import *
if __name__ == '__main__':
main()

Here is awr_rpt.yml playbook file:

cat awr_rpt.yml
---
- hosts: 192.168.56.101
tasks:
- name: Create AWR report
awr_report:
tns_name: 'testdb'
begin_interval: '2017-07-27 14:25'
end_interval: '2017-07-27 14:50'
out_directory: '/tmp/'
date_format: 'yyyy-mm-dd hh24:mi'
report_type: 'html'
register: v_result
- name: Fetch AWR report from remote node
fetch:
src: "{{ v_result.fname }}"
dest: '/home/oracle/working/'
flat: yes

Playbook creates AWR on remote host, fetches file from remote machine and stores it locally. Playbook execution will lead to next output:

ansible-playbook awr_rpt.yml
PLAY [192.168.56.101] **************************************************************************************************
TASK [Gathering Facts] *************************************************************************************************
ok: [192.168.56.101]
TASK [Create AWR report] ***********************************************************************************************
changed: [192.168.56.101]
TASK [Fetch AWR report from remote node] *******************************************************************************
ok: [192.168.56.101]
PLAY RECAP *************************************************************************************************************
192.168.56.101 : ok=3 changed=1 unreachable=0 failed=0

In my opinion, it’s worth it to work on new Ansible modules which implement database related functionality. There are a lot of tasks besides installation / upgrades which can be automated by Ansible.




posted @ 2020-07-02 22:41  feiyun8616  阅读(324)  评论(0)  编辑  收藏  举报