Creating Ansible Custom Module for AWR Report Generation

Posted in: Technical Track

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.

email

Interested in working with Vyacheslav? Schedule a tech call.

1 Comment. Leave new

Mikael Sandström
August 3, 2017 1:47 am

For more Ansible/Oracle modules, there is also this: https://github.com/oravirt/ansible-oracle-modules

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *