Search code examples
oracleansibleansible-2.x

Ansible playbook takes forever to execute an Oracle DB query

I'm writing a playbook that will select all users from an Oracle Database. I've taken an approach shown in this article.

Here's the entire playbook

---

- name: Gather all users details and save to JSON
  hosts: all
  become: yes
  gather_facts: yes
  tasks:
    - block:

      - name: query
        shell: |
              sqlplus / as sysdba
              set pagesize 999;
              set linesize 999;
              select username from dba_users;
              exit;

        register: user_output

        environment:
          ORACLE_SID: "MYDB"
          ORACLE_HOME: "myOracleHome"
          PATH: "myOraclePATH"
          LD_LIBRARY_PATH: "myLibPath"


      - name: Print output
        ansible.builtin.debug:
          msg: out {{ user_output.stdout_lines }}

      become: yes
      become_user: oracle

I've got all the necessary environment variables set for this particular machine (for now I'm testing only on a single host).

Now what is the problem? The problem is that when ansible gets to the query task it hangs, I don't know for how long it would last as when it reached 10 minutes I've given up on waiting.

Granted the machine I'm testing this on has a lot of databases and is really big, but I'm running the queries on a smaller DB, that only has 46 users.

I've made sure the query works, when ran locally it takes less than a second, so the problem isn't there.

I've also ran this playbook in debug mode, adding the -vvv flag, and looks like it hangs exactly on the query task, it sets the environment variables succesfully and then does nothing!

Interesting fact, when I ran the playbook without environment variables set it wouldn't hang, instead it gave an expected error sqlplus: command not found, so it is actually doing something.

Here's the debug log for the query task:

<x.x.x.x> ESTABLISH SSH CONNECTION FOR USER: myUser
<x.x.x.x> SSH: EXEC sshpass -d12 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o 'User="myUser"' -o ConnectTimeout=10 -o 'ControlPath="/home/ansible/.ansible/cp/686fbcfc81"' x.x.x.x '/bin/sh -c '"'"'echo ~myUser && sleep 0'"'"''
<x.x.x.x> (0, b'/home/myUser\n', b'')
<x.x.x.x> ESTABLISH SSH CONNECTION FOR USER: myUser
<x.x.x.x> SSH: EXEC sshpass -d12 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o 'User="myUser"' -o ConnectTimeout=10 -o 'ControlPath="/home/ansible/.ansible/cp/686fbcfc81"' x.x.x.x '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /var/tmp `"&& mkdir "` echo /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426 `" && echo ansible-tmp-1726830229.8966174-18573-196770779260426="` echo /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426 `" ) && sleep 0'"'"''
<x.x.x.x> (0, b'ansible-tmp-1726830229.8966174-18573-196770779260426=/var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426\n', b'')
Using module file /usr/lib/python3.11/site-packages/ansible/modules/command.py
<x.x.x.x> PUT /home/ansible/.ansible/tmp/ansible-local-18525yaa_4d63/tmpw0g9yu2l TO /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426/AnsiballZ_command.py
<x.x.x.x> SSH: EXEC sshpass -d12 sftp -o BatchMode=no -b - -C -o ControlMaster=auto -o ControlPersist=60s -o 'User="myUser"' -o ConnectTimeout=10 -o 'ControlPath="/home/ansible/.ansible/cp/686fbcfc81"' '[x.x.x.x]'
<x.x.x.x> (0, b'sftp> put /home/ansible/.ansible/tmp/ansible-local-18525yaa_4d63/tmpw0g9yu2l /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426/AnsiballZ_command.py\n', b'')
<x.x.x.x> ESTABLISH SSH CONNECTION FOR USER: myUser
<x.x.x.x> SSH: EXEC sshpass -d12 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o 'User="myUser"' -o ConnectTimeout=10 -o 'ControlPath="/home/ansible/.ansible/cp/686fbcfc81"' x.x.x.x '/bin/sh -c '"'"'setfacl -m u:oracle:r-x 
/var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426/ /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426/AnsiballZ_command.py && sleep 0'"'"''
<x.x.x.x> (0, b'', b'')
<x.x.x.x> ESTABLISH SSH CONNECTION FOR USER: myUser
<x.x.x.x> SSH: EXEC sshpass -d12 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o 'User="myUser"' -o ConnectTimeout=10 -o 'ControlPath="/home/ansible/.ansible/cp/686fbcfc81"' -tt x.x.x.x '/bin/sh -c '"'"'sudo -H -S -n  -u oracle 
/bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-tqqxhtcntukpuwtfraxcpodtkmmxahln ;
 ORACLE_SID=MySID ORACLE_HOME=homePath PATH=myPath LD_LIBRARY_PATH=myLibPath /usr/bin/python3.6 /var/tmp/ansible-tmp-1726830229.8966174-18573-196770779260426/AnsiballZ_command.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded

After the Escalation succeeded message, it hangs. I have ran out of ideas to troubleshoot this, any other ansible task not involving the database works just fine.

Oracle DB full version info:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
Solution

  • You appear to be running sqlplus and the playbook is waiting for that to complete before it runs the next line in the shell. It does not appear to be passing the subsequent lines into sqlplus so that command is running forever, waiting for user input.

    You can try:

    echo "set pagesize 999;
set linesize 999;
select username from dba_users;" | sqlplus / as sysdba

    or:

    sqlplus / as sysdba <<EOF
set pagesize 999;
set linesize 999;
select username from dba_users;
exit;
EOF

    (It looks like the page you link to is missing the <<EOF in its scripts - although it does include it later on in one Python script.)