Sunday, April 08, 2007

An “ideal database health check” – what does that really mean?

A good friend of mine, a senior Oracle DBA, recently looked at the automated database health check report we provide our customers and pointed out an interesting fact. He said "out of the 40 or so metrics you guys look at, at least 10 are outdated!" When I asked him to expound further, he responded “well, your report is still looking at cache hit ratios and such. The prevailing sentiment in the DBA community (especially the Oracle DBA community) is that hit ratios are useless. What truly matters are the wait stats and trace output.”

Now, does it really? I queried about a dozen of my other DBA friends on what they thought and unfortunately, it was a mixed message. Hours of Google searches showed a disappointing trend: the term “health check” is somewhat abused and there are no two similar health check utilities out there. Every DBA group that uses one seems to kinda make one up on the fly. I wish there was a standard health check that one could rely on.

Anyway, my limited research makes me wonder – why isn’t there an easily available standard health check? And more importantly, what really constitutes an “ideal” health check? Here are my two cents on the topic:

An ideal health check should provide an overview of a database’s stability across three major areas for consideration:

  • Availability
  • Performance
  • Scalability

Now, one could argue that all 3 of the above areas just point to one more area: performance. However from a practical standpoint, I would like to treat these 3 areas as distinct. For instance, in my mind, the well-being of a hot standby maintained via a Data Guard physical configuration is required for ensuring high availability, not necessarily for performance; whereas, ensuring sufficient buffer caches have been configured helps facilitate optimal performance moreso than availability. Also, in certain cases, performance and availability related actions may be at odds with one another. For instance, one may configure a redo-log switch every 10 minutes to ensure the primary database and hot standby are within 10-15 minutes of each other (helps availability), whereas the same action may result in higher I/O and negatively impact performance (especially if the system is already I/O saturated).

So, going back to the “ideal healthcheck”, one should ensure that there are no existing or upcoming issues that are impeding or are likely to impede any of these three areas. Below is a “top 40” list of things that would help ensure that is indeed the case. Comments are placed in-line, next to the statistic name/type, in italics.

Category: Availability
1. Database space (Should consider both database space and OS space; would be ideal to also consider growth trends.)
2. Archive log space
3. Dump area space (bdump, cdump, adump, udump, etc.)
4. Archive logs being generated as per SLA (Note: This requires the health check utility to have an understanding of the SLA pertaining to standby database upkeep.)
5. Archive logs being propagated as per SLA
6. Snapshot/Materialized view status
7. Status of DBMS Jobs
8. Replication collisions
9. Backup status
10. Online redo logs multiplexed (On different mount-points.)
11. Control file multiplexed (On different mount-points.)
12. Misc errors (potential bugs) in alert.log

Category: Performance
13. Disparate segment types (tables, indexes, etc.) in same tablespace
14. SYSTEM tablespace being granted as default or temp tablespace
15. Temporary tablespace not being a true temp tablespace
16. Deadlock related errors in alert.log
17. Non-symmetric segments or non-equi sized extents in tablespace (For dictionary managed tablespaces.)
18. Invalid objects
19. Any event that incurred a wait over X seconds (“X” to be defined by user during healthcheck report execution. Default value could be 5 seconds. Obviously, for this value to be available, some kind of stats recording mechanism needs to be in place. In our case, Data Palette is used to collect these stats so the health check report can query the Data Palette repository for wait events and corresponding durations.)
20. Hit ratios: DB buffer cache, redo log, SQL area, dictionary/row cache, etc. (While there is a mixed opinion on whether these are useful or not, I like to include them for DBAs that do rely on them to identify whether any memory shortage exists in the database instance and adjust the related resource(s) accordingly. While I do have an opinion on this matter, my goal is not to argue whether this stat is useful or not, instead, it’s to provide them to people that need them (and there are quite a few folks that still value hit ratios.)
21. I/O / disk busy (I/O stats, at the OS and database levels.)
22. CPU load average or queue size
23. RAM usage
24. Swap space usage
25. Network bandwidth usage (Input errors, output errors, queue size, collisions, etc.)
26. Multi-threaded settings (Servers, dispatchers, circuits, etc.)
27. RAC related statistics (False pings, cache fusion and interconnect traffic, etc. – based on the Oracle version.)

Category: Scalability (Note: For ensuring there are no scalability related issues, the health check generating mechanism ideally should be able to relate to current resource consumption trends and apply predictive algorithms to discern whether there will be contention or shortfall. In the absence of such predictive capabilities, a basic health check routine can still use thresholds to determine whether a resource is close to being depleted.)

28. Sessions
29. Processes
30. Multi-threaded resources (dispatchers, servers, circuits, etc.)
31. Disk Space
32. Memory structures (locks, latches, semaphores, etc.)
33. I/O
34. CPU
35. RAM
36. Swap space
37. Network bandwidth
38. RAC related statistics (False pings, cache fusion and interconnect traffic, etc. – based on the Oracle version.)
39. Understanding system resources consumed by non-DB processes running on the same server/domain (3rd party applications such as ETL jobs, webservers, app servers, etc.)
40. Understanding system resources consumed by DB-related processes running outside their normal scheduled window (Applications such as backup processes, archive log propagation, monitoring (OEM) agents, etc. This requires the health check utility to know which processes are related to the database and their normal execution time/frequency.)

The health check utility can show the above areas in red, yellow or green depending on whether any statistics needs urgent attention (red), needs review (yellow) or is healthy (green). Accordingly, the health of the database can be numerically quantified based on where each of the statistics show up (a statistic being in green status earns it 1 point, a yellow earns it 0.5 and a red earns it 0).

The above list can be expanded to accommodate additional configurations such as a custom standby setup or database audit requirements, and statistics pertaining to more complex environments such as RAC and Advanced Replication. Even entire categories can be added as appropriate. For example, “Security” would be a good category to add with statistics pertaining to user related information including account lock outs, and password change status, tablespace quotas, audit usage, virtual private database configuration, and so on.

Database and instance level health checks need to be shown separate, regardless of RAC. Things that need to show up at the database level would be statistics such as database space, etc., whereas statistics such as memory settings and hit ratios would be in the Instance health check report.

Lastly, it would be nice if the health check could include a recommendation on how to resolve a statistic that’s showing up in yellow or red, or even better, offer a link to an automated standard operating procedure that would help fix the situation.

I have meant this to be an open list. So any thoughts from the community on further augmenting (or better yet, standardizing) this list would be appreciated.


Tuesday, February 06, 2007

Using Decision Automation in Disaster Recovery

DM Review recently published my article on “Using Decision Automation in Database Administration” (http://www.datawarehouse.com/article/?articleId=6876). Right after that, I get this interesting question from a reader enquiring about leveraging decision automation in disaster recovery (DR). Specifically, she asks “a good application I can think of for decision automation is disaster recovery. Can you outline a model for how one could go about implementing it?”. I wanted to share my response with everyone. So here I go…

Once the right DR model is designed, automation in general, is invaluable in rolling it out and maintaining it; ensuring it works as per spec ("spec" being the enterprise service level requirements pertaining to uptime, failover and performance). However the one area that decision automation specifically is suited for is establishing a centralized command / control mechanism to allow failover to occur under the right circumstances, and in the process, dealing with false alarms.

The foremost (and most natural) reaction of many IT administrators when they face an outage (even a simple disk-array failure, much less a true disaster) is panic. During this state, the last thing you want them to do is think on their feet to figure out whether to fail over to the DR system and if so, go about making that happen manually. Especially, if there are other unanticipated hiccups in the process (which happen more often than not at that very time, given Murphy's law), that usually results in service level violations or worse, a failed DR initiative.


To better appreciate this, imagine a nation’s nuclear arsenal being subject to manual controls and individual whims rather than a fully automated and centralized command / control system. If relying on a human being (be it the president or prime minister or your local fanatic dictator) or worse, multiple humans to make the call re: launch and then deploy manually, the system would be prone to mood swings , and other assorted emotions and errors resulting in the weapons being deployed prematurely, or not at all even when the situation calls for it. Further, once the decision is made to deploy or not deploy, manual errors can delay or prevent the required outcome.

These are the situations where decision automation can come to the rescue. Decision automation can completely do away with the manual aspects of deciding whether or not to initiate failover and how (partial or full). It can coldly look at the facts it has accumulated and allow pre-determined business logic to figure out next steps and initiate an automated response sequence as required. Let's look at what that means.

Let's say the service level agreement requires a particular system or database to provide 99.999% uptime (five 9s). That means, that database can be down for a maximum of 5 minutes of unplanned downtime during the year.

Let’s make the problem even more interesting. Let's pretend the company is somewhat cash-poor. Instead of spending gobs of money on geo-mirroring solutions like EMC SRDF and other hardware/network level replication (even “point-in-time copy” generators like EMC’s SnapView, TimeFinder or NetApp’s SnapVault), it has invested in a simple 2-node Linux cluster to act as the primary system and two other SMP machines to host a couple of different standby databases. The first standby database is kept 15 minutes behind the primary database and the other standby remains 30 minutes behind the primary. (The reason for the additional delay in the latter case is to provide a longer window of time to identify and stop any user errors or logical corruption from being propagated to the second standby.) The primary cluster is hosted in one location (say, Denver) and the other two standby databases are being hosted in geographically disparate data centers with data propagation occurring over a WAN.

Given this scenario, at least two different types of failover options emerge:
- Failover from the Primary cluster to the first standby
- Failover from the Primary to the second standby

The underlying mechanisms to initiate and process each option is different as well. Also, given that the failover occurs from two-node clustered environment to a single node, there may be a service level impact that needs to be considered as well. But first things first!

Rolling out this DR infrastructure should be planned, standardized, centralized and automated (not “scripted”, but automated via a robust run book automation platform). You may think, why the heck not just do it manually? Because building any DR infrastructure, no matter how simplistic or arcane it is, is never a one-time thing! (Not unlike most IT tasks…). Once failover occurs, failback has to follow eventually. The failover/failback cycle has to be repeated multiple times during fire-drills as well as during an actual failure scenario. Once failover to a secondary database happens, the primary database has to be rebuilt and restored to become a secondary system itself or in the above situation since it's a cluster (presumably provides more horsepower and the system when it fails over to a single node has to operate in reduced capacity), failback has to happen to reinstate the cluster as the primary server. So in the frequent cycles of failover, rebuilding and failback, you don't want people to keep doing all these tasks manually. The inconsistency in quality of work (depending on which DBA is doing which task) could cause inefficiencies to creep in resulting in a flawed DR infrastructure. Human errors could compromise the validity of the infrastructure.

So the entire process of building, testing, maintaining, and auditing the DR infrastructure needs to be standardized and automated. Also included in the “maintenance” part is the extraction of the archived/transaction logs every 15 minutes, propagating to the first standby server and applying it. Similarly, in the case of the second standby, the logs have to be applied every 30 minutes to ensure the fixed latency is maintained. In the case of certain versions of certain database platforms (namely, Oracle and DB2), there are DBMS supplied options to establish the same. Regardless, the right mechanisms and components have to be chosen and deployed in a standardized and automated manner. Period.

Now, the DR infrastructure could be well deployed, tested and humming, but during the critical time of failover, it needs to be evaluated what kind of failure is being experienced, what is the impact to the business, how best to contain the problem symptoms, transition to a safe system and to deal with the impact on performance levels. Now, applying that to our example, it needs to determined:
- Is the problem contained in a single node of the cluster. To use an Oracle example, if it’s an instance crash, then services (including existing connections, in some cases such as via Transparent Application Failover or TAF) can be smoothly migrated to the other cluster node.


- If the database itself is unavailable due to the shared file-system having crashed ( in spite of its RAID configuration), then the cluster has to be abandoned and services need to be transferred to the first standby (the one that’s 15 mins behind). As part of the transition, any newer archived logs files that haven’t yet been applied need to be copied and applied to the first standby. If parts of the filesystem that hold the online redo logs are not impacted, they need to be archived as well to extract the most current transaction(s), copied over and applied. Once the standby is synchronized to the fullest extent possible, it needs to be brought up along with any related database and application services such as the listener process. Applications need to be rerouted to the first standby (which is now the primary database) either via implicit rerouting by reconfiguring any middleware or via explicit rerouting (caused by choosing a different application configuration file/address or even, lower level mechanisms such as IP address takeover, wherein the standby server will take over the public IP address of the cluster).

- If for any reason, the first standby is not reliable (say, logical corruption has spread to that server as well or it is not starting up in the manner expected due to other problems), the decision needs to be made to go to the second standby, carry out the above described process and bring up the necessary services.

The real challenge is not just having to make all these decisions on the fly based on the type and scope of failure, and the state of the different primary and standby systems , it is having to make them and complete the entire failover process within the required time (5 minutes, in our example). Organizations cannot realistically expect an panic stricken human being to carry out all this quickly on the fly (yet, ironically, that’s exactly what happens 80% of the time!).

IT professionals sometimes say, “I don’t trust automation; I need to be able to do things myself to be able to trust it.” Well, would YOU entrust such a delicate process wrought with constant analyzing/re-analyzing and ad-hoc decision making to a human being who may be sick or on vacation and out of the office? Someone who may have left the company since then? Would you be content merely allowing this process to be documented and hoping someone else (other than the person who designed and built the solution) in the IT team can perform it without problems? I for one, wouldn’t.

The range of situations for applying decision automation are manifold and implementing a centralized command / control system for initiating failover in the DR process just happens to be an ideal candidate.

Friday, December 29, 2006

A Layperson’s Guide to IT Automation Market Speak

My company was a sponsor at the recent Gartner Data Center conference in Las Vegas (http://www.gartner.com/2_events/conferences/lsc25.jsp). Sure enough, the words “IT automation” were all pervasive at the event. There seemed to be so many companies crawling out of the woodwork, all vying for total market dominance. It is energizing to see that automation has truly gone beyond being just a buzzword. However when I spoke to many of the folks who had attended the show, one common frustration seemed to exist across the board - the amount of irrelevant, almost bordering on the nonsensical, marketing chatter in this space.

Imagine you are a senior IT manager (pick your own fancy title) and you are interested in enhancing the level of operational maturity in your environment. (Nice!) Your organization is already following ITIL standards closely and you are keen on seeing what else is out there that might help you. So you attend the next IT/data center optimization show or even, simply do a Google search and…. boom! A ton of offerings with terms ranging from the simple (“lights out monitoring”) to the arcane (“autonomics", “adaptive infrastructure”, "run book automation") hit you between the eyes. (Where oh where, were many of these companies and offerings just a year or two ago?) Almost all of them flush with venture capital and slick marketing, no doubt, but confusing as heck! So you go to your favorite analyst’s website and guess what, it’s a total letdown. There is no single comprehensive coverage of all these offerings and in fact, each analyst seems to have a fetish for a certain buzzword or two. And pitifully, some of the big analysts are still caught up in old world IT agendas, blissfully unaware of the progress in automation; if anything, they are scrambling to come up to speed just as quickly as the rest of us, mere mortals.

After much time and research, I noticed each automation vendor and offering is focused on a horizontal or vertical subset of IT. However from their marketing collateral, it appears as though each one does pretty much everything under the sun – obviously, a lot of redundancy. So what’s an IT manager to do? Who do they believe? Where does one offering end and the other starts? Obviously, it’s not practical to try and evaluate each and every vendor and attempt to do a POC to determine fit…

Here is a guide I built to navigate my way through the different kinds of offerings out there and make the right recommendations for my clients. Hopefully you will agree that it is simple to grasp (and without the usual glib marketing speak!). Based on all the companies I witnessed during the conference, I was able to categorize companies and their offerings into six clean “buckets”.

1. The first category is caveman-style “script-based automation”. Other than being very prolific, it is the dumbest way to attempt to automate anything because (as I have mentioned in prior postings), raw scripts are difficult and time-consuming to write, maintain and deploy, especially across large server farms.

2. The second category is “simple repair automation” – the kind brought about by GUI monitoring and administrative tools such as BMC Patrol, Quest Central, Embarcadero dbArtisan / Performance Center and Oracle Enterprise Manager (OEM). Again as I have mentioned in prior postings, these GUI tools are awesome when it comes to monitoring and performing one-off tasks, but ill suited for carrying out the simplest of repairs in a consistent way across multiple servers. The main problem with them is that they make specific assumptions about the underlying environment which may not be applicable to all environments. Since they have “canned repair logic”, they do not accommodate custom business rules or IT policies very well. Some of these tools do allow scripts to be executed when certain conditions are detected. However this approach runs into most, if not all, of the problems associated with scripts mentioned above. As such, the so-called automation capabilities within these products are of limited practical utility.

3. The third category comprises “automated provisioning and configuration management” tools. While automating provisioning and configuration management is useful, these activities comprise a rather slim portion of the average IT administrator’s workload. There are several other tasks emanating from user requests, environmental changes (unanticipated changes), change control requests (anticipated changes), software releases and alerts that these tools are not meant to deal with. Such limited utility means customers are forced to introduce a bunch more tools in their environment (to automate tasks besides provisioning.) Not ideal.

4. So a fourth category aptly named as “run book automation” (heavily “supported” by Gartner) has come into existence. These tools offer a framework for automating activities driven by custom business logic via a workflow GUI and pre-built integration into commercial monitoring and ITIL packages (especially incident management and ticketing). However the underlying business logic code often has to be written by the IT administrators themselves. Thus while this approach is superior to script-based automation by centralizing script logic onto a workflow GUI, it still suffers from the biggest deficiency of scripts, i.e., it requires an expert administrator to code the business logic into the workflow engine, thereby competing with regular day-to-day tasks for the administrator’s time. If the administrator doesn’t have the bandwidth to properly instrument and deploy these products, they ends up as shelfware (which is more than likely, since administrator bandwidth is the core issue these tools attempt to increase, but instead they consume more than they provide in the short run). Companies that are usually successful in leveraging run book automation are ones that have already attained significant operational maturity even prior to deploying these tools. Companies that are still struggling with daily fires are better off spending time to shore up their internal processes and gradually looking to the other categories here to attain efficiencies.

5. Given the drawbacks of run book automation, another category that is fast emerging as a more viable solution is “domain knowledge automation”. Products in this category offer a run book automation platform with built in expertise for one or more horizontal areas in the IT stack, such as server administration, network administration, application administration and/or database administration. Via this approach, products eliminate much of the problem with script-based automation and more importantly, reduce the need for administrators to code custom logic from scratch. StrataVia’s Data Palette™ is a good example of products in this category. Data Palette represents domain expertise in database automation by providing a library of standard operating procedures (SOPs) pertaining to common DBA tasks. The entire SOP code-base is exposed in an open-source manner such that administrators can tweak the business logic code to fit their environments, if the pre-existing SOPs aren’t suited for the task. Products in this category support several scripting environments to avoid the administrator having to learn a new language. In order to build domain knowledge and awareness, products in this category also need to have the capability to detect external events, along with decision automation prowess to deal with specific changes in event states. This capability is useful for triggering pre-configured SOPs at opportune times without human intervention.

6. The sixth and last category is self-managing software, or what is often referred to as “autonomics”. This is true automation nirvana. Products in this category are aware of themselves, their environments and the interplay thereof. They use this awareness to optimally install, configure, maintain, update and recover themselves. Much of this functionality can be built into products to make them autonomic from the ground up or layered in via an external service to make existing software products autonomic. For instance, Data Palette uses the latter approach to make databases autonomic. The good news is that most of the large vendors like IBM, Microsoft and HP are heading in this direction, even though they may label this functionality differently. (IBM coined the term autonomic computing, HP calls it adaptive infrastructure and Microsoft calls it dynamic systems initiative.) However the bad news (not surprisingly) is that most of the large vendors focus on their own products and their partner ecosystems and worse, take a low-level hardware or operating system level approach to autonomics. In other words, they are mostly about different components of the IT stack collaborating with each other which in my opinion, just doesn’t work – because the level of collaboration required is impractical and doesn’t happen. Even if it does, it doesn’t keep up consistently past a release or two; there are just too many moving parts. Of all the six categories I have listed, autonomics is probably in the most nascent stage.

Hopefully this six-bucket categorization helps the average customer understand what’s actually out there and even if a vendor uses different marketing speak (which is more than likely), they can still read between the lines and place each product in the right bucket. Furthermore, it is my hope that these six buckets will let customers take a step back and see what is most prudent for their organization to evaluate and focus on offerings in that category, allowing them to move faster towards their preferred area of IT optimization.

Just one final word of advise to vendors – don’t even bother appeasing all the big analyst groups out there and rigging your marketing message. As long as your customers know where your offering fits in the above categories, it results in a more mature market for IT automation. Such a market has clean segmentation as its primary attribute allowing different products to fit into appropriate segments, and allowing each to play nicely with others in complimentary categories. In the end, this approach makes it a lot less painful for customers to find what they are looking for, thereby accelerating user evaluation and adoption. Vendors that do not gain this realization and hide their offerings behind a cloud of ambiguity are not only doing themselves a disservice, but also holding the market back by a decade.

Tuesday, November 28, 2006

DBA Script Exodus to Python or Ruby Expected in the Near Term

During the last year and half, I have been observing an increase in the number of DBAs heading in the direction of learning and using “new age” scripting languages. I refer to Ruby and Python specifically. However this migration is not a new phenomenon; the move to such new age scripting was preceded by the migrations to the “axial age” from the “proprietary age” and in turn to the “open age”. And if anything, this migration towards Ruby and Python is not just a passing fad; I expect it to turn into a full-fledged exodus enabling higher scripter productivity and fostering more automation in the DBA world.

During the proprietary age, I’m sure most of us have encountered DBAs specializing in a single (proprietary) DBMS language like PL/SQL or T-SQL. The main problem with that approach was the “only tool being a hammer” situation (where, if the only tool you have is a hammer, every problem begins to look like a nail). DBAs started using their favorite proprietary language everywhere, even to address scripting needs outside the database. For instance, in a Windows/SQL Server environment, I would run into T-SQL routines doing OS-level backups via NetBackup. Granted you could do backups this way, however backups done via T-SQL would need to be driven off a SQL Server environment. This approach was fine for backing up database servers (and that too, only environments comprising SQL Server predominantly), however in the case of other (non-database) servers like application, mail and web servers this was grossly inefficient and somewhat of a misuse of T-SQL’s capabilities.

I saw this leading to the axial age, wherein DBAs began using DBMS independent scripting languages such as Korn Shell and VBScript. These routines served as the “axis” (axes) to connect a variety of operating system, database and application related functionality. This worked pretty well in handling both DBMS and non-DBMS routines and tasks alike, however they were usually confined to a particular operating system platform. For instance, Korn Shell scripts wouldn’t work on Windows and VBScript wouldn’t function on UNIX without a lot of heavy-lifting. (Certain 3rd parties came up with specialized wrappers to allow shell scripts to work on Windows, however that required separate purchase and installation of such layers and they had their fair share of problems in deployment, invocation and runtime exception handling.)

Thus came in the open age, wherein DBAs could go with an “open” scripting language such as Perl, Tcl/Tk and even PHP that would run on a plethora of operating system platforms (Windows and/or anything ending with an “x”) including thin clients. Most of these languages were great for writing one-off scripts however they severely lacked the strengths of a full-blown language, especially the newer ones such as C# or Java (especially real object-oriented capabilities such as inheritance and polymorphism.) Hence while their applicability was high, their reusability remained rather limited. DBAs had to pretty much start from scratch each time to write newer routines. At most, they could take an existing script and hack it until it met their requirements (turning it into a whole different script in the process.) There was no library of reusable code routines that they could reference to put together newer functionality using existing code blocks. That greatly restrained DBA productivity and also resulted in a plethora of one-off scripts – each of which needed to reside on each target server increasing complexity and maintenance overhead.

The new age scripting languages have emerged as a viable solution. Python and Ruby both provide an eclectic and formidable mix of the capabilities of the open age languages, combined with the versatility of the axial languages. They retain all the power of their scripting predecessors, yet put almost the entire power of Java and C# (more, some would argue) in the DBA’s hands. DBAs can truly write code libraries once and reference them (note, reference them, not copy and hack them) many times. A single line of Python or Ruby can do so much resulting in tightly written code yet with clean and elegantly stated constructs, and documented classes and methods, resulting in fairly easy to understand code. For instance, look at the following line of Ruby code:

1.upto(3) { databaseStartup.retry }

One doesn’t need years to scripting experience to understand what it’s attempting to do. One doesn’t have to mess around with complex structures and code just to manipulate a simple string. And best of all, there appears to be an unprecedented growth in communities of followers, around both Python and Ruby. Larger efforts based on these languages such as Ruby on Rails and Django are gaining mainstream support resulting in significant value-add to these languages via more reusable code libraries, classes, methods and documentation. Based on this momentum, neither of these languages appear to be going away anytime soon. So if you are a DBA that’s already embraced one or both of these scripting languages, kudos, you now have more options in your toolbox to create and deploy industrial strength automation, especially coupled with a centralization platform like Data Palette*. However if you are still on the fence or worse, dedicated to playing with isolated Korn shell scripts and T-SQL routines, it may be time to consider giving yourself an upgrade!


(* Incidentally, Data Palette’s SOP automation framework allows automation routines in any scripting language to be written, including T-SQL if that’s your flavor of choice. However it is my experience that the reusability and power of each SOP increases manifold by writing SOPs in Python or Ruby. Have fun scripting in the new age!)

Wednesday, November 22, 2006

Need for orchestration managers in IT automation

I was having a conversation earlier this week with the chief architect of a multinational (Top 3) IT services company regarding our company’s Data Palette automation product. He is no stranger to IT complexity since his company manages the IT departments and business processes of literally hundreds of organizations in the private, public and government sectors. He asked me an interesting question: if Data Palette could be used as an “orchestration platform” in calling different standard operating procedures not just related to database administration per se, but managing other components of the IT stack such as system level products and network level products.

That took me a bit by surprise since all along I have just been focused on database management while talking with him. I told him that Data Palette has traditionally been used to call other DB related product APIs and 3rd party routines within different SOPs (Data Palette’s standard operating procedures) if that 3rd party or DBMS product routine is the best way to accomplish a given task (for instance, a Data Palette SOP could call StatsPack if required for Oracle DBAs that are used to utilizing that functionality for macro-level performance diagnosis, or in the case of a more complex situation, execute an SOP based on receiving an SNMP trap from enterprise monitoring tools like HP Openview or Mercury Sitescope). He mentioned that while this was useful, Data Palette could potentially serve a larger role in the environment wherein its SOP Module could be deployed as a central administration and audit console and provide an abstraction layer around other niche applications.

Come to think of it, there's nothing to really stop an organization from using Data Palette (or any similar product) in such a capacity and I told him as such. In fact, this approach would help companies in two ways:
1. Allow companies that rely on a plethora of tools to simplify their environment by orchestrating tool usage via a central automation platform (Data Palette was the catalyst in my conversation, however any similar automation product could be used in this capacity).

2. Allow teams situated around the world, as well as newer hires to get upto speed faster by just having to learn one product interface, as opposed to having to come upto speed on several tools and point solutions.

Thinking further about it, one of the reasons automation efforts are seen as unrealistic in many complex environments is because of multiple people doing things their own way and using whichever tool they feel is appropriate for their situation. By having such an orchestration platform, it allows everyone (not just DBAs or specific administration staff) be on the same page about what tools are meant for what use and stick to that standard via a single command and control interface. If a team member really has a favorite tool for say, server provisioning, they can even introduce that tool to a larger audience via such orchestration and make that part of the standard. This encourages multiple team members to have a conversation about why a certain approach or tool is better for a task and win over others to their way of operating.

The chief architect’s suggestion is a stellar one that will allow IT environments with higher complexity to embrace automation in a faster and more meaningful manner. These are the kind of innovative thinkers and strong champions our IT industry in general, and automation efforts in particular need at this moment in dealing with complexity caused by silos of people and ongoing changes. It’s kinda like having your cake and eating it too by retaining the tools and technologies that one is most familiar with and weaving them into larger scale automation efforts.

Sunday, November 12, 2006

Autonomic Oracle 10g Installs? Forget about it!

DBAs and users alike eagerly await the benefits of automation and autonomics which will translate into less routine administration and faster completion of work. An autonomic database environment is marked by self-managing software including installs and maintenance, all the way to disaster recovery. But unfortunately, it seems software vendors like Oracle do everything they can to keep this goal elusive and breed an ever-increasing population of mundane and under-appreciated DBAs that need to stay married to their Blackberries and do a lot of nocturnal heavylifting.

Take my own very recent example. Last week, I was requested to install an Oracle 10g Release 2 database on a Linux RHEL 4 64-bit x86 server for a new Web application that needs to go live shortly. The install and database creation needed to be completed prior to Monday morning. Simple enough. I was so confident about this process which I had probably done dozens of times with many different versions of Oracle, I didn’t even start on the work until Saturday night (last night).

I installed the database in silent mode via the Unattended Install SOP within Data Palette (SOP = Standard Operating Procedure, a set of documented task plans and corresponding automation routines and workflows within the Data Palette automation platform). I was happy to see it work like a charm! (Look Ma, no hands...). Then I was off to create the database. Since I didn’t have a canned SOP to do the task (StrataVia is due to release a Database Creation SOP shortly), I figured I would do it manually. I started up sqlplus with the credentials ‘/ as sysdba’. Since I was logged into the box as oracle:dba, I expected to get to the SQL> command-line prompt so I could execute the CREATE DATABASE command. (Being an old-world sort of DBA, I prefer the command-line to the GUI tools like the Database Assistant that take 3 minutes just to start up.)

Immediately, I got an error message: “error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
ERROR: ORA-12547: TNS:lost contact”.

Thinking that for some reason, the libaio RPMs weren’t available, I ran the “ldd” command to figure out the shared library dependencies:

$ ldd $ORACLE_HOME/bin/oracle

Here’s a subset of the output:
libaio.so.1 => file not found
libdl.so.2 => /lib64/libdl.so.2 (0x0000003d1b700000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000003d1b900000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x0000003d1bf00000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003d1ef00000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000003d1b400000)
/lib64/ld-linux-x86-64.so.2 (0x000000552aaaa000)

Aa ah! I found the problem (in bold above). The shared library that Oracle was expecting was not to be found. Maybe I figured our Sys Admin had not installed all the prerequisite RPMs beforehand. The Data Palette Unattended Install SOP does check for these RPMs and optionally install them, however I figured somehow one had been missed since our Sys Admin prefers to install RPMs manually. So I manually checked for the existence of the libaio RPM:

$ rpm –q libaio
libaio-0.3.105-2

OK, so it did exist. This was now puzzling. Why wasn’t Oracle using the libaio rpm that was on the system?

One thought was to just disable Async I/O and relink oracle so it didn’t try to utilize the libaio shared library.

(Side note, but one way I have done this in the past for 10g R2 is as follows:
$ su – oracle
$ . oranev
$ [ Ensure that all databases using this ORACLE_HOME and related services are down ]
$ cd $ORACLE_HOME/rdbms/lib
$ make –f ins_rdbms.mk async_off

[ Now update relevant init.ora parameters such as disk_asynch_io=false and filesystemio_options=none ].

Now one can confirm that async I/O is not in use by Oracle (or any other apps on the machine) by grepping the /proc/slabinfo file for “kioctx” and “kiocb” (egrep "kioctxkiocb" /proc/slabinfo) and ensuring the first couple of columns show zeros.

If other apps might need async I/O, you can check if Oracle is still using async I/O by rerunning the ldd command (ldd $ORACLE_HOME/bin/oracle grep libaio) to ensure that the output doesn’t still refer to the libaio file. The output of the nm command (nm $ORACLE_HOME/bin/oracle grep io_getevent) can also be verified to ensure any object file symbols are not referring to LIBAIO.) [END OF SIDE NOTE.]

In this particular situation, I couldn’t refrain from using AIO since that this Web application was expected to be I/O intensive and I wanted to keep both asynch I/O and direct I/O enabled. So the only option was to dig in and see why Oracle was not accepting the existing libaio RPM and see if I could install the right RPM version.

I called up our Sys Admin and explained what I was running into and requested for sudo access so I could deal with this shared library problem. I told him I would only reinstall or relink any libraries pertaining to oracle. As he graced me with sudo access, he reminded me that he would check the sudo logs to ensure I didn't mess with anything else, and also mentioned that he didn’t think the problem had to do with the Red Hat OS, and that all RPMs were backward compatible. He even referred to the Oracle Installation Guide that stated that ensure that the XYZ or higher version of an RPM existed before proceeding with the install.

Fair enough, the Oracle documentation can’t ever be wrong, right? I started Googling the “error while loading shared libraries: libaio.so.1:” error message and found quite a few 3rd party sites talking about having the libaio-0.3.96-3 RPM. However I had a newer version of this RPM. If the Oracle documentation was accurate, I shouldn’t be having this problem.

I then sudo’d in, copied the .src file for the older RPM from the Red Hat site and tried installing it. However it errored out stating a newer version already was installed. Then I retried using the “--replacepkgs” flag. Again to no avail. Finally after some vigorous head-scratching, I uninstalled (erased) the existing newer RPM and installed the older version.

That did the trick! The ldd and rpm -q commands revealed the following:

$ ldd $ORACLE_HOME/bin/oracle
libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000002a96f43000)

$ rpm –q libaio
libaio-0.3.96-3

After this, I could create the database I wanted with the appropriate configuration. But by the time it was all done, it was past 3:30 in the morning. I couldn’t believe I had just spent almost 6 hours on this insignificant issue. Autonomic installs – yeah right! I couldn’t believe Oracle wouldn’t even bother updating its software install archive and documentation regarding this issue so our Sys Admin would have installed the right RPMs in the first place (or update the Data Palette Unattended Install SOP to check for and install the right RPM version).

Based on this experience, I feel even with their latest 10g release, Oracle has done very little in making their software self-managing such that even novices can install and maintain it with ease. During the recent OpenWorld 2006, they were already announcing Oracle 11g and its whopping 482 new features! (http://www.oracle.com/technology/events/oracle-openworld-2006/oow_tuesday.html).

I think I echo the sentiment of many tired DBAs when I say: give us a break guys! We don’t wanna see yet another new whizbang release loaded with cool marketing features (aka grid computing) that businesses aren’t quite ready to use. We don’t care about the newest release of OEM/Grid Control and its RAC monitoring features (which by the way, also have some serious bugs). Instead just give us a release that is stable when it comes to basic functionality (the 20% functionality that's used 80% of the time) and such that a layer of abstraction is provided around mundane administration. Make your documentation current, make the database a little more smarter to spare us the late nights and abuse associated with installing and managing your product so that DBAs can work on things that are more relevant to our, our customers’ and users' businesses and not worry about which version of a shared library Oracle is expecting in order to function smoothly.

For my part I have to state, at the risk of sounding perverse, I was actually somewhat glad to go through this pain. After this was done, I poured myself another cup of coffee and started documenting this problem and solution and sent it off to the Data Palette Engineering team at StrataVia so they could build in this problem scenario and the corresponding solution into their new Database Creation SOP so other users of Data Palette (especially less experienced personnel) do not have to battle the same issues I did last night. But talk about loss of sleep and wasted productivity the next day… Yeesh!!!

Tuesday, November 07, 2006

State of database tools is appalling!

When one thinks of “database tools”, an overly crowded image comprising lots and lots of GUI utilities and point solutions comes to mind. Given all these tools, one would think the database tools market is a mature one such that all that is to be invented in this area is already invented and available. Right? On the contrary, the truth was never further away.

Almost all of the tools in the market today focus on just two things:
1. Monitoring and alerting
2. Administrative GUIs to carry out ad-hoc/one-off tasks.

There are a handful of large companies out there that specialize in this area and they produce all kinds of tools – utilities to point and click and create users, compare schemas, add space, roll out releases, tune SQL, produce wait event graphs and other such performance metrics, etc. But at the end of the day, all of these tools belong to one or both of the above categories in various permutations and combinations. (You name a tool and I will tell you which of the above category it focuses on.) There is no earth-shattering innovation here. Add to this the fact that many DBAs prefer the flexibility of the command-line anyway for the two categories above and many of these tools eventually morph into shelfware grossly under-delivering on the investment made in them.

Don’t get me wrong – these large tools providers do their fair share of R&D (approx. 18 to 28% of their overall revenue, as per last year’s SEC filings of heavyweights BMC, Quest Software and Embarcadero… whoo hoo!). But it looks like most if not all of their R&D is relegated to figuring out better and nicer looking GUIs for monitoring. That’s great! I’m a big fan of usability improvements that result in nice intuitive interfaces, however think of this: if I want to create a user in one database, I might as well use the command-line (unless I’m too lazy to memorize the syntax or look it up in the online SQL Reference guide). If I’m dealing with say, half a dozen databases where I need to create the same user, I would love to use these fancy GUI tools and point and click my way to glory. But what happens if I need to propagate this same new user and privileges to a couple dozen databases, or even, hold your breath - a 100 databases? That would be a lot of pointing and clicking, no matter how easy to use that tool is. Now add to these 100 databases, a variety of mundane/repeatable tasks – such as installs, configs, refreshes and cloning, maintenance plans, patches, upgrades, migrations, managing load processes, DDL releases, and so on. That makes for a very busy DBA team working around the clock and no amount of point solutions and nice GUIs can change that. None of these mainstream tools are even architected for that kind of functionality and task replication.

Now some of these tools vendors are getting smart. They are introducing newer "automation" features. Yaay! But guess what, much of these automation features are built on top of the monitoring functionality they previously had. And they are restricted to certain vanilla automation or allowing a script to be kicked off whenever an alert fires off. In the latter case, the script still has to be written and tested by a DBA. DBAs in complex, heterogeneous environments are already super busy. They don’t have the time to write these complex scripts. The most they may do is look on Google for a particular script and if found, pick it up. But again, they don’t have the bandwidth to test these recently downloaded scripts in all the database environments they support. And oh by the way, the DBA that wrote or downloaded a particular script may be well versed with how it works, but none of the other DBAs in the team are likely to be familiar with it. So now each DBA starts building her own toolbox of scripts. Most of these scripts are not documented in a central location and there is no pre-defined (commonly agreed upon) methodology regarding which script to use in what circumstances. Worse yet, each of these scripts usually need to reside locally on each of the (100) database servers. Even if one line of code needs to change in one script to accommodate a change in the environment, someone needs to manually log into each of the DB servers and make the change. Not a bad approach to follow, if fat-fingering and typos were non-existent!!!

Now you begin to see what many veteran DBAs and IT managers have been seeing for years – the DBA tools industry is in a state of despair without any light at the end of the tunnel. All that’s coming into the tunnel is more monitoring tools – they have become more light-weight, they now carry out extra audit functions, etc. But at the end of the day, all they do is monitoring and ad-hoc administration.

In the meantime, some of the DBMS vendors have been introducing automation capabilities into their core products in recent years. However the bulk of that automation is also vanilla in nature and hence fairly limited in its capability. Let's understand what this "vanilla automation" means. Let's take for instance, Oracle's datafile auto-extend capability. Oracle has had this feature for some time now. However the feature is quite set in its way such that it just makes the datafile grow until it reaches a pre-set limit or until the underlying mount-point/drive gets full. It does not accommodate sys admin policies prevalent in most organizations such as implicit quotas on different mount-point (due to diverse applications and databases sharing the same storage devices as the DBMS) and gracefully growing onto the next appropriate mount-point. In other words, vanilla automation capabilities built into DBMS products and 3rd party tools do not necessarily accommodate custom IT policies and environmental rules. Any kind of custom policies need a script to be built by the DBA and that approach runs into the script-related problems mentioned above. Worse yet, some of these tools require the DBA to learn a proprietary scripting language before they can build any custom automation capability (a notable example is BMC Patrol). Yeah, like they have the time to do that...

Some of the DBMS vendors have also begun to provide DBA tools separate from their core DBMS product. But they are not exactly stellar either! For instance, Oracle has been dutifully releasing its Enterprise Manager product since version 7. It’s “intelligent agent” technology has been difficult to install, was unreliable and would fail often. At times, it would suck up more system resources than the database itself (a la BMC Patrol or an HP Openview DBSPI). I know of an old school DBA that has been burnt by OEM more than once on a production environment. He's sworn never to ever touch OEM with a barge pole no matter how much it evolves! It's just lost all credibility with him and others like him. He feels Oracle should stick to building databases rather than trying to do it all! Anyway, the latest incarnation of OEM, Grid Control is certainly more reliable and powerful than its predecessors. However its power mostly applies just to Oracle and that too, the latest versions of Oracle. For instance, one of the most powerful capabilities of Grid Control is dynamic RAC node provision and configuration for Oracle10g, but that doesn’t work for Oracle8i and Oracle9i. OEM claims to support SQL Server and IBM DB2 as well, but only for monitoring (no automation features are included) and that too for a hefty surcharge! Well, that’s no surprise right? Think about it, why would Oracle make it easy for customers to manage its competitors’ products? A lot of my customers (though they use Oracle databases for some of their applications) think that Oracle, being the crafty company that it always has been, is in the business of selling databases and applications and along the way, cutting off the oxygen for competing products while ostensibly supporting them.

So what’s a customer to do? Continue to participate in the great monitoring rollout from these behemoths and hire human DBAs by the truckloads to do the real work when the pagers go off? Sure, you need high quality DBAs. But merely adding head-count doesn’t scale. Talk to the companies that have 100s of DBAs. They have already tried that approach and are consumed in their own hodgepodge of problems…

There is still hope yet. There are some smaller innovative companies that are rising up to the challenge – namely, companies like Opalis, Appworx, Realops, Opsware (this one isn’t so small any more!) and StrataVia (warning, implicit marketing push, since this is the company I work for) with its Data Palette product. All of these companies are beginning to address the above problems in their particular area of focus – application administration, systems administration, database administration, et al. Take a look at what they have to offer and see if it’s suitable for your organization. Maybe it is, may it is not. But you owe it to yourself and your shareholders to see if you can use their newer offerings to go beyond mere monitoring and ad-hoc administration to actually improve the operating efficiency of your IT environment.

The coming years should be interesting to see which of these innovative companies and products the market adopts and embraces and which ones wither away. But more than anything, it would be intriguing to see how long the 800-pound DBMS tools behemoths continue to survive focusing on improving monitoring and ad-hoc administration alone.