Tuesday, March 13, 2018

To Run Any Sql Scripts from Sqlplus on Targets via Ansible Playbook

The main part of Yaml file is below.
 name description self-explains

 - hosts: "{{ v_host | default('test.com') }}"
    - myparameter: "{{ v_para | default('db_unique_name') }}"
    - mysid:  "{{ v_sid | default('ORCL') }}"
    - myhost: "{{ v_host | default('test.com') }}"
    - dbhome_locl: ""
    - parameter_sql: /u01/app/ansible/repository/show_parameter.sql

   become: true
   become_method: pbrun
   become_user: oracle
   become_flags: 'content-ansible'
   gather_facts: False
   - name: find the location of db home for the sid
     shell: 'grep {{ mysid }} /etc/oratab |grep -v ^[#] | cut -d: -f2|head -1'
     register: dbhomeoutput
   - set_fact:
       dbhome_locl: "{{ dbhomeoutput.stdout }}"
   - debug: var=dbhome_locl

   - name: copy show parameter sql to target host /tmp
          src: "{{ parameter_sql }}"
          dest: /tmp/ansible_sql.sql
          force: yes
          mode: 0755
   - name: run the sql to get the result
     shell: |
          export ORACLE_SID={{ mysid }}
          export ORACLE_HOME={{ dbhome_locl }}
          {{ dbhome_locl }}/bin/sqlplus -s "/ as sysdba" @/tmp/ansible_sql.sql "{{ myparameter }}"
     register: sqloutput

   - name: display details of sql output
     debug: msg="{{ sqloutput.stdout_lines }}"

   - name: delete sql file on the remote target
       path: /tmp/ansible_sql.sql
       state: absent


Shashank Barki said...

Thanks a lot for this. I need help in below scenarios-
1) Is it possible to run the same .sql files going into specific DB directories ?
2) If there is a shell script which prompts for values, can Ansible call this shell script as is and let shell prompt for the input parameter values ?

1N0T said...

Thank you, it has inspired me to solve some administration tasks.