Introduction

On UNIX servers, SnapManager for Oracle is able to protect its backups on a secondary storage system through a DataFabric Manager server. It can even restore from that secondary location.

SnapManager for Oracle on Windows, on the other hand, is unfortunately not on par with the UNIX version. When you want to protect a backup with it, you just can’t. The option is shaded in the graphical user interface.

So how can we protect our backups with SnapVault then ?

The only way is through some scripting and I’ll show you how I implemented it. But first of all, let’s take a look at the big picture here. What are the basic principles in protecting a backup with SnapVault ?

1. First, we need to create a backup at the source with SnapManager for Oracle.
2. Then, we have to update our SnapVault relationship using the snapshots we made before.
3. Finally we can take a snapshot at the destination and our backup is protected

So now let’s take a look at our setup.

We got an oracle database which Oracle SID is BLOGONTAP.

All files are hosted on NetApp LUNs in these volumes :

• ora_blogontap_datavol : Data Files
• ora_blogontap_archvol : Archive Logs
• ora_blogontap_logvol : Redo Logs
• ora_blogontap_ctrlvol : Control Files
• ora_blogontap_tmpvol : Temporary Files

We initialized our snapvault relationships using these commands :

snapvault start -S BOT1:/vol/ora_blogontap_datavol/- BOT2:/vol/svora_blogontap_backup/data
snapvault start -S BOT1:/vol/ora_blogontap_archvol/- BOT2:/vol/svora_blogontap_backup/arch
snapvault start -S BOT1:/vol/ora_blogontap_logvol/- BOT2:/vol/svora_blogontap_backup/log
snapvault start -S BOT1:/vol/ora_blogontap_ctrlvol/- BOT2:/vol/svora_blogontap_backup/ctrl

In the SMO Profile properties, I edited the Snapname Pattern to be able to locate easily a new snapshot in a NetApp FlexVol.

Here is the Snapname Pattern I use : {db-sid}_{class}_{label}_{smid}

As you’ll see a little further, I will create backups using custom labels. Adding the label to the snapshot name will make it possible for me to locate my last snapshot.

Finally, you’ll need some tools.

To create these scripts, I will use Powershell and the NetApp DataONTAP Powershell Toolkit. You’ll be able to download it from here.

So how to proceed ?

I – Creating Backups

Schedule backups in SMO are identified by their auto-generated label. We will use these labels to free our backups on primary storage a few days after their creation. Unfortunately, the auto-generated labels are unpredictable. They are refering not only to the date of creation but also to the exact second of creation …

I don’t understand the naming convention as we do not backup our Oracle databases several times a minute. Anyway, a workaround is to create the backups ourselves using the smo backup create command. Then we will be able to specify a label name.

So, here is the command I would use to create a backup from command line :

# Command to create a daily backup with a custom label
smo backup create -profile blogontap -online -full -retain -daily -label S_V_20110221
 
# Command to create a daily backup with a custom auto-generated label using Windows Command line
smo backup create -profile blogontap -online -full -retain -daily -label S_V_%date:~6,4%%date:~3,2%%date:~0,2%

I made my script using Powershell so it would look like this :

# Database SID
$orasid = "blogontap"
 
# Getting the date as YYYYMMDD
$date = Get-Date -uformat "%Y%m%d"
 
# The command line to execute
$command = "smo backup create -profile $orasid -online -full -retain -daily -label S_V_$($date)"
 
# There I execute that command remotely using PSExec from the Sysinternals Suite
psexec \\ORAHOST cmd /c $command

II – Getting Snapshot Names

Once the backup is created, we are going to update the snapvault relationships with it. As you may know, while making a backup, SMO creates two snapshots. The first one will backup the database files during the Online Backup Mode while the second will backup everything else : archivelogs, redologs and control files.

Consequently, we will update the SnapVault relationship of ora_blogontap_datavol from the first snapshot and the other ones from the second.

So the first thing to do here, is to get the name of the snapshots we just made.

That’s where the NetApp DataONTAP Powershell Toolkit comes in handy :

# Loading the DataONTAP Module
Import-Module DataONTAP
 
# Database SID
$orasid = "blogontap"
 
# Date in same format as in our Label
$date = Get-Date -uformat "%Y%m%d"
 
# Prefix for first snapshot
$prefix1 = ($orasid + "_daily_s_v" + "_" + $date + "_1")
 
# Prefix for second snapshot
$prefix2 = ($orasid + "_daily_s_v" + "_" + $date + "_2")
 
# Connect to the filer hosting the LUNs
Connect-NaController BOT1
 
# Get last snapshot of Data Files
foreach ($snapshot in (Get-NaSnapshot ("ora_" + $orasid + "_datavol"))) {
     if ($snapshot.name.StartsWith($prefix1)) {$lastdata = $snapshot.name}
}
 
# Get last snapshot of Redo Logs
foreach ($snapshot in (Get-NaSnapshot ("ora_" + $orasid + "_logvol"))) {
     if ($snapshot.name.StartsWith($prefix2)) {$lastlog = $snapshot.name}
}
 
# Get last snapshot of Archive Logs
foreach ($snapshot in (Get-NaSnapshot ("ora_" + $orasid + "_archvol"))) {
     if ($snapshot.name.StartsWith($prefix2)) {$lastarch = $snapshot.name}
}
 
# Get last snapshot of Control Files
foreach ($snapshot in (Get-NaSnapshot ("ora_" + $orasid + "_ctrlvol"))) {
     if ($snapshot.name.StartsWith($prefix2)) {$lastctrl = $snapshot.name}
}

III – Creating the XML Specification File for restore operations

We are going to protect our backups on a Secondary storage system but SMO for Windows can’t handle protected backups so how are we going to restore from secondary ?

After we free a backup on primary, if we try to restore it, SMO for Windows will ask for a Specification File. Actually we will have to connect the LUNs in the snapshot from secondary manually with snapdrive and create that Specification file that will tell SMO where our backups are mounted.

This specification file will also contain the original mount points and the original snapshot names.

My LUNs got these mount points :

• C:\Databases\blogontap\arch
• C:\Databases\blogontap\data
• C:\Databases\blogontap\log
• C:\Databases\blogontap\ctrl
• C:\Databases\blogontap\tmp

I know that when I’ll mount a backup from secondary, I will use these mount points :

• C:\Databases\blogontap\arch_0
• C:\Databases\blogontap\data_0
• C:\Databases\blogontap\log_0
• C:\Databases\blogontap\ctrl_0

So I got everything I need to generate that XML file during the protection process.

To do that I will execute that powershell file which contains the XML content, the snapshots and Oracle SID variables :

# file restore.ps1
@"
<?xml version="1.0" encoding="UTF-8"?>
<restore-specification xmlns="http://www.netapp.com"> 
<!-- "Specification File" --> 
<mountpoint-mapping> 
    <original-location>C:\Databases\$orasid\data</original-location> 
    <snapname>$lastdata</snapname> 
    <alternate-location>C:\Databases\$orasid\data_0</alternate-location> 
</mountpoint-mapping>
<mountpoint-mapping>
    <original-location>C:\Databases\$orasid\log</original-location> 
    <snapname>$lastlog</snapname> 
    <alternate-location>C:\Databases\$orasid\log_0</alternate-location> 
</mountpoint-mapping>
<mountpoint-mapping>
    <original-location>C:\Databases\$orasid\arch</original-location> 
    <snapname>$lastarch</snapname> 
    <alternate-location>C:\Databases\$orasid\arch_0</alternate-location> 
</mountpoint-mapping>
<mountpoint-mapping>
    <original-location>C:\Databases\$orasid\ctrl</original-location> 
    <snapname>$lastctrl</snapname> 
    <alternate-location>C:\Databases\$orasid\ctrl_0</alternate-location> 
</mountpoint-mapping>
</restore-specification>
"@

The encoding of the file must be ANSI. If not, you’ll get an error while loading it with SMO.

So here is the command I use :

# Generate the XML Specification File that will be used to restore from secondary
.\restore.ps1 | out-file C:\RestoreXML\$($orasid)_$date.xml -encoding Default

IV – Updating the SnapVault relationships

We made our backup and now we will update the SnapVault relationships like this on the destination filer :

plink -ssh root@BOT2 -pw pass snapvault update -w -s $lastdata /vol/svora_$($orasid)_backup/data
plink -ssh root@BOT2 -pw pass snapvault update -w -s $lastlog /vol/svora_$($orasid)_backup/log
plink -ssh root@BOT2 -pw pass snapvault update -w -s $lastarch /vol/svora_$($orasid)_backup/arch
plink -ssh root@BOT2 -pw pass snapvault update -w -s $lastctrl /vol/svora_$($orasid)_backup/ctrl

As I was not using the latest version of DataONTAP powershell Toolkit I couldn’t update my relationships using a source snapshot so I used plink instead.

So here we are, our backup is on the secondary filer. We just need to enable scheduled snapshots on the volume and we are done.

V – Freeing backup on Primary

The main goal of SnapVault is to use Secondary less expensive storage to retain more backup days.

The manual backups we made won’t free itselves. We must free them using their label name.

For example, at the end of each backup, I free the 3-day-old backup to keep only 3 daily on Primary :

# The date three days before
$datefree = (get-date (get-date).AddDays(-3) -uformat %Y%m%d)
 
# The command to free the backup
$commandfree = "smo backup free -profile $orasid -label S_V_$($datefree)"
 
# Here I execute the backup remotely on my Oracle Host
psexec \\ORAHOST cmd /c $commandfree

Mission Accomplished ; )