Streamlining Oracle Standby Database Patching: An Ansible Automation Blueprint

Patching Oracle databases is a critical maintenance task that ensures system security, stability, and performance. However, patching a standby database in a Data Guard configuration requires careful execution to maintain data protection and high availability. This blog post explores an Ansible playbook we've developed to automate the Oracle standby database patching process, making it more reliable, consistent, and efficient.

yaml
# File: standby_db_patching.yml
---
- name: Apply Oracle Patch on Standby Database
  hosts: "{{ hostlist }}"
  become: yes

  vars:
    oracle_home: /opt/oracle/product/19.3.0/db_1
    local_patch_dir: /tmp
    patch_dir: /oraback/patch
    patch_db_zip: p37642901_190000_Linux-x86-64.zip
    patch_opatch_zip: p6880880_190000_Linux-x86-64.zip

  tasks:
    # Tasks will be discussed in detail below

Why Automate Oracle Patching?

Manual patching processes are prone to human error, time-consuming, and often lack proper documentation. By automating the patching process with Ansible:

• We reduce human error through consistent execution
• We save significant time with parallel execution across multiple servers
• We create self-documenting operations that improve compliance and audit readiness
• We ensure all pre and post-checks are performed consistently

The Oracle Standby Database Patching Playbook

Our playbook (standby_db_patching.yml) automates the complete patching workflow for Oracle standby databases. Let's explore its key components and functionality.

Playbook Structure and Syntax

Let's break down the Ansible playbook syntax and structure:

Playbook Header

yaml
---  # YAML document start marker
- name: Apply Oracle Patch on Standby Database  # Playbook name/description
  hosts: "{{ hostlist }}"                       # Target hosts (variable)
  become: yes                                   # Enable privilege escalation

The playbook begins with a YAML document marker (---) and defines a play with a descriptive name. The hosts parameter uses a variable (hostlist), making the playbook reusable across different environments. The become: yes directive enables privilege escalation for tasks requiring root permissions.

Variables Section

yaml
  vars:  # Variables section
    oracle_home: /opt/oracle/product/19.3.0/db_1  # Oracle installation directory
    local_patch_dir: /tmp                         # Source directory for patch files
    patch_dir: /oraback/patch                     # Target directory for patch files
    patch_db_zip: p37642901_190000_Linux-x86-64.zip    # Database patch filename
    patch_opatch_zip: p6880880_190000_Linux-x86-64.zip  # OPatch utility filename

This section defines playbook variables using the vars dictionary. These variables:

• Make the playbook more maintainable by centralizing configuration
• Allow easy customization for different environments
• Can be overridden using command-line parameters or inventory variables

These variables define:

• The Oracle Home directory
• Local and remote patch file locations
• Specific patch files for the database and OPatch utility

Patching Workflow

The playbook implements a comprehensive patching workflow:

1. Environment Setup: Export Oracle environment variables
2. Preparation: Create directories and transfer patch files
3. Pre-Checks: Verify database status and configuration
4. Database Shutdown: Cancel managed recovery and shut down properly
5. OPatch Update: Backup existing OPatch and install the new version
6. Patch Application: Unzip and apply the database patch
7. Database Restart: Start the database and re-enable recovery

Let's explore each step in detail.

Step-by-Step Workflow Explanation

Initial Environment Setup

yaml
- name: Export Oracle environment variables  # Task name
  shell: |                                   # Multi-line shell command using YAML pipe syntax
    export ORACLE_HOME={{ oracle_home }}     # Variable interpolation with {{ }}
    export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
    echo "ORACLE_HOME: $ORACLE_HOME"
    echo "PATH: $PATH"
  become_user: oracle                        # Run as 'oracle' user, not root

This task uses Ansible's shell module to set up essential Oracle environment variables. Key syntax elements:

• name: Descriptive task name for readability and logging
• shell: |: YAML pipe (|) syntax for multi-line commands
• {{ oracle_home }}: Variable interpolation with double curly braces
• become_user: oracle: Execute as the Oracle software owner, not root

While Ansible is declarative in nature, Oracle administration often requires procedural shell commands like these.

Patch File Management

yaml
- name: Create patch directory           # Task name
  file:                                  # Ansible file module
    path: "{{ patch_dir }}"              # Directory path (from variables)
    state: directory                     # Ensure directory exists
    owner: oracle                        # Set owner to oracle user
    group: oinstall                      # Set group to oinstall
    mode: '0775'                         # Set permissions (rwxrwxr-x)

- name: Copy DB patch zip to patch directory  # Task name
  copy:                                       # Ansible copy module
    src: "{{ local_patch_dir }}/{{ patch_db_zip }}"  # Source file
    dest: "{{ patch_dir }}/{{ patch_db_zip }}"      # Destination path
    owner: oracle                             # Set file owner
    group: oinstall                           # Set file group

- name: Copy OPatch zip to patch directory    # Similar task for OPatch zip
  copy:
    src: "{{ local_patch_dir }}/{{ patch_opatch_zip }}"
    dest: "{{ patch_dir }}/{{ patch_opatch_zip }}"
    owner: oracle
    group: oinstall

These tasks demonstrate Ansible's declarative approach to file management:

1. File Module: The file module creates a directory with specified ownership and permissions
   • state: directory ensures the directory exists (idempotent operation)
   • Setting permissions follows Linux standards but uses string notation ('0775')
2. Copy Module: The copy module transfers files from control node to targets
   • Uses variable interpolation in both source and destination paths
   • Sets proper Oracle ownership automatically

Each task follows Ansible's YAML structure with:

• Task name
• Module name (file, copy)
• Module parameters as key-value pairs with proper indentation

characters to prevent Ansible variable interpretation

2. Result Capturing: The register directive stores command output
   • db_status_output becomes a variable containing:
     • stdout: Command standard output
     • stderr: Command standard error
     • rc: Return code
3. Special Character Handling:
   • Dollar signs in SQL commands are escaped with backslashes
   • This prevents Ansible from interpreting them as variables

By registering the output, we can use it for decision-making or debugging later in the playbook.

Stopping Database Services

yaml
- name: Cancel managed recovery and shut down database  # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    sqlplus / as sysdba <<EOF                # Connect as SYSDBA with heredoc
    alter database recover managed standby database cancel;  # Stop recovery
    shutdown immediate;                      # Shut down database
    exit                                     # Exit SQL*Plus
    EOF
  become_user: oracle                        # Run as oracle user

- name: Stop listener                        # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    lsnrctl stop                             # Stop Oracle listener
  become_user: oracle                        # Run as oracle user

These tasks demonstrate Ansible's approach to executing operational database commands:

1. Task Sequencing: Tasks run in order, ensuring proper shutdown sequence
   • First task stops managed recovery and database
   • Second task stops the listener service
   • This order is critical for proper standby database maintenance
2. Oracle Administration Commands:
   • alter database recover managed standby database cancel; - Halts Data Guard recovery
   • shutdown immediate; - Closes database and disconnects users
   • lsnrctl stop - Stops the Oracle listener process
3. User Context: Both tasks run as oracle user via become_user
   • Maintains proper permissions and environment settings
   • Avoids potential security issues from running as root

The playbook's ordered task execution ensures that database shutdown follows proper sequence - an essential feature for complex operational procedures.

OPatch Update Procedure

yaml
- name: Backup existing OPatch directory     # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    cd $ORACLE_HOME                          # Change to ORACLE_HOME directory
    mv OPatch OPatch_new                     # Rename existing OPatch directory
  become_user: oracle                        # Run as oracle user

- name: Verify OPatch zip file exists in /oraback/patch  # Task name
  shell: |                                   # Multi-line shell command with conditional
    if [ ! -f "{{ patch_dir }}/{{ patch_opatch_zip }}" ]; then
      echo "ERROR: OPatch zip file {{ patch_dir }}/{{ patch_opatch_zip }} does not exist."
      exit 1                                 # Exit with error if file missing
    fi
  become_user: oracle                        # Run as oracle user

- name: Copy OPatch zip to Oracle Home       # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    cp {{ patch_dir }}/{{ patch_opatch_zip }} $ORACLE_HOME/  # Copy zip file
  become_user: oracle                        # Run as oracle user

- name: Unzip OPatch tool                    # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    cd $ORACLE_HOME                          # Change to ORACLE_HOME directory
    unzip -o p6880880_190000_Linux-x86-64.zip  # Extract OPatch with overwrite
  become_user: oracle                        # Run as oracle user

- name: Verify OPatch version                # Task name
  shell: |                                   # Multi-line shell command
    . ~/.bash_profile                        # Source oracle user profile
    $ORACLE_HOME/OPatch/opatch version       # Check OPatch version
  become_user: oracle                        # Run as oracle user

This section demonstrates important Ansible operational patterns:

1. Error Handling Logic:
   • The second task uses shell conditionals to check file existence
   • exit 1 causes the task to fail if conditions aren't met
   • This prevents continuing with invalid preconditions
2. Task Chaining:
   • Tasks build on each other in logical sequence
   • Each task has a specific, focused purpose
   • Together they implement a complete procedure
3. Tool Verification:
   • The final task validates the installation by checking the version
   • This confirms success before proceeding with patching

The structured approach ensures each step in the OPatch update completes successfully before proceeding, minimizing the risk of incomplete operations.

Patch Application Process

yaml
- name: Unzip DB patch zip in /oraback/patch
  shell: |
    . ~/.bash_profile
    cd {{ patch_dir }}
    unzip -o {{ patch_db_zip }}
  become_user: oracle

# [Additional patch verification steps]

- name: Check patch conflicts in /oraback/patch/37642901
  shell: |
    . ~/.bash_profile
    cd {{ patch_dir }}/37642901
    opatch prereq CheckConflictAgainstOHWithDetail -ph ./ 
  become_user: oracle

- name: Apply the patch from /oraback/patch/37642901
  shell: |
    . ~/.bash_profile
    cd {{ patch_dir }}/37642901
    $ORACLE_HOME/OPatch/opatch apply -silent
  become_user: oracle

The patch application includes:

1. Extracting the patch files
2. Multiple validation checks to ensure patch directory accessibility
3. Pre-patching conflict verification
4. Silent patch application

Database Restart and Recovery

yaml
- name: Start database in mount, re-enable recovery, and start listener
  shell: |
    . ~/.bash_profile
    sqlplus / as sysdba <<EOF
    startup mount;
    alter database recover managed standby database disconnect from session;
    select sequence#, thread#, to_char(FIRST_TIME,'dd-mm-yyyy hh24:mi:ss') "SYNC TIME"
      from v$log_history
     where sequence# in (select max(sequence#) from v$log_history group by thread#);
    exit
    EOF
    lsnrctl start
  become_user: oracle

The final steps:

1. Start the database in mount mode (appropriate for standby)
2. Re-enable the managed standby recovery process
3. Verify synchronization status with primary database
4. Restart the listener service

Best Practices Implemented

This playbook incorporates several database administration best practices:

1. Comprehensive Validation: Multiple verification steps before and after patching
2. Error Handling: Checks for file existence and permissions before proceeding
3. Service Management: Proper stopping and starting of database services
4. Recovery Process: Careful handling of the managed recovery process
5. Documentation: Status recording at critical stages

Running the Playbook

To execute this playbook, use the standard Ansible playbook command with appropriate options:

bash
# Basic execution
ansible-playbook standby_db_patching.yml -e "hostlist=ora_standby_servers"

# With verbosity for debugging
ansible-playbook standby_db_patching.yml -e "hostlist=ora_standby_servers" -v

# With different inventory file
ansible-playbook -i custom_inventory.ini standby_db_patching.yml -e "hostlist=standby_db_group"

# Limiting to specific hosts
ansible-playbook standby_db_patching.yml -e "hostlist=ora_standby_servers" --limit standby01.example.com

The -e "hostlist=ora_standby_servers" option passes the target host group as a variable to the playbook.

Conclusion

Automating Oracle standby database patching with Ansible significantly improves the reliability and efficiency of database maintenance operations. This playbook provides a robust foundation that can be customized to meet specific organizational requirements.

The complete playbook is available in our GitHub repository at: https://github.com/Saravananlokesh/Ansible/tree/main/Standbydb_patching

Next Steps

To enhance this automation further, consider:

1. Adding post-patching validation tasks using Ansible's assert module
2. Implementing rollback procedures with Ansible's block/rescue/always structure
3. Integrating with monitoring and notification systems using the mail module
4. Creating variable files for different environments with Ansible Vault for sensitive data

yaml
# Example of block/rescue for error handling
- block:
    - name: Apply the patch
      shell: opatch apply -silent
  rescue:
    - name: Handle patch failure
      shell: opatch rollback -id 37642901
  always:
    - name: Notify completion status
      mail:
        subject: "Patch status for {{ inventory_hostname }}"
        to: dba-team@example.com
        body: "Patching completed with status: {{ patch_status }}"

By continually refining our automation approach, we can make database patching a more streamlined, reliable process while maintaining the high availability our systems require.

Have you automated your Oracle patching process? Share your experiences in the comments below!