Site icon Ron Ekins' – Oracle Technology, DevOps and Kubernetes Blog

How to refresh your Oracle Database on Windows in seconds with PowerShell

@RonEkins

Introduction

In this short post I will share how we can automate the refresh of an Oracle database on Windows in few seconds with a simple PowerShell script and the Pure Storage PowerShell SDK.

Database Volumes

In my lab I have an 2 Oracle database each using 2 volumes for the database, which are presented as RDM (Raw Device Mappings) to my Windows Servers.

The Pure Storage FlashArray volumes are thus:

From the FlashArray WebUI we navigate to the Storage -> Volume -> Details to see the volume serial numbers:

As I am using RDMs the same serial numbers should be visible in Windows. We can confirm that Windows is using the correct disks by checking the serial number(s) in a cmd window with the wmic diskdrive get serialnumber command e.g

Now we know our Source and Target volumes we can use a PowerShell script to refresh our database volumes.

PowerShell SDK

The PowerShell script below requires the Pure Storage PowerShell SDK to be installed from within a PowerShell Window using the Install-Module command e.g.

Install-Module -Name PureStoragePowerShellSDK

PowerShell Script

With the volume details and the PowerShell SDK in place we can now automate the off-off-lineing of the Windows disks, storage snapshot, volume overwrite and the on-lineing of the Windows disks using the PowerShell script below.

 Import-Module PureStoragePowerShellSDK
#Requires -RunAsAdministrator
#
$CurrentID = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$CurrentPrincipal = new-object System.Security.Principal.WindowsPrincipal($CurrentID)
$adminRole = [System.Security.Principal.WindowsBuiltInRole]::Administrator
$UserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name

write-host "Welcome " $UserName

# Check to see if session is currently with admin privileges
#
if ($CurrentPrincipal.IsInRole($adminRole)) {
    write-host "Yes we are running elevated."
}else{
   write-host "No this is a normal user session."
}


$TargetVM = 'z-re-ora-dev5.uklab.purestorage.com'
$TargetVMSession = New-PSSession -ComputerName $TargetVM

# Use wmic diskdrive get serialnumber to get disk Serial Number
# G:\ 50C939582B0F46C0003FBBB4
# H:\ 50C939582B0F46C0003FBBB5


# Offline the volume
Write-Host "Offlining the volumes..." -ForegroundColor Red
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003FBBB4' } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003FBBB5' } | Set-Disk -IsOffline $True }

# Connect to the FlashArray's REST API, get a session going
# THIS IS A SAMPLE SCRIPT WE USE FOR DEMOS! _PLEASE_ do not save your password in cleartext here. 
# Use NTFS secured, encrypted files or whatever else -- never cleartext!
Write-Host "Establishing a session against the Pure Storage FlashArray..." -ForegroundColor Red
$FlashArray = New-PfaArray –EndPoint 10.225.112.12 -UserName pureuser -Password (ConvertTo-SecureString -AsPlainText "password" -Force) -IgnoreCertificateError

# Perform the volume overwrite (no intermediate snapshot needed!)
Write-Host "Overwriting the dev instance's volume with a fresh copy from production..." -ForegroundColor Red
New-PfaVolume -Array $FlashArray -VolumeName z-re-ora-dev5-G -Source z-re-ora-cluster-G -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName z-re-ora-dev5-H -Source z-re-ora-cluster-H -Overwrite

# Online the volume
Write-Host "Onlining the volume..." -ForegroundColor Red
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003FBBB4' } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003FBBB5' } | Set-Disk -IsOffline $False }

# Clean up
Remove-PSSession $TargetVMSession
Write-Host "All done." -ForegroundColor Red

Database Refresh

The workflow to refresh our non-Production Oracle database is as simple as.

  1. Shutdown Oracle database
  2. Run PowerShell Script
  3. Startup Oracle database

You can use either SQLPlus or the Windows ordaim command to startup/shutdown your database as per your preference.

Summary

The time it takes to complete the process will be dependent on how long it takes to perform a database shutdown and startup, the PowerShell script will complete in only a few seconds regardless of database size.

In this post I shared how we can use a Windows PowerShell script to perform an Oracle database refresh using the Pure Storage PowerShell SDK to automate a storage snapshot and overwrite.

My next step is to upload the code to my GitHub repo and then publish the accompanying video, so if you want to keep informed follow my Blog or Twitter account to make sure you don’t miss it.

[twitter-follow screen_name=’RonEkins’ show_count=’yes’]

Exit mobile version