Using Powershell for Oracle DBA scripts


I needed to deploy an RMAN backup script on a Windows OS machine and couldn’t to use OEM in the target environment. So I decided to try out powershell rather than use BAT and VBScript. Powershell proved to be a handy, easy to use scripting environment.

While powershell is object based and is tightly plugged into both the Windows OS and .NET, I took the approach of writing a straight-forward script similar to a unix shell script.

To execute a powershell script, you create a text file that ends with the extension PS1, e.g. backup_script.ps1. Here’s how to run the script from the windows command line.

C:\> powershell -file backup_script.ps1

I learned that powershell scripts can have signed security certificates to prevent rogue scripts from causing damage. Powershell can run in different security modes, ranging from restricted (interactive use only, no scripts) to unrestricted, where you get a prompt in order to run a script from the internet. I settled for “RemoteSigned” mode, where scripts downloaded from the internet must be signed, but not ones that you create. Default mode is restricted, so you’ll need to run the following from the powershell prompt to get local scripts to run:

PS C:\> set-executionpolicy remotesigned

Powershell supports two things that help out the unix shell scripter: aliases and pipelines. There are built-in aliases so that you can use familiar commands like ls and echo for the equivalent powershell commands that have much longer names. The powershell commands behave differently with a different set of command switches, but it’s still helpful to have the aliases. The pipeline support allows one to pipe virtually all powershell commands, unlike the meager pipeline support for windows bat commands.

In powershell 2.0 there’s an email command, send-mailmessage, which DBAs will find handy for monitoring and reporting.

The help system is built in to the powershell command line. Typing

PS C:\> help

gets you a complete list of commands and aliases.
And typing

PS C:\> help specific-command

gets you help on that command. There are also switches on the help command for examples, details and full help, e.g. help specific-command -examples.

What’s radically different about powershell from unix style shell languages is that it’s object based. When you pipe a command to another command, you are piping an object, not text. The receiving command can then reference properties of the object that is passed.

One item not supported in powershell: input redirection. This is a mainstay in unix shell scripting. It’s a handy way to keep your sql statements embedded in your shell script and to use variables derived from the shell script directly in the sql without passing them as positional arguments. By input redirection, I mean the following bit of unix scripting:

sqlplus /nolog << EOF
connect $user/$pass@$sid 
select * from table
where f1 = $another_var;

Here’s a pared down sample of the powershell script I created. This sample does not do anything with the .NET or object based attributes of powershell, with the exception of the line that deletes old files.

It illustrates the following:
> Getting command line arguments
> Formating a date
> String concatenation, done a la unix shell without a concatenation operator
> Assigning variables
> Launching an external command (nothing special, just run it)
> Sending email
> Deleting files older than a certain date

# Get script input params

# Assemble other variables
$datetime = get-date -format ""
$RMAN_LOG = "$LOG_DIR\$ORACLE_SID.$RMAN_TYPE.$datetime.rman.log"

# Start logging to file and then run RMAN command. Echo is an alias for Write-Output.
$newline = "Start Execution of $RMAN_TYPE backup on $ORACLE_SID"
echo $newline | out-file $RMAN_LOG
get-date | out-file $RMAN_LOG -append
rman cmdfile=$BIN_DIR\rman_backup_disk_compressed.rman using $ORACLE_SID | out-file $RMAN_LOG –append

# Send email. Note that cat is an alias for Get-Content.
$body = cat $RMAN_LOG | out-string 
send-mailmessage -to "" -from "" -body $body -subject "RMAN log output" -smtpserver mailserver

# Remove files older than 8 days.
# Note that ls is an alias for Get-ChildItem and del is an alias for Remove-Item.
# Also, ? is an alias for Where-Object and $_ is a built-in variable 
# to refer to the collection of objects passed from the previous pipeline.
ls $LOG_DIR\$ORACLE_SID.$RMAN_TYPE.* |? {$_.LastWriteTime -lt (get-date).AddDays(-8)} | del

Excel Pace Maker for Races

Leave a comment

A bit off topic, but does involve Microsoft Excel…You runners, swimmers and bikers out there may occasionally do interval training in order to increase your speed. I’m always trying to answer the following question: how fast do I need run to get time X in a race of distance Y? And how fast do I have to run shorter interval Z to see what that pace feels like? I made an Excel file that makes such extrapolations easy. You can time yourself running a short interval and see what time you would get if you kept up that pace for a whole race. Download it with the link earlier in this post or using the Downloads page on this blog.

New deinstall tool

Leave a comment

I needed to uninstall Oracle database from a windows host and came across something new to 11G R2.

I ran the Oracle Universal Installer (oui) and chose the “Deinstall” button, which is located where it’s always been. But instead of beginning the deinstallation set of screens, it just popped up a message box reading, “Please run the deinstall command $ORACLE_HOME/deinstall/deinstall.”

It turns out there is a new utility just for deinstallation. It is uniform across all Oracle platforms. The documentation details can be found here.

I was sufficiently surprised that I checked some 10.2.0 and 11.1.0 installations. They did not have the $ORACLE_HOME/deinstall directory, so it definitely is new for 11.2.0.