Introduction
In a previous post I introduced ORCA for Windows and shared a Video of it in use, in this post we will spend a bit of time looking at some of the techniques I used to implement it..
Working with Ansible Templates
One of the Ansible features I find myself using time and time again within my Playbooks is Templates.
Ansible Templates are processed by the Jinja 2 templating language, a Jinja template is a text file, which when processed can generate any text-based format (HTML, XML, CSV, SQL, CMD, etc…) The template contains variables and/or expressions, which get replaced with values when a template is rendered.
The template files are identified by the j2 extension, for example rename.sql.j2 will generate rename.sql
ORCA for Windows uses a number of templates to dynamically create Windows command files, SQL scripts and my ora init files.
Example Jinga 2 SQL file
Below is an example Oracle SQL Jinga2 file which when processed will substitute the {{ src_db }} and {{ tgt_db }} variables for values at runtime.
set lines 256
set head off
set echo off
set veri off
set feed off
set term off
spool rename_files.sql
prompt set veri off
prompt set echo off
prompt set feed off
prompt set term off
select 'alter database rename file '''||
name||''' to '''||
substr(name,1,instr(name,'{{ src_db | upper }}')-1) ||'{{ tgt_db | upper }}'||substr(name,instr(name,'{{ src_db | upper }}')+{{ src_db | length }}) ||''';'
from v$datafile;
select 'alter database rename file '''||member||''' to '''||
substr(member,1,instr(member,'{{ src_db | upper }}')-1) ||'{{ tgt_db | upper }}'||substr(member,instr(member,'{{ src_db | upper }}')+{{ src_db | length }}) ||''';'
from v$logfile;
prompt set term on
spool off
Calling Windows PowerShell and CMD Scripts
To automate the off-lining of Windows disks, I use use the Ansible win_command module to call a PowerShell script on the target database server e.g.
---
# Offline volumes
- name: offline target database volumes
win_command: powershell.exe -ExecutionPolicy ByPass -File {{ tgt_dir }}\offline.ps1
register: out
- debug:
var: out.stdout_lines
verbosity: 1
Example Off-lining Windows Disks
Below is my Windows PowerShell script, I have highlighted the 2 lines which actual perform the offline of my database volumes.
#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-dev2.uklab.purestorage.com'
$TargetVMSession = New-PSSession -ComputerName $TargetVM
# Offline the volume
# Use wmic diskdrive get serialnumber to get disk Serial Number
# E:\ 50C939582B0F46C0003E2D24
# F:\ 50C939582B0F46C0003E59A6
#
Write-Host "Offlining the volumes..." -ForegroundColor Red
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003E2D24' } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetVMSession -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq '50C939582B0F46C0003E59A6' } | Set-Disk -IsOffline $True }
Write-Host "Development volumes offlined." -ForegroundColor Red
# Clean up
Remove-PSSession $TargetVMSession
Write-Host "All done." -ForegroundColor Red
I also use the Ansible win_command module to execute Windows CMD files, below is an example of how I rename my Oracle database calling a script to run NID as a privileged user.
---
# Rename source database
- name: rename source database
win_command: dbnewid.cmd
args:
chdir: "{{ tgt_dir }}"
become: yes
become_method: runas
become_user: Administrator
register: out
- debug:
var: out
verbosity: 1
Using Ansible Collections
Ansible has recently moved away from shipping 3rd party Ansible modules in the distribution and since Ansible 2.9 has supported Collections, you can find a list of a growing number of Vendor and Community supported Collections by visiting Ansible Glaxay.

Pure Storage Collection
To help with your storage automation Pure Storage provides a comprehensive number of Ansible modules for both FlashArray and FlashBlade which you can obtain from Ansible Galaxy.

Collection Installation and Usage
The installation of Vendor and Community Ansible Collections can be performed using ansible-galaxy collection install <collection name> command e.g.
$ ansible-galaxy collection install community.vmware
$ ansible-galaxy collection install purestorage.flasharray
Once installed, you can provided a list of required collections within your playbook.
---
# Summary of Steps
# 1. Stop target database (if running)
# 2. Unmount the filesystem on target server (if mounted)
# 3. Take snapshot of the source volumes on Pure
# 4. Mount the cloned volumes on target server
# 5. Bring up the database on target server in mount mode
# 6. Change the database name using nid
# 7. Rename the directories on cloned database
# 8. Bring up the database & change filenames
#
- name: Oracle Database Refresh for Windows
hosts: tgtwinsvr
vars_files:
- vars/arrays.yaml
- vars/database.yaml
collections:
- purestorage.flasharray
gather_facts: false
The Pure Storage Protection Group Snapshot and Overwrite can both be performed by using the Pure Ansible purefa_pgsnap module, as you can see in the examples below all the parameters are using variables to support greater re-use.
Example Taking a Protection Group Snapshot
---
# Perform PURE Flasharray Database snapshots
- name: Perform PURE database snapshot {{ suffix }}
purefa_pgsnap:
fa_url: "{{ array.ip }}"
api_token: "{{ array.array_token }}"
name: "{{ sourceName }}"
suffix: "{{ suffix }}"
state: present
register: snap
Example Performing Volumes(s) Overwrite
We can also use the purefa_pgsnap module to overwrite our database volumes
---
# Local PG snapshot name example:
# z-oracle-u01.ansible123
# z-oracle1PG.ansible162.z-oracle1-u01
#
# Replicated volume name example
# FlashArray:z-oraclePG.11551.z-oracle-u01
#
#
- name: Overwrite Database Volume(s) v2
purefa_pgsnap:
name: "{{ sourceName }}"
suffix: "{{ suffix }}"
restore: "{{ item.value.source }}"
target: "{{ item.value.target }}"
overwrite: true
fa_url: "{{ array.ip }}"
api_token: "{{ array.array_token }}"
state: copy
with_dict: "{{ tgtmap }}"
ORCA for Windows Ansible Playbook
If you have found this interesting you can find all the example code above and ORCA for Windows on GitHub here.
Alternatively, if Linux is your preferred Operating System, you can check-out ORCA for Linux which is also shared on GitHub here.
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]
Leave a Reply