oracle_asmdg

#!/usr/bin/python
# -*- coding: utf-8 -*-

DOCUMENTATION = '''
---
module: oracle_asmdg
short_description: Manage diskgroups in an Oracle database
description:
    - Manage diskgroups in an Oracle database
version_added: "2.1.0.0"
options:
    name:
        description:
            - The name of the diskgroup
        required: true
        default: None
        aliases: ['diskgroup','dg']
    state:
        description:
            - The intended state of the diskgroup. 'status' will just show the status of the diskgroup
        default: present
        choices: ['present','absent','status']
    disks:
        description:
            - A list of disks that should be part of the diskgroup. Only the listed disks will be part of the DG, meaning if the disk is removed from the list it will also be removed from the DG
        default: None
    redundancy:
        description:
            - The redundancy configuration for the diskgroup, It does not yet support putting disks in specific failure groups
        default: external
        choices: ['external','normal','high']
    attribute_name:
        description:
            - The attribute name (e.g compatible.rdbms)
        default: None
        aliases: ['an']
    attribute_value:
        description:
            - The attribute value (e.g 12.1.0.2)
        default: None
        aliases: ['av']
    username:
        description:
            - The ASM username
        required: false
        default: sys
        aliases: ['un']
    password:
        description:
            - The password for the ASM user
        required: false
        default: None
        aliases: ['pw']
    service_name:
        description:
            - The diskgroup_name to connect to the database if using dbms_diskgroup.
        required: false
        default: +ASM
        aliases: ['sn']
    hostname:
        description:
            - The host of the database if using dbms_diskgroup
        required: false
        default: localhost
        aliases: ['host']
    port:
        description:
            - The listener port to connect to the database if using dbms_diskgroup
        required: false
        default: 1521
    oracle_home:
        description:
            - The GI ORACLE_HOME
        required: false
        default: None
        aliases: ['oh']



notes:
    - cx_Oracle needs to be installed
requirements: [ "cx_Oracle" ]
author: Mikael Sandström, oravirt@gmail.com, @oravirt
'''

EXAMPLES = '''
# Create a diskgroup
oracle_asmdg:
        name: MYDG1
        disks:
           - ORCL:MYDG1
           - ORCL:MYDG2
        attribute_name: compatible.asm
        attribute_value: 12.1.0.2
        redundancy: external
        state: present
        un: sys
        pw: oracle123
        sn: '+ASM'
        host: localhost
        oh: /u01/app/oracle/12.1.2.0/grid

oracle_asmdg:
        name: DATA
        disks:
           - /dev/oracle/data1
           - /dev/oracle/data2
        attributes:
            - {name: compatible.asm, value: 12.2.0.1.0 }
            - {name: compatible.rdbms, value: 12.2.0.1.0 }
        redundancy: external
        state: present
        un: sys
        pw: oracle123
        sn: '+ASM'
        host: localhost
        oh: /u01/app/oracle/12.2.0.1/grid

'''
import os

try:
    import cx_Oracle
except ImportError:
    cx_oracle_exists = False
else:
    cx_oracle_exists = True


# Check if the diskgroup exists
def check_diskgroup_exists(cursor, module, msg, name):

    sql = 'select count(*) from gv$asm_diskgroup where lower (name) = \'%s\'' % (name.lower())
    result = execute_sql_get(module, msg, cursor, sql)
    #msg[0] = 'Normal Result is: %s, [0] is: %s, [0][0] is: %s, len is: %s, type is: %s' % (result,result[0],result[0][0],len(result), type(result))
    #module.exit_json(msg=msg[0])
    if result[0][0] > 0:
        return True
    else:
        return False

def create_diskgroup(cursor, module, msg, oracle_home, name, disks, redundancy, attribute_name, attribute_value):

    add_attr = False
    if not any(x == 'None' for x in attribute_name):
        add_attr = True
    if not any(x == None for x in attribute_name):
        add_attr = True


    if add_attr:
        attributes =','.join(['\''+str(n)+'\'' +'='+'\''+ str(v) + '\'' for n,v in zip(attribute_name,attribute_value)])

    disklist = "','".join(disks)
    sql = 'create diskgroup %s ' % (name)
    sql += '%s redundancy ' % (redundancy)
    sql += 'disk \'%s\' ' % (disklist)
    if add_attr:
        sql += ' attribute %s' % (attributes.lower())

    if execute_sql(module, msg, cursor, sql):
        if rac:
            command = '%s/bin/srvctl start diskgroup -g %s' % (oracle_home, name.lower())
            (rc, stdout, stderr) = module.run_command(command)
            if rc != 0:
                if 'CRS-5702' in stdout: #'Edge-case', where there is only one instance in the cluster. The diskgroup is already running after create statement so this command errors
                    return True
                else:
                    msg[0] = 'Error, couldn\'t mount the dg on all nodes. stdout: %s, stderr: %s, command is %s' % (stdout, stderr, command)
                    return False
            else:
                return True
        else:
            return True
    else:
        return False


def remove_diskgroup(cursor, module, msg, oracle_home, name):


    mountsql = 'alter diskgroup %s mount' % (name.lower())
    dropsql = 'drop diskgroup %s' % (name.lower())

    # If in a rac config, we need to unmount the dg on all nodes, then mount
    if rac:
        command = '%s/bin/srvctl stop diskgroup -g %s' % (oracle_home, name.lower())
        (rc, stdout, stderr) = module.run_command(command)
        if rc != 0:
            msg[0] = 'Error, couldn\'t unmount the dg. stdout: %s, stderr: %s, command is %s' % (stdout, stderr, command)
            return False

        if execute_sql(module, msg, cursor, mountsql):
            if execute_sql(module, msg, cursor, dropsql):
                return True
            else:
                return False
        else:
            return False
    else:
        if execute_sql(module, msg, cursor, dropsql):
            return True
        else:
            return False

def ensure_diskgroup_state(cursor, module, msg, name, state, disks, attribute_name, attribute_value):

    total_sql   = []
    #disk_sql    = []
    disk_sql    = 'alter diskgroup %s ' % (name.upper())
    change_attr = False
    change_disk = False
    get_ro_attr_sql = 'select distinct(name) from v$asm_attribute where read_only = \'Y\''
    read_only_attributes = []

    # Deal with attribute differences
    if (attribute_name and attribute_value):
        # Get all read only attributes
        get_ro_attr = execute_sql_get(module, msg, cursor, get_ro_attr_sql)
        for a in get_ro_attr:
            read_only_attributes.append(a[0])

        # Make sure properties are lower case
        attribute_name =  [x.lower() for x in attribute_name]
        attribute_value =  [y.lower() for y in attribute_value]
        wanted_attributes = zip(attribute_name,attribute_value)

        # Make sure we don't try to modify read only attributes. Removing them from the wanted_attributes list
        for a in wanted_attributes:
            if a[0] in read_only_attributes:
                wanted_attributes.remove(a)

        # Check the current attributes
        attribute_names_ =','.join(['\''+str(n[0])+'\'' for n in (wanted_attributes)])
        # Only get current attributes if we still have attributes in the wanted list
        if len(attribute_names_) != 0:
            current_properties = get_current_properties (cursor, module, msg, name, attribute_names_)
            # Convert to dict and compare current with wanted
            if cmp(dict(current_properties),dict(wanted_attributes)) is not 0:
                change_attr = True
                for i in wanted_attributes:
                    total_sql.append("alter diskgroup %s set attribute '%s'='%s'" % (name, i[0], i[1]))


    list_current_name = []
    list_current_path = []
    list_wanted = [x.upper() if ':' in x else x for x in disks]
    list_current = get_current_disks(cursor, module, msg, name)

    for p,n in list_current:
        list_current_name.append(n)
        list_current_path.append(p)

    # List of disks to add
    list_add=set(list_wanted).difference(list_current_path)
    # List of disks to remove
    list_remove=set(list_current_path).difference(list_wanted)
    # Pick out the v$asm_disk.name from the diskgroup
    remove_disks = [a[1] for a in list_current if a[0] in list_remove ]

    add_disk =  "','".join(list_add)
    remove_disk=  "','".join(remove_disks)
    if sorted(list_current_path) == sorted(list_wanted) and change_attr == False:
    	msg[0] = "Diskgroup %s is in the intended state" % (name)
    	module.exit_json(msg=msg[0], changed=False)

    if len(list_add)>= 1:
        change_disk = True
        disk_sql += ' add disk '
    	disk_sql += "'%s'" % add_disk

    if len(list_remove) >= 1:
        #disk_sql    = 'alter diskgroup %s ' % (name.upper())
        change_disk = True
        disk_sql += ' drop disk '
    	disk_sql += "'%s'" % remove_disk
    if change_disk:
        total_sql.append(disk_sql)

    if ensure_diskgroup_state_sql(module,msg,cursor,total_sql):
        msg[0] = 'Diskgroup %s has been put in the intended state' % (name)
        module.exit_json(msg=msg[0], changed=True)
    else:
        return False

def ensure_diskgroup_state_sql(module,msg,cursor,total_sql):

    for a in total_sql:
        execute_sql(module, msg, cursor, a)
    return True

def get_current_disks(cursor, module, msg, name):


    sql = 'select d.path,d.name from v$asm_disk d, v$asm_diskgroup dg '
    sql += 'where dg.group_number = d.group_number '
    sql += 'and upper(dg.name) = \'%s\'' % (name.upper())

    result = execute_sql_get(module, msg, cursor, sql)
    return result

def get_current_properties(cursor, module, msg, name,attribute_names_):


    sql = 'select lower(a.name),lower(a.value) from v$asm_attribute a, v$asm_diskgroup dg '
    sql += 'where dg.group_number = a.group_number '
    sql += 'and upper(dg.name) = \'%s\' ' % (name.upper())
    sql += 'and a.name in (%s) ' % (attribute_names_.lower())

    result = execute_sql_get(module, msg, cursor, sql)
    return result

def execute_sql_get(module, msg, cursor, sql):

    #module.exit_json(msg="In execute_sql_get", changed=False)
    try:
        cursor.execute(sql)
        result = (cursor.fetchall())
    except cx_Oracle.DatabaseError, exc:
        error, = exc.args
        msg[0] = 'Something went wrong while executing sql_get - %s sql: %s' % (error.message, sql)
        module.fail_json(msg=msg[0], changed=False)
        return False

    return result


def execute_sql(module, msg, cursor, sql):

    try:
        cursor.execute(sql)
    except cx_Oracle.DatabaseError, exc:
        error, = exc.args
        msg[0] = 'Something went wrong while executing sql - %s sql: %s' % (error.message, sql)
        module.fail_json(msg=msg[0], changed=False)
        return False
    return True



def main():

    msg = ['']
    cursor = None
    mode = 'sysasm'
    global rac

    module = AnsibleModule(
        argument_spec = dict(
            name                = dict(required=True, aliases = ['diskgroup','dg']),
            disks               = dict(required=False, type='list'),
            redundancy          = dict(default="external", choices = ["external","normal","high"]),
            attribute_name      = dict(required=False, type='list', aliases=['an']),
            attribute_value     = dict(required=False, type='list', aliases=['av']),
            state               = dict(default="present", choices = ["present", "absent", "status"]),
            user                = dict(required=False, aliases = ['un','username']),
            password            = dict(required=False, no_log=True, aliases = ['pw']),
            hostname            = dict(required=False, default = 'localhost', aliases = ['host']),
            port                = dict(required=False, default = 1521),
            service_name        = dict(required=False, default = '+ASM', aliases = ['sn']),
            oracle_home         = dict(required=False, aliases = ['oh']),



        ),

    )

    name                = module.params["name"]
    disks               = module.params["disks"]
    redundancy          = module.params["redundancy"]
    attribute_name      = module.params["attribute_name"]
    attribute_value     = module.params["attribute_value"]
    state               = module.params["state"]
    user                = module.params["user"]
    password            = module.params["password"]
    hostname            = module.params["hostname"]
    port                = module.params["port"]
    service_name        = module.params["service_name"]
    oracle_home         = module.params["oracle_home"]


    if not cx_oracle_exists:
        msg[0] = "The cx_Oracle module is required. 'pip install cx_Oracle' should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME & LD_LIBRARY_PATH is set"
        module.fail_json(msg=msg[0])

    wallet_connect = '/@%s' % service_name
    try:
        if (not user and not password) : # If neither user or password is supplied, the use of an oracle wallet is assumed
            connect = wallet_connect
            conn = cx_Oracle.connect(wallet_connect, mode=cx_Oracle.SYSASM)
        elif (user and password):
            dsn = cx_Oracle.makedsn(host=hostname, port=port, service_name=service_name)
            connect = dsn
            conn = cx_Oracle.connect(user, password, dsn, mode=cx_Oracle.SYSASM)
        elif (not(user) or not(password)):
            module.fail_json(msg='Missing username or password for cx_Oracle')

    except cx_Oracle.DatabaseError, exc:
        error, = exc.args
        msg[0] = 'Could not connect to ASM: %s, connect descriptor: %s, username: %s, pass: %s' % (error.message, connect,user,password)
        module.fail_json(msg=msg[0], changed=False)

    cursor = conn.cursor()

    checkifracsql = 'select parallel from v$instance'
    checkifrac = execute_sql_get(module, msg, cursor, checkifracsql)
    if checkifrac[0][0] == 'YES':
        rac = True
        if oracle_home is not None:
            os.environ['ORACLE_HOME'] = oracle_home
        elif 'ORACLE_HOME' in os.environ:
            oracle_home = os.environ['ORACLE_HOME']
        else:
            msg[0] = 'ORACLE_HOME variable not set. Please set it and re-run the command'
            module.fail_json(msg=msg[0], changed=False)
    else:
        rac = False


    if state == 'present':
        if not check_diskgroup_exists(cursor, module, msg, name):
             if create_diskgroup(cursor, module, msg, oracle_home, name, disks, redundancy, attribute_name, attribute_value):
                 msg[0] = 'Successfully created diskgroup %s ' % (name)
                 module.exit_json(msg=msg[0], changed=True)
             else:
                 module.fail_json(msg=msg[0], changed=False)
        else:
            ensure_diskgroup_state(cursor, module, msg, name, state, disks, attribute_name, attribute_value)

    elif state == 'absent' :
        if check_diskgroup_exists(cursor, module, msg, name):
            if remove_diskgroup(cursor, module, msg, oracle_home, name):
                msg[0] = 'Diskgroup %s successfully removed' % (name)
                module.exit_json(msg=msg[0], changed=True)
            else:
                module.exit_json(msg=msg[0], changed=False)
        else:
            msg[0] = 'Diskgroup %s doesn\'t exist' % (name)
            module.exit_json(msg=msg[0], changed=False)

    elif state == 'status' :
        if check_diskgroup_exists(cursor, module, msg, name):
            result = get_current_disks(cursor, module, msg, name)
                #msg[0] = 'Diskgroup %s successfully removed' % (name)
            module.exit_json(msg=result, changed=False)
        else:
            module.exit_json(msg=msg[0], changed=False)


    module.exit_json(msg="Unhandled exit", changed=False)




from ansible.module_utils.basic import *
if __name__ == '__main__':
    main()