ansible – sponton

checking Oracle PDB status

This demands that you have an account on the database server that can become ‘oracle’ user

# INFO:
# Run as: ansible-playbook check_PDBs.yml -e "db=hostname" -i my_hosts
# Check in the inventory file my_hosts for hostnames
- hosts: "{{db}}"
  gather_facts: no
  become: yes
  become_user: oracle
  vars:
    ansible_ssh_extra_args: -o ConnectTimeout=60 -o ServerAliveInterval=50

  tasks:

    - name: Check PDBs
      shell: printf 'column name format a30\n column open_time format a40\n column total_size format 999999999999999\n column creation_time format a40\n column restricted format a12\n alter session set nls_date_format = "YYYY/MM/DD HH24:MI:SS";\n select name, open_mode, total_size, open_time, creation_time, restricted from v$pdbs;\n' | $ORACLE_HOME/bin/sqlplus -S "/AS SYSDBA"
      register: db_out
      environment:
        ORACLE_HOME: /opt/ora/oracle/product/19c
        LD_LIBRARY_PATH: /opt/ora/oracle/product/19c/lib:/usr/openwin/lib:/usr/dt/lib
        ORACLE_SID: "{{ORACLE_SID}}"
      args:
        executable: /bin/bash

    - name: Print output of sql script
      debug:
        msg: "{{ db_out.stdout }}"

Example inventory line:

hostname_a    ansible_user=dba   ORACLE_SID=CDB

next topic….