Sql Server UnionAggregate to merge geofences

Over the past two years I have been learning more and more about the power of SQL Server’s  spatial functions. During this time a co-worker, and myself have developed server side geofence processes to track vehicles using GPS data. Some customers had over 12 geofences defined, and keeping the polygons accurate became important and sometimes  time consuming.

One day a customer wanted to change three adjacent geofences and rename the geofence to just one merged geofence. At first thought I would just take the exterior cords and plug them in as the new shape. Then I thought, there has to be a better way. Since I was rather new to spatial geometry, I hit the msdn website to learn a better solution.


based on this information it was as easy as doing the following:



shape geometry,

shapeType nvarchar(50)


— The following table has a column of type geometry in the spatial_geofence table. So we are inserting the three geofences into our @Geom table

INSERT INTO @Geom(shape,shapeType)
select  [Geofence].ToString(),’POLYGON’

— Perform UnionAggregate on @Geom.shape column

SELECT geometry::UnionAggregate(shape).ToString() FROM @Geom;

using the output from the above query I could easily just insert the new polygon row and delete the previous three geofences.

After transposing the WKT to KML and checking it with Google Maps I discovered it was exactly the clean solution I was looking for.

Posted in Sql Server | Leave a comment

RMAN backup server switching your ORACLE_UNQNAME

Normally we set the ORACLE_UNQNAME setting in our bash_profile. But in my case I use the backup server to restore both production and stage backupsets for testing.  It is very easy to forget setting this environment variable. Therefore I decided to modify my oraenv file on just this server to :

# Install any “custom” code here
case “$ORACLE_SID” in


Granted if you set the ORACLE_SID to something not in your oratab file the unqname , and home will not be set, but this would be the case with no changes to the default environment. Seems there are a lot of posts about this subject in a RAC environment but not mentioned often when using Data Guard and or RMAN.

Let me know your thoughts on this, any best practices, and if you have done something similar for your environments.

Posted in Oracle | Leave a comment

Avamar 7.3 RMAN configuration


The Avamar 7.3 for Oracle User guide is full of details on configuring RMAN and the avamar plugin. In attempting to use RMAN with the plugin I came across a few gotcha’s and this post will hopefully save others time.

After testing the Avamar Administrator Gui I quickly came to the conclusion that all backups and restores work more smoothly from the client on demand rather than through the Administrator. Especially if you are backing up instances in noarchivelog mode where you must stop oracle and mount and do other pre and post processing steps. The Gui has pre and post script options but keeping the context and error handling is quite limited.

Backups should always be performed from either the client or from the GUI.  You should not mix them as Avamar claims it will cause issues. However if you use the prefix command on the client you will be able to restore from the GUI if need be. However from my experience using the client side on demand using RMAN scripting is less risky, and has a higher success rate.

Using just the client means you do not have to setup datasets, and schedules in the Avamar Adminstrator. Basically you can see your backups and because we use a taskfile.txt on the client and send a prefix, the administrator will show all the requests and notify us if we have a failure or success.

Installation of the plugin on a client is straight forward, and the guide tells you the commands to either upgrade or install the plugin.

In my case I upgraded the plugin on Oracle Enterprise Unix using the following commands as root.

rpm –Uvh AvamarClient-linux-sles11-x86_64-7.3.100-233.rpm

rpm –Uvh AvamarRMAN-linux-sles11-x86_64-7.3.100-233.rpm


Login to Avamar Administrator

A domain can be created for instance which just contains your oracle databases to be backed up and restored.

I named my domain Oracle so when running the avregister command I just had to type in the domain as /Oracle

Also in Avamar Administrator you should create a user that is going to be used to login from the client to either backup or restore from the RMAN plugin. You can scope this user to the entire administrator site or to a specific domain.

I currently have oracle configured at the management servers top level.

I created a user named oracle with a role of Backup/Restore and took note of the password for when we setup the client scripts.

On the oracle server go to /usr/local/avamar/bin as root and run avregister.

typing in the /Oracle or whatever domain you created in your Avamar Administrator for this server.

also typing in the Avamar Server host name.

Now If you login to Avamar Administrator you should see your oracle server registered under the domain you chose.


The next step that you may miss because the instructions jump around on topics, and don’t show you a working example from the oracle account’s perspective. See the plugin is installed as root, and of course RMAN and the oracle account is  really what we need configured to run RMAN and write to the logs.

So make sure that the following directories have read write access for oracle.



Now that we have a user account configured in Avamar Administrator, and a Domain, and a oracle server registered there we can configure the oracle server with RMAN scripts to backup or restore from the client.

On this first post I’m just going to show you a simple backup script.

Logged into our oracle server as oracle we can create a avamar flag file named my-avtar-flags.txt

In my-avatar-flags.txt we will have entries as follows:

–pidnum=1002        #(specific to OS see Avamar 7.3 user guide for number)
–id=oracle               #(name of user account on avamar server)
–ap=password        #( avamar server acccount password)
–path=/Oracle/viradbstage        # (Domain name/server name)
–server=Avamar.Lojackone.lojack.com      #(Avamar server name)
–expires=30 #(Requested days of backup: RMAN Policies will override)

after saving that file we create a taskfile.txt as follows:


after saving that file we create an RMAN script rman_ljstage_backup.rm

run {
configure controlfile autobackup on;
allocate channel c1 type sbt PARMS=”SBT_LIBRARY=/usr/local/avamar/lib/libobk_avamar64.so”;
set controlfile autobackup format for device type sbt to ‘CONTROLFILE.LJSTAGE.%F’;
send ‘”–prefix=11g/LJSTAGE/” “–flagfile=/home/oracle/scripts/my-avtar-flags.txt” “–bindir=/usr/local/avamar/bin” “–taskfile=/home/oracle/scripts/taskfile.txt”‘;
backup database plus archivelog;
release channel c1;

so the above rman script is setup for a SID of LJSTAGE. This RMAN script and the myavtar-flags.txt and taskfile.txt are stored in /home/oracle/scripts directory.

so to test this script we can just log in to RMAN and run the script

In my case I have an rman repository so I would login as follows:

source the oracle environment we want to backup

. oraenv


Launch rman using our repository

rman target / catalog rman/password@LJCAT

once at the RMAN prompt we would just type the script to run.

RMAN> @rman_ljstage_backup.rm

If you are not using an oracle repository you would obviously just login to rman without the catalog. and run the script the same using only the control file as a source of backup meta data.

Also note: if the above script fails you can rerun it with a little more detail in the logs by adding –debug to your myavtar-flags.txt file.

Once you have a working RMAN script you may create a bash script with all your oracle configurations and place on a cron job for daily backups:

example: rman_backup.sh


export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=viradbstage.LoJackOne.LoJack.com

. oraenv

rman target /  catalog rman/password@LJCAT cmdfile=/home/oracle/scripts/rman_ljstage_backup.rm msglog=/home/oracle/logs/rman_ljstage_backup.`date +%Y%m%d-H%M%S`.log


Note: the above script has  an ORACLE_UNQNAME set.

This becomes an important setting for RMAN configured with a catalog and using dataguard. Since oracle is registered with the ORACLE_SID and if you want to do backups on your secondary RMAN will recognize the standby database when connected to the catalog properly.

My next post on this subject will be to show the steps to perform a restore using the Avamar plugin.

Thanks and as always “DBA’s don’t fly by the seat of your pants!”

Posted in Oracle | Leave a comment

WCOMPUTE is back online.

Well I just couldn’t stay away. I’ve decided to post some of my more recent projects and issues. Hopefully we can learn together what makes us better DBA’s and as always  “DBA’s don’t fly by the seat of your pants!”

urls now are: wcompute.com, wcompute.wordpress.com, and wcompute.blog

Posted in Uncategorized | 1 Comment

Avamar Administrator 6.1 RMAN backup agent not showing instance on client

If you are running an Avamar RMAN backup on Linux Oracle Enterprise you may need to run avregister to re-activate the client with the Administrator server.

Run from /usr/local/avamar/bin

# ./avregister

=== Client Registration and Activation

This script will register and activate the client with the Administrator server.

Enter the Administrator server address (DNS text name or numeric IP address, DNS name preferred): avamarserver.mydomainx.mydomain.com

The following result will take place after prompting for the Administrator server

Enter the Avamar server domain [clients]:

avagent.d Info: Stopping Avamar Client Agent (avagent)… avagent.d

Info: Client Agent stopped.                      [  OK  ]

avagent Info <5008>: Logging to /usr/local/avamar/var/avagent.log avagent.d

Info: Client activated successfully.             [  OK  ]

avagent Info <5008>: Logging to /usr/local/avamar/var/avagent.log avagent Info <5417>: daemonized as process id 13046 avagent.d Info:

Client Agent started.                      [  OK  ] Registration Complete.

Posted in Oracle | Leave a comment

SQL Server: A simple PowerShell script for my crashing log reader agent used in replication.

Over the past few weeks one of my production servers involved in replication has had the log reader agent just stop for no apparent reason. Currently upgrading from 2005 sp2 is not an option on this particular server. So to buy myself some time to troubleshoot why this event is happening, I decided to revisit monitoring processes, and how I get alerted when they don’t exist. If you were to ask how I would have written something like this about a year ago, I would have most likely whipped out some C# code in SSIS. But today I wanted something monitored quickly, and did not want to have to deploy the solution to our production servers.

My criteria: If the log reader agent process is not present I want a notification right away. The process name I need to monitor is “logread”.

Here is the simple powershell script I used on a remote server.

   1: $process=get-process -computer yourserver logread -ErrorAction "silentlycontinue" 

   2: if ($process) {

   3:            "exists"

   4:     }

   5:     else     { 


   7:         $CredUser = "user@domain.com"


   9:         $EmailFrom = "user@domain.com"

  10:         $EmailTo = "dba@domain.com" 

  11:         $Subject = "SERVERX Logreader is not running"

  12:         $Body = "Please check the replication log reader on ServerX" 

  13:         $SMTPServer = "mail.domain.com" 

  14:         $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25) 

  15:         #$SMTPClient.EnableSsl = $true 

  16:         $SMTPClient.Credentials = New-Object System.Net.NetworkCredential($CredUser, $CredPassword); 

  17:         $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)



  20:     }

It was as simple as pasting this into a job step on the sql server agent on another 2008 instance. One simple line to retrieve if the process is running or not. Can it get any more simple than this?

PowerShell is quickly becoming my scripting language of choice. Now to figure-out why it suddenly stops, that’s going to be the real difficulty I face.

Update to script above. My SmtpServer is not on port 25. So sending mail with the Net.Mail.SmtpClient was the only way for me to access the Smtp server as –Port option did not seem to work.

As far as the log reader agent issue is concerned. I have alerts enabled on the agent, and yes I have the  -Continuous switch when launching. There are no errors in the eventlog, or on the agent itself. The process just stops every few days. So if you are also running on Sql Server 2005 sp2, and have seen this behavior please feel free to share where my problem may reside.

As Always, “DBA’s don’t fly by the seat of you pants!”

Posted in Sql Server | Leave a comment

SQL Saturday #156: September 15th

Reminder to all. Please register for SQL Saturday #156 in East Greenwich, RI.


The schedule is here:


What a great opportunity to network and learn from the best.

Hope to see you there!!!

As always DBA’s don’t fly by the seat of your pants.

Posted in Sql Server | Leave a comment