Andrew Fraser DBA

Ansible Simple SQL Shell Script Setup

Ansible example of running a sql script from a shell script on target servers.

1) First setup a central Ansible control host server (if you don’t have one already).

Pick a server which has a reasonably recent version of Linux OS. No need to be a dedicated server, and no need to be a physical server or otherwise high-spec server.

In my case I used the oracle user throughout, but you could use root, or a dedicated ansible user instead.

/usr/bin/ssh-keygen -t rsa
# when prompted by keygen, enter these values:
# > file = $HOME/.ssh/ansible_key
# > passphrase = can just press return here to leave blank
vi ~/.ssh/ansible_key.pub  # append some meaningful info to the end
# of the last field (the comment field), so that comment field
# becomes e.g.: oracle@myserver_ansible_mydnsalias instead of just
# the default oracle@myserver which is not very meaningful
cat ~/.ssh/ansible_key.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh

2) Setup the ssh key on each individual target server (if that hasn’t been done already). This is the only step that needs done on the target servers.

• Source file (a one line file) = ~/.ssh/ansible_key.pub = on the ansible control server.
• Destination file = ~/.ssh/authorized_keys = on each individual target server.
• Append the line from the source file (ansible control server) as a new line at the end of the destination file (target server), by copy+paste.
• Repeat for every target server.
• For security, on each target server: “chmod 600 ~/.ssh/authorized_keys ; chmod 700 ~/.ssh”

3) Create the Ansible script directories and files on the central Ansible control host server:

mkdir -p ~/ansible/files
chmod 700 ~/ansible
chmod 700 ~/ansible/files
chmod 600 ~/ansible/ansible.cfg
chmod 600 ~/ansible/sys_grant.yaml
chmod 600 ~/ansible/inventory.ini
chmod 600 ~/ansible/files/sys_grant.sh

Contents of ~/ansible/ansible.cfg:

[defaults]
inventory = ~/ansible/inventory.ini
private_key_file = ~/.ssh/ansible_key
interpreter_python = auto_legacy_silent

Contents of ~/ansible/sys_grant.yaml:

- name: sys grant
  hosts: all
  tasks:

  - name: Copy script file to remote server
    copy:
      src: sys_grant.sh
      dest: /tmp
      mode: '700'

  - name: Run script on remote server
    shell: /tmp/sys_grant.sh {{ oracle_sid }} {{ pdb_name | default('') }}
    register: output

  - name: Display output of script that was run from remote server
    debug: msg="{{ output.stdout_lines }}"

  #- name: Delete script file from remote server
    #file:
      #path: /tmp/sys_grant.sh
      #state: absent

Contents of ~/ansible/inventory.ini:

[production]
mydb1 ansible_host=myserver1.mydomain.com oracle_sid=mydb1
mydb2_dc1 ansible_host=myserver2.mydomain.com oracle_sid=mydb2
mydb2_dc2 ansible_host=myserver3.mydomain.com oracle_sid=mydb2

[test]
mydb3 ansible_host=myserver4.mydomain.com oracle_sid=mydb3

[dev]
mydb4 ansible_host=myserver4.mydomain.com oracle_sid=mydb4
mydb5 ansible_host=myserver4.mydomain.com oracle_sid=mydb5
mydb6 ansible_host=myserver4.mydomain.com oracle_sid=cdb1 pdb_name=mydb6
mydb7 ansible_host=myserver4.mydomain.com oracle_sid=cdb1 pdb_name=mydb7

Notice the two entries for mydb2 – one for primary and one for standby in a data guard setup. Also parameter pdb_name being specified when there is a pluggable/container database setup.

Contents of ~/ansible/files/sys_grant.sh:

#!/bin/bash
####################################################################
# Name : myAnsibleServer:~oracle/ansible/files/sys_grant.sh
# Purpose : Grant AWR privileges to user.
# Change History :
####################################################################
export ORACLE_SID=$1
if [ ! -z $2 ]
then
  l_pdbCommand="alter session set container = $2 ;"
fi
export ORAENV_ASK=NO
. oraenv > /dev/null
unset ORAENV_ASK
sqlplus -s /nolog << END_SQL
conn / as sysdba
$l_pdbCommand
set serveroutput on feedback off pages 9999 lines 130
DECLARE
   l_count NUMBER ;
BEGIN
   IF sys_context ( 'userenv' , 'database_role' ) = 'PRIMARY'
   THEN
      SELECT COUNT(*) INTO l_count
        FROM dba_users
       WHERE username = 'MYUSER'
      ;
      IF l_count = 1
      THEN
         SELECT COUNT(*) INTO l_count
           FROM dba_tab_privs
          WHERE grantee = 'MYUSER'
            AND owner = 'SYS'
            AND table_name = 'DBMS_WORKLOAD_REPOSITORY'
            AND privilege = 'EXECUTE'
         ;
         IF l_count = 0
         THEN
            dbms_output.put_line ( 'Info: action: granting privilege to user myUser on $1 $2' ) ;
            execute immediate 'GRANT EXECUTE ON sys.dbms_workload_repository TO myUser' ;
         ELSE
            dbms_output.put_line ( 'Info: no action taken: user myUser already had required privilege on $1 $2' ) ;
         END IF ;
      ELSE
         dbms_output.put_line ( 'Error: user myUser not found on $1 $2' ) ;
      END IF ;
   ELSE
      dbms_output.put_line ( 'Info: no action taken: database is not primary on $1 $2' ) ;
   END IF ;
END ;
/
exit
END_SQL
# (end of file).

4) Smoke test. Especially important the first time around when you will be prompted “Are you sure you want to continue connecting (yes/no/…)” until the target server entry is added into file ~/.ssh/known_hosts, which will happen automatically if you choose yes.

ansible myserver1.mydomain.com -m ping
ansible production -m ping
ansible all -m ping
ansible-playbook sys_grant.yaml --limit myserver1.mydomain.com

5) Run for real, using e.g.:

ansible-playbook sys_grant.yaml --limit production

6) Target servers running extremely old versions of Operating System won’t work with Ansible, so can instead handle these the old (ssh script) way with e.g.:

ssh myoldserver.mydomain.com -q -i  ~/.ssh/ansible_key 'bash -s MYOLDSID' < ~/ansible/files/sys_grant.sh