Statspack setup in CDB

[talek]

Hello,

I'm playing around with oradb_manage_statspack role. It's working flawless in a non-CDB database, but it's not in a CDB. I always get:

TASK [opitzconsulting.ansible_oracle.oradb_manage_statspack : oradb_manage_statspack | Create Statspack Schema in PDBs (existing user is ignored)] ***
Monday 11 September 2023  13:36:13 +0300 (0:00:00.059)       0:00:05.125 ******
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: NoneType: None
failed: [molecule] (item=MYCDB PDB1 MYCDB) => {"ansible_loop_var": "opdb", "changed": true, "failed_when_result": true, "msg": "non-zero return code", "opdb": [{"cdb": "MYCDB", "datafile_dest": "/data/oradata/MYCDB", "home": "19c", "pdb_name": "PDB1", "state": "present", "statspack"
: {"purgedays": 14, "snaplevel": 7, "state": "present", "tablespace": "PERFSTAT_TBS"}, "tablespaces": [{"autoextend": true, "bigfile": false, "content": "permanent", "maxsize": "4G", "name": "PERFSTAT_TBS", "next": "50M", "size": "10M", "state": "present"}]}, {"cdb": "MYCDB", "dataf
ile_dest": "/data/oradata/MYCDB", "home": "19c", "pdb_name": "PDB2", "state": "present", "statspack": {"purgedays": 14, "snaplevel": 7, "state": "present", "tablespace": "PERFSTAT_TBS"}, "tablespaces": [{"autoextend": true, "bigfile": false, "content": "permanent", "maxsize": "4G",
"name": "PERFSTAT_TBS", "next": "50M", "size": "10M", "state": "present"}]}], "rc": 127, "stderr": "", "stderr_lines": [], "stdout": "ORACLE_HOME=\r\nORACLE_SID=MYCDB\r\n/var/tmp/ansible-tmp-1694428574.030089-81994-118030467811178/spcreate.sh: line 10: /bin/sqlplus: No such file or
directory\r\n", "stdout_lines": ["ORACLE_HOME=", "ORACLE_SID=MYCDB", "/var/tmp/ansible-tmp-1694428574.030089-81994-118030467811178/spcreate.sh: line 10: /bin/sqlplus: No such file or directory"]}

Apparently, the oracle_home_db (maybe in orasw_metada) is not computed correctly when this role is used in a CDB context.

The playbook:

---
- name: Statspack setup
  hosts: "{{ hostgroup }}"
  become: true
  vars:
    dbpasswords:
      MYCDB:
        sys: "Oracle123"
        system: "Oracle123"
    db_homes_config:
      19c:
        home: 19c
        oracle_home: /u01/app/oracle/product/19c/ee_db01
        version: 19.3.0.0
        edition: EE
        opatch_minversion: 12.2.0.1.32
        opatchauto: []
        opatch: []
    oracle_databases:
      - home: 19c
        oracle_db_name: MYCDB
        is_container: true
        state: present
    oracle_pdbs:
      - home: 19c
        pdb_name: PDB1
        cdb: MYCDB
        datafile_dest: /data/oradata/MYCDB
        state: present
        statspack:
          purgedays: 14
          snaplevel: 7
          tablespace: PERFSTAT_TBS
          state: present
        tablespaces:
          - name: PERFSTAT_TBS
            size: 10M
            autoextend: true
            next: 50M
            maxsize: 4G
            content: permanent
            bigfile: false
            state: present
      - home: 19c
        pdb_name: PDB2
        cdb: MYCDB
        datafile_dest: /data/oradata/MYCDB
        statspack:
          purgedays: 14
          snaplevel: 7
          tablespace: PERFSTAT_TBS
          state: present
        tablespaces:
          - name: PERFSTAT_TBS
            size: 10M
            autoextend: true
            next: 50M
            maxsize: 4G
            content: permanent
            bigfile: false
            state: present
        state: present
  tasks:
    - name: Provision tablespaces
      ansible.builtin.include_role:
        name: opitzconsulting.ansible_oracle.oradb_manage_tablespace

    - name: Install statspack
      ansible.builtin.include_role:
        name: opitzconsulting.ansible_oracle.oradb_manage_statspack

Any ideas? Thanks!

[github-actions]

This issue is stale because it has been open for 30 days with no activity. Auto close in 30 days.

[Rendanic]

Sorry for long delay. I did a lot of work in 4.0. It should be fixed there.

Do we need to backport this to 3.x?

[talek]

No need to backport this to 3.x. I'll give it a try with the latest 4.0 and come back to you with a feedback. Thanks!

[talek]

Something odd happens with the latest version. I'm trying to create a new CDB database to test statspack role, but I get the following error:

TASK [opitzconsulting.ansible_oracle.oradb_manage_db : Create/Modify database] ***
Thursday 26 October 2023  09:31:29 +0300 (0:00:01.040)       0:00:05.291 ******
included: /Users/talek/.ansible/collections/ansible_collections/opitzconsulting/ansible_oracle/roles/oradb_manage_db/tasks/manage-db.yml for molecule => (item=oracle_db_name=MYCDB)

TASK [opitzconsulting.ansible_oracle.oradb_manage_db : Assert oracle_home_db] ***
Thursday 26 October 2023  09:31:29 +0300 (0:00:00.127)       0:00:05.418 ******
fatal: [molecule]: FAILED! => {"msg": "The conditional check '_oracle_home_db | length > 0' failed. The error was: error while evaluating conditional (_oracle_home_db | length > 0): {%- if _odb_loop_helper is defined -%}{{ db_homes_config[_odb_loop_helper.home]['oracle_home'] | defa
ult(oracle_base + '/' + db_homes_config[_odb_loop_helper.home]['version'] + '/' + _odb_loop_helper.home) }}{%- elif opdb[0] is defined -%}{{ db_homes_config[_opdb_home]['oracle_home'] | default(oracle_base + '/' + db_homes_config[_opdb_home]['version'] + '/' + _opdb_home) }}{%- endi
f -%}: {{ (oracle_databases | selectattr('oracle_db_name', 'match', opdb[0]['cdb']))[0]['home'] }}: 'opdb' is undefined"}

Inventory data:

  ---
  oracle_listeners_config:
    LISTENER:
      home: 19c
      address:
        - host: "{{ansible_hostname}}"
          protocol: TCP
          port: 1521
  listener_installed:
    - home: 19c
      listener_name: LISTENER
      state: present
  db_homes_config:
    19c:
      home: 19c
      oracle_home: /u01/app/oracle/product/19c/ee_db01
      version: 19.3.0.0
      edition: EE
      opatch_minversion: 12.2.0.1.32
      opatchauto: []
      opatch: []
  db_homes_installed:
    - home: 19c
      apply_patches: true
      state: present
  dbpasswords:
    MYCDB:
      sys: "Oracle123"
      system: "Oracle123"
      perfstat: "Oracle123"
  configure_oracle_profile: false
  oracle_databases:
    - home: 19c
      archivelog: false
      is_container: true
      storage_type: FS
      oracle_db_type: SI
      redolog_size_in_mb: 1024
      oracle_db_mem_percent: 75
      listener_name: LISTENER
      oracle_database_type: MULTIPURPOSE
      characterset: AL32UTF8
      state: present
      oracle_db_name: MYCDB
  oracle_pdbs:
    - home: 19c
      pdb_name: PDB1
      cdb: MYCDB
      datafile_dest: /data/oradata/MYCDB
      state: present
    - home: 19c
      pdb_name: PDB2
      cdb: MYCDB
      datafile_dest: /data/oradata/MYCDB
      state: present

[Rendanic]

I found the bug - will be fixed with next release.