5

A look at the core database modules in Ansible documentation shows no signs of a module for Oracle. What is the best way to handle SQL/PLSQL deployments via Ansible for Oracle databases?

Are we expected to use roles from Ansible Galaxy to handle this? Very few people seem to have downloaded roles listed on Galaxy for Oracle.

Irfan N
  • 93
  • 1
  • 1
  • 7
  • 1
    When you say "SQL scripts" do you mean large SQL*Plus scripts, or just random SQL and PL/SQL commands? If you're looking for a way to automate plain SQL and PL/SQL commands you may want to look at my open source program [Method5](https://method5.github.io/). It's an agentless, parallel remote execution program; sort of like Ansible, but it's designed specifically for Oracle databases. – Jon Heller Dec 28 '16 at 01:55
  • Thanks Jon. I am specifically looking for Ansible here. – Irfan N Dec 30 '16 at 09:42

2 Answers2

1

I have created a role to install apex 5 (where I first uninstall apex 4). I use modules like 'script' and 'shell'. I am not too happy about environment initialization but I am still learning. For any SQL/PLSQL task, sqlplus is the right tool. (perhaps SQLcl can do better..?)

- name: Determine apex version
  become: yes
  become_user: oracle
  shell: source /etc/profile &&  sqlplus -S / as sysdba @"{{ temp_dir }}/apexver.sql"
  register: apexver
  args:
     executable: /bin/bash
  changed_when: "'APEX_040000' in apexver.stdout"

- name: oracle apex remove
  become: yes
  become_user: oracle
  script: apex_remove.sh {{ item }} 
  with_items: 
    - 'XE'
  ignore_errors: yes
  register: result
  when: "'APEX_040000' in apexver.stdout"

22:18 $ cat apex_remove.sh
#!/bin/sh

# set oracle environment
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
ORACLE_SID=$1

sqlplus -s /nolog <<EOF
connect / as sysdba
@?/apex/apxremov.sql
exit
EOF
Bjarte Brandt
  • 4,191
  • 2
  • 23
  • 25
0

I'm not sure if this is related to your question, but I originally was looking for Ansible modules to Start/Stop and get the Status of an Oracle database. I couldn't find anything suitable so I wrote my own ansible modules. Modules give you the power to define a standard interface, with OK/Failed/Changed responses to tasks, while performing as much low-level activity/commands as you need (in this way they are much more flexible than the simple shell/command modules). I wrote the modules to be idempotent -- they won't attempt to start databases that are already started, and won't attempt to stop databases that are already stopped. And if a stop/stop function isn't successful, it returns Failed with stdout/stderr.

Along with Oracle databases, I've also written modules to provide interfaces for Business Objects and Weblogic services. The modules are significant up-front work, but once they are stable they can be used in a wide range of playbooks.

I haven't looked at Galaxy to see if there is anything else like this, and unfortunately due to the nature of my client/contract I'm not sure that I can share the modules that we have developed. I just thought I'd offer this as a possible avenue for you to explore.

Wolske
  • 146
  • 2
  • 7