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
Leave a Reply