This post has been copied from the former Compellent User Forum and placed here for your reference.  This code is made available AS IS, without warranty of any kind.  The entire risk of the use or the results from the use of this code remains with the user.

Oracle 10gR2 Live backup – Originally posted By professorfrink, 04 May 2010

professorfrink - I thought that I would share the script I have written for doing a live backup of an Oracle database via powershell.

We use Backup Exec here and I have tested this out with version 2010 and it seems to work good. Essentially I just use the "post commands" option of the backup job to call "powershell.exe C:\backup\scripts\DatabaseBackup.ps1".

This script will connect to the Oracle database, alter it to put it into backup mode, connect to the Storage Center, take a replay of the volume, alter the database to take it out of backup mode, and finally create a local recovery of the volume for export.

These steps come from the KB doc at https://kc.compellent.com/Pages/Download.aspx?DocID=724.

I am using a Windows 2008 server with PowerShell 2.0 installed.

I installed 10204_vista_w2k8_x64_production_db.zip from here:

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204_winx64_vista_win2k8.html

It installs an OraHome with the ODP.net included.

I have of course also installed the Compellent PowerShell Command Set.

To make use of the local recovery volume, you will need to run "recover database" before you start it up. You will need the appropriate archive logs to do the recovery.

Here is the script:

 

#############################################################################

## Script to pause Oracle DB and take Compellent SAN replay

##

## Ian Stirling

## April 27th 2010

##

##

## Note: passwords created by using the following 2 lines of code

##

## $pass = read-host -assecurestring -prompt "Enter password to encrypt: "

## convertfrom-securestring $pass -key (1..16) > c:\Backup\scripts\password.file

##

#############################################################################

 

############################### Functions ###################################

#Function to connect to SAN

function connectToSAN {

    #Connect to SAN

    "Connecting to Compellent Storage Center" >> $logfile

    $sanpassword=ConvertTo-SecureString -key (1..16) (gc "c:\backup\scripts\sanpassword.file")   

    return Get-SCConnection -HostName compellent -User Admin -Password $sanpassword

}

function takeReplay($conn) {

    #Take a snapshot of the volume. Keep snapshot (replay) for 30 days

    #Should use SourceVolume instead of SourceVolumeName but it is not working right now

    "Taking a snap shot of the volume" >> $logfile

    return New-SCReplay -SourceVolumeName $volume -Description $description -MinutesToLive 40320 -Connection $conn

}

function createLocalRecoveryVolume {

    $rep,$conn = $args;

    #Create a local recovery from the replay

    "Creating Local Recovery of the replay" >> $logfile

    $monthDay = Get-Date –format M

    $newVolumeName = $volume + " " + $monthDay + " BACKUP"

    $volume = New-SCVolume -Name $newVolumeName -SourceReplay $rep -Connection $conn

}

#############################################################################

 

# Load the Compellent Command Set

"Load Compellent Commands" >> $logfile

if (!(Get-PSSnapin Compellent.StorageCenter.Scripting -ea SilentlyContinue)) {

      Add-PSSnapin -Name Compellent.StorageCenter.Scripting

}

#log file location

$logfile = "C:\backup\logs\DatabaseBackup.log"

#setup date/time information

$date = Get-date -uformat "%Y-%m-%d"

$time = Get-date -displayhint time -uformat "%H-%M-%S"

#Add seperator lines to log file

"" >> $logfile

"-------------------------------------------------------------------------------" >> $logfile

get-date -f "HH:mm:ss dd/MM/yyyy" >> $logfile

#Get Volume object

#$volume = Get-SCVolume -Name 'HEPPIAN Database - LUN 22'    #Not working at this time

$volume = "MyVolume"

$description = "Backup from " + $date

$oraclepassword=ConvertTo-SecureString -key (1..16) (gc "c:\backup\scripts\oraclepassword.file")

$oraclepassword = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($oraclepassword)

$oraclepassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($oraclepassword)

#Load Oracle Client Library

"Loading Oracle Client..." >> $logfile

[Reflection.Assembly]::LoadFile("C:\oracle\product\10.2.0\db_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll") >> $logfile

"" >> $logfile   

#establish Oracle connection parameters and open the connection

"Connecting to Oracle..." >> $logfile

$constr = "User Id=user;Password=" + $oraclepassword + ";DBA Privilege=SYSDBA;Data Source=MyDS"   

$conn = New-Object Oracle.DataAccess.Client.OracleConnection($constr)

#Try to establish connection

$conn.open()       

#If connected, run Oracle commands else assume database is already down and just take replay

if ($conn.State -eq "Open"){

    #put database in backup mode

    "Put database into backup mode" >> $logfile

    $query = "ALTER DATABASE BEGIN BACKUP"

    $command = New-Object Oracle.DataAccess.Client.OracleCommand($query,$conn)

    $return = $command.ExecuteNonQuery()

    $controlfile = "control.ctl." + $date + "_" + $time

    $query = "alter database backup controlfile to '/u04/app/oradata/hepp/" + $controlfile + "'"

    $command = New-Object Oracle.DataAccess.Client.OracleCommand($query,$conn)

    $return = $command.ExecuteNonQuery()

    $query = "alter database backup controlfile to trace"

    $command = New-Object Oracle.DataAccess.Client.OracleCommand($query,$conn)

    $return = $command.ExecuteNonQuery()

    $query = "alter system archive log current"

    $command = New-Object Oracle.DataAccess.Client.OracleCommand($query,$conn)

    $return = $command.ExecuteNonQuery()

    #Connect to SAN and take replay

    "" >> $logfile

    $sanconnection = connectToSAN

    $replay = takeReplay $sanconnection

    #take database out of backup mode

    "" >> $logfile

    "Take database out of backup mode" >> $logfile

    $query = "ALTER DATABASE END BACKUP"

    $command = New-Object Oracle.DataAccess.Client.OracleCommand($query,$conn)

    $return = $command.ExecuteNonQuery()

    #close db connection

    $conn.close()

} else {

    "Oracle appears to be down!" >> $logfile

    "Replay will be taken, but may not be recoverable if database is not down or not in backup mode." >> $logfile

    #just take the replay if the db is down

    "" >> $logfile

    $sanconnection = connectToSAN

    $replay = takeReplay $sanconnection

}

#make a volume that can be replicated or copied to portable volume

createLocalRecoveryVolume $replay $sanconnection

"" >> $logfile

"All done!" >> $logfile

 

I hope it can be useful to some others.