Automating Oracle Database Cloning on Windows with Ansible

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.

Ansible Galaxy Home

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.

Ansible Galaxy – Pure Collections

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

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: