Orchestrating Oracle Database Administration with Ansible and SQL*Plus

The integration of Ansible with Oracle SQLPlus represents a critical bridge between modern infrastructure-as-code (IaC) methodologies and legacy relational database management systems. While Ansible is primarily designed for configuration management and application deployment through SSH, Oracle SQLPlus serves as the primary command-line interface for database administration, query execution, and script-based maintenance. Achieving a seamless marriage between these two tools requires a deep understanding of environment variable propagation, SSH authentication protocols, and the specific nuances of the Oracle Instant Client architecture. By leveraging Ansible, administrators can move away from manual, error-prone database interventions toward a version-controlled, repeatable, and scalable automation framework. This transition allows for the precise management of Pluggable Databases (PDBs), DataGuard configurations, and complex user grant operations across heterogeneous database environments.

Technical Prerequisites and Environmental Foundation

Before deploying Ansible roles to manage SQL*Plus, the control node and target environments must meet specific software and system requirements to ensure stable communication and package installation.

The software requirements for the automation environment are as follows:

• Ansible: Version 1.9 or higher is required. This can be installed via the Python package manager using the command sudo pip install ansible==1.9.2.
• Vagrant: Version 1.7 or higher is necessary for those utilizing virtualized testing environments.
• sshpass: This package is mandatory if the Ansible configuration relies on SSH authentication via passwords rather than public key exchange. On Ubuntu or Debian systems, it is installed using sudo apt-get install sshpass.
• Virtualbox: Required for hosting the virtual machines managed by Vagrant.

The operational environment for the SQL*Plus installation role supports a variety of Linux distributions:

• Debian: Versions 7 and 8.
• Ubuntu: Precise and Trusty releases.
• Enterprise Linux (EL): Versions 6 and 7.

The technical necessity of sshpass arises from Ansible's reliance on the SSH protocol. While key-based authentication is the gold standard, certain legacy environments or initial bootstrapping phases require password-based entry. sshpass allows Ansible to provide the password to the SSH connection in a non-interactive manner, which is critical for the non-interactive nature of CI/CD pipelines.

Advanced Installation and Configuration of SQL*Plus via Ansible

The installation of SQL*Plus on a remote target is not a simple package manager call, as Oracle binaries are typically distributed as proprietary RPMs that must be manually sourced and placed on the target system.

The following table details the primary configuration parameters required for the SQL*Plus Ansible role:

The deployment process follows a strict sequence. First, the administrator must download the required RPMs from the official Oracle website. Once acquired, these files must be transferred to the remote host. By default, Ansible looks for these files in the /srv/files/ directory. If the files are not present in this location, the installation will fail.

The technical impact of this requirement is that the administrator must treat the RPM files as artifacts. Using the sqlplus_rpm_download_directory parameter allows the user to specify where these artifacts reside. This ensures that the yum or rpm command can find the local file to perform the installation without needing an external internet connection from the target database server, which is often isolated in a secure network zone.

Establishing Secure Connectivity and Control Node Setup

To execute SQL*Plus commands, Ansible must first establish a secure, passwordless communication channel with the target database servers. This is achieved through the generation and distribution of RSA keys.

The following steps describe the initial setup on the central Ansible control host:

1. Generate a secure RSA key pair using the command /usr/bin/ssh-keygen -t rsa. It is recommended to use a non-default filename, such as /home/.ssh/ansible_key, to avoid overwriting existing identity files. During this process, the passphrase should be left empty to allow for automated, non-interactive logins.
2. Configure the authorized keys file by appending the public key: cat ~/.ssh/ansible_key.pub >> ~/.ssh/authorized_keys.
3. Set strict directory and file permissions to comply with SSH security standards: chmod 600 ~/.ssh/authorized_keys and chmod 700 ~/.ssh.
4. Distribute the key to the target server: ssh-copy-id -i ~/.ssh/ansible_key dbserver1.mydomain.com. If ssh-copy-id is unavailable, the administrator must manually append the content of ansible_key.pub to the ~/.ssh/authorized_keys file on the target server as the oracle user.
5. Verify the connection with a test command: ssh -i ~/.ssh/ansible_key dbserver1.mydomain.com hostname.

The directory structure on the control host should be organized to keep configuration and files separate:

• mkdir -p ~/ansible/files
• chmod 700 ~/ansible
• chmod 700 ~/ansible/files

This strict permissioning (700 for directories and 600 for keys) is not merely a suggestion but a requirement of the SSH daemon. If the .ssh directory or the authorized_keys file has permissions that are too open (e.g., group-writable), the SSH server will reject the key for security reasons, resulting in a failed Ansible connection.

Inventory Management and Variable Mapping

The Ansible inventory file defines the targets and assigns specific Oracle-related variables to each host, which is essential for multi-tenant or multi-instance environments.

The inventory.cfg file configures the environment:

ini inventory = ~/ansible/inventory.ini private_key_file = ~/.ssh/ansible_key interpreter_python = auto_legacy_silent

The inventory.ini file maps the servers to their respective Oracle System Identifiers (SIDs) and Pluggable Database (PDB) names:

```ini
[test]
dbserver1.mydomain.com oraclesid=CMYDB01 pdbname=mydb01

[dev]
dbserver2.mydomain.com oraclesid=MYDB02
dbserver2.mydomain.com oraclesid=MYDB03
```

By defining oracle_sid and pdb_name within the inventory, these values become available as Ansible variables. This allows the administrator to write a single generic playbook that can be applied to multiple servers, where the specific database instance targeted is dynamically determined by the host's assigned variables.

Implementing SQL*Plus Execution via Shell Modules

Executing SQLPlus commands through Ansible requires the use of the shell or command modules. Because SQLPlus is an interactive tool, it must be fed commands via standard input (stdin) or through a script file.

Script-Based Execution

The most reliable method for complex operations, such as granting user permissions, involves copying a shell script to the remote server and then executing it.

Example playbook for user grants:

yaml - name: myUser grant hosts: all tasks: - name: Copy script file to remote server copy: src: myUser_grant.sh dest: /tmp mode: '700' - name: Run script on remote server shell: /tmp/myUser_grant.sh

In this workflow, the copy module ensures the script is present on the target with executable permissions (700). The subsequent shell module triggers the execution. This method is preferred for complex SQL logic as it avoids the escaping issues associated with multi-line strings in YAML.

Inline Execution and Here-Documents

For quick queries, administrators can use "Here-Documents" (<<EOF) to pass commands directly to SQL*Plus. This is often used to retrieve specific database values, such as the RMAN SCN.

Example of inline execution:

yaml - hosts: source tasks: - include_vars: sql.yml - shell: | . /u05/oracle/VISEBS/12.1.0/VISEBS_sal-devtl10.env val='v$archived_log' R=`sqlplus '/as sysdba' <<-EOF set numw 1 lines 80 pages 0; select max(NEXT_CHANGE#) "RMAN_SCN" from $val where BACKUP_COUNT > 0; exit; EOF` echo $R register: cmd - debug: msg: "{{cmd}}"

In the example above, the command begins by sourcing the environment file (. /u05/oracle/VISEBS/...). This is critical because SQLPlus requires the ORACLE_HOME and PATH variables to be correctly set to locate the binary and connect to the instance. The use of <<-EOF allows the shell to pass the SQL commands into the SQLPlus prompt until the exit; command is reached.

Advanced Error Handling and Result Validation

A significant challenge in automating SQL*Plus is that the shell exit code (rc) of the sqlplus command often indicates whether the binary started, not whether the SQL command inside it succeeded. An Oracle error (e.g., ORA-00942: table or view does not exist) does not necessarily trigger a non-zero exit code from the shell.

To ensure factual accuracy in automation, the failed_when attribute must be used to inspect the standard output (stdout) for Oracle-specific error patterns.

Correct error handling implementation:

yaml - name: Execute query shell: echo "select count(*) from all_users;" | sqlplus / as sysdba register: number_of_users failed_when: "number_of_users.rc != 0 or 'ORA-' in number_of_users.stdout"

The logic here is two-fold:
1. number_of_users.rc != 0: This catches catastrophic failures, such as the sqlplus binary not being found or the server being unreachable.
2. 'ORA-' in number_of_users.stdout: This catches Oracle-specific errors. Since Oracle returns errors as text within the output rather than as system exit codes, searching for the string ORA- is the only reliable way to determine if a database operation failed.

Strategic Alternatives and Local Execution

While the standard approach involves SSHing into a target VM, there are scenarios where this is not feasible or optimal.

Local Execution on Control Host

If the Ansible control host has the Oracle Instant Client installed and a correctly configured tnsnames.ora file, it can "shell out" locally to connect to the remote database. This eliminates the need to SSH into the target VM for the purpose of database interaction, though it requires the control host to have network connectivity to the database listener port (typically 1521).

Python-Based Integration

A more robust alternative to calling the sqlplus binary is the development of an Ansible module using the cx_Oracle Python library. This approach allows for native Python handling of database cursors and results, removing the need to parse text output from a shell command and providing a more "Ansible-native" experience.

Testing and Validation Frameworks

To ensure the stability of the SQL*Plus installation and execution, a rigorous testing framework is recommended.

Docker-Based Testing

For rapid iteration and testing of roles, the sath89/oracle-12c Docker image can be utilized to simulate a database environment.

Steps to deploy the test container:

1. Pull the image: sudo docker pull sath89/oracle-12c:latest
2. Create a persistent data directory: sudo mkdir -p /var/lib/oracledb/data
3. Run the container: sudo docker run --name oracle-db -d -p 8080:8080 -p 1521:1521 -v /var/lib/oracledb/data:/u01/app/oracle -e DBCA_TOTAL_MEMORY=1024 sath89/oracle-12c
4. Test the connection locally: export ORACLE_SID=xe.oracle.docker followed by sqlplus -L sys/oracle@<DB-SERVER-IP-ADDRESS>/xe.oracle.docker as sysdba.

Integration Testing with Test Kitchen

The professional standard for verifying Ansible roles is the use of Test Kitchen. The .kitchen.yml file defines the test suites and environment configurations. All integration tests are stored in the ./test/integration/ directory, allowing the developer to verify that the role correctly installs the RPMs and that the sqlplus binary is functional across different operating systems before deploying to production.

Conclusion

The orchestration of Oracle SQL*Plus through Ansible transforms database administration from a series of manual, isolated tasks into a streamlined, automated pipeline. By adhering to strict SSH security protocols, utilizing precise RPM management, and implementing sophisticated error handling via failed_when logic, administrators can ensure that their database configurations are consistent and reproducible. The transition from simple shell execution to the use of structured scripts and potential Python-based modules like cx_Oracle represents the maturity curve of database automation. Ultimately, the ability to programmatically manage SIDs, PDBs, and user grants through Ansible not only reduces the risk of human error but also provides a comprehensive audit trail of all changes made to the database environment.

Sources

1. ansible-sqlplus GitHub Repository
2. Andrew Fraser DBA - Ansible Oracle SQL
3. Ansible Forum - sqlplus discussion
4. DBI Services - Error Handling in Ansible
5. Google Groups - Ansible Project Discussion