Oracle 11g on Oracle Linux

Intro
So here's the thing...

I decided to have a more detailed look at Oracle 11g. I like the Oracle relational databases, really I do. They are a mature, stable, scalable database which is feature rich. It can run on Linux, Unix, Windows and even a Mac if you want. However, my focus in this blog is the installation of the database on Linux. Oracle's own in fact...

So Oracle offer a "distro" of Linux. Sounds good as a concept, Oracle offering an OS on which you can install their database and all. However, if you are a newbie to Linux and/or Oracle you gonna need to set aside quite a bit of time for this. (By quite a bit, read: "a lot!")

Now my issue isn't so much the number of settings required during installation, it's a complex piece of software, nor the planning on how it should be or where it should be installed. All that is fine. I understand that. It's just that the actual act of installing Oracle 11g on Oracle Linux should be, well, seamless should it not?

Grumbles
I'm sorry, the fact that I installed Oracle linux with practically all package options selected (didn't select the KDE development ones) yet still didn't get the "oracleasm" packages installed mystifies me. The fact that I didn't get prompted with a question asking "are you intending to use this installation as a platform for another Oracle product" and then providing me with a list, is a little concerning. Some way of being able to configure the installation of the OS based on what I want to do later with other Oracle products seems somewhat natural, yet it is lacking. But what really sends me down a rabbit hole of despair, is the fact that of the 3 "oracleasm" packages required for using Automated Storage Management, one of them is not included in any of the 5 disks of Oracle Enterprise 5 ISO images.

I cannot help but wonder if I am just being picky. It's just that it seems natural to me to include all requirements for a successful installation in the installation software. In order to use ASM I need to have already installed the ASM libraries, yet to do that I need to go to OTN and download the missing package. To make matters worse, the package seems to have been released a long time ago yet the ISO images for Oracle Linux Enterprise 5 have not been updated to include them.

Then, to top it all off, once you actually get the dang thing installed, (i.e. the OS and 11g) there are no installations of start up scripts or anything. Nope. Nada, bupkiss, didley-squat! You want Oracle 11g to start up automatically you darn well get to write them yourself or find someone else's on the web and use that. As it happens, using someone else's is probably natural seeing as by the time you've actually managed to install the software you are probably on to your 2nd or 3rd blog, the writers of which have probably included theirs.

Add to this that if you are using ASM without RAC, you need to include the marking of your RAW disks for oracle ASM in your start up scripts in order to get the ASM service to know about else the Oracle database won't start. (Maybe this is meant to be included in a config file somewhere but I couldn't find any documentation on that. Some blogs talk about using "rawdevices" but that has been deprecated now.)

Finally
The long and short of all of this is that I can fully understand now, why someone would install Microsoft SQL Server for a demo etc. You could have Windows installed, the SQL Server software installed and configured and the demo built whilst you are still faffing about trying to get everything you need for the Oracle installation.

What a shame... In my opinion, for what its worth, the Oracle RDBMS is far better and complete in its offering than SQL Server. I guess I mean it is more mature. But lets face it, money talks, and if at a fraction of the price you can have a database and have it installed dancing on one leg whistling Dixie in the fraction of the time of something else, why wouldn't you?

Links I used:

1. Install Oracle 11g for RAC
2. Create RAW devices using LVM

Custom SSIS Control Flow Task

Okay, so if you're reading this chances are you're looking to create a custom SSIS task using C# to do your bidding. Either that or you've just stumbled across this blog on your tea break whilst surfing the web. Either way, you have my sympathies. ;-)

So, why this entry? Well, one morning I woke up and suddenly thought "how cool to write a custom SSIS task that will process Analysis Services objects as defined by information in a relational database." Now I know I can use a "for-loop" task along with an Analysis Services DDL task but (a) where's the challenge in that (b) I want access to the lower level calls and information that SSAS returns and (c) how does that help me understand the SSIS internals I ask you?

Now as it happens you may, like I did, have the dismal mis-conception that it should be fairly easy to create a "nice" custom task, what with all the information in BOL and on the internet. Alright, maybe that's a bit harsh. To create the task itself isn't really that hard I suppose, but it's the stuff around it that I find really annoying and time consuming. Things that really should be easy, aren't. Stuff that you have to do before you even get to build the task is just badly documented or scattered around, not in one place. In some cases there's even confusion amongst developers and posts are clearly incorrect. Only, it's only clearly incorrect after you know the answer.

So, in a nutshell, here are my thoughts and findings. If you are a seasoned C# developer then maybe these things are trivial, in which case this blog isn't for you. However, for the rest of us:

1. The SSIS model, as compared to DTS, is actually okay. Microsoft appear to have thought about this somewhat and came up the logic of allowing the separation of the UI from the task, such that at runtime the amount of code to be loaded is reduced etc. Good idea! They even specify that that is the best practice. <rant><sarcasm>Well, thank goodness for Microsoft's life saving documentation then!</sarcasm>So why is it, that if according to Microsoft's own recommendations for splitting the UI from the task, does their own "definitive guide", and by that I mean the sample to which we are continually referred, for creating custom tasks have the UI and the task in one assembly? Just how is that helpful?</rant>
   
   This reference to the Increment Task includes the following statement:
   "A separate assembly for the user interface is recommended for ease of deployment because it allows you to update and redeploy the connection manager or its user interface independently."
   And this Microsoft link states:
   "A task and its user interface are separate entities. They should be implemented in separate assemblies to reduce localization, deployment, and maintenance work."
   Further "Increment Task" sample links:
   Creating a Custom Increment Task
   or
   Creating a Custom Increment Task
   
2. So you would like to use your own icon for your task eh? Pity lots of documentation out their is either grossly inaccurate or totally out of date.
3. Resizing list boxes smoothly by keeping it in sync. with a PropertyGrid when the form is resized, like the Analysis Services DDL Task, isn't a particularly intuitive thing to achieve.
   
4. Then there's the PropertyGrid. It seems to me that with the ".net" languages Microsoft discovered the concept of reflection and like XML, it must be used everywhere! Now don't get me wrong, I'm not saying that they didn't know about it before. I mean Delphi (not MS I know) had RTTI etc. so it's a general concept but it just seems that everything uses reflection, including the PropertyGrid.
   
5. Anyone found any decent documentation on using ConnectionManagers? So we have a ConnectionManager that can define a connection of varying types which is cool. Very handy. The connections can be OLE DB, ADO, ADO.NET, MSOLAP.2 (or 3) etc. Yet most of the documentation seems to imply that you can just call AcquireConnection, cast the object returned to an object of type SqlServer and "Bob's your uncle". Yeeeeesssss, if only.
   

Those are the ones that have caused me the most grief so far. I'll add more as and when I find them, but in the meantime, to save you the pain, here are my hints and tips to prevent this from happening to you, starting with the concept of keeping the task and UI separate.


Separating the UI from the task
I reckon that this is a solid approach and one which should be followed as a matter of course. Once you've worked out how it all works it actually wouldn't be that hard to knock up some boilerplate code or a project set at a starting point for all future tasks. The idea is as follows:

• Identify all the properties about the task that you need to capture from the user. Things like connection types and connection managers, starting values and filter conditions etc. Once you know what they are, you're pretty much set.
  
  
• In the task (i.e. the class that extends Task and contains the methods InitializeTask, Validate and Execute), create private, class level variables of the appropriate type for each of the properties identified above. Naturally include the DtsTaskAttribute required to link the task to the UI as described in all documentation and the samples etc.
  
  


• In the same class, create properties for the task (i.e. "methods" using get and set) allowing each of the required variables identified above to be set and retrieved.



• Create a new assembly for the UI. Add a class that extends Form, and include the class identified by the DTSAttribute annotating the Task that is to be called to instantiate the UI. The constructor for the form should, at the very least, require the TaskHost to be supplied on instantiation in order to allow the retrieval and setting of the Task's properties. Note that there is nothing really special here. Just follow the standard documentation regarding building a UI for your task but do so in a separate assembly.
  

UI Class

UI Class Methods (Initialize method repeated here)
Note the "GetView" method that passes the TaskHost object into the form's constructor.


• In the constructor for the form, record the TaskHost variable passed into the constructor in a class level global variable of type TaskHost for ease of access.



• Now to save any changes made in the UI back to the Task's property methods, use code similar to the following, where "g_TaskHost" is the global variable for the TaskHost object, "SQLConnectionType" is the property created on the Task and "ADO.NET" is the value to which it should be set.
  g_TaskHost.Properties["SQLConnectionType"].SetValue
  (g_TaskHost, "ADO.NET");


• To load the values from the Task for display in the UI, use code similar to the following, using the same conventions as described in the point immediately above.
  g_TaskHost.Properties["SQLConnectionType"].GetValue
  (g_TaskHost).ToString();

Custom Icon
I have seen a lot of documentation on the web regarding the value for "IconResource" as it appears in the DtsTaskAttribute and what it should be. They state things like the name of the icon must be "MyTaskIcon.ico" and/or the resource must be fully qualified with the namespace etc. This may or may not have been correct in beta versions etc. but as it stands today you can call the icon whatever you want and the qualified name must be the name of the assembly in which the icon resides "dot" the name of the file.

See: DtsTaskAttribute.IconResource

So, if your assembly is called: "foo.bar.dll" using a namespace of "me.baz" and the name of your icon is "Designer.ico", then the correct value for "IconResource" is: foo.bar.Designer.ico


Resize List Boxes in sync. with a PropertyGrid
Set the ListBox property "IntegralHeight" to "False". That's it!


Using PropertyGrids
Hmmmm, this is a section all on its own. If anyone ever actually reads this blog and would like some information on this I'll post more but beware of what you ask for...


ConnectionManagers
I've yet to follow this through myself. Currently I'm coding my task specifically around the ADO.NET connection type which allows me to cast the connection to "SqlServer". I will support more connection types later.

My feelings thus far are that to accommodate different connection types in a way that minimizes code rework, one could either create a class that encapsulates the nuances of the different connection types, providing common functionality, or use method overloading in some way.



Well that's it. If you got this far either pat yourself on the back for your perseverance or shake your head in disgust at how geeky you really are. :-)

'Till next time...
Phil