Pythia can setup a Dataguard replication.

Is your feature request related to a problem? Please describe.
Sometimes it's hard to track down an error when using Pythia.

Describe the solution you'd like
Implement a debug TAG. This will then print out debugging informations with variable contents. It's then easier to understand why Pythia behaves as it does.

Additional context
The whole Kernel parameter setting stuff is predestinated for this.

At the Moment, there is no difference made between Oracle 18 and 19 prerequisite Packages. But there is a difference.

https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/supported-suse-linux-enterprise-server-12-distributions-for-x86-64.html#GUID-8BF62362-685D-4B7C-BD6F-094D53C76900
https://docs.oracle.com/en/database/oracle/oracle-database/18/ladbi/supported-suse-linux-enterprise-server-12-distributions-for-x86-64.html#GUID-8BF62362-685D-4B7C-BD6F-094D53C76900

Check if the Oracle Version you are about to install when defining the "rdbms" tag is supported on the OS Version.

Supported OS has to be specified in the rdbms_dict.yml

This becomes more important since there is no support for Oracle 18c for RedHat / OEL 8.

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
Pythia should support Oracle Database 21c

Describe alternatives you've considered
None

Additional context
None

When creating a PDB from the SEED, the tempfile of the new PDB will be named something like that: /db_data/ORA21/data01/pdbtest1/temp012022-01-09_14-21-33-850-PM.dbf. We need to implement a proper renaming of the tempfile. Either after creation or during creation using the PDB_FILE_NAME_CONVERT Parameter.

Is your feature request related to a problem? Please describe.
Under some circumstances, it is necessary, to call the runinstaller for RDBMS installation with more specific parameters. This is not possible at the moment.

Describe the solution you'd like
Implement a variable where I can add additional runinstaller parameters to. The will get attached to the runinstaller command during RDBMS installation.

Describe alternatives you've considered
None

Additional context
You might want to call the variable runinstaller_parameters and place it under defaults/main.yml.

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
Pythia could also build and manage Docker container (images). We could use our own image building process for this (see here).

Additional context
This has first to be researched if this is even feasible.

Most Patchsets / Release Updates in Oracle are installed by installing it to the RDBMS using opatch apply under the root Patch directory. But there are other Patches as well (like DBBPs or other Stuff from Oracle 12c) which have to be installed by opatch applyall or where the datapatch have to be run when the Database is started in Upgrade mode.

We could define some kind of special Installation Instructions in the Patch Dictionary (patch_dict.yml). Not sure how this can be implemented yet.

When separately installing RDBMS and Database, the sysctl.conf Values for shmall and shmmax will be calculated wrongly. For instance, when installing a RDBMS standalone. A basic sysctl.conf will be created and made active.
When later on installing a Database, the Role will only check, if the sysctl.conf is stable (which it is) and then add the basic sysctl.conf Values (wrongly) to the given Database Parameter calculated Values for shmall and shmmax.

Maybe we need to implement a "basic" check for this case. So when finding the keyword "basic" in the sysctl.conf, no addition will be made and only the values for a new Database will be implemented.

• Duplicate
• Client Installation
• Kernel Parameter / Hugepages

All Variables are now defined under vars/main.yml. Following the Variable precedence from Ansible, this makes it pretty high in the hirarchy and therefore only overwriteable by extra Variables defined when running the Playbook. This should be changed for most of the Variables. They should get moved under defaults/main.yml.

We have to review the required software packages for Oracle 21c. The installation with Pythia works, but not sure, if there is still something missing.

This is kind of related to #5 .

https://docs.oracle.com/en/database/oracle/oracle-database/21/ladbi/supported-oracle-linux-7-distributions-for-x86-64.html#GUID-3E82890D-2552-4924-B458-70FFF02315F7

Describe the bug
Tempfiles are getting deleted during the duplicate on the target database. But they should be manually recreated after a successful duplicate. Else, there will be some errors later on sort operations and the tempfiles have to be created manually.

To Reproduce
Steps to reproduce the behavior:

1. Do a duplicate with Pythia
2. Try a larger sort operation after the duplicate has been finished which will use the temp tablespace
3. It will fail with something like ORA-01157: cannot identify/lock data file 201 data file 201: '/db_data/CLONEDST/temp/temp01CLONEDST.dbf'

Expected behavior
All tempfiles should be recreated (touched) after a duplicate.

Ansible Oracle Environment:

• Ansible Version: 2.9.6
• Oracle Version: 19.5
• Pythia Tag: duplicate

Additional context
Add any other context about the problem here.

Is your feature request related to a problem? Please describe.
We're using ansible-lint Github Action module in an old version. The reason for this is, that a more current reason doesn't offer the options we need anymore.

Describe the solution you'd like
We should do the linting with ansible-lint by ourself instead rely on the Github action module.

Additional context
This could be done like this:

---
name: Lint
'on':
  pull_request:
  push:
    branches:
      - master

jobs:

  test:
    name: Lint
    runs-on: ubuntu-latest

    steps:
      - name: Check out the codebase.
        uses: actions/checkout@v2

      - name: Set up Python 3.7.
        uses: actions/setup-python@v2
        with:
          python-version: '3.x'

      - name: Install test dependencies.
        run: pip3 install yamllint ansible-lint ansible

      - name: Run yamllint.
        run: yamllint .

      - name: Run ansible-lint.
        run: ansible-lint . -x command-instead-of-shell -x risky-shell-pipe -x no-jinja-when -x fqcn-builtins

Describe the bug
Since the molecule version 3.6.1, we cannot run molecule tests anymore. Also, the molecule test fails when trying to use the ansible posix module for checking selinux.

To Reproduce
Steps to reproduce the behavior:

1. run molecule test
2. See error

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: ModuleNotFoundError: No module named 'selinux'
fatal: [oel]: FAILED! => {"changed": false, "msg": "Failed to import the required Python library (libselinux-python) on oel's Python /usr/libexec/platform-python. Please read the module documentation and install it in the appropriate location. If the required library is installed, but Ansible is using the wrong Python interpreter, please consult the documentation on ansible_python_interpreter"}

Please edit meta/main.yml and assure we can correctly determine full role name:

galaxy_info:
role_name: my_name  # if absent directory name hosting role is used instead
namespace: my_galaxy_namespace  # if absent, author is used instead

Namespace: https://galaxy.ansible.com/docs/contributing/namespaces.html#galaxy-namespace-limitations
Role: https://galaxy.ansible.com/docs/contributing/creating_role.html#role-names

As an alternative, you can add 'role-name' to either skip_list or warn_list.

Expected behavior
Molecule tests should run without an error.

Describe the bug
The dbstart and dbshut script will get deployed with oracle:dba ownership and 0750 permissions. So when installing a service for DB autostart, the service cannot start the database using the script. The following error shows up in the systemctl status view.

[root@vmdbsora21 ~]# systemctl status dboraORA21.service
● dboraORA21.service - The Oracle Database Service
   Loaded: loaded (/usr/lib/systemd/system/dboraORA21.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sat 2022-01-22 23:44:30 CET; 57s ago
  Process: 772 ExecStart=/oracle/product/21_ENT/bin/dbstart /oracle/product/21_ENT (code=exited, status=203/EXEC)
 Main PID: 772 (code=exited, status=203/EXEC)

Jan 22 23:44:30 vmdbsora21 systemd[1]: Started The Oracle Database Service.
Jan 22 23:44:30 vmdbsora21 systemd[772]: dboraORA21.service: Failed to execute command: Permission denied
Jan 22 23:44:30 vmdbsora21 systemd[772]: dboraORA21.service: Failed at step EXEC spawning /oracle/product/21_ENT/bin/dbstart: Permission denied
Jan 22 23:44:30 vmdbsora21 systemd[1]: dboraORA21.service: Main process exited, code=exited, status=203/EXEC
Jan 22 23:44:30 vmdbsora21 systemd[1]: dboraORA21.service: Failed with result 'exit-code'.
[root@vmdbsora21 ~]# ls -ltr /oracle/product/21_ENT/bin/dbstart

To Reproduce
Steps to reproduce the behavior:

1. Run Pythia with autostart TAG on a host
2. Reboot host
3. Check autostart status with systemctl status dboraxxx
4. See error

Expected behavior
The dbstart and dbshut scripts should be deployed with 0755 permissions.

Additional context
This is likely a new bug since version 2.2.5. There were specific permission modes implemented. Before this should have been the default.

• Merged in develop branch
• Merged in master branch

Describe the bug
Pythia uses a static path in template and blockinfile module calls. This looks like this roles/pythia/templates/xxx. This is unclean code. As soon as you call Pythia not from the repository root directory, the role will fail because it cannot find the templates to copy to the target system.

To Reproduce
Steps to reproduce the behavior:

1. Create a Playbook that is not in this repositories root directory
2. Call the Playbook

Expected behavior
Pythia as a role should dynamicly use the role_path variable in addition with the templates directory. This could look like this:

template:
    src: "{{ role_path }}/templates/{{ oracle_version }}_Database.rsp.j2"

Screenshots
None

Is your feature request related to a problem? Please describe.
The output of Pythia is getting more and more messy.

Describe the solution you'd like
Clean output only related to the actual tasks and TAGs you've specified. This could be achieved by using blocks. This will leave out some skipped tasks when a precondition is not fulfilled anyway.

It seems that we have a problem here which needs to be further analyzed. When starting a duplicate with an already existing Target Database, because the temp files of the source are not part of the actual duplicate process, Oracle tries to reuse the already existing tempfiles of the "old" and existing database. This only happens of course, when the filename of the tempfiles from target and source are the same.

After the duplicate has finished and the startup is completed, we receive some error, that the temp files do not belong to the database (which makes sense).

First we have to analyze this behaviour, one option would be, to enforce the recreation of the tempfiles in the RMAN duplicate command in some way, or to delete already existing datafiles and tempfiles of the target database. This was excluded as a feature at the moment because it's quite complicated to do.

Is your feature request related to a problem? Please describe.
We have very long lines of code for our complex SQL commands we run in Pythia.

Describe the solution you'd like
Replace the command module with an own written Oracle SQL module. This would make cleaner code as well as better functionality when running more complex SQLs.

Additional context
The module has to be content in the ˋlibˋ folder of Pythia.

• Merged in develop branch
• Merged in master branch

Describe the bug
For Oracle 21c and Oracle 19c with RU > 10 installed, the environment script ora_set.sh shows the wrong version (21.3 or 19.3 respectively)

To Reproduce
Steps to reproduce the behavior:

1. Install Oracle 21c with RU4 or higher
2. Login as oracle OS user
3. See the message shows the wrong version

Expected behavior
ora_set.sh should show the correct installed Oracle version including the RU version.

• Merged in develop branch
• Merged in master branch

For Instance, there should be a more detailed documentation for the duplicate task of the role. We could add even more documentation in there for the several tasks.

Describe the bug
There are reports, that the compat-libpthread-nonshared package for SLES15 is needed. Else it will lead to the following error during installation.

Exception Name: MakefileException

Exception String: Error in invoking target 'libasmclntsh19.ohso libasmperl19.ohso client_sharedlib' of makefile '/oracle/product/19_ENT/rdbms/lib/[ins_rdbms.mk](http://ins_rdbms.mk/)'. See '/tmp/(…).log' for details.

Exception Severity: 1

Expected behavior
Installation should finish without an error.

Ansible Oracle Environment:

• Ansible Version: unknown
• Oracle Version: 19
• Pythia Tag: [e.g. rdbms + db] rdbms

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
At the moment, there are several hundret linting errors when running ansible-lint over pythia. We need to fix these errors and implement a Github Action, that will automatically do some linting, when we push to the repository.

Describe alternatives you've considered
Running manual linting with ansible-lint -p pythia.yml roles/pythia/ -x command-instead-of-shell -x risky-shell-pipe -x no-jinja-when -x line-length

Additional context
There is already a Github Action on the marketplace we could implement as our Github Action. We decided, that we will skip command-instead-of-shell, risky-shell-pipe and no-jinja-when for linting.

Is your feature request related to a problem? Please describe.
We want to have the possibility to do Exports / Imports using datapump. For Pythia this should be done by using Database Links.

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
To make testing less time consuming and stressfull, we could use the Ansible Molecule Github Action hence, Molecule itself, to do the teseting for us. So as soon, as we do a push on a feature branch or creating a PR to master, we could run this action. This could be, that we create us some docker containers and run testing playbooks against them.

Describe alternatives you've considered
We could also run molecule tests manually.

Additional context
First we need to get some knowledge in molecule and then implement a basic molecule test scenario for Pythia.

Is your feature request related to a problem? Please describe.
Pythia doesn't check if SELinux is set to enforcing. If so, for instance, the autostart feature of Oracle will not work with following message in the /var/log/messages:

SELinux is preventing dbstart from create access on the file ORA21_lgwr_2085.trc.#012#012*****  Plugin catchall_labels (83.8 confidence) suggests   *******************#012#012If you want to allow dbstart to have create access

Describe the solution you'd like
Pythia should check in the prerequisites (at least for enabling autostart) that SELinux is set to permissive. This can be done with the module selinux which is in the collection ansible.posix.

Example:

- name: Put SELinux in permissive mode, logging actions that would be blocked.
  ansible.posix.selinux:
    policy: targeted
    state: permissive

Additional context
Most likely a reboot is required when setting SELinux to permissive.

• Merged in develop branch
• Merged in master branch

Is your feature request related to a problem? Please describe.
No

Describe the solution you'd like
During DB creation, a list of parameters you want to get set for the finished build instance should be defineable. This could be by a dictionary in the defaults folder (e.G. sid_parameters.yml). The content could be something like this:

sid_parameters:
  ORA21:
    - FILESYSTEMIO_OPTIONS: "setall"
    - FAST_START_MTTR_TARGET: "150"

Describe alternatives you've considered
None

Additional context
We need to check how conflicting parameter configurations work out during db creation. (By setting sga_max_size for instance).
We plan to implement this by templating the *_Database.rsp.j2. Adding all parameters from the list.

• Merged in develop branch
• Merged in master branch