Andrew Fraser DBA

Ansible Oracle SQL

Ansible being used to run a sqlplus script, handling pluggables and dataguard.

1) Initial setup, on central ansible control host

/usr/bin/ssh-keygen -t rsa
# filename = /home/.ssh/ansible_key instead of default id_rsa.
# passphrase = not entered.
cat ~/.ssh/ansible_key.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh
ssh-copy-id -i ~/.ssh/ansible_key dbserver1.mydomain.com  # if you know oracle password
ssh -i ~/.ssh/ansible_key dbserver1.mydomain.com hostname  # to test passwordless ssh working ok
mkdir -p ~/ansible/files
chmod 700 ~/ansible
chmod 700 ~/ansible/files

The use of a non-standard / non-default filename for the key is purely optional.

If ssh-copy-id is not an option, then can instead logon to each target dbserver as oracle and manually append the line from the central ansible_key.pub to ~/.ssh/authorized_keys on the target.

2) File ~/ansible/inventory.cfg

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

3) File ~/ansible/inventory.ini

[test]
dbserver1.mydomain.com oracle_sid=CMYDB01 pdb_name=mydb01

[dev]
dbserver2.mydomain.com oracle_sid=MYDB02
dbserver2.mydomain.com oracle_sid=MYDB03

Can test connectivity with:

cd ~/ansible
ansible all -m ping

4) File ~/ansible/myUser_grant.yaml

- name: myUser grant
  hosts: all
  tasks:

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

  - name: Run script on remote server
    shell: /tmp/myUser_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/myUser_grant.sh
      state: absent

5) File ~/ansible/files/myUser_grant.sh

#!/bin/bash
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 ;
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).

6) To run the play:

cd ~/ansible
ansible-playbook hp_diag_grant.yaml --limit dev  # dev servers only
ansible-playbook hp_diag_grant.yaml --limit dbserver1.mydomain.com  # one specific server
ansible-playbook hp_diag_grant.yaml  # all servers

More info from Tim Hall at https://www.youtube.com/playlist?list=PLbgSgneb0mSShDTcL7pSCISRj0-GC9qZ6