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
  (“LJPROD”) export ORACLE_UNQNAME=”LJPROD” ;;
  (“LJSTAGE”) export ORACLE_UNQNAME=”LJSTAGE” ;;
  esac

 

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.

image

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.

/usr/local/avamar

/var/avamar

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:

–pidname=Oracle
–pidnum=1002        #(specific to OS see Avamar 7.3 user guide for number)
–logfile=/home/oracle/logs/backup_stage.log
–vardir=/usr/local/avamar/var
–id=oracle               #(name of user account on avamar server)
–bindir=/usr/local/avamar/bin
–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:

–no_of_channels=1
–operation=backup

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;
}
exit

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

LJSTAGE

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

#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=viradbstage.LoJackOne.LoJack.com
export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’

export ORACLE_SID=LJSTAGE
export ORACLE_UNQNAME=LJSTAGE1
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

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     { 

   6:  

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

   8:  

   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)

  18:      

  19:                       

  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.

THIS SATURDAY!!!!!!

The schedule is here:

http://www.sqlsaturday.com/156/schedule.aspx

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

SQL Server: Learning StreamInsight using Arduino with a custom Input Adapter.

Microsoft released StreamInsight with SQL Server 2008R2, and I’ve been putting the learning curve off for quite a long time. So this blog post is my attempt to learn some of its basic capabilities. What better way to learn this technology than to do it in a public forum where it can be reviewed by the experts to offer even more advice on my huge learning curve using this development platform.

StreamInsight provides a powerful platform for developing and deploying complex event processing (CEP) applications. CEP is a technology for high-throughput, low-latency processing of event streams. Typical event stream sources include data from manufacturing applications, financial trading applications, Web analytics, or operational analytics. The StreamInsight stream processing architecture and the familiar .NET-based development platform enable developers to quickly implement robust and highly efficient event processing applications

image

My Ardunio example is based on the following being installed:

Visual Studio 10

StreamInsight 1.2 installed (licensing tied to SQL Server 2008R2).  this is the older version used in this example.

Ardunio Uno or Mega 2560 R3. With the proper USB cable to upload your program (Sketch) to the controller.

Once you have installed StreamInsight a good example to get started with is on the codeplex web site named: “StreamInsight Events to Windows Form”

 http://streaminsight.codeplex.com/releases/view/64805

This example shows six different LINQ queries to run against a typed point input adapter using a fake data source. For the last query in the example the output adapter binds to a web service, and posts the results real time to a chart in a windows forms application. Having this visual representation was the catalyst for me to take the next step and try to replace the fake data source with an Ardunio controller data stream.

The Ardunio communicates to the outside world using a serial port with a maximum speed of 115,200 bits/sec. The Uno can be purchased at Radio Shack, or your favorite electronic hobby store for about $35, and the more powerful Mega 2560 R3 is about $69. Being rich I used the 2560 R3 for this project.

Using the same StreamInsight design structure as the codeplex example, I rewrote the solution, and changed the typed point Input Adapter to have a slightly different payload.

public class ArduinoComEventType
   {
     public   string RequestType { get; set; }
     public   double RowNum { get; set; }
     public   float ivalue { get; set; }
   }

Like the codeplex example I also used RequestType. But instead of representing “Orders” my RequestType is just a three generic ouputs “Output1”,”Output2”,”Output3”. These three different events that we are monitoring are generated from the Ardunio controller.

The RowNum , and ivalue numbers represent changes to values generated from the controller when the RequestType we are interested in is observed on the communication port.

The ArdunioStreamInsight solution used is similar to the codeplex solution not by accident. The rewrite of the example solution offered me the shortest learning path for my solution. The four projects contained in the solution are as follows:

image

The above Arduino project is the windows form for the chart control  (“UI in codeplex example”)

image

The DataPublisher is where the Input Adapter is defined to monitor the COM port input buffer from the Ardunio controller.

image

The ArdunioQuery is the console application which connects to the streaminsight server engine, and allows us to do the LINQ queries against our input stream. This is set as the default project in the solution. Which means you need to manually start the ArdunioStreamInsight form application prior to running the console application. This also assumes you open the soap listener using the listen button on the form. Also as all projects using http endpoints you will have to make sure your namespace endpoints are allowed on your machine by using netsh with your proper permissions.

The two namespaces is first your StreamInsight server endpoint http://localhost:80/StreamInsight/DEFAULT/ 

and then target adapter service endpoint http://+8733/ArduinoStreamInsight/FeedbackListenerService.

image

Lastly the WebOutputAdapter converts the CEP events into XML using a simple XSLT transformation. Then sends the transformed CEP events over the web service to the form application and the final destination the chart control.

On the Ardunio side we have uploaded a very simple sketch to the controller:

The Arduino Sketch used is as follows:

int x = 0;
int row = 0;
void setup() {
  Serial.begin(115200); // opens serial port, sets data rate to 9600 bps

}

void loop() {
  Serial.print("Output1"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(sin(x*PI/180));
  Serial.println(",End");
  Serial.print("Output2"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(cos(x*PI/180));
  Serial.println(",End");
  Serial.print("Output3"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(2*sin(x*PI/180));
  Serial.println(",End");

  row++;
  x++;
  if (row > 360) 
  {
    row=0;

  }
  //delay(100);
}

As you can see from the sketch it simply outputs a sine wave, cosine wave, and another sine wave which is amplified by a factor of 2. The sketch is simple, but if you wanted to use StreamInsight on a robot sensor, or other project the concept would be similar. I chose the math functions because having a controlled source yields a better understanding to what is happening when applying our LINQ queries to the stream. And after all this is about learning StreamInsight so for me it means keeping it simple.

Putting the delay loop into the script helps make the wave cycles visible on the chart. At higher frequencies the waves look like an RF carrier wave instead of the common sine wave we are use too.

The following code is the DataPublisher which was changed to use a COM port from the fake datasource in the original codeplex example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO.Ports;
using System.Threading;

namespace DataPublisher
{
     public class ArduinoComEventType
    {
      public   string RequestType { get; set; }
      public   double RowNum { get; set; }
      public   float ivalue { get; set; }
    }

   public class ArduinoComDataSource
    {

    static bool _continue;

    static SerialPort comm = new SerialPort("COM5", 115200, Parity.None, 8, StopBits.One);

    static ArduinoComEventType oevent = new ArduinoComEventType();

    public ArduinoComDataSource()
    {
        //how long to wait between generated messages
        this.period = 1;
        this.variance = 0;

        // Initalize timer.
        this.timer = new System.Timers.Timer(period);
        this.timer.Elapsed += new System.Timers.ElapsedEventHandler(this.ArduinoData);
        this.timer.AutoReset = true;

        comm.Open();
        Thread readThread = new Thread(Read);
        _continue = true;
        readThread.Start();

    }

    public static void Read()
    {
        string message = "";
        while (_continue)
        {
            try
            {
                message = "";  
              message = comm.ReadLine();
            //  Console.WriteLine(message);

              int startpos = 0;
              int endpos=0;

               if (message.Contains("O") && message.Contains(","))
               {
                   startpos = 0;
                   endpos = 0;

                   if (message.Contains("Output1"))
                   {
                       double r=0;
                       float i=0;
                        oevent.RequestType = "Output1";
                       if ( message.Contains("Row=") && message.Contains("Value=")) {

                           startpos = message.IndexOf("Row=") + 4 ;
                           endpos = message.IndexOf("Value") -1 ;

                        Double.TryParse(message.Substring(startpos, endpos-startpos), out r);
                        oevent.RowNum = r;
                       }
                        if (message.Contains("Value=") && message.Contains("End"))
                        {
                            startpos = message.IndexOf("Value=") + 6;
                            endpos = message.IndexOf("End") -1; 

                            float.TryParse(message.Substring(startpos, endpos -startpos), out i);
                            oevent.ivalue = i;
                        }

                   //    Console.WriteLine(message);
                   }

                   if (message.Contains("Output2"))
                   {
                       double r = 0;
                       float i = 0;
                       oevent.RequestType = "Output2";
                       if (message.Contains("Row=") && message.Contains("Value"))
                       {

                           startpos = message.IndexOf("Row=") + 4;
                           endpos = message.IndexOf("Value") -1;

                           Double.TryParse(message.Substring(startpos, endpos - startpos), out r);

                        oevent.RowNum = r; 
                       }
                       if (message.Contains("Value=") && message.Contains("End"))
                       {
                           startpos = message.IndexOf("Value=") + 6 ;
                            endpos = message.IndexOf("End") -1;

                           float.TryParse(message.Substring(startpos, endpos - startpos), out i);

                            oevent.ivalue = i;
                       } 

                  //     Console.WriteLine(message);
                   }

                   if (message.Contains("Output3"))
                   {
                       double r = 0;
                       float i = 0;
                       oevent.RequestType = "Output3";
                       if (message.Contains("Row=") && message.Contains("Value"))
                       {
                            startpos = message.IndexOf("Row=") + 4;
                           endpos = message.IndexOf("Value") -1;

                           Double.TryParse(message.Substring(startpos, endpos - startpos), out r);

                           oevent.RowNum = r;
                       }
                       if (message.Contains("Value=") && message.Contains("End"))
                       {
                          startpos = message.IndexOf("Value=") + 6;
                            endpos = message.IndexOf("End") -1;

                           float.TryParse(message.Substring(startpos, endpos - startpos), out i);

                            oevent.ivalue = i;
                       }

                 //      Console.WriteLine(message);
                   }

               }

            }
            catch (TimeoutException) {  }
        }
    }

    /// <summary>
    /// Generates data and calls the callback
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void ArduinoData(object sender,System.Timers.ElapsedEventArgs e)
    {
        lock (this.thisLock)
            {
                // Set a new timer interval.
                this.timer.Interval = Math.Max(new Random().Next((int)this.period - (int)this.variance,
                                                                 (int)this.period + (int)this.variance), 1);

            // push the data into the callback, with current time.
            this.callback( oevent, DateTime.Now);
        }
    }

          /// <summary>
        /// Period at which to produce a new data item.
        /// </summary>
        private uint period;

        /// <summary>
        /// Maximum variance to deviate from the exact period.
        /// </summary>
        private int variance;

        /// <summary>
        /// Timer used to trigger the generation of new data items.
        /// </summary>
        private System.Timers.Timer timer;

        /// <summary>
        /// Mutex for the timer-triggered data generation.
        /// </summary>
        private object thisLock = new object();

    /// <summary>
    /// Sets the callback.
    /// </summary>
    public EventCallback Callback
    {
        set { this.callback = value; }
    }

    /// <summary>
    /// Starts the data generation.
    /// </summary>
    public void Start()
    {
         this.timer.Enabled = true;
    }

    /// <summary>
    /// Stops the data generation.
    /// </summary>
    public void Stop()
    {
        this.timer.Enabled = false;
    }

    /// <summary>
    /// Callback object.
    /// </summary>
    private EventCallback callback;

    /// <summary>
    /// Callback type to push new data to.
    /// </summary>
    /// <param name="data">New data item to push to the callback.</param>
    /// <param name="timestamp">Timestamp of the data.</param>
    public delegate void EventCallback(ArduinoComEventType data, DateTime timestamp);

    }

}

Rather than going into the entire four projects code line by line  I will just briefly explain some of the overall changes I made. And then I will finish up with a couple of example queries, and their resulting appearance on the chart control.

To use the chart control for my ivalue data. I modified the XSLT to match the three CEP events , and then adjusted the  GridUpdater callback on the form receiver side to accept these values .  The chart series was set to use ivalue for the y data value for each of the three Output series.

Now for the fun part. Lets see what results we get from all this work.

First lets look at all of the payload events being graphed:

image

The LINQ query was for all events:

var query1 = from e in inputStream
                       select e;

image

The LINQ query was a simple filter of events where ivalue > .5

var query1 = from e in inputStream
                               .Where(e => (e.ivalue > .5))

On the above query I also decreased the delay on the Arduino side from 500 ms to 50 ms  so you can see this shortened our waves period overall cycle (indicated by an increase in frequency).

This stuff is pretty cool!!!!

image

The following LINQ query generated the number of events on Ouput2 over a 10 second interval.

long PeriodAsTicks = TimeSpan.TicksPerSecond * 10;

var query5 = from e in inputStream
                               .Where(f => f.RequestType == “Output2”)
                               .AlterEventDuration(f => TimeSpan.FromTicks(PeriodAsTicks – (f.StartTime.Ticks % PeriodAsTicks)))
                               .SnapshotWindow(SnapshotWindowOutputPolicy.Clip)
                                 select new
                                 {   RequestType = “Output2”,
                                     ivalue = e.Count()
                                 };

On the above query I also decreased the delay on the Arduino side from 500 ms to 50 ms

image

The above LINQ query was the same as the previous one, but I removed the delay on the Arduino side from 50ms to no delay. So it shows we average 200 Output2 events every 10 seconds.

This is only the beginning of my investigation of StreamInsight.

The possibilities for this technology seem endless, I could easily send feedback to the Arduino based on an particular event happening. Perhaps every time a sensor reached a threshold, I could alter the servo to change the response of my robot. Currently I don’t have an XBee module, which would allow wireless feedback to the controller based on these events. Although, I probably have enough parts lying around to get started on designing my own from scratch. Just joking a little, I’d rather buy that then attempt it from scratch. The days of recreating circuits from scratch that already exist are over for me. Unless its just flashing an LED or something.

If your interested in playing with this solution please leave a message on my site.

Hope you enjoyed this. As always: DBA’s don’t fly by the seat of your pants!

ArduinoStreamInsight

 

Posted in Sql Server | 3 Comments