To administer an Oracle Database Appliance (ODA), you probably use the odacli commandline tool.

The principle of this tool is to run jobs in background. Fire-and-forget… or Fire-and-poll

odacli update-repository -f "/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip"

This command returns a jobId (e.g. f1338963-87a2-4cb9-8a3e-06e104270203) and the job is executed in background. To see if this job is (sucessfully) completed, you have to poll for the above jobId and wait for Status: Success.

odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203
watch -n 2 odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203

Automation with Ansible

For automation, e.g. with Ansible, that is not optimal. We have to implement a polling mechanism for the odacli commands.

For example. we will add new software to the ODA-repository. (version=19.30, software_zip=/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip)

First of all, we run odacli to create the job to add the software to the repository and extract the jobId from the output.

  - name: import software in repository
    ansible.builtin.shell: |
      /opt/oracle/dcs/bin/odacli describe-dbsystem-image -j \
        | jq -e '.[].dbSystemImageComponents[]|select(.componentName=="DB")|.availableVersions[]|select(startswith("{{version2}}")) ' \
        |grep  {{version2}} >&2 && echo 'ALREADY_INSTALLED' && exit 0
      /opt/oracle/dcs/bin/odacli update-repository -f "{{software_zip}}"
    register: repo
    changed_when: "'ALREADY_INSTALLED' not in repo.stdout"

  - name: set job-id
    set_fact:
      jobid: "{{ (repo.stdout | from_json).jobId }}
    when: "'ALREADY_INSTALLED' not in repo.stdout"

Hint: the 1st command is to check if this software is already imported

Now, we can poll the job until it is completed. Ansible is optimized to work with json. So we will enforce odacli to return the output in json format (-j):

/opt/oracle/dcs/bin/odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203 -j
{
  "jobId" : "f1338963-87a2-4cb9-8a3e-06e104270203",
  "status" : "Created",
  "message" : "/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip",
  "reports" : [ ],
  "createTimestamp" : "February 24, 2026 13:58:30 PM CET",
  "resourceList" : [ ],
  "description" : "Repository Update",
  "updatedTime" : "February 24, 2026 13:58:30 PM CET",
  "jobType" : null,
  "cpsMetadata" : null
}

For polling, we can use the ansible loop control, see the Ansible documentatioon

  - name: check until job completed
    ansible.builtin.shell: /opt/oracle/dcs/bin/odacli describe-job -j -i {{jobid}}
    register: check_status
    until: "(check_status.stdout|from_json).status == 'Success'"
    retries: 10
    delay: 8
    changed_when: false
    when: "'ALREADY_INSTALLED' not in repo.stdout"

That means, Ansible will run the command every 8 seconds until status Success is returned (Success) or after 10 attemts (Failed)

TASK [check until job completed] ************************************************************************
FAILED - RETRYING: check until job completed (10 retries left).
FAILED - RETRYING: check until job completed (9 retries left).
FAILED - RETRYING: check until job completed (8 retries left).
FAILED - RETRYING: check until job completed (7 retries left).
FAILED - RETRYING: check until job completed (6 retries left).
FAILED - RETRYING: check until job completed (5 retries left).
FAILED - RETRYING: check until job completed (4 retries left).
ok: [server01]

Re-usability with roles

For re-usablility, I recommend to move the job-polling to a role, so that it can be used for all asynchronous jobs

# roles/odacli_job/tasks/main.yml
  - name: set job-id
    set_fact:
      jobid: "{{ (job_stdout | from_json).jobId |default('') }}"
    when: job_stdout|default('') != ''
  - debug: var=jobid

  - name: check until repo job completed
    ansible.builtin.shell: /opt/oracle/dcs/bin/odacli describe-job -j -i {{jobid}}
    register: check_status
    failed_when: false
    until: "(check_status.stdout|from_json).status == 'Success' or (check_status.stdout|from_json).status == 'Failure'"
    retries: "{{retries}}"
    delay:   "{{delay}}"
    changed_when: false

The role can be used as follows:

  - name: include role to poll job
    include_role:
      role: odacli_job
    when: "'ALREADY_INSTALLED' not in repo.stdout"
    vars:
      job_stdout: repo.stdout 
      retries: 50
      delay: 3

The job to poll can be specified by variable “jobid”, or you can provide the json-output of launching the job via “job_stdout”, then the role extracts the jobid

After the import of the software (you will see it in /opt/oracle/oak/pkgrepos/orapkgs/clones/), you can now deploy an ORACLE_HOME with it. For that, we can use the same role to poll this asynchronous job:

  - name: create database home
    ansible.builtin.shell: |
      /opt/oracle/dcs/bin/odacli create-dbhome -j -v {{version}}
    register: home

  - name: include role to poll job
    include_role:
      role: odacli_job
    vars:
      job_stdout: home.stdout
      retries: 80
      delay: 10

Errorhandling

What is not done in the role is the error-handling. It is up to you to define an adequate error-handling. You will get the result of the asynchronous job in the variable check_status.stdout which is of json format.

(check_status.stdout|from_json).status

If the status is

• “Success”, it is OK
• “Failure”, it is not OK
• Any other value (e.g. “Created”), you got a timeout (>retries*timeout sec.), then the result it is unknown (maybe the job completes at a later time, sucessful or not).

L’article Administer Oracle Database Appliance (ODA) with odacli and Ansible est apparu en premier sur dbi Blog.

However, some extractions and conversions are required before these certificates can be used on Windows and configured for SQL Server.

Here, the idea is to propose a solution (the architecture) that prepares the certificate, imports it on the SQL Server host, and configures SQL Server to use it.

We will also see how to separate the preparation and activation steps in order to reduce the impact on the SQL Server service.

In this blog post, we will describe the global approach and the Ansible logic used to implement certificate-based TLS encryption for SQL Server.

Implementation logic

Before configuring TLS encryption on SQL Server, the first point was to understand the certificate format provided by the customer PKI.

In our case, the generated file is a <machine>.pem file. This file contains the server certificate used for TLS, the private key and the certificate chain with the intermediate and root certificates.

As this format cannot be directly used as-is on the Windows side for SQL Server, some extraction and conversion steps are required.

The general idea is to use the Ansible control node as a working area.

The PEM file is first copied into a temporary folder where the different parts of the certificate are extracted:

• the leaf certificate
• the intermediate certificate
• the root certificate
• the private key

These elements are then used to build a PFX file which can be imported on the Windows SQL Server host.

The PFX is installed in the LocalMachine\My certificate store while the intermediate and root certificates are imported into the appropriate Windows certificate stores.

The implementation has been designed around three different execution modes: stage, activate, and full.

The stage mode is used to prepare the certificate without any impact on the SQL Server service. It copies the PEM file, performs the extractions, builds the PFX file, copies it to the managed Windows node and imports the certificates into the Windows certificate stores. No registry change is performed, and the SQL Server service is not restarted. This mode is useful when we want to prepare the server in advance before switching SQL Server to the new certificate.

The activate mode assumes that the certificate is already present on the Windows server. Its role is to configure SQL Server to use the installed certificate and depending on the selected option, restart the SQL Server service or leave the change pending until the next planned reboot.

This can be useful when the certificate activation must be aligned with an existing maintenance window, for example during monthly OS patching.

The full mode executes the complete configuration from end to end. It performs the extraction and conversion steps, imports the certificates, grants the required permissions, configures SQL Server to use the expected certificate, and restarts the SQL Server service only if required. To avoid unnecessary impact, the role relies on the certificate thumbprint. If the expected certificate is already configured, no change is applied and the SQL Server service is not restarted. This behavior is important for idempotency.

For example, if the full mode is executed after an activate mode, nothing should be changed if the certificate is already the correct one. The same logic applies if the playbook is executed by mistake while the certificate has not been renewed.

Another point to manage is the restart of the SQL Server service. SQL Server loads the certificate configuration when the service starts. Therefore, when a new certificate is configured, the change is only effective after a restart of the SQL Server service.

For this reason the role should provide an option to control whether the restart is performed immediately or postponed to the next planned reboot.

We also have to consider DNS aliases. The standard use case is to generate a certificate containing at least the short name and the FQDN of the SQL Server host in the subjectAltName. If DNS aliases are used by client applications, they can also be added to the certificate SAN.

For example:

[alt_names]
DNS.1 = A-WS2022-2.lab.local
DNS.2 = A-WS2022-2

Finally, the customer confirmed that the private key included in the PEM file is not encrypted.

This simplifies the conversion process to PFX, but it also means that the PEM file must be handled carefully during the Ansible execution, especially in temporary folders and during file transfers. With this approach, the role provides a controlled way to prepare, activate, or fully configure TLS encryption for SQL Server while keeping the impact on the SQL Server service under control.

Logical workflow

The complete workflow can be represented as follows:

Architecture summary

The certificate manipulation is performed on the Ansible control node.

The Windows certificate import and SQL Server configuration are performed on the managed Windows SQL Server host.

This separation is useful because the PEM processing and PFX generation are handled with Linux tools such as OpenSSL while the certificate installation, private key permissions, registry configuration and SQL Server restart are handled through Windows modules and PowerShell. The design also supports a controlled deployment approach.

The certificate can first be staged without service impact then activated later during a maintenance window.

The full mode can be used when the complete implementation must be executed in a single run. The use of the certificate thumbprint is important for idempotency. It allows the role to detect whether SQL Server is already configured with the expected certificate and avoids unnecessary service restarts when no change is required.

Remarks

For certain reasons we do not disclose the code of the created role.

Thank you. Amine Haloui

L’article Customer case study – automating SQL Server TLS Encryption with Ansible and Certificates (Architecture) est apparu en premier sur dbi Blog.

The Multi-Layered Threat: Why One Tool is Never Enough

We’ve all left the key in our bike lock at least once. This simple human oversight makes the heaviest chain irrelevant and we often see the exact same logic applied to data environments. Most organizations spend months hardening their production core but leave the keys in the locks of the dev and staging systems that sit right next to it.

The numbers back this up. While 91% of organizations are concerned about their exposure across lower environments, a staggering 86% still allow data compliance exceptions in non-production. This gap between concern and action has real consequences: more than half of these organizations have already experienced a breach or audit failure in their testing and development systems (PR Newswire).

Effective security is rarely a single-layer problem. Between the stolen backup that lands in the wrong hands, the analyst running a SELECT on a table they probably shouldn’t see, and the packet quietly crossing an unsecured network segment, the attack surface is wide, and no single mechanism covers it all.

Transport Layer Security (TLS), Transparent Data Encryption (TDE), symmetric encryption, dynamic masking, row-level security, data anonymization: for most RDBMS, the options exist and they work. Most teams already have access to at least one of them. The real challenge isn’t finding a solution; it’s understanding what each one actually protects, where it breaks down, and whether it survives contact with a production environment.

Shadow Environments: The Weakest Link in Your Data Chain

Here is the uncomfortable truth: non-production environments are often where security policies are quietly buried. It starts with a backup restored without encryption, or real customer data seeding a dev database “just for a quick test“.

The fundamental problem is that most protections assume a controlled environment. Encryption can be bypassed by someone with the right credentials. Masking can be misconfigured. Row-level security doesn’t help much when the whole database is sitting on a developer’s laptop.

Technical Trade-offs: Finding Your Strategic Fit

To make this reasoning concrete, the table below maps six core techniques against the operational criteria that define their success. The goal isn’t to pick a favorite tool, but to identify which combination actually addresses your specific vulnerabilities.

	Physical File Theft	Read Access (SELECT)	Network Sniffing	Performance Impact	Granularity	Applicable in Prod (live data)	Applicable in DEV
TLS					Data packet
TDE					Column Tablespace Datafile
Symmetric encryption (applicative)					Field Value
Dynamic Masking					Column
Row-level security					Row
Data anonymization					Field Column

• TLS protects data in motion. The moment a packet leaves a server, TLS ensures anyone intercepting it sees encrypted noise. What it doesn’t do is equally important: it has no opinion about who queries your database or what’s stored on disk. Once the data arrives, TLS’s job is done.
  TLS is now the industry standard for securing data in motion.
  (SQL Server technical blog about TLS here)
• TDE encrypts the physical files that make up your database (data files, log files, backups), so that anyone who gets their hands on them without the encryption key can’t read them. The performance impact is a negligible overhead; in fact, Microsoft for example enables TDE by default for all its cloud-based databases.
  (PostgreSQL technical blog about TDE here)
  However, deploying TDE in development is a security best practice, but it quickly becomes an operational nightmare for environment refreshes, especially if you want to use distinct certificates to avoid leaking production secrets into lower environments.
• Symmetric encryption is field-level encryption applied directly in the application layer. Unlike TDE, it survives a legitimate SELECT; even a user with full read access sees ciphertext unless they hold the applicative key. The tradeoff is performance: encrypting and decrypting at scale adds up quickly.
  (MongoDB technical blog about Client-side Field Level Encryption here)
• Dynamic masking doesn’t encrypt anything. It intercepts query results and replaces sensitive values with masked equivalents based on the user’s role. Fast, lightweight, zero application changes required. The catch: it only controls what’s displayed, not what’s stored. A user with sufficient privileges can bypass it entirely.
  (SQL Server technical blog about dynamic masking here)
• Row-Level Security enforces access at the row level directly inside the database engine. Users see only the rows they’re allowed to see, regardless of how the query is written. No application changes, no trust placed in the calling layer. The policy lives in the database and applies universally.
  (Oracle technical blog about Virtual Private Database here)
• Data anonymization doesn’t protect sensitive data, it eliminates it. Real values are replaced with realistic but fictional equivalents (synthetic data), permanently and irreversibly. No encryption key to steal, no masking rule to bypass. Whatever leaks simply isn’t sensitive anymore. This is why anonymization is the only control that makes unconditional sense in non-production environments. A stolen backup, a misconfigured SELECT, a sniffed packet: none of it matters if the data was anonymized before it ever reached a staging environment. We covered how to implement it in practice in a previous post

Ownership Gaps: The Security No Man’s Land

We are shifting from a technical challenge to a human and organizational one. The security landscape moves so fast that the struggle of mastering every layer has become overwhelming.

This complexity is where governance goes to die. Infrastructure teams build the walls, developers write the code, and DBAs manage the house, but the accountability for the data itself often falls through the cracks. The most dangerous gap isn’t a missing feature; it’s the absence of a governance model strong enough to stop the game of hot potato and force a cross-domain ownership of security.

The CISO’s role in this landscape is not to master every technical layer, it is to force the question of ownership into the open. Who signs off on what data enters a non-production environment? Who is accountable when a dev database is restored without encryption? Who audits that masking policies are still effective after a release?

Without explicit answers to these questions, security becomes a game of assumptions. Every team assumes another layer is holding. And the gaps compound silently, until they don’t.

From Handcrafted Scripts to Enterprise Platforms

Every technique in this table can be implemented on a spectrum, from a carefully written script to a fully automated enterprise solution. The right choice depends on your scale, your team, and how much operational overhead you can realistically absorb.

• TLS certificate deployment: you can generate and rotate certificates manually, instance by instance. Or you can automate the entire lifecycle using Ansible against an internal PKI with a consistent and auditable way that is invisible to the teams consuming it. The security outcome is identical; the operational cost is not.
• Data anonymization: a custom script that detects PII columns and replaces values with masked data works well at small scale. The challenge appears when your data spans multiple database engines (SQL Server, Oracle, PostgreSQL, …) and when anonymized values need to remain consistent across foreign keys and referential constraints. Replacing a customer ID in one table while leaving it intact in another isn’t anonymization, it’s a GDPR incident waiting to happen. Solutions like Delphix Continuous Compliance handle cross-DBMS consistency, constraint awareness, and sensitive field detection out of the box, turning a fragile hand-rolled process into a governed, repeatable and auditable one.
• Dynamic masking and row-level security: defining a handful of policies manually in SSMS is perfectly reasonable for a contained environment. Automating policy deployment across environments and instances is a different challenge entirely. It is a level of scale where ad-hoc scripts quickly become a liability.

Conclusion: Moving Beyond Security by Accident

Security is not a one-time project. It is an operational discipline that requires the same rigor in a developer’s sandbox as it does in production, and that rigor has to be enforced by design, not by goodwill.

Most breaches in non-production environments don’t happen because a tool failed. They happen because nobody owned the decision to use it in the first place.

At dbi services, we help organizations move from fragile, handcrafted scripts to governed, auditable architectures across every environment, every database engine, and every team.

Because under GDPR, one incident is all it takes to make ownership everyone’s problem.

L’article Beyond TDE and TLS: Bridging the Data Security Governance Gap in Lower Environments est apparu en premier sur dbi Blog.

1. OTDS Replica installation failure

Single-instance installations using the silent properties file were always successful, and most multi-instance installations worked as well. However, I encountered a very specific issue twice: the Primary instance would install successfully, but the Replica installation would fail with an error stating “parameter JDBC_CONNECTION_STRING not defined“. Since everything runs in automated environments (Kubernetes or Ansible), I knew it was not a human error. When comparing the silent properties files, everything looked correct. The file used on the Primary was exactly the same as the one used on the Replica, except for “ISREPLICA_TOPOLOGY=0” and “ENCRYPTION_KEY=” on the Primary versus “ISREPLICA_TOPOLOGY=1” and “ENCRYPTION_KEY=XXXXXXX” on the Replica.

This is the expected configuration. A Replica needs to take the value of “directory.bootstrap.CryptSecret” from the “otds.properties” file of the Primary and use that value for “ENCRYPTION_KEY“. Therefore, when you install the Primary instance, the value remains empty because nothing is installed yet. During the Replica installation, the automation retrieves this value and populates the parameter accordingly. But then why would the Primary installation succeed while the Replica fails when using the exact same silent properties file? Quite strange, right? First of all, I tried running the installer manually (outside of Kubernetes or Ansible) to see whether additional details would appear in the console:

[tomcat@otds-1 workspace_otds]$ /app/scripts/workspace_otds/otds/setup -qbi -rf /app/scripts/workspace_otds/otds/silent.properties

OpenText Directory Services 24.4.0

Error, parameter JDBC_CONNECTION_STRING not defined.
[tomcat@otds-1 workspace_otds]$

2. Going further into the installation logs

The generated log file was not really helpful either:

[tomcat@otds-1 workspace_otds]$ cat otds.log
...
2025-08-08  6:38:40 chmod ran successfully on /etc/opentext/unixsetup
2025-08-08  6:38:40 Setting environment variable "ACTION" to "-1" : Success
2025-08-08  6:38:40 Setting environment variable "UPGRADE" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "PATCH" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "INSTALLED" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "INSTALLEDVERSION" to "0.0.0" : Success
2025-08-08  6:38:40 Setting environment variable "PRODUCTINSTANCE" to "1" : Success
2025-08-08  6:38:40 Setting environment variable "PRODUCTVERSION" to "24.4.0.4503" : Success
2025-08-08  6:38:40 Setting environment variable "PRODUCTNAME" to "OpenText Directory Services" : Success
2025-08-08  6:38:40 Setting environment variable "PRODUCTID" to "OTDS" : Success
2025-08-08  6:38:40 Setting environment variable "PATCHVERSION" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "ROOTUSER" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "Main_INSTALLED" to "-1" : Success
2025-08-08  6:38:40 Setting environment variable "INST_GROUP" to "tomcat" : Success
2025-08-08  6:38:40 Setting environment variable "INST_USER" to "tomcat" : Success
2025-08-08  6:38:40 Setting environment variable "INSTALL_DIR" to "/app/tomcat/app_data/otds" : Success
2025-08-08  6:38:40 Setting environment variable "TOMCAT_DIR" to "/app/tomcat" : Success
2025-08-08  6:38:40 Setting environment variable "PRIMARY_FQDN" to "otds-1.otds.otdsdev.svc.cluster.local" : Success
2025-08-08  6:38:40 Setting environment variable "ISREPLICA_TOPOLOGY" to "1" : Success
2025-08-08  6:38:40 Setting environment variable "IMPORT_DATA" to "0" : Success
2025-08-08  6:38:40 Setting environment variable "OTDS_PASS" to "*****" : Success
2025-08-08  6:38:40 Setting environment variable "ENCRYPTION_KEY" to "mqLgucZ8UIUnNcLwjwmhNw==" : Success
2025-08-08  6:38:40 Setting environment variable "MIGRATION_OPENDJ_URL" to "" : Success
2025-08-08  6:38:40 Setting environment variable "MIGRATION_OPENDJ_PASSWORD" to "*****" : Success
2025-08-08  6:38:40 Setting environment variable "JDBC_CONNECTION_STRING" to "" : Success
2025-08-08  6:38:40 Setting environment variable "JDBC_USERNAME" to "" : Success
2025-08-08  6:38:40 Setting environment variable "JDBC_PASSWORD" to "*****" : Success
2025-08-08  6:38:40 Setting environment variable "ACTION" to "3" : Success
2025-08-08  6:38:40 Setting environment variable "Main_ACTION" to "3" : Success
2025-08-08  6:38:40 Adding Pre-req "TOMCAT7_HIGHER"
...
2025-08-08  6:38:40 Action #1 ended: OK
2025-08-08  6:38:40 Setting environment variable "PRIMARY_FQDN" to "otds-1.otds.otdsdev.svc.cluster.local" : Success
2025-08-08  6:38:40 Setting environment variable "ISREPLICA_TOPOLOGY" to "1" : Success
2025-08-08  6:38:40 Skipping IMPORT_DATA parameter (condition is false)
2025-08-08  6:38:40 Skipping OTDS_PASS parameter (condition is false)
2025-08-08  6:38:40 Setting environment variable "ENCRYPTION_KEY" to "mqLgucZ8UIUnNcLwjwmhNw==" : Success
2025-08-08  6:38:40 Skipping MIGRATION_OPENDJ_URL parameter (condition is false)
2025-08-08  6:38:40 Skipping MIGRATION_OPENDJ_PASSWORD parameter (condition is false)
2025-08-08  6:38:40 Error, parameter JDBC_CONNECTION_STRING not defined.
2025-08-08  6:38:40 Setup Ended: 1
2025-08-08  6:38:40 ============= Verbose logging Ended =============
[tomcat@otds-1 workspace_otds]$

For reference, here is the content of the “silent.properties” file that this Replica installation uses:

[tomcat@otds-1 workspace_otds]$ cat otds/silent.properties
[Setup]
Id=OTDS
Version=24.4.0.4503
Patch=0
Basedir=/app/scripts/workspace_otds/otds
Configfile=/app/scripts/workspace_otds/otds/setup.xml
Action=Install
Log=/app/scripts/workspace_otds/otds/otds.log
Instance=1
Feature=All

[Property]
INST_GROUP=tomcat
INST_USER=tomcat
INSTALL_DIR=/app/tomcat/app_data/otds
TOMCAT_DIR=/app/tomcat
PRIMARY_FQDN=otds-1.otds.otdsdev.svc.cluster.local
ISREPLICA_TOPOLOGY=1
IMPORT_DATA=0
OTDS_PASS=m1z6GX+HEX81DRpC
ENCRYPTION_KEY=mqLgucZ8UIUnNcLwjwmhNw==
MIGRATION_OPENDJ_URL=
MIGRATION_OPENDJ_PASSWORD=
JDBC_CONNECTION_STRING=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db_host.domain.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=db_svc.domain.com)))
JDBC_USERNAME=OTDS
JDBC_PASSWORD=Shu#Asd#Tgb;6799
[tomcat@otds-1 workspace_otds]$

(These are the real passwords from that environment. I have changed them since then, obviously, but I included them so you can understand the details below. The encryption key is altered, though – the system originally took the real one from the “Primary” instance.)

3. Status of the installer created/managed files

After the failure, I checked the parameter file that the OTDS installer populates during installation, but it was mostly empty and not yet filled:

[tomcat@otds-1 workspace_otds]$ cat /etc/opentext/unixsetup/OTDS_parameters_1.txt

#GROUP name that should be used to change file group ownership (group of USER)
INST_GROUP=tomcat

#USER name that should be used to change file ownership (user running processes)
INST_USER=tomcat

#Specify the installation directory for OpenText Directory Services
INSTALL_DIR=/usr/local/OTDS

#Specify the directory, where (64-bit) Apache Tomcat 10 or higher is installed
TOMCAT_DIR=/app/tomcat

#This hostname is used by other instances to connect to the synchronization master host.
PRIMARY_FQDN=

#Is this server a supplementary instance to an existing environment?
ISREPLICA_TOPOLOGY=0

#Specify OpenDJ connection for import.
IMPORT_DATA=0

#Specify the data encryption key from an existing instance.
ENCRYPTION_KEY=

#OpenDJ LDAP URL (example: ldap://localhost:1389)
MIGRATION_OPENDJ_URL=

#Specify JDBC connection String (example: jdbc:postgresql://localhost:5432/postgres). NOTE: Enter these values carefully since they cannot be validated here. Refer to the OTDS installation and administration guide for JDBC URL samples for supported databases.
JDBC_CONNECTION_STRING=

#Specify Database User Name
JDBC_USERNAME=
[tomcat@otds-1 workspace_otds]$

Finally, the “otds.properties” file (normally generated during the installation) was also not present yet:

[tomcat@otds-1 workspace_otds]$ ls -l $APP_DATA/otds/config/otds.properties
ls: cannot access '/app/tomcat/app_data/otds/config/otds.properties': No such file or directory
[tomcat@otds-1 workspace_otds]$

4. Attempts to debug the issue

I tried launching the installer multiple times, on that OTDS Replica, while making small changes to the silent properties file to see if something specific would cause it to fail. Starting by modifying the “JDBC_CONNECTION_STRING” parameter, since that is what the installer complained about, but without success. I then suspected the password parameter. Because passwords are masked in the logs (“*“), it is impossible to see whether the value is parsed correctly or not.

Therefore, I replaced the OTDS Admin password in the silent properties file with “dummyPassword“, and the installer suddenly proceeded further… I cancelled the installation because this was not the real password of the “otadmin” account on the Primary instance, but in this case the “JDBC_CONNECTION_STRING” parameter was no longer empty and the installer continued normally.

Note: the OTDS documentation specifies that passwords must contain at least eight characters, including one lowercase letter, one uppercase letter, one number, and one special character. However, it appears that this rule may not be strictly validated during Replica installations (and possibly not for the Primary either?).

At that point it became clear that the password itself was involved in the issue, somehow. Looking at the script “tools/setup.sh“, you can see that the installer extracts the value of “OTDS_PASS“, applies a function called “AsctoHex“, and then encrypts it. My original “otadmin” password was 16 characters long and satisfied all complexity requirements. However, I noticed that the password contained the string “HEX“. Since the installer converts the password to hexadecimal before encryption, I wondered whether the presence of the string “HEX” might interfere with this process. That would be quite unbelievable, right?

5. A problem with the password length or content?

To test this idea, I removed the “E” in the middle, transforming “HEX” into “HX” and effectively reducing the password length by one character:

[tomcat@otds-1 workspace_otds]$ grep OTDS_PASS otds/silent.properties | awk -F= '{print $2}' | wc -c
17
[tomcat@otds-1 workspace_otds]$ # 17 means 16 char since wc -c count the new line in this command
[tomcat@otds-1 workspace_otds]$
[tomcat@otds-1 workspace_otds]$ sed -i 's,HEX,HX,' otds/silent.properties
[tomcat@otds-1 workspace_otds]$
[tomcat@otds-1 workspace_otds]$ grep OTDS_PASS otds/silent.properties | awk -F= '{print $2}' | wc -c
16
[tomcat@otds-1 workspace_otds]$ # 16 means 15 char now

To re-execute the installer after a failure, you must remove the content of the “/etc/opentext” directory (which kind of caches the content from the “silent.properties” file) and also delete the “otds.properties” file if it exists (not in my case):

[tomcat@otds-1 workspace_otds]$ rm -rf /etc/opentext/*
[tomcat@otds-1 workspace_otds]$

In addition to modifying the “silent.properties” file, I also changed the “otadmin” password through the OTDS otds-admin UI (see the OTDS Install & Admin Guide, section 7.2.5 “Resetting a user password”). Then I started a new Replica installation to see whether changing “HEX” to “HX” from the password would resolve the issue:

[tomcat@otds-1 workspace_otds]$ /app/scripts/workspace_otds/otds/setup -qbi -rf /app/scripts/workspace_otds/otds/silent.properties

OpenText Directory Services 24.4.0

------------------------------------------------------------------------------
  OpenText Directory Services
------------------------------------------------------------------------------
Installing OpenText Directory Services Component
Please wait .
Installation of OpenText Directory Services Component OK

Installation completed. Results:

OpenText Directory Services Component OK

Installation finished.

[tomcat@otds-1 workspace_otds]$

… It worked …?

If the issue was really caused by the presence of “HEX” in the password, then replacing it with “HXE” should also work, right? Unfortunately, when I tried that, the issue came back… This indicates that the real problem is not the literal “HEX” string but maybe something related to password length, complexity, or how the installer processes and encrypts the password internally?

6. Conclusion

In the end, I reverted to the shorter 15-character password that worked and prepared all higher environments at this customer to use 15-character passwords. This approach worked without issue for five additional environments until, of course, it failed again, in Production…

Since it failed in another environment even with a 15-character password, the length alone does not seem to be the root cause. When reviewing previously installed environments across multiple customers, I found a few instances running with “otadmin” passwords of up to 19 characters long (about 111/120 bits of entropy according to a password manager like KeePass). This is significantly stronger than the 15-character password (96 bits) used in the Production environment where the issue occurred.

Therefore, since I couldn’t find any logical reasons why the issue happened on some environments but not with others, I opened a ticket with OpenText. I described everything and we went through several weeks of exchanges to try to find an explanation but without success. As of today, I still don’t know why ~10% of the OTDS Replicas that I installed faced an issue with the OT Admin password, but the fix, was simply to change the password in the UI and start the silent installation again. I don’t have an environment to test / debug that issue anymore, since it’s not easily reproducible. Guess I will need to wait for next time to get more debug logs from the OTDS installer (“-debug” option). In the meantime, I can only assume something is probably wrong in the way OTDS manages the password or its hash.

L’article OTDS – Installation of Replicas fail if the OT Admin password is too long? est apparu en premier sur dbi Blog.

Today, the benefits of automation no longer need much explanation: saving time, reducing human error, and ensuring every environment remains aligned with internal standards. What is less obvious, however, is how using an Ansible Playbook can provide advantages that more traditional scripting approaches — such as large PowerShell scripts — struggle to offer. That is exactly what I want to explore here.

When you complete an automated deployment of a SQL Server environment on Windows Server, there is a real sense of achievement. You have invested time and effort, and you expect that investment to pay off thanks to the reliability and repeatability of automation.

But everything changes when the next Windows Server upgrade or SQL Server version arrives… or when corporate standards evolve. Suddenly, you need to reopen a multi-thousand‑line PowerShell script and:

• Integrate the required changes while keeping execution stable,
• Avoid subtle but potentially critical regressions,
• Maintain clear and usable logging,
• Retest the entire automation workflow,
• Troubleshoot new issues introduced by the modifications.

This is precisely the type of situation where Ansible becomes a far better long‑term investment. Its architecture and philosophy offer several advantages:

• Native idempotence, ensuring the same result even after multiple runs,
• A declarative YAML approach, focusing on the desired end state rather than the execution steps,
• Windows Server and SQL Server modules, providing built‑in idempotence and saving significant time,
• Agentless connectivity, simplifying deployment on new machines,
• A modular structure (roles, modules, variables), making adaptation and reuse of your automation much easier.

In this article, I will give you a concrete overview by walking you through how to configure the disks required for SQL Server using Ansible.

1-Map iSCSI controllers to disk numbers

When developing an Ansible Playbook, one fundamental principle is to design for idempotence from the very start—not just rely on idempotent modules.

On Windows, disk numbering is not guaranteed: it depends on several factors – how disks are detected at startup, the firmware, and so on.
As a result, disk numbers may change from one reboot to another.

To ensure consistent and reliable execution of your deployment, this behavior must be accounted for directly in the design of your Playbook.
Otherwise, it may introduce wrong behaviors, and lead to:

• formatting the wrong disk,
• mounting volumes on incorrect devices,
• completely breaking the SQL Server provisioning workflow.

In other words, idempotence is no longer guaranteed.

To ensure stable and predictable executions, you must determine dynamically the correct disk numbering at each execution.

You can use Get-Disk PowerShell command to achieve your goal, by searching iSCSI controller number and LUN position from Location property.

$adapter = {{ disk.adapter }}
$lun = {{ disk.lun}}
(Get-Disk | Where-Object {
      $_.Location -match "Adapter $adapter\s+:.*\s+LUN $lun"
    }).number

We have done our mapping between VM specifications and Windows disk numbers.

2-Loop SQL Server disks

Since we often have several disks to configure — Data, Logs, TempDB — we need to perform the same actions repeatedly on each disk:

• dynamically determine the disk number,
• initialize it in GPT,
• create the partition and format the volume in NTFS with a 64 KB allocation unit size,
• assign an access path (drive letter or mountpoint),
• apply certain specific configuration settings, such as disabling indexing,
• verify the compliance of the disk configuration.

As these actions are identical for all disks, the best approach is to factorize the tasks.
The Ansible pattern, for such scenario, is to loop that call in a dedicated Task File.

---

- name: Manage all disk properties based on Location and Target numbers
  ansible.builtin.include_tasks: disks_properties.yml
  loop:
    - name: data
      location: "{{ disk_specs.data.location }}"
      target: "{{ disk_specs.data.target }}"
      label: "{{ disk_specs.data.label }}"
      letter: "{{ disk_specs.data.letter }}"
    - name: logs
      location: "{{ disk_specs.logs.location }}"
      target: "{{ disk_specs.logs.target }}"
      label: "{{ disk_specs.logs.label }}"
      letter: "{{ disk_specs.logs.letter }}"
    - name: tempdb
      location: "{{ disk_specs.tempdb.location }}"
      target: "{{ disk_specs.tempdb.target }}"
      label: "{{ disk_specs.tempdb.label }}"
      letter: "{{ disk_specs.tempdb.letter }}"
  loop_control:
    loop_var: disk

...

3- Implement SQL Server disk configuration

Since we performed our loop in the previous section on the disks_properties.yml file, we can now implement the configuration actions inside this file.
First, we will retrieve the disk number and then begin configuring the disk according to best practices and our internal standards.

To guarantee idempotence, we will mark this step as not changed: this is only a Get action:

---

- name: Identify the {{ disk.name }} disk number
  ansible.windows.win_shell: |
    $adapter = {{ disk.target }}
    $lun = {{ disk.location }}
    (Get-Disk | Where-Object {
      $_.Location -match "Adapter $adapter\s+:.*\s+LUN $lun"
    }).number
  register: disk_num
  changed_when: false

Then, we will register the disk number as an Ansible Fact for all this task file execution call.

- name: Set fact for {{ disk.name }} disk number
  ansible.builtin.set_fact:
    "disk_number_{{ disk.name }}": "{{ disk_num.stdout | trim | int }}"

We can now initialize the disk using community.windows module. Of course, use Ansible module if possible.

The parameter disk_bps.partition_style is a variable of my Ansible Role, to guarantee GPT will be used.

- name: Initialize disks
  community.windows.win_initialize_disk:
    disk_number: "{{ lookup('vars', 'disk_number_' + disk.name) }}"
    style: "{{ disk_bps.partition_style }}"

From there, we can create our partition:

- name: Create partition with letter {{ disk.letter }} for disk {{ disk.name }}
  community.windows.win_partition:
    drive_letter: "{{ disk.letter }}"
    partition_size: "-1"
    disk_number: "{{ lookup('vars', 'disk_number_' + disk.name) }}"

And now format our volume with allocation unit size 64KB:

- name: Create a partition letter {{ disk.letter }} on disk {{ disk.name }} with label {{ disk.label }}
  community.windows.win_format:
    drive_letter: "{{ disk.letter }}"
    allocation_unit_size: "{{ disk_bps.allocation_unit_size_bytes }}"
    new_label: "{{ disk.label }}"

...

As I mentioned earlier in previous section, we can also add tasks relative to some specific standards or a tasks to guarantee disk compliance.

4- Execute the Playbook

Now that our Ansible Role windows_disks is ready, we can call it through a Playbook.
Of course, we must adjust the reality of the iSCSI configuration of the Virtual Machine.

---

- name: Configure Disks by detecting Disk Number
  hosts: Raynor
  gather_facts: false
  vars:
    disk_specs:
      data:
        location: 0
        target: 1
        label: SQL_DATA
        letter: E
      logs:
        location: 0
        target: 2
        label: SQL_TLOG
        letter: L
      tempdb:
        location: 0
        target: 3
        label: SQL_TEMPDB
        letter: T
  tasks:
    - name: gather facts
      ansible.builtin.setup:
      changed_when: false
      tags: [always]
    - name: Configure Disks
      ansible.builtin.import_role:
        name: windows_disks
      tags: windows_disks


...

CONCLUSION

We have had an overview of how Ansible makes automation easier to maintain and to evolve, by focusing on the logic of our deployment and not on the code to achieve it.
Now, updating your standards or upgrading versions will no longer require rewriting scripts, but mainly adapting variables.

However, it is important to be aware that idempotence must also be maintained through design.

L’article How to Standardize SQL Server Disks on VMs using Ansible est apparu en premier sur dbi Blog.

In this article, we will look at how to standardize and automate this deployment using PowerShell.
This will provide the core of the TLS setup, based on a certificate name and an instance name.
For full generalization, it only requires to loop over a list of instances, or to integrate it into an Ansible playbook, for example.

In our automation, we assume that the certificate is already present in Cert:\LocalMachine\My (Local Machine Personal). The certificate is ws202201.dbi.test in this output:

PS> ls cert:\LocalMachine\My


   PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\My

Thumbprint                                Subject
----------                                -------
E6DB2D082B41B36F08806EA82B25064150618E1F  CN=CLIUSR
D4EBB1260BECFD28254082A734FD4D5663199B16  CN=CLIUSR
30B20A2A54A3DAB60E6AC601B94F6E5A626D7C2D  CN=ws202201.dbi.test

The first step is to give the SQL Server service account read permissions on the certificate’s private key, otherwise the instance will not be able to use the certificate for TLS.
The script uses two input parameters : Instance Name of SQL Server and Friendly Name of the certificate.

#---- Variables -------------------------------------------------------------
$InstanceName = 'INSTANCE1';     # Instance name to adapt
$FriendlyName = 'Instance1 TLS'; # Certificate Friendly Name to adapt

#---- Begin Script ----------------------------------------------------------
# Construct Virtual Account for SQL Server instance
If ($InstanceName -eq 'MSSQLSERVER') {
    $user = 'NT SERVICE\MSSQLSERVER';
} else {
    $user = "NT SERVICE\$('MSSQL$'+$InstanceName)";
};

# Retrieve certificate based on Friendly Name
$cert = Get-ChildItem -Path 'Cert:\LocalMachine\My' ` 
| Where-Object {$_.FriendlyName -eq "$FriendlyName"};

# Give Virtual Account read permission
$permission = "Read"; 
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $permission, "Allow"); 
$keyPath = [System.IO.Path]::Combine("C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys", $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName); 
$acl = Get-Acl -Path $keyPath;
$acl.SetAccessRule($rule); 
Set-Acl -Path $keyPath -AclObject $acl;

The next step is to reference the certificate’s thumbprint in the SQL Server registry configuration.
A key detail here: while PowerShell is case-insensitive, the thumbprint stored in the registry is case-sensitive. SQL Server expects the value to be written in lowercase; otherwise, the instance will not load the certificate at startup.

# Root registry entry of instance (Regex)
$Pattern = "MSSQL\d\d\.$($InstanceName)";

# Construct path where is stored Certificate property
$RegMSSQL = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\';
$RegRootInstance = Get-childItem -Path $RegMSSQL `
| Where-Object Name -match "$Pattern";

# Update Certificate property with certificate thrumprint in lower case
$RegTCPCertificate = 'MSSQLServer\SuperSocketNetLib\';
Set-ItemProperty ` 
-Path (Join-Path -Path $RegRootInstance.PSPath -ChildPath $RegTCPCertificate) `
-Name 'Certificate' -Value "$($cert.Thumbprint.ToLower())";

All that remains is to restart the SQL Server service to apply the changes.

To confirm encryption, connect SQL Server and run the following T-SQL command:

SELECT 
  session_id 
  ,encrypt_option
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID


session_id encrypt_option
---------- --------------
        60 TRUE

L’article Automate encryption-in-transit for SQL Server est apparu en premier sur dbi Blog.

I was fortunate to be able to attend at the pgconf.eu 2025.
This year event was happening in RIGA and joined together once again key members of the community, contributors, committers, sponsors and users from across the world.
I would summarize this year event with those three main topics : AI/LLM – PG18- Monitoring.

AI/LLMs

Compared to last year the formula changed a bit regarding the Community events day of Tuesday where for the first time different “Summits” where organized. If you want full details on the event and the schedule as well as the presentation slides of each talk you may find it here : Schedule — PostgreSQL Conference Europe 2025
I had the chance to be chosen as a speaker for the AI Summit. It was quite interesting for me. In total there was 13 short talks (10min) on various topics related to PostgreSQL and AI/LLMs it was dense with a lot of interesting ideas of implementations – you can find the details and slides here PGConf.EU 2025 PostgreSQL AI Summit – PostgreSQL wiki. AI/LLMs are the hot topic of the moment and naturally it came up often during this event, in the talks and in the discussions. You can find the pdf of my presentation here. I explained a business case implementation of a BI self-service agentic RAG to find relevant fields for a target KPI and data marts creation as output. Since the talks were short, it allowed to have a debate at the end between the audience and the speakers. The discussion nicely moderated by organizers was interesting because it exposed the same strong thoughts people have in general about AI/LLMs. A blend of distrust and not fully understanding of what it is about or how it could help organizations. Which, in itself, shows that the PostgreSQL community has the same difficulties at explaining technical challenges versus organizational/human challenges. My view here is that we don’t have technical challenges, they are almost un-relevant to most arguments but rather human relation and understanding of what values a DBA for example, brings to the organization. To me installing and configuring PostgreSQL has no benefits in terms of personal growth so automating it is quite natural and adding AI/LLMs on top is “nice to have” but not fundamentally different than an Ansible playbook. But for the junior DBA this an additional abstraction that can be dangerous because it provides tools that users can’t grasp the full extent of their consequences. This outlines that the main issue of integrating AI/LLMs workflows is more a governance/ C-management issue than a technical one and it can’t be the last excuse for adding to the technological debt.
Jay Miller from Aiven explained how you can fail at exposing PII from LLMs and MCPs. This is rely a relevant topic knowing that more and more organization are facing issues like shadow IT. He also was quite the show host and was funny to hear. I recommend strongly watching the recording when it will be released.

PG18

This year was just after the PostgreSQL 18 version release which is one the version that brought major improvements and is initiating changes for future release to come. I was quite enthusiast to listen to Melanie Plagemen on how she worked on the improvements on freezing in this release. I have to say, usually when I am going at an advanced internal talk, I am more confused after than before. But here, Melanie did an amazing job at talking about a technical complex topic without loosing the audience.
Gülçin Yıldırım Jelínek, on her side explained what’s new in PG18 about constraints like NOT ENFORCED and NOT NULL and how to use them. The COO of Cybertec Raj Verma, during a sponsor talk, explained why compliance matters and how to minimize the risks and how PostgreSQL is helping us to be PCI DSS, GDPR, nLPD or HIPAA compliant.
Another interesting talk I was happy to attend was from Floor Drees and Gabriele Bartolini. they explain how they went on joining the CloudNativePG project to the CNCF.

Monitoring

This leads me to another important topic, I wasn’t looking for it but became a bit of a main subject for my over the years as a DBA that was interested in performance tuning. Monitoring on PostgreSQL was introduced by several talks like Luigi Nardi and his idea of workload fingerprint with the DBtune tool they have. Additionally, Lukas Fittl presented pg_stat_plans, an extension which aims at tracking execution plans over time. This is definitely something I am going to try and will push for implementation in the core extensions if not the core code itself.
The reason for that is obvious for me, PostgreSQL is becoming more and more central to enterprise organizations and appart from subject like TDE, monitoring is going to become a key aspect of automation, CloudNativePG and AI/LLM workflows. Having PostgreSQL being able to be monitored better and easier at the core will allow leveraging at all this levels. Cloud companies release that already hence there involvement in similar projects.

In the end, this year was once again the occasion for me to think about many relevant topics and exchange with PostgreSQL hackers as well as users from around the world. I came back home with the head full of ideas to investigate.

Additionally after the conference the videos of the each talks will be uploaded to the pgconf Europe Youtube channel : PostgreSQL Europe, but you can already check previous amazing talks and this year pgday Paris.

So once again the PostgreSQL flag was floating up high !

L’article pgconf.eu 2025 – RECAP est apparu en premier sur dbi Blog.

A Shift Toward Jakarta EE

The most visible and impactful change in JBoss EAP 8 is the transition from Java EE to Jakarta EE.

• Namespace migration: All javax.* packages are now replaced by jakarta.*.
• This means that even if your application compiles fine on EAP 7, it won’t deploy on EAP 8 without updating imports and dependencies.
• While this migration can sound painful, it’s a necessary step to stay compatible with the modern Java ecosystem and future versions of Jakarta EE.

We can count on the Red Hat’s EAP Migration Toolkit to automatically detect and fix most of the package name changes.

New Java and Platform Support

JBoss EAP 8 officially supports Java 17 and later.
This brings performance, security, and syntax improvements, while dropping support for older Java versions (like Java 8 in many cases).

Other platform updates include:

• Updated Undertow web server version for improved HTTP/2 and security.
• Enhanced datasource and driver management via the CLI and management console.
• Simplified configuration through YAML and CLI scripts, helping automate deployments and tuning.

Updated Subsystems and Architecture Improvements

EAP 8 brings a more modular, streamlined architecture:

• Legacy subsystems deprecated (e.g., older messaging or logging frameworks).
• MicroProfile updates: More APIs for observability, configuration, and fault tolerance.
• Improved clustering and domain mode management, faster startup and better node synchronization.

For administrators, these changes mean fewer manual tweaks and more consistent runtime behavior across environments.

Ready for the Cloud (for Real )

Red Hat has made significant investments to make EAP 8 cloud-native:

• Better support for OpenShift and Kubernetes with optimized container images.
• Smaller footprint and faster startup thanks to tuned modules and lazy loading.
• Compatibility with Red Hat build of Quarkus for microservice migration paths.

In other words, JBoss EAP 8 is no longer just a traditional application server, it’s a hybrid platform that bridges the gap between legacy Java EE workloads and modern cloud architectures.

Security and Compliance Enhancements

Security was a major focus in JBoss EAP 8:

• Integrated Elytron 2 for modern authentication and authorization.
• Stronger TLS configurations by default.
• Simplified credential store management (replacing legacy vault mechanisms).

Administrators will appreciate the more centralized, policy-driven security model.

My Experience & Recommendations

After long time working with both JBoss EAP 7 and JBoss EAP 8, I can say the migration is more about preparation than complexity.
The most common pitfalls I’ve seen include:

• Forgetting the Jakarta namespace migration.
• Using old JDBC drivers or libraries no longer supported.
• Missing dependencies when running in containerized environments.

Our best practice is always to:

• Test the migration in a clean environment.
• Use automation (Ansible or CI/CD pipelines) for consistent builds.
• Validate performance, logging, and metrics integration (especially with Zabbix or Elastic).

Once properly prepared, JBoss EAP 8 runs smoother, faster, and integrates much better with modern infrastructure.

Conclusion

JBoss EAP 8 isn’t just an upgrade, it’s a modernization step.
It pushes Java EE into the Jakarta EE era, embraces cloud-native deployments, and simplifies operations for enterprises.
While the migration from EAP 7 requires careful planning, the long-term benefits in performance, maintainability, and compliance make it well worth the effort.

If you’re planning a JBoss migration, feel free to reach out for guidance or a technical exchange.
Have a look at our JBoss EAP blogs for more insights.

Happy to share,

David

L’article From JBoss EAP 7 to 8: What Really Changed est apparu en premier sur dbi Blog.

Per default, if a task in a playbook fails, then the execution of the playbook is stopped for that host.

- name: PLAY1
  hosts: localhost
  gather_facts: no
  tasks:
    - name: let this shell-command fail
      ansible.builtin.shell: exit 1

    - name: let this shell-command complete
      ansible.builtin.shell: exit 0

As you can see, the 2nd task is not executed. If you want to continue in such a case, the ignore_errors parameter is your friend

    - name: let this shell-command fail
      ansible.builtin.shell: exit 1
      ignore_errors: true

Custom error conditions

Per default, Ansible evaluates the exit-code of the module, in case of the shell-module, the exit-code of the last command.

But for some commands, that is not adequate. Example: The Oracle commandline tool sqlplus to submit sql-commands will have an exit-code of 0 if it can connect to the database-instance. It is not related to the result of your SQL-commands. Error-messages in Oracle are prefixed by ORA-.

So, if you want to check for application errors, you have to implement it yourself. For that, you can use the failed_when option.

    - name: let this shell-command fail
      ansible.builtin.shell: |
        . ~/.profile
        echo "select count(*) from all_users;" | sqlplus / as sysdba
      register: number_of_users
      failed_when: "'ORA-' in number_of_users.stdout"

Caution: In this case the exit-code of the shell is no longer evaluated. To also get the exit-code of the sqlplus call (e.g., sqlplus can not connect to the database, or sqlplus binary not found), you have to add this (default) condition:

      failed_when: "number_of_users.rc != 0 or 'ORA-' in number_of_users.stdout"

But caution! Not all modules will have an rc field.

Tuning: Perform several checks at once

Conceptually, Ansible is not the fastest tool. For each task, it will usually login with ssh to the remote server. If you have to run several checks in the shell, then, instead of running each in a separate task, you can run all these check-commands in one shell-task, and evaluate the result afterwards.

    - name: run many check commands
      ansible.builtin.shell: |
        mount | grep ' on /u00 '  #check if /u00 is mounted
        rpm -q ksh 2>&1  #check if ksh is installed
        exit 0 # do not fail if rpm exit != 0; we will do our own errorhandling
      register: checks

    - name: fail if no /u00 is mounted
      fail:
        msg: "/u00 is not mounted"
      when: "' on /u00 ' not in checks.stdout"

    - name: No ksh found, try to install it
      yum:
        name: ksh
        state: present
      when: "'package ksh is not installed' in checks.stdout"

If you only want to throw an error, then you can do it directly in the shell-task:

when: "' on /u00 ' not in checks.stdout" or 'package ksh is not installed' in checks.stdout

But if you parse the output afterwards, you can run tasks to fix the error.

Sometimes it is difficult to parse the output if some commands return the same output, e.g. “OK”.
If your check-commands always return exactly 1 line, then you can directly parse the output of the command. The output of the 3rd command is in checks.stdout_lines[2].
In the above example that will not work because grep will return the exit-code 0 (not found) or 1 (found) plus the found line. So, expand it as: mount | grep ' on /u00 ' || echo error

Print errormessages more readable

Do not fail the task itself, it is very usually unreadable because all information is printed on one line.

Instead, use ignore_errors: true and failed_when: false in the task. Do the errorhandling in a separate task with a customized errormessage. To print the multiline list of stdout_lines, use debug: otherwise you can directy use ansible.builtin.fail: with a customized message:

    - name: force sqlplus to throw error because of missing environment
      ansible.builtin.shell: |
        /u00/app/oracle/product/19.7.0/bin/sqlplus -L / as sysdba @myscript.sql 2>&1
      register: checks
      ignore_errors: true
      failed_when: false

    - name: Check for errors of task before
      debug: var=checks.stdout_lines
      failed_when: checks.rc != 0 or 'ORA-' in checks.stdout
      when: checks.rc != 0 or 'ORA-' in checks.stdout

Re-run failed hosts

As an example for this scenario: A customer of mine will do an offline backup of the development databases. And I have to run an Ansible playbook against all databases. If the playbook for this host is run at the backup time, it will fail because the database is down. But after some minutes the database will be restarted.

What we can do now?

Wait until the database is up again. That is possible, see the example of ansible.builtin.wait_for in my blog post Parallel execution of Ansible roles. But for this scenario it is a waste of time. The database can be stopped (not for backup) and will not be restarted within the next few minutes.

Try later after a while. My playbook for all hosts (parallel forks=5) takes about 1 hour. The idea now is to remember the host with the stopped database and to continue with the next host. After the play finished for all hosts, restart the play for the remembered hosts.

• The 1st play running against all database hosts:
• gets the status of the databases on the host
• assigns the database instances to a is_running and a not_open list
• Include the role to run against the running databases
• Dynamically add the host to the group re_run_if_not_open if there are not_open databases

• The next play only runs for the re_run_if_not_open group
• Include the role to run against the (hopefully now running) databases
• If the database then is still down, we assume it is stopped permanently.

L’article Errorhandling in Ansible est apparu en premier sur dbi Blog.

Check ZDM build

Let’s first see how we can check ZDM version using zdm cli.

[zdm@zdmhost ~]$ zdmcli -v
RHP_PT.ZDM21_LINUX.X64_221207.30

[zdm@zdmhost ~]$ zdmcli -build
version: 21.0.0.0.0
full version: 21.4.0.0.0
patch version: 21.4.5.0.0
label date: 221207.30
ZDM kit build date: Mar 21 2024 22:07:12 UTC
CPAT build version: 24.6.0
[exauser@sva-oelexa501 ~]$

Version installed is 21.4, and we would see how to update it to last current version, which is 21.5.

Manuel installation and update

Manuel installation and update is quite easy as it will be done through the zdminstall.sh script. It will be the option that will highlight if it is a first installation or an update. For a first installation, the option will be setup and for an update it will be update.

Automatisation of the installation and update of ZDM

The playbook to automatise the installation and the update, in my case named deploy_zdm.yml, is composed of following tasks.

We start by defining the playbook and the variable that will be used in the included tasks to install or update to zdm current last version, which is 21.5. The zip file to unarchive will be stored in the ../oracle_swfiles directory.

---
# Playbook for deploying ZDM host
# Marc Wagner - dbi
# Date : 06.10.2023
# 11.06.2025 : stop service + new 21.5 version
- name: Deploy ZDM
  hosts: "zdmhost"
  vars:
    sfw_folder: "../oracle_swfiles"


    zdm_base: "/u01/app/oracle"
    zdm_install_dir: "zdm21.5"
    zdm_download_dir: "/u01/app/zdm_download_dir"
    zdm_archive: "{{ sfw_folder }}/V1045330-01.zip"

    # ZDM update is an in-place process
    zdm_home: "/u01/app/oracle/product/zdm"
  environment:
    HTTP_PROXY: ""
    HTTPS_PROXY: ""

  tasks:

Then we will have all the tasks performing the installation.

The first one will be used to define the variable for the option provided as extra argument of the ansible playbook. And we will assert that the variable is provided.

- name: Assert extra-var has been passed as argument to playbook
  ansible.builtin.assert:
    that:
      - deploy_option is defined
    quiet: false
    fail_msg: "Please provide --extra-var deploy_option="
    success_msg: "deploy_option={{ deploy_option }}"

The next task will stop ZDM service and will only run if following file exists : /u01/app/oracle/product/zdm/bin/zdmservice.

This check is done so the task will only run if ZDM tool is already installed.

- name: Stop ZDM service
  ansible.builtin.shell: |
    cmd: |
    {{ zdm_home }}/bin/zdmservice stop
  args:
    executable: "/bin/bash"
    removes: "/u01/app/oracle/product/zdm/bin/zdmservice"

The next task will install some prerequisite if not already installed.

- name: Install ZDM software prerequisites
  become: true
  become_user: root
  ansible.builtin.dnf:
    name: "{{ item }}"
  loop:
    - perl
    - expect
    - libaio
    - glibc-devel
    - unzip
    - libnsl
    - ncurses-compat-libs
    - oraclelinux-developer-release-el8

The next task will create all needed directories like zdm base, zdm home and the file used to store the installation archive file.

- name: Create directories for ZDM tool
  become: true
  become_user: root
  ansible.builtin.file:
    path: "{{ item }}"
    state: directory
    owner: exauser
    group: exauser
    mode: '755'
  loop:
    - "{{ zdm_base }}"
    - "{{ zdm_home }}"
    - "{{ zdm_download_dir }}"

The next task will unarchive the installation zip file.

- name: Unarchive ZDM
  ansible.builtin.unarchive:
    src: "{{ zdm_archive }}"
    dest: "{{ zdm_download_dir }}"

And the next task will finally installed or update zdm tool according to the option given to the playbook.

- name: Install or update ZDM
  ansible.builtin.shell:
    cmd: |
      {{ zdm_download_dir }}/{{ zdm_install_dir }}/zdminstall.sh \
        {{ deploy_option }} oraclehome={{ zdm_home }} oraclebase={{ zdm_base }} \
        ziploc={{ zdm_download_dir }}/{{ zdm_install_dir }}/zdm_home.zip -zdm
  args:
    executable: "/bin/bash"

And we can finally with the last steps start ZDM service.

- name: Start ZDM service
  ansible.builtin.shell: |
    cmd: |
    {{ zdm_home }}/bin/zdmservice start
  args:
    executable: "/bin/bash"

Run the playbook

As prerequisite, let’s first check that the appropriate ZDM installation zip file is in the expected ansible folder.

[myuser@domain.com@admin-host zdm_ansible]$ ls -ltrh ./oracle_swfiles/
total 874M
-rw-r--r--. 1 myuser@domain.com myuser@domain.com 871M Jun 11 10:12 V1045330-01.zip
[myuser@domain.com@admin-host zdm_ansible]$

We can check that the assert task to ensure we put the appropriate –extra-vars works.

[myuser@domain.com@admin-host zdm_ansible]$ ansible-playbook ./playbooks/deploy_zdm.yml

PLAY [Deploy ZDM] ***********************************************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ******************************************************************************************************************************************************************************************************************************************************************
[WARNING]: Platform linux on host zdmhost is using the discovered Python interpreter at /usr/bin/python3, but future installation of another Python interpreter could change the meaning of that path. See https://docs.ansible.com/ansible-
core/2.15/reference_appendices/interpreter_discovery.html for more information.
ok: [zdmhost]

TASK [Assert extra-var has been passed as argument to playbook] *************************************************************************************************************************************************************************************************************************
fatal: [zdmhost]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'deploy_option' is undefined. 'deploy_option' is undefined\n\nThe error appears to be in '/home/nfs/domain.com/myuser/ExaCCGit/zdm_ansible/playbooks/deploy_zdm.yml': line 25, column 7, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n\n    - name: Assert extra-var has been passed as argument to playbook\n      ^ here\n"}

PLAY RECAP ******************************************************************************************************************************************************************************************************************************************************************************
zdmhost              : ok=1    changed=0    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0

[myuser@domain.com@admin-host zdm_ansible]$

Then we can run the playbook with the update option.

[myuser@domain.com@admin-host zdm_ansible]$ ansible-playbook ./playbooks/deploy_zdm.yml -e deploy_option="update"

PLAY [Deploy ZDM] ***********************************************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ******************************************************************************************************************************************************************************************************************************************************************
[WARNING]: Platform linux on host zdmhost is using the discovered Python interpreter at /usr/bin/python3, but future installation of another Python interpreter could change the meaning of that path. See https://docs.ansible.com/ansible-
core/2.15/reference_appendices/interpreter_discovery.html for more information.
ok: [zdmhost]

TASK [Assert extra-var has been passed as argument to playbook] *************************************************************************************************************************************************************************************************************************
ok: [zdmhost] => {
    "changed": false,
    "msg": "deploy_option=update"
}

TASK [Stop ZDM service] *****************************************************************************************************************************************************************************************************************************************************************
changed: [zdmhost]

TASK [Install ZDM software prerequisites] ***********************************************************************************************************************************************************************************************************************************************
ok: [zdmhost] => (item=perl)
ok: [zdmhost] => (item=expect)
ok: [zdmhost] => (item=libaio)
ok: [zdmhost] => (item=glibc-devel)
ok: [zdmhost] => (item=unzip)
ok: [zdmhost] => (item=libnsl)
ok: [zdmhost] => (item=ncurses-compat-libs)
ok: [zdmhost] => (item=oraclelinux-developer-release-el8)

TASK [Create directories for ZDM tool] **************************************************************************************************************************************************************************************************************************************************
ok: [zdmhost] => (item=/u01/app/oracle)
ok: [zdmhost] => (item=/u01/app/oracle/product/zdm)
ok: [zdmhost] => (item=/u01/app/zdm_download_dir)

TASK [Unarchive ZDM] ********************************************************************************************************************************************************************************************************************************************************************
ok: [zdmhost]

TASK [Install or update ZDM] ************************************************************************************************************************************************************************************************************************************************************
changed: [zdmhost]

TASK [Start ZDM service] ****************************************************************************************************************************************************************************************************************************************************************
changed: [zdmhost]

PLAY RECAP ******************************************************************************************************************************************************************************************************************************************************************************
zdmhost              : ok=8    changed=3    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Check new ZDM version

And we can check that the new ZDM tool version is 21.5.

[zdm@zdmhost ~]$ zdmcli -v
RHP_PT.ZDM21_LINUX.X64_240219.12

[zdm@zdmhost ~]$ zdmcli -build
version: 21.0.0.0.0
full version: 21.5.0.0.0
patch version: N/A
label date: 240219.12
ZDM kit build date: Sep 10 2024 21:59:18 UTC
CPAT build version: 24.6.0
[zdm@zdmhost ~]$

To wrap up…

Installing and updating ZDM cli is quite easy, and writing an ansible playbook will help automatising the installation and further update.

L’article Automatisation for Oracle ZDM installation and update est apparu en premier sur dbi Blog.