====== oracle_accounts ======

Project => MANAGE_ORACLE

Structure:
  * oracle_account.yml
  * collections
      * requirements.yml
  * templates
      *  oracle_get_pdbs.j2

 \\
== oracle_account.yml ==
<code YAML>
---
- name: "Execute Oracle Script"
  hosts: all
  gather_facts: true
  
  tasks:
  - name: "Get running DB on remote server"
    shell: "for i in $(ps -ef | grep  oracle | grep ora_pmon  | sed 's/ora_pmon_//'| awk '{print $8}' |grep -v grep | grep -v sed); do echo ${i::-1}; done"
    register: alldb_raw
  - name: 
    set_fact:
      allrunningdb: "{{ alldb_raw.stdout_lines | list | reject('eq', '/bin/sh')  | reject('eq', '/bin/s')}}"
 
  - name: "Get running SIDs on cluster node"
    shell: "for i in $(ps -ef | grep  oracle | grep ora_pmon  | sed 's/ora_pmon_//'| awk '{print $8}' |grep -v grep | grep -v sed); do echo ${i}; done"
    register: allsid_raw
  - name: 
    set_fact:
      allrunningsid: "{{ allsid_raw.stdout_lines | list | reject('eq', '/bin/sh') | reject('eq', '/bin/s') }}"

  - name: "Display all running CDBs and SIDs"
    debug: 
      msg: 
        - "connected to  {{ ansible_hostname }} => Found  {{ allrunningsid | count }}  CDB"
        - "SIDs are :"
        - "{{ allrunningsid }}"
        - "####"
        - "CDBs are:"
        - "{{ allrunningdb }}"

  - name: "Generate temporary sql file"
    become: true # root privilege
    template:
      src: "oracle_get_pdbs.j2"
      dest: "/tmp/oracle_get_pdbs.sql"
      mode: 0600
      owner: oracle
      group: oinstall


  - name :
    debug:
      msg: 
        - "Retrieving all PDBs of {{ allrunningdb[0] | upper }}"
        - "connected to instance {{ allrunningsid[0] }}"

  - name: "Get all PDBs of  database"
    become: true # oracle privilege
    become_user: oracle
    shell: |
      export ORACLE_SID={{ allrunningsid[0] | upper }}
      export ORACLE_HOME=`grep -w $ORACLE_SID /etc/oratab | cut -d ":" -f 2`
      $ORACLE_HOME/bin/sqlplus -s -l / as sysdba @/tmp/oracle_get_pdbs.sql
    register: pdb_list
    
  - name: "Print all PDBs of  database"
    debug:
      msg:
        - "found  {{ pdb_list | count }}  PDB"
        - "{{ pdb_list }}"

  - name: " Create Empty dictionary"
    set_fact:
      CDBs: []

  - name: "combine CDB and PDB into Dictionary"
    set_fact:
      CDBs: '{{ CDBs + [ { "PDBs": {{ pdb_list }} } ] }}' 

        
  - name: "Print dictionary content"
    debug: var=CDBs

</code>

 \\
== requirements.yml ==
<code YAML>
---
collections:
  - name: community.general
  - name: community.windows 
</code>

 \\
== oracle_get_pdbs.j2 ==
<code YAML>
SET HEADING OFF
set feedback off
select name from gv$pdbs where name not in('PDB$SEED');
exit</code>