Example of configuring oradb_manage_tablespace role

[talek]

Hello,

Is there any example of how to configure oradb_manage_tablespace role? This is the playbook I'm playing around with:

---
- name: Tablespace setup
  hosts: "{{ hostgroup | default('all')}}"
  become: true
  vars:
    dbpasswords:
      MOLDB:
        sys: "Manager123!"
        system: "Manager123!"
    db_homes_config:
      19c:
        home: 19c
        oracle_home: /u01/app/oracle/product/19c/ee_db
        version: 19.3.0.0
        edition: EE
    oracle_databases:
      - home: 19c
        oracle_db_name: MOLDB
        is_container: false
        state: present
        tablespaces:
          - name: perfstat_tbs
            size: 10M
            autoextend: true
            next: 50M
            maxsize: 4G
            content: permanent
            state: present
            bigfile: false
            datafile: "/data/oradata/MOLDB/perfstat01.dbf"
  tasks:
    - name: Provision tablespaces
      ansible.builtin.include_role:
        name: opitzconsulting.ansible_oracle.oradb_manage_tablespace

The error I get is:

TASK [opitzconsulting.ansible_oracle.oradb_manage_tablespace : Manage tablespaces (db/cdb)] ***
Wednesday 15 February 2023  18:32:53 +0200 (0:00:00.078)       0:00:04.163 ****
fatal: [molecule]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'db_password_cdb' is undefined\n\nThe error appears to be in '/Users/talek/.ansible/collections/ansible_collections/opitzconsulting/ansible_oracle/roles/oradb_manage_tablespace/tasks/main.yml': line 2, column 3, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n---\n- name: Manage tablespaces (db/cdb)\n  ^ here\n"}

I can define db_password_cdb variable, but most likely I need to come up with some logic to get it out of dbpasswords variable, especially if I have more than one database entry defined in oracle_databases. But that's not all. I also need to play smart with the oracle home if it happens to have more homes in the db_homes_config.

Any pointers in the right direction?
Thank you!

[Rendanic]

Hi Alexandru,
I think I found the reason why this problem was not visible in the past.
I test with the playbook playbooks/manage-db.yml which includes different roles. One of them sets the db_password_cdb in defaults/main.ymland the execution of opitzconsulting.ansible_oracle.oradb_manage_tablespace has a valid password.

The reason is the missing default for db_password_cdb in defaults/main.yml for the tablespace role.

I'll fix and test it.

Regards
Thorsten

[Rendanic]

The problem with db_password_cdb should be fixed by #323 .
Sorry for closing the issue. I reopen it, because github closed it automatically due tto the fixed keyword in the PR.

Could you describe a requirement for your different oracle_homes in db_homes_config?

[talek]

Hi Thorsten,

The db_homes_config issue is related to the error below which I still got with the #323 fix.

...
TASK [debug] *******************************************************************
Friday 17 February 2023  12:49:15 +0200 (0:00:03.499)       0:00:03.549 *******
ok: [molecule] => {
    "oracle_pdbs": "VARIABLE IS NOT DEFINED!"
}

...

TASK [opitzconsulting.ansible_oracle.oradb_manage_tablespace : Manage tablespaces (db/cdb)] ***
Friday 17 February 2023  12:49:15 +0200 (0:00:00.081)       0:00:03.667 *******
ok: [molecule] => (item=port: 1521 service: MOLDB tablespace: perfstat_tbs content: permanent state: present)
[WARNING]: Both option datafile and its alias datafile are set.

TASK [opitzconsulting.ansible_oracle.oradb_manage_tablespace : Manage tablespaces (pdb)] ***
Friday 17 February 2023  12:49:17 +0200 (0:00:01.942)       0:00:05.610 *******
fatal: [molecule]: FAILED! => {"msg": "The field 'environment' has an invalid value, which includes an undefined variable. The error was: {'ORACLE_HOME': '{{ oracle_home_db }}', 'LD_LIBRARY_PATH': '{{ oracle_home_db }}/lib'}: {%- if dbh is defined -%} {%- if db_homes_config[dbh.home]['oracle_home'] is defined  -%} {{ db_homes_config[dbh.home]['oracle_home'] }} {%- else -%} {{ oracle_base }}/{{ db_homes_config[dbh.home]['version'] }}/{{ db_homes_config[dbh.home]['home'] }} {%- endif -%} {%- elif pdb.0.home is defined %} {%- if db_homes_config[pdb.0.home]['oracle_home'] is defined  -%} {{ db_homes_config[pdb.0.home]['oracle_home'] }} {%- else -%} {{ oracle_base }}/{{ db_homes_config[pdb.0.home]['version'] }}/{{ db_homes_config[pdb.0.home]['home'] }} {%- endif -%} {%- elif item.0 is defined -%} {%- if db_homes_config[item.0.home]['oracle_home'] is defined  -%} {{ db_homes_config[item[0].home]['oracle_home'] }} {%- else -%} {{ oracle_base }}/{{ db_homes_config[item.0.home]['version'] }}/{{ db_homes_config[item.0.home]['home'] }} {%- endif -%} {%- elif item[1] is defined -%} {%- if db_homes_config[item[1].home]['oracle_home'] is defined  -%} {{ db_homes_config[item[1].home]['oracle_home'] }} {%- else -%} {{ oracle_base }}/{{ db_homes_config[item[1].home]['version'] }}/{{ db_homes_config[item[1].home]['home'] }} {%- endif -%} {%- elif item is defined -%} {%- if db_homes_config[item.home]['oracle_home'] is defined  -%} {{ db_homes_config[item.home]['oracle_home'] }} {%- else -%} {{ oracle_base }}/{{ db_homes_config[item.home]['version'] }}/{{ db_homes_config[item.home]['home'] }} {%- endif -%} {%- endif -%}: 'dict object' has no attribute '19300-base'\n\nThe error appears to be in '/Users/talek/.ansible/collections/ansible_collections/opitzconsulting/ansible_oracle/roles/oradb_manage_tablespace/tasks/main.yml': line 31, column 3, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n- name: Manage tablespaces (pdb)\n  ^ here\n"}

So, my intuition was that the oracle_home_db played its part as well. I see now that it's working for the non-CDB database but fails as part of the PDB task, even though oracle_pdbs variable is not defined. If I set oracle_pdbs to an empty array, then all goes well.

TASK [debug] *******************************************************************
Friday 17 February 2023  13:04:15 +0200 (0:00:04.116)       0:00:04.170 *******
ok: [molecule] => {
    "oracle_pdbs": []
}

...

TASK [opitzconsulting.ansible_oracle.oradb_manage_tablespace : Manage tablespaces (db/cdb)] ***
Friday 17 February 2023  13:04:15 +0200 (0:00:00.077)       0:00:04.283 *******
ok: [molecule] => (item=port: 1521 service: MOLDB tablespace: perfstat_tbs content: permanent state: present)
[WARNING]: Both option datafile and its alias datafile are set.

TASK [opitzconsulting.ansible_oracle.oradb_manage_tablespace : Manage tablespaces (pdb)] ***
Friday 17 February 2023  13:04:17 +0200 (0:00:01.662)       0:00:05.946 *******

[Rendanic]

The main problem is, that I never tested ansible-oraclein a way it is used in your example.
It is a bug in ansible-oracle and I have a solution for it but not in next couple of days, because I work on a complete refactoring of all variable definitions in defaults/main.yml for all roles.
The variables are placed in meta-role who are added as a dependency to each role. That guarantees, that all needed variables are visible in each role, without the need, to test it for each role which is a nightmare btw...

The next step is assert for variables in each role, so we see when a variable is missing in isolated executions of roles like your example. I prefer to create an assert task for common variables in the meta role and some special asserts in the dedicated role.
That will realy help to prevent such problems.

What could you do?
I'll check what is missing in your example and we extend the db_homes_config.

The idea in using ansible-oracle in your example is great. I like it so I am interested to see it working.

Regards
Thorsten

[Rendanic]

I found the bug. It is related to a default variable who is not changed in your setup.

Just add following variable:
oracle_pdbs: []

I start working on an assertation for orasw_metato test it.
The 1st PR will come soonly for that.

The final solution will come in the future but that's a breaking change, because I have to change a lot of defaults to empty lists or dictionaries to fix this issue.