Oracle DBA task automation through python and ansible

Question : oracledb basic syntax for oracle dba

The oracledb driver (the modern successor to cx_Oracle) connects Python to Oracle Databases. For DBAs, it supports two modes: Thin mode (default, requires no Oracle Client libraries) and Thick mode (requires Oracle Client, needed for older databases or advanced features)

Here is the basic syntax and essential snippets for Oracle DBAs.

Installation

bash

pip install oracledb

1. Basic Connection (Thin Mode)

python

import oracledb

# Define connection parameters
username = "system"
password = "manager"
dsn = "localhost:1521/free"  # host:port/service_name

# Connect to the database
connection = oracledb.connect(
    user=username,
    password=password,
    dsn=dsn
)

# Verify connection
print("Database version:", connection.version)

# Always close connections when done
connection.close()

2. Connecting as SYSDBA / SYSOPER

DBAs frequently need administrative privileges. You must specify the mode parameter. 

python

import oracledb

connection = oracledb.connect(
    user="sys",
    password="sys_password",
    dsn="localhost:1521/free",
    mode=oracledb.AUTH_MODE_SYSDBA  # Use AUTH_MODE_SYSOPER for SYSOPER
)

3. Running SQL Queries (Fetching Performance Data) 

Use a cursor to execute queries against data dictionary views (e.g., V$SESSION, V$INSTANCE). 

python

import oracledb

with oracledb.connect(user="sys", password="manager", dsn="localhost:1521/free", mode=oracledb.AUTH_MODE_SYSDBA) as connection:
    with connection.cursor() as cursor:
        # Example: Fetch active sessions
        sql = "SELECT username, status, osuser FROM v$session WHERE status = 'ACTIVE'"
        cursor.execute(sql)
        
        # Fetch and print results
        for row in cursor:
            print(f"User: {row[0]}, Status: {row[1]}, OS User: {row[2]}")

4. Executing DDL and Admin Commands

You can run statements like ALTER SYSTEM, CREATE USER, or GRANT.
Note: DDL statements commit automatically in Oracle. 

python

import oracledb

with oracledb.connect(user="sys", password="manager", dsn="localhost:1521/free", mode=oracledb.AUTH_MODE_SYSDBA) as connection:
    with connection.cursor() as cursor:
        # Example: Kill a specific session
        sid = 123
        serial = 4567
        sql = f"ALTER SYSTEM KILL SESSION '{sid},{serial}' IMMEDIATE"
        
        try:
            cursor.execute(sql)
            print("Session killed successfully.")
        except oracledb.DatabaseError as e:
            error, = e.args
            print(f"Error Code: {error.code}, Message: {error.message}")

5. Executing PL/SQL Blocks

Useful for compiling schemas, running DBA packages (like DBMS_STATS), or gathering performance metrics.

python

import oracledb

with oracledb.connect(user="sys", password="manager", dsn="host:1521/free", mode=oracledb.AUTH_MODE_SYSDBA) as connection:
    with connection.cursor() as cursor:
        # Example: Gather statistics for a specific schema
        schema_name = "SYSTEM"
        
        # Using a PL/SQL block with a bind variable
        plsql = """
        BEGIN
            DBMS_STATS.GATHER_SCHEMA_STATS(ownname => :schema);
        END;
        """
        
        cursor.execute(plsql, schema=schema_name)
        print(f"Statistics gathered for schema: {schema_name}");

Parsing OS Command Output (Subprocess)

DBAs regularly combine database scripts with local OS actions. Use Python's built-in subprocess module to check server metrics like disk usage (df -h). 

python

import subprocess

# Run a shell command and capture the output string
result = subprocess.run(["df", "-h", "/"], capture_output=True, text=True)
print(result.stdout)

Question: How do you connect Python to an Oracle Database to run administrative health checks, and what basic steps must your code follow?

Answer:
To interact with Oracle Database via Python, you use the oracledb driver. The communication follows a strict object-oriented lifecycle: 

1. Establish a Connection: Initialize a session using credentials and a Data Source Name (DSN).
2. Create a Cursor: Open a cursor object to serve as the execution context for SQL statements.
3. Execute SQL Command: Run administrative queries (e.g., fetching system metrics from V$ views).
4. Fetch and Process Data: Extract row data using methods like fetchall() or an iterator loop.
5. Resource Cleanup: Close the cursor and database connection to prevent lingering process locks. 

 Required Python Basic Syntax

This script connects as a DBA to extract the active database version and current status. It implements best-practice Python structures, including Exception Handling (try-except) and Context Managers (with) to safely manage database states. 

python

import oracledb

# Define connection credentials and targets
DB_USER = "sys"
DB_PASSWORD = "manager"
# Syntax format for DSN: "hostname:port/service_name"
DB_DSN = "localhost:1521/free" 

try:
    # 1. Open connection safely using a context manager
    # Note: Connecting as SYSDBA requires specifying the 'mode'
    with oracledb.connect(
        user=DB_USER, 
        password=DB_PASSWORD, 
        dsn=DB_DSN, 
        mode=oracledb.SYSDBA
    ) as connection:
        
        print("Successfully connected to the Oracle Database Instance.")
        
        # 2. Allocate an isolated database cursor
        with connection.cursor() as cursor:
            
            # 3. Define an administrative DBA task query
            # Checking database version information
            query = "SELECT banner FROM v$version"
            
            # 4. Execute statement inside the engine
            cursor.execute(query)
            
            # 5. Fetch structural dataset records
            records = cursor.fetchall()
            
            print("\n--- System Version Information ---")
            for row in records:
                # 'row' is returned as a data tuple
                print(row[0])
                
except oracledb.DatabaseError as error:
    # Trap and output database failure conditions safely
    print(f"Oracle Database failure occurred: {error}")
    
except Exception as general_error:
    # Trap system or network layer errors
    print(f"Application environment error: {general_error}")

# Context managers automatically invoke '.close()' on connection and cursor items
print("\n Resources closed out successfully.")

 Core Technical Concepts for DBAs

When explaining this code during an interview, be sure to highlight these structural components:

• oracledb.connect(): Instantiates your session. For primary container instance work, explicit assignment parameters (host, port, service_name) can be declared individually as alternative input parameters. 
• mode=oracledb.SYSDBA: Vital for DBA operations. It escalates privileges so you can query performance and structural dictionary boundaries (like V$VERSION, V$INSTANCE, or DBA_DATA_FILES). 
• The with Statement: A standard Python context manager. It guarantees that even if a query crashes or encounters a semantic error halfway through, it gracefully handles connection teardown hooks automatically. 

Question:How do you check for tablespace usage space alerts in an Oracle Database, and which data dictionary views would you use to prevent a production application outage?

Answer:

To monitor tablespace capacity, a DBA should query the DBA_TABLESPACE_USAGE_METRICS view. This view calculates space utilization dynamically by taking into account data files that have AUTOEXTEND enabled. 

• Critical Metrics: You must calculate the USED_PERCENT. Production alerts should trigger a warning at 85% and a critical alarm at 95% capacity.
• Alternative Views: If specific block breakdowns or physical file limits are needed, you can join DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_TABLESPACES.
• Actionable Fixes: If a tablespace hits 99% utilization, immediate remediation includes adding a new datafile, resizing an existing datafile with ALTER DATABASE DATAFILE... RESIZE, or verifying that AUTOEXTEND hasn't hit its OS file system limit. 

---

Part 2: Python Script for Oracle DBA Task

This script automates the task described above. It connects to the database using the modern Thin Mode of the python-oracledb Driver and lists tablespaces exceeding a configurable threshold. 

python

import os
import oracledb

def get_db_connection():
    """
    Establishes a connection to the Oracle Database using environment variables.
    Utilizes the modern thin driver mode (no Instant Client required).
    """
    # Fetch database credentials from environment variables for security
    db_user = os.environ.get("DB_USER", "system")
    db_password = os.environ.get("DB_PASSWORD", "manager")
    db_dsn = os.environ.get("DB_DSN", "localhost:1521/free")

    if not db_password:
        raise ValueError("Database password must be set via DB_PASSWORD environment variable.")

    # Establish standalone thin connection
    connection = oracledb.connect(
        user=db_user,
        password=db_password,
        dsn=db_dsn
    )
    return connection

def check_tablespace_usage(threshold_percent=85.0):
    """
    Queries the database for tablespaces exceeding the specified usage threshold percentage.
    """
    query = """
        SELECT tablespace_name, 
               ROUND(used_space * 8192 / 1024 / 1024, 2) as used_mb,
               ROUND(tablespace_size * 8192 / 1024 / 1024, 2) as max_mb,
               ROUND(used_percent, 2) as used_pct
        FROM dba_tablespace_usage_metrics
        WHERE used_percent >= :threshold
        ORDER BY used_percent DESC
    """
    
    connection = None
    alerts = []
    
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # Bind parameters to guard against SQL injection
            cursor.execute(query, threshold=threshold_percent)
            
            # Fetch all matching problem tablespaces
            for row in cursor.fetchall():
                alerts.append({
                    "tablespace_name": row[0],
                    "used_mb": row[1],
                    "max_mb": row[2],
                    "used_percent": row[3]
                })
    except oracledb.DatabaseError as e:
        print(f"Oracle Database Error occurred: {e}")
        raise
    finally:
        if connection:
            connection.close()
            
    return alerts

if __name__ == "__main__":
    # Example local script execution configuration
    os.environ["DB_PASSWORD"] = "YourSecurePasswordHere" 
    
    print("Scanning Oracle Database for high-utilization tablespaces...")
    problem_spaces = check_tablespace_usage(threshold_percent=80.0)
    
    if problem_spaces:
        print("\n WARNING: The following tablespaces require immediate attention:")
        for space in problem_spaces:
            print(f"- {space['tablespace_name']}: {space['used_percent']}% used "
                  f"({space['used_mb']}MB / {space['max_mb']}MB)")
    else:
        print("\n All database tablespaces are operating within healthy capacity boundaries.")

Question: How do you connect Python to an Oracle Database, what library do you use, and how would you implement a lightweight, production-grade monitoring script that handles exceptions safely?

Answer:

1. Library Selection: Use the modern oracledb driver. It runs in an architecture called Thin mode by default, meaning it does not require heavy Oracle Instant Client installations. [
2. Connection Strategy: Use context managers (with blocks) for both the connection and the execution cursor. This guarantees that database connections and resources are safely closed even if runtime exceptions or system failures occur. [
3. Monitoring Strategy: Query diagnostic virtual views like V$SESSION, V$INSTANCE, or V$SYSSTAT. For a basic health check, a query like SELECT status FROM v$instance; verifies whether the instance is up and accepting traffic. 
4. Error Handling: Catch oracledb.Error to intercept database-specific network drops, invalid credentials, or timeout constraints while isolating systemic script issues. 

---

Part 2: Production Monitoring Script

Save this script as db_monitor.py. It establishes a connection, executes a heartbeat check, and safely closes out resources. 

python

import os
import oracledb

def check_oracle_health(username, password, dsn):
    """
    Connects to the Oracle Database and validates its runtime status.
    Returns a dictionary indicating health metrics.
    """
    health_status = {
        "status": "DOWN",
        "database_status": "UNKNOWN",
        "error_message": None
    }
    
    try:
        # Thin mode initialization (No thick client libraries required)
        # For legacy thick clients, you would call oracledb.init_oracle_client() here
        with oracledb.connect(user=username, password=password, dsn=dsn) as connection:
            with connection.cursor() as cursor:
                # Primary heartbeat query to check instance status
                cursor.execute("SELECT status FROM v$instance")
                row = cursor.fetchone()
                
                if row:
                    health_status["status"] = "UP"
                    health_status["database_status"] = row[0]  # E.g., 'OPEN'
                    
    except oracledb.Error as db_error:
        # Catch specific Oracle engine errors (e.g., ORA-12154, ORA-01017)
        health_status["status"] = "DOWN"
        health_status["error_message"] = str(db_error)
    except Exception as general_error:
        # Catch unexpected infrastructure issues
        health_status["status"] = "DOWN"
        health_status["error_message"] = f"Unexpected system error: {general_error}"
        
    return health_status

if __name__ == "__main__":
    # Real-world usage simulation using environment variables or fallbacks
    DB_USER = os.getenv("DB_USER", "system")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "manager")
    DB_DSN = os.getenv("DB_DSN", "localhost:1521/free") # Easy Connect string
    
    print("Initiating database health validation diagnostics...")
    report = check_oracle_health(DB_USER, DB_PASSWORD, DB_DSN)
    print(f"Diagnostic Results: {report}")

Part 3: Automated Unit Test Case

Save this file as test_db_monitor.py. This script uses Python's built-in unittest.mock package to validate your monitoring code without establishing real network sockets or depending on a live Oracle database instance. 

python

import unittest
from unittest.mock import patch, MagicMock
import oracledb
from db_monitor import check_oracle_health

class TestOracleMonitoringScript(unittest.TestCase):

    @patch("oracledb.connect")
    def test_health_check_success(self, mock_connect):
        """Test the monitoring script when the database is fully functional."""
        # Arrange: Setup mock hierarchy for connection and cursor context managers
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        
        # Configure cursor to return standard 'OPEN' state row
        mock_cursor.fetchone.return_value = ["OPEN"]
        
        # Chain context managers: connect().__enter__() returns connection object
        mock_connect.return_value.__enter__.return_value = mock_conn
        # connection.cursor().__enter__() returns cursor object
        mock_conn.cursor.return_value.__enter__.return_value = mock_cursor

        # Act: Run function
        result = check_oracle_health("test_user", "password", "localhost:1521/ORCL")

        # Assert: Validate success flags
        self.assertEqual(result["status"], "UP")
        self.assertEqual(result["database_status"], "OPEN")
        self.assertIsNone(result["error_message"])
        mock_cursor.execute.assert_called_once_with("SELECT status FROM v$instance")

    @patch("oracledb.connect")
    def test_health_check_database_down(self, mock_connect):
        """Test script resilience when Oracle rejects connections (e.g., wrong password)."""
        # Arrange: Raise an official oracledb driver exception on connect
        error_message = "ORA-01017: invalid username/password; logon denied"
        mock_connect.side_effect = oracledb.Error(error_message)

        # Act: Run function
        result = check_oracle_health("wrong_user", "bad_pass", "localhost:1521/ORCL")

        # Assert: Validate failure reporting
        self.assertEqual(result["status"], "DOWN")
        self.assertEqual(result["database_status"], "UNKNOWN")
        self.assertIn("ORA-01017", result["error_message"])

if __name__ == "__main__":
    unittest.main()

 Execution Instructions

1. Install dependencies: Run pip install oracledb.
2. Execute production script: Run python db_monitor.py.
3. Execute testing suite: Run python test_db_monitor.py to see passing assertions. 

Question :How to connect with oracle database and write python script for monitoring with example and test case

 To connect a Python script to an Oracle Database, you must use the official, open-source python-oracledb driver, which has completely replaced the legacy cx_Oracle library. The modern driver operates in a lightweight "Thin mode" by default, meaning you do not need to install Oracle Instant Client libraries to establish a database connection. 

Below is a complete implementation guide containing the required environment setup, a structured health-monitoring script, and an automated test case using mock structures.

---

1. Prerequisites and Installation

Install the official Oracle driver using pip: 

bash

pip install oracledb

2. The Monitoring Script (monitor.py)

This production-ready monitoring script tracks essential database metrics: Database Availability (Heartbeat Check) and Tablespace Usage. It uses standard oracledb parameters to securely fetch metrics. 

python

import os
import sys
import oracledb

def get_db_connection():
    """Establishes standalone connection using environment variables."""
    # Fetch connection credentials securely from the system environment
    db_user = os.environ.get("DB_USER", "system")
    db_password = os.environ.get("DB_PASSWORD", "manager")
    db_dsn = os.environ.get("DB_DSN", "localhost:1521/free")
    
    try:
        # Connects in Thin mode directly to Oracle Database
        connection = oracledb.connect(user=db_user, password=db_password, dsn=db_dsn)
        return connection
    except oracledb.DatabaseError as e:
        print(f"CRITICAL: Failed to connect to database. Error: {e}")
        sys.exit(1)

def monitor_heartbeat(cursor):
    """Verifies that the database is active and accepting basic queries."""
    try:
        cursor.execute("SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL")
        db_time = cursor.fetchone()[0]
        print(f"[OK] Heartbeat Status: Database is up. Current Server Time: {db_time}")
        return True
    except oracledb.DatabaseError as e:
        print(f"[CRITICAL] Heartbeat Status: Failed query execution. Error: {e}")
        return False

def monitor_tablespaces(cursor, threshold_pct=85):
    """Checks tablespace allocation and flags paths exceeding the safety threshold."""
    query = """
    SELECT 
        df.tablespace_name,
        ROUND(((df.total_space - NVL(fs.free_space, 0)) / df.total_space) * 100, 2) AS used_percent
    FROM 
        (SELECT tablespace_name, SUM(bytes) AS total_space FROM dba_data_files GROUP BY tablespace_name) df
    LEFT JOIN 
        (SELECT tablespace_name, SUM(bytes) AS free_space FROM dba_free_space GROUP BY tablespace_name) fs
    ON df.tablespace_name = fs.tablespace_name
    """
    try:
        cursor.execute(query)
        issues_found = False
        print("\n--- Tablespace Storage Status ---")
        
        for row in cursor.fetchall():
            ts_name, used_pct = row[0], row[1]
            if used_pct >= threshold_pct:
                print(f"[ALERT] Tablespace '{ts_name}' is critically high: {used_pct}% used! (Threshold: {threshold_pct}%)")
                issues_found = True
            else:
                print(f"[OK] Tablespace '{ts_name}': {used_pct}% used.")
                
        return not issues_found
    except oracledb.DatabaseError as e:
        print(f"[ERROR] Failed to query tablespace data. Error: {e}")
        return False

if __name__ == "__main__":
    # Ensure automated cleanup using context managers
    with get_db_connection() as conn:
        with conn.cursor() as cur:
            heartbeat_ok = monitor_heartbeat(cur)
            storage_ok = monitor_tablespaces(cur, threshold_pct=80)
            
    if not (heartbeat_ok and storage_ok):
        print("\nResult: Monitoring script finished with alert conditions.")
        sys.exit(1)
    else:
        print("\nResult: All core health checks passed successfully.")
        sys.exit(0)

3. Automated Test Case (test_monitor.py)

To isolate and test monitoring logic without altering production database configurations, utilize Python's standard unittest.mock framework to simulate API cursors and query results. 

python

import unittest
from unittest.mock import MagicMock, patch
import oracledb
from monitor import monitor_heartbeat, monitor_tablespaces

class TestOracleMonitoring(unittest.TestCase):

    def setUp(self):
        """Build mock database objects before each test execution step."""
        self.mock_cursor = MagicMock()

    def test_monitor_heartbeat_success(self):
        """Ensure correct parsing and True return on a working connection."""
        self.mock_cursor.fetchone.return_value = ["2026-06-19 14:20:00"]
        
        result = monitor_heartbeat(self.mock_cursor)
        
        self.assertTrue(result)
        self.mock_cursor.execute.assert_called_once_with(
            "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL"
        )

    def test_monitor_heartbeat_failure(self):
        """Ensure exceptions are properly intercepted and return False."""
        self.mock_cursor.execute.side_effect = oracledb.DatabaseError("ORA-00942: table or view does not exist")
        
        result = monitor_heartbeat(self.mock_cursor)
        
        self.assertFalse(result)

    def test_monitor_tablespaces_under_threshold(self):
        """Confirm valid paths pass successfully under structural safety parameters."""
        # Simulating data: USERS tablespace at 45% capacity, SYSTEM at 70% capacity
        self.mock_cursor.fetchall.return_value = [("USERS", 45.00), ("SYSTEM", 70.00)]
        
        result = monitor_tablespaces(self.mock_cursor, threshold_pct=80)
        
        self.assertTrue(result)

    def test_monitor_tablespaces_over_threshold_alert(self):
        """Verify that breaching the safety ceiling yields an appropriate warning flag."""
        # Simulating data: DATA_TS tablespace at 88% capacity (Breaches 80% ceiling)
        self.mock_cursor.fetchall.return_value = [("USERS", 40.00), ("DATA_TS", 88.00)]
        
        result = monitor_tablespaces(self.mock_cursor, threshold_pct=80)
        
        self.assertFalse(result, "Expected False because a tablespace exceeded the limit.")

if __name__ == "__main__":
    unittest.main()

Running the Test Case 

Execute the test file in your terminal workspace to confirm that your logic functions flawlessly:

bash

python -m unittest test_monitor.py



Question : oracle dba patching steps through python script

As an Oracle DBA, you can use Python's built-in libraries like subprocess to execute operating system commands (like opatch and datapatch) and oracledb (or cx_Oracle) to query the database. [1, 2, 3]
Here is a simple, reusable Python template to automate standard database pre/post-patching steps:
1. Execute OS Commands (subprocess)
Use this for pre-checks and applying binaries. 
python
import subprocess

def run_os_command(command):
    try:
        # Run the command and capture output
        result = subprocess.run(command, shell=True, check=True, text=True, capture_output=True)
        print(f"SUCCESS: {command}\n{result.stdout}")
    except subprocess.CalledProcessError as e:
        print(f"ERROR executing {command}: {e.stderr}")

# Example: Check OPatch version and current patches
run_os_command("$ORACLE_HOME/OPatch/opatch lspatches")
2. Connect to Database (oracledb / cx_Oracle)
Use this for checking invalid objects, the registry, and running datapatch. 
python
import oracledb

# Connect to the DB (Using system/sysdba for patching operations)
connection = oracledb.connect(
    user="sys", 
    password="YourPassword", 
    dsn="localhost:1521/ORCL", 
    mode=oracledb.SYSDBA
)

cursor = connection.cursor()

try:
    # Check for pre-patch invalid objects
    query = "SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID'"
    cursor.execute(query)
    
    print("Invalid Objects before patching:")
    for row in cursor.fetchall():
        print(row)

finally:
    cursor.close()
    connection.close()
3. Apply Datapatch 
To execute datapatch and apply data dictionary updates after the binaries are patched:
python
def run_datapatch():
    # Datapatch must be run from ORACLE_HOME/OPatch
    datapatch_cmd = "cd $ORACLE_HOME/OPatch && ./datapatch -verbose"
    run_os_command(datapatch_cmd)

run_datapatch()
or

To automate Oracle Database patching, DBAs typically combine Python's subprocess module (for binary patching via OPatch) 
and the oracledb library (for post-patch dictionary updates via datapatch). 
1. Execute OS Commands (OPatch)
To run binary patches (opatch apply) or check inventory status (opatch lspatches), 
use the subprocess module. It is safer and more capable than older OS module methods. 
python
import subprocess

def run_opatch_command(command_args):
    try:
        # Run the command and wait for it to finish
        result = subprocess.run(
            command_args, 
            capture_output=True, 
            text=True, 
            check=True
        )
        print("STDOUT:\n", result.stdout)
        
    except subprocess.CalledProcessError as e:
        print(f"Command failed with exit code {e.returncode}")
        print("STDERR:\n", e.stderr)

# Example 1: Check currently applied patches
run_opatch_command(["/u01/app/oracle/product/19c/dbhome_1/OPatch/opatch", "lspatches"])

# Example 2: Apply a patch (use absolute paths)
# run_opatch_command(["/u01/app/oracle/product/19c/dbhome_1/OPatch/opatch", "apply", "/stage/patches/12345678"])
2. Connect to Database (datapatch)
To finalize patching by updating the data dictionary, you must restart the database 
in UPGRADE mode and run datapatch. While datapatch is executed from the shell,
 you can use Python's oracledb library to verify the patch registry before and after patching. 
python
import oracledb

# Connect to the database as SYSDBA
try:
    connection = oracledb.connect(
        user="sys", 
        password="YourAdminPassword", 
        dsn="localhost:1521/ORCL", 
        mode=oracledb.AUTH_MODE_SYSDBA
    )
    cursor = connection.cursor()

    # Query the patch registry
    query = """
    SELECT action_time, status, patch_id, version 
    FROM sys.dba_registry_sqlpatch 
    ORDER BY action_time DESC
    """
    
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    cursor.close()
    connection.close()

except Exception as e:
    print(f"Database connection failed: {e}")


Question : patching through python and ansible script
Devops Ansible Helps for Oracle DBA’s | Oracledbwr

Automating Oracle DBA patching involves combining Ansible Playbooks 
(written in YAML) for server orchestration with Python scripts 
for running interactive OPatch commands or data verification. 
Ansible requires Python to be installed on both the control machine and 
the target database servers to execute its tasks. 
Below is the foundational syntax for both Python and Ansible tailored to standard Oracle patching
 steps. 
1. Pre-Patching: Free Space Check (Python) 
Before running Ansible, Python's subprocess module can check if the filesystem 
has enough space to hold the unzipped Oracle patch. 
python
import subprocess
import shutil

def check_disk_space(path, required_gb=10):
    # Total, used, and free space in bytes
    total, used, free = shutil.disk_usage(path)
    free_gb = free / (1024 ** 3)
    
    if free_gb < required_gb:
        print(f"Error: Only {free_gb:.2f} GB free. Requires {required_gb} GB.")
        return False
    print(f"Success: {free_gb:.2f} GB free.")
    return True

# Validate the staging mount point
check_disk_space("/u01/stage")
2. File Transfer & Unzipping (Ansible) 
This playbook targets database servers as the oracle OS user to create a stage directory, 
push the patch zip, and unzip it. 
yaml
---
- name: Oracle Patch Stage Preparation
  hosts: db_servers
  become: true
  become_user: oracle
  vars:
    patch_stage: "/u01/stage/36123456"
    patch_zip: "p36123456_190000_Linux-x86-64.zip"

  tasks:
    - name: Create patch directory
      ansible.builtin.file:
        path: "{{ patch_stage }}"
        state: directory
        mode: '0755'

    - name: Copy patch zip file to server
      ansible.builtin.copy:
        src: "/local/repo/{{ patch_zip }}"
        dest: "{{ patch_stage }}/{{ patch_zip }}"

    - name: Extract the Oracle patch
      ansible.builtin.unarchive:
        src: "{{ patch_stage }}/{{ patch_zip }}"
        dest: "{{ patch_stage }}"
        remote_src: yes
3. Executing Binary Patches: OPatch (Ansible) 
To apply the binary patch, you must shut down the Oracle database and listener,
 invoke opatch apply, and restart the services. 
yaml
    - name: Stop Oracle Database
      ansible.builtin.shell: |
        export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
        export ORACLE_SID=orcl
        $ORACLE_HOME/bin/srvctl stop database -d {{ oracle_sid }}

    - name: Apply OPatch Binary Patch
      ansible.builtin.shell: |
        export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
        export PATH=$ORACLE_HOME/OPatch:$PATH
        opatch apply -silent -ocm_rf /u01/stage/ocm.rsp
      args:
        chdir: "{{ patch_stage }}/36123456"

    - name: Start Oracle Database
      ansible.builtin.shell: |
        export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
        $ORACLE_HOME/bin/srvctl start database -d {{ oracle_sid }}
4. Post-Patching: Datapatch and Verification (Python) 
Once the binaries are updated and the database is open, a python script utilizing 
the official oracledb driver can verify that the dictionary views reflect the new patch bundle. 
python
import oracledb

def verify_database_patches():
    # Connect using 'thin' mode (default in modern python-oracledb)
    connection = oracledb.connect(
        user="sys",
        password="YourSecureSysPassword",
        dsn="db-server-hostname:1521/orcl",
        mode=oracledb.AUTH_MODE_SYSDBA
    )
    
    cursor = connection.cursor()
    # Query the registry to check applied Release Updates (RU)
    query = "SELECT patch_id, status, description FROM dba_registry_sqlpatch"
    cursor.execute(query)
    
    print("--- Applied SQL Patches ---")
    for row in cursor:
        print(f"ID: {row[0]} | Status: {row[1]} | Description: {row[2]}")
        
    cursor.close()
    connection.close()

if __name__ == "__main__":
    verify_database_patches()





Question : How to Manage an Oracle Database on a Windows environment

Managing an Oracle Database on a Windows environment with Ansible relies on Windows Remote Management (WinRM) rather than SSH. Commands are executed using Windows-native modules like win_shell or win_command to invoke PowerShell scripts, sqlplus, or RMAN directly. 

Core Syntax Rules for Windows Playbooks
Use ansible.windows or community.windows modules.
Use \ (backslash) for file paths, but escape them as \\ or wrap the entire path in single quotes ('C:\oracle\product').
Environment variables (like ORACLE_HOME) must be explicitly set in the task environment or executed via PowerShell. 
1. Target Host Setup (Windows)
Before Ansible can run on your Windows servers, you must ensure WinRM is configured on the target node. Run this script in an elevated PowerShell session on the remote Windows server: 
powershell
# Enable and configure WinRM for basic/unencrypted or HTTPS connections
winrm quickconfig -q
Set-Item WSMan:\localhost\Shell\MaxMemoryPerShellMB 1024
Set-Item WSMan:\localhost\Service\AllowUnencrypted $true
Set-Item WSMan:\localhost\Service\Auth\Basic $true
2. Ansible Inventory Setup (hosts.yaml)
Define your Windows host, credentials, and connection variables. 
yaml
all:
  hosts:
    oracle_win_server01:
      ansible_host: 192.168.1.50
      ansible_user: Administrator
      ansible_password: "YourPassword123"
      ansible_port: 5985
      ansible_connection: winrm
      ansible_winrm_server_cert_validation: ignore
3. Example Ansible Playbook for Oracle DBA
This playbook demonstrates how to set environment variables and execute an Oracle SQL*Plus script or command directly on your Windows database server. 
yaml
---
- name: Manage Oracle Database on Windows
  hosts: oracle_win_server01
  gather_facts: no
  vars:
    oracle_home: 'D:\app\oracle\product\19.0.0\dbhome_1'
    oracle_sid: 'ORCL'

  tasks:
    - name: Run SQL*Plus script to check database status
      win_shell: |
        $env:ORACLE_HOME = "{{ oracle_home }}"
        $env:ORACLE_SID = "{{ oracle_sid }}"
        $env:PATH = "$env:ORACLE_HOME\bin;$env:PATH"
        
        # Execute SQL statement and output to a log file
        sqlplus -s / as sysdba @ "C:\DBA_Scripts\check_status.sql"
      register: sqlplus_output

    - name: Display SQL*Plus Output
      debug:
        msg: "{{ sqlplus_output.stdout }}"

    - name: Run Windows batch or PowerShell script directly
      win_command: cmd.exe /c D:\app\scripts\rman_backup.bat
4. How to Run the Playbook
From your Ansible control node (Linux or WSL), run the following command to execute your playbook: 
bash
ansible-playbook -i hosts.yaml dba_playbook.yml
If you want to test connectivity before executing tasks, you can use the Windows winrm ping module: 
bash
ansible oracle_win_server01 -i hosts.yaml -m win_ping
or
Ansible cannot run directly on a native Windows engine as a control node. To run it on a Windows local host, you must install Ansible inside the Windows Subsystem for Linux (WSL) or a Docker container, and then target your Windows host using Windows-specific automation modules (ansible.windows). 
1. Set Up the Run Environment on Windows Local Host
Step 1.1: Install Ansible via WSL
Open PowerShell as Administrator and run:
powershell
wsl --install
Restart your computer and set up your Linux username/password when prompted.
Open your new WSL terminal (e.g., Ubuntu) and install Ansible:
bash
sudo apt update
sudo apt install -y ansible python3-pip
pip3 install pywinrm

Step 1.2: Configure Windows for WinRM Connections 
Ansible requires the Windows Remote Management (WinRM) service to talk to Windows hosts. Open PowerShell as an Administrator on your Windows host and run: 
powershell
Configure-SMRemoting.ps1 -Enable
Set-Item -Path WSMan:\localhost\Service\Auth\Basic -Value $true
Set-Item -Path WSMan:\localhost\Service\AllowUnencrypted -Value $true
(Note: Use Basic/Unencrypted configurations for testing on a local host only; use certificates for production). [
2. Inventory Syntax for Local Host Execution 
Create a file named inventory.ini inside your WSL environment. Because WSL communicates with the underlying Windows OS over a virtual network adapter, target the Windows host IP or use a specialized local bridge connection. 
ini
[windows_localhost]
127.0.0.1 ansible_port=5985

[windows_localhost:vars]
ansible_user=YourWindowsUsername
ansible_password=YourWindowsPassword
ansible_connection=winrm
ansible_winrm_server_cert_validation=ignore
ansible_winrm_transport=basic
3. Ansible Playbook Syntax for Oracle DBA Tasks
Oracle Database automation on Windows relies on the ansible.windows.win_shell or ansible.windows.win_command modules to interact with local system environment variables and binary executables (sqlplus, rman). 
Create a playbook named oracle_tasks.yml:
yaml
---
- name: Oracle DBA Automation on Windows Local Host
  hosts: windows_localhost
  gather_facts: false

  vars:
    # Explicitly define Windows path formats
    oracle_home: 'C:\app\client\product\19.0.0\client_1'
    oracle_sid: 'ORCL'

  tasks:
    - name: Test WinRM connectivity to Windows Host
      ansible.windows.win_ping:

    - name: Verify Oracle Service Status
      ansible.windows.win_service:
        name: "OracleService{{ oracle_sid }}"
        state: started

    - name: Execute SQL script via SQL*Plus
      ansible.windows.win_shell: |
        $env:ORACLE_HOME="{{ oracle_home }}"
        $env:ORACLE_SID="{{ oracle_sid }}"
        $env:PATH="$env:ORACLE_HOME\bin;$env:PATH"
        
        echo "SELECT status FROM v`$instance;" | sqlplus -S sys/YourSysPassword as sysdba
      register: sql_output
      changed_when: false

    - name: Display Oracle DB Status Result
      ansible.builtin.debug:
        var: sql_output.stdout_lines
4. Running the Playbook
To run the playbook from your WSL terminal, pass the inventory file you created: 
bash
ansible-playbook -i inventory.ini oracle_tasks.yml