Using Ansible how to connect Oracle DB on Linux & run command "show pdb" to display oracle pluggable DB, "shutdown immediate" to stop DB and start DB

Question

Using Ansible how to connect Oracle DB on Linux Machine to use commands "show pdb" to display oracle pluggable DB, "shutdown immediate" to stop DB and start DB. please guide. I have tried below code which will copy oracle home path from /etc/oratab file and connect the DB as a sysdba user to run the command as show pdbs , shutdown immediate and startup wherever it required.

cat /etc/oratab (Oratab fiel output) +ASM:/u01/app/oracle/19.3.0.0/grid:N # line added by Agent CDB123:/u01/app/oracle/product/19.3.0.0/db_1:N # line added by Agent

code I tried:

• name:Connect to Oracle DB command: CDBS = /etc/oratab |egrep -v '^#|^$' |grep -v ASM |grep -v agent for CDB in CDBS do CDB_NAME=echo $CDB |awk -f":" '{print $1}' ORACLE_HOME==echo $CDB |awk -f":" '{print $2}' $ORACLE_HOME/bin/sqlplus / as sysdba <<EOF show pdbs EOF done

Error: ERROR! We were unable to read either as JSON nor YAML, these are the errors we got from each: JSON: No JSON object could be decoded

Syntax Error while loading YAML. mapping values are not allowed in this context

The error appears to be in '/home/yogeshka/DB_tools/ansible/db_server_new/roles/oracle_patch/tasks/main.yml': line 85, column 12, but may be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:

• name:Connect to Oracle DB command: CDBS = /etc/oratab |egrep -v '^#|^$' |grep -v ASM |grep -v agent ^ here

Trying to connect oracle db using ansible on linux machine. looking for code fix to connect db , shutdown db , startup db whenever it required.

Answer 1

I am using function json_object to return a valid JSON document from the database.

- name: query database
  become: oracle
  shell:
    cmd: |
      $ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
      set feedback off
      set heading off
      SET SERVEROUTPUT ON SIZE 5000;
      SET LINESIZE 2500;
      set pagesize 5000;
      set long 5000;
      select json_object('db_version' VALUE  BANNER) from v\$version;
      EOF
  register: simple_out
  environment:
    ORACLE_HOME: "{{ oracle_home }}"
    ORACLE_SID: "ORCL"
    LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

- name: Transform response
  set_fact:
    simple_json: "{{ simple_out.stdout|from_json }}"
- name: Show result
  debug:
    var: simple_json

Best of luck!