A colleague who used to be a DBA but now is into design and development was telling me the other day how in a previous company the DBAs always congregated around the coffee machine. They were known as ‘those guys at the coffee machine’. A universal trait of DBAs seems to be that we do not like to work harder and therefore have to work smarter (a trait shared with sales people) and keep whatever we do to get things going under wraps!

We do often work strange hours though setting things up. When things go haywire too where do people congregate first? My case rests, we do have a value. I do not like rude and impatient DBAs although the people we sometimes have to deal with do drive me to tears! Every time I loose my temper at an ignorant user I do berate myself for it, and have more coffee. A project manager might come by and say “We need another instance, how long will it take?”. We will groan and say a few days and the manager would saunter off to update his Microsoft Project, we would dig into our scratchbag of scripts, do a bit of a search and replace with vi or sed, fire it off and head for the coffee machine while the script does all the hard work. “How’s my new instance going?” the manager would ask a day or so later, “No, it’s going fine, nearly done” while filling up again, at the coffee machine. Meantime it was done an hour after he asked! But we need the coffee time to recover.

Like all DBAs I have my own collection that I take with me from site to site and grow it as I go along. We are also the princes of thieves too, never believe that a DBA has written them all himself. One grabs stuff all over the place, Internet, books, looking over colleague’s shoulders, favourite is stealing them from the SGA after a nice utility has run. Some utilities like Toad even allow you to spool the SQL to screen, very nice. These get copied and pasted and cleaned up a bit. Then there is the Oracle documentation too. I spend a lot of time in the documentation and on Oracle’s Metalink support site. I do miss the nice printed original manuals but have adapted to PDfs and HTML documentation, great for copy and paste!

As I have said before Oracle Retail has pretty rudimentary installation scripts so I have put together a set of scripts to create database instances, build the database schemas, put together the batch Proc source code from the base distribution as well as all the patches, the same for Oracle Forms and Reports, etc. Of course I have the required set of database SQL scripts too, ever growing and customised for particular clients. I have taken to storing them in a CVS repository along with other application code I like to keep there too. CVS is an open source free version control system that works very well on Unix and even has a Windows-based GUI so that the sources can be updated from a PC too. It is easy to get going and not over-bearing in complexity and is used by a huge community on the Internet so support and advice abounds. It’s great for keeping source in and extracting to test and production servers. It has one command (cvs) with many arguments.

I have two piece da resistance shell scripts. The first is a menu based shell script I put together because developers were bugging me too much for repetitive stuff. I like enabling them wherever I can so that I can concentrate on the interesting stuff, and coffee. This script shows database details like available space, sessions, cache hit ratios, etc. It has a huge user section where they can create public synonyms and grants for our Oracle Retail schemas, even kill a session, etc. It’s all menu based and I call it from a Unix user .profile. Whenever a new requirement comes up I will add it in if I deem it necessary. The Oracle user is an ops$ login, authenticated by the OS with all the required grants. The other script is a script that runs Oracle Retail batch programs. It acts as a wrapper script and is written to pick up all the Oracle Retail batch restart information required and does the necessary processing. Oracle Retail uses Proc programs and a rudimentary but effective method for multi-threading. They have a set of restart tables that contain all the batch program details like number of threads, etc. These are read at run time and if a program has ten threads it needs to be run ten times concurrently and each thread picks up it’s own dataset. The script puts ten copies into the background and waits for them to finish. This script gets called with all the relevant arguments for whatever batch scheduler the client prefers, as long as it’s not cron! I use cron for rudimentary scripts but it does not compare with a proper multi-server based scheduler with a proper GUI that can be monitored by operators. It needs colours for them and a set of instructions, green is ok and red is a problem, go and hunt down the DBA at the coffee machine! But seriously speaking I am a firm believer in a good batch scheduler. These two scripts have served me well and running in production at the six sites I have done Oracle Retail work on.

My work is a balance of ‘green screen’ telnet and GUI work. I do not have much faith in Oracle’s Enterprise Manager but it is growing in prominence and capabilities with each version. I have had a very knowledgeable colleague come to grief with the 10G Application Server web version by accidentally hitting the wrong button and wiping out the Forms Server which resulted in an 11pm Saturday night callout for me, after frequent warnings not to do it. I love simplicity and very few tools beat Toad for that. I introduced it to the first Oracle Retail project I worked on when it was still freeware and the developers and consultants soon became inseparable from it. Unfortunately the developer realised he had a good thing going and after having his user community debug it for free sold it to a software company but good for him for cashing in on a good product. I hope he earns a good royalty portion from each expensive sale. Wherever I go a recommend that the client purchase a few licences if they do not have it already. Oracle’s own SQL Developer is not bad and is free but is not quite as feature rich. GUIs like Toad are nice for database browsing and monitoring and for debugging PL/SQL. On the other hand they are not always available and not suited for everything. Shell and SQL scripts are quick and dirty and easy to develop and modify. Terminal emulation protocols like telnet are the bread and butter tools of the Unix and Oracle community and my personal favourite is Putty, also free.

It’s time for coffee. If only we had a Starbucks across the road!

This entry was posted on Tuesday, June 19th, 2007 at 1:21 pm and is filed under - Global Oracle Contractors Network, Oracle Database, Industry Solutions. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.