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.