Automating Oracle 19c Database Backup with PowerShell and Windows task scheduler to network path

 

In this guide, we will walk through the process of automating an Oracle 19c database backup using PowerShell. The backup will be stored on a network location, ensuring accessibility and disaster recovery readiness. 

1. Oracle Database & Environment Configuration

Before proceeding, ensure that the following system configurations are properly set up:

Operating System Requirements:

• Windows Server 2016/2019/2022 or Windows 10/11 (Admin access required)

• PowerShell 5.1 or later installed

• Oracle 19c installed and configured

Database & Storage Setup:

• Oracle Database Home Directory: D:\Mominul\Ora19c

• Oracle Database SID: EGERPDB

• Schema to Backup: EGERPDB

• Data Pump Directory: D:\Ora19c\OracleBase\admin\EGERPDB\dpdump

• Network Backup Path: \\ip-address\e\db_bk

1.1:  Prerequisites

Before setting up an automated backup, ensure the following:

✅ Oracle 19c is installed on a Windows Server
✅ PowerShell is enabled and has admin rights
✅ Oracle Data Pump (expdp/impdp) utilities are available
✅ Network storage is accessible for backup storage
✅ User has DBA privileges for Oracle backup

2. Configure Oracle Data Pump Backup Directory

Step 1: Create Backup Directory in Oracle

To allow Oracle Data Pump to export backups, create a directory object in Oracle:

sql

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'D:\OracleBackup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;

🔹 Explanation:

• This creates an Oracle Directory Object linked to D:\OracleBackup.

• Grants read/write access to the SYSTEM user (adjust as needed).

3. Setting Folder and Path Permissions

Ensure the Oracle Database Service account has read/write access to:

• Backup Folder (D:\OracleBackup)

• Network Storage Path (e.g., \\ip-address\e\db_bk)

Step 1: Set Permissions on Backup Folder

1️⃣ Right-click on D:\OracleBackup → Select Properties
2️⃣ Go to Security tab → Click Edit
3️⃣ Add Oracle user (e.g., ORACLE_SID or SYSTEM)
4️⃣ Grant Full Control

Step 2: Ensure Network Path Access

If storing backups on a network drive, allow access:

1️⃣ Map network drive (e.g., \\ip-address\e\db_bk)
2️⃣ Right-click folder → Properties → Sharing → Advanced Sharing
3️⃣ Click Permissions, add Oracle User, and grant Full Control

4. PowerShell Script for Oracle Backup

Below is a PowerShell script that:
✅ Exports Oracle schema backup
✅ Transfers backup to a network location
✅ Logs every step

PowerShell Script (Backup & Network Transfer)

powershell

# ========================== CONFIGURATION ========================== #
$oracle_home = "D:\Mominul\Ora19c"  # Oracle Home Path
$oracle_sid = "ERPDB"             # Database SID
$schema_name = "ERPDB"            # Schema to Backup
$network_path = "\\ip-address\e\db_bk"  # Backup Storage Path
$backup_user = "osFTPUserName"          # Network Share Username
$backup_password = "<NETWORK_PASSWORD>"  # Placeholder: Use Secure Vault Instead
$oracle_user = "ERPDBUser"            # Oracle User
$oracle_password = "<ORACLE_PASSWORD>"  # Placeholder: Use Secure Vault Instead

# Oracle Export Directory
$oracle_export_path = "D:\Ora19c\OracleBase\admin\EGERPDB\dpdump"

# Log file configuration
$logPath = "D:\backup_logs"
$logFile = "$logPath\oracle_backup_log_$(Get-Date -Format 'yyyyMMdd').log"

# ========================== FUNCTIONS ========================== #
# Function to log messages
function Write-Log {
    param([string]$message)
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "$timestamp - $message"
    Write-Output $logMessage
    Add-Content -Path $logFile -Value $logMessage
}

# ========================== START SCRIPT ========================== #
Write-Log "INFO: Starting Oracle Database Backup Script."

# Validate ORACLE_HOME
if (-Not (Test-Path $oracle_home)) {
    Write-Log "ERROR: ORACLE_HOME directory does not exist: $oracle_home"
    exit 1
}
Write-Log "INFO: ORACLE_HOME validated."

# Set Environment Variables
$env:ORACLE_HOME = $oracle_home
$env:ORACLE_SID = $oracle_sid
$env:PATH = "$oracle_home\bin;$env:PATH"
Write-Log "INFO: ORACLE_HOME and Environment variables set."

# Get Timestamp for Backup Naming
$timestamp = (Get-Date -Format "yyyyMMddHHmmss")
$dumpFileName = "${schema_name}_$timestamp.dmp"
$logFileName = "${schema_name}_$timestamp.log"

# Check if Oracle export directory exists
if (-Not (Test-Path $oracle_export_path)) {
    Write-Log "ERROR: Oracle export directory not found: $oracle_export_path"
    exit 1
}
Write-Log "INFO: Oracle export directory found: $oracle_export_path"

# Construct expdp Command Using Direct Connection String
$expdpCommand = @"
expdp $oracle_user/<ORACLE_PASSWORD>@//localhost:1521/$oracle_sid schemas=$schema_name directory=DATA_PUMP_DIR dumpfile=$dumpFileName logfile=$logFileName
"@

# Execute expdp Command and Capture Output
try {
    Write-Log "INFO: Starting expdp backup for schema $schema_name..."
    $output = Invoke-Expression $expdpCommand 2>&1
    if ($output -match "ORA-") {
        Write-Log "ERROR: expdp failed with error: $output"
        exit 1
    } else {
        Write-Log "INFO: expdp backup completed successfully."
    }
} catch {
    Write-Log "ERROR: expdp command execution failed - $_"
    exit 1
}

# ========================== TRANSFER BACKUP TO NETWORK ========================== #
$sourceFile = "$oracle_export_path\$dumpFileName"
$destinationFile = "$network_path\$dumpFileName"

# Check if the .dmp file exists before transferring
if (Test-Path $sourceFile) {
    Write-Log "INFO: Backup file found at $sourceFile. Preparing to transfer."

    # Ensure Network Path is Accessible
    if (!(Test-Path $network_path)) {
        Write-Log "ERROR: Network path $network_path does not exist."
        exit 1
    }

    # Try to Copy the Backup File to Network
    try {
        Copy-Item -Path $sourceFile -Destination $destinationFile -Force
        if (Test-Path $destinationFile) {
            Write-Log "INFO: Backup file successfully copied to $destinationFile"
        } else {
            Write-Log "ERROR: Backup file copy failed!"
            exit 1
        }
    } catch {
        Write-Log "ERROR: Failed to copy backup file to network path - $_"
        exit 1
    }
} else {
    Write-Log "ERROR: Backup file not found in export directory: $sourceFile"
    exit 1
}

Write-Log "INFO: Oracle Database Backup Script completed successfully."
exit 0

---

4.1: Secure Credential Handling

DO NOT store passwords in plaintext inside scripts. Instead, use:

Option 1: Use Secure Credential Store

$securePassword = ConvertTo-SecureString "YourPassword" -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ("username", $securePassword)

Option 2: Store in Windows Credential Manager

• Use cmdkey /add:targetname /user:username /pass:password

• Retrieve using Get-StoredCredential

5. Automate Backup Using Windows Task Scheduler

Step 1: Open Task Scheduler

• Press Win + R, type taskschd.msc, and press Enter.

Step 2: Create a New Task

• Click Create Basic Task → Set Backup Name → Click Next

• Choose Daily / Weekly

• Select Start time

• Click Next → Start a Program

Step 3: Configure the PowerShell Script Execution

• Program/Script: powershell.exe

• Add Arguments:

  arduino
  
  -ExecutionPolicy Bypass -File "D:\scripts\oracle_backup.ps1"
  

• Click Next → Finish

Step 4: Set User Permissions

• Right-click the task → Properties

• Under General, check Run with highest privileges

• Under Security Options, select Run whether user is logged in or not

---

6. Verification & Monitoring

• Check Logs: D:\backup_logs\oracle_backup_log_YYYYMMDD.log

• Confirm Backup Files: \\ip-address\e\db_bk

• Manually Run the Task: Task Scheduler → Right-click Task → Run

---

Conclusion

This guide provides a fully automated Oracle 19c backup solution using PowerShell, Oracle Data Pump, and Task Scheduler. 🚀