Everybody needs a spare database

I've gotten a little preachy in this blog lately, so I thought this time I'd give you something useful. Have you ever wished you had a quicky little set of database tables so you could do some generally wacky stuff that would likely get you fired if you did it on your production database? I thought so. In the past, the only way to do something like this was to build another database somewhere. Of course, where to put it? Some of us weirdos have machines at home where we build databases, do virtual machines or stuff like that. Guilty. But not everyone wants to tie up their home machine with the multi-gigabyte behemoth that Oracle 11g has become. Well, have I got a deal for you.

Oracle provides a nifty little free service to show off their Oracle Application Express product (APEX), which I'm not sure has been as popular as they'd like it to be. You can register at their site and get your own little workspace that will allow you to play around with Oracle a little.

Here's how it works.

• Go to http://apex.oracle.com and click the link to "Sign Up"
• Click through the "next" buttons, giving Oracle your name and email address. Give them a real one since they'll send the verification link to it.
• Provide a name for your workspace and a schema name for your database objects
• Next you have to give a reason for requesting an account. Now, I don't know if anyone actually reads these or not, but you'd probably be better off if you didn't put something like "That dork from alt.oracle said it would be cool." Try "Evaluation purposes" instead.
• Next, you type in your little verification thing with the goofy letters and click "Submit Request"
• After a bit, you'll hopefully get an email back saying "click this link to verify, etc".
• Lastly, you'll get another email with your login.

Then you can login and poke around. Truthfully, you can do a lot of stuff on your new personal Apex. I'm not super familiar with it yet, but it looks like you can...

• Create your own tables, indexes, constraints, sequences, etc
• Run SQL statements and scripts
• Build PL/SQL objects
• Build your own webby-type applications with the GUI "Application Builder"

I'm not sure yet if you can build web apps that you and others could access from a browser without going through the whole Apex frontend, but if so, that would be uber-cool. One word of warning however. FOR THE LOVE OF ALL THAT IS HOLY, DON'T PUT ANY REAL DATA IN THIS CRAZY THING! I have no idea as to how secure it is – it's only for evaluation purposes, so DON'T DO IT.

You can't do a lot of administration-type stuff with your own personal Apex. If you're looking to mess with parameter files and flash recovery areas, it's time to bust out a virtual machine. But it is nice to have a place where you could try some SQL stuff without fear of a pink-slip visit from HR. So go get your account and do some crazy, webby SQL stuff. And, finally, FOR THE LOVE OF ALL THAT IS HOLY, DON'T PUT ANY REAL DATA IN THIS CRAZY THING!

Oooohhh... shiny!

I went to last year's Oracle Open World. I'd always wanted to go, but having been a consultant for so many years, those opportunities don't always come your way. In my experience, companies will spring for their own employees to go to Open World, but "no way" to that lousy, overpaid consultant who probably won't even be here next week. That leaves it up to the consulting company, whose take on things is usually, "If you don't already know everything they're talking about at Open World, then why did we hire you? Get back to work!" But since I work for a good consulting company, they offered me the chance to go.

Open World is a blast. If you're a geeky Oracle person like me, it's a complete nerd-o-gasm. First of all, Oracle's always announcing the "next big thing" – this year, it was the Oracle Linux kernel (perhaps the subject of a future post) and the latest in Exadata. Then you have your session speakers, most of which are pretty good. The technology booths full of people trying to sell you stuff are always cool. Of course, best of all is all the free swag you get. I came home with more techie junk than you can shake a datafile at. Let me tell you, it takes some mad ninja skilz to nab 11 t-shirts from Open World and get them home. I had to throw away all my underwear just to get them to fit in my luggage (don't ask me how the flight home was...).

Of course, the real focus of any Open World is same as that of a lot of the software industry – better, faster, stronger, more. Newer and shinier. What you have isn't what you need. I can't fault them for this – they need to keep selling stuff to compete and to stay in business, and innovation is a huge part of what we do. Progress is good. But sometimes a DBA needs to distinguish between something that represents progress and something that represents a big ol' pile of shiny.

I talked last time about how being a good DBA means having a healthy dose of skepticism. That has to apply to "new feature-itis" too. Part of what I do involves evaluating new technologies. Not only do I have to evaluate the tech to verify that it does what it says it does, I need to assess that its benefits are worth the time, risks and cost of adopting it. As an evaluator, there's an implied trust with my employers that if I recommend a shiny, new feature, it's because it will benefit their interests – not necessarily mine. I haven't seen it often, but I can remember working with more than one DBA who didn't share my take on this. I've come to expect non-technical people to fall into the whole "Look! Shiny!" thing when it comes to new tech. But some technical folks in positions of authority see new tech as way to 1) pad their resume ("why yes I've worked with feature X, I helped bring it into my last company"), or 2) make them indispensable, since they adopted it and are the only ones who understand it. When I was a newbie DBA, I knew a senior DBA who did just that - repeatedly. Everybody could see it, but nobody was in a position to do anything about it. Then, he left and the rest of us were put in the position of having to support this big, expensive, shiny nightmare.

Flying along the bleeding edge can be a bumpy ride. Resist the urge to pad your resume at the expense of your employer. Otherwise, your big ol' pile of shiny might become a big ol' pile of something else.

Magical Snapshotty-things

Magical Snapshotty-Things

I spent some time with a storage vendor recently. Vendors kill me. No matter what you say, they still cannot for the life of them understand why you are not using their product. And if you are, they are mystified by the fact that you're not using every last bell and whistle. In this case, the vendor was questioning why we weren't using their magical-snapshotty backup solution. Basically the way their backup feature works (similar to most snapshotty type of features) is that when a backup occurs, only the deltas are written out. Then, pointers/vectors (vectors sounds cooler) act as reference points to the delta blocks. If a recovery has to occur, the product is smart enough to The upshot of stuff like this is that recoveries are blazingly fast and the amount of data written is extremely small.

Very attractive - too good to be true right? Maybe a little - which takes me to my conversation with the vendor and my point about the inability of vendors to see past their product.

Me: So, your solution doesn't actually copy the data anywhere, except for the deltas?
Them: Yes, that makes it extremely fast and it uses tiny amounts of space.
Me: Okay, but that means there's not a complete copy of the data on physically separate part of the SAN?
Them: Yes, and it's extremely fast by the way.
Me: Um, yeah. So what if something radical happens? What if you lose more disks in the RAID group than you have parity disks?
Them: --Laughs--. That never happens.
Me: Really? I've seen RAID5 groups where two disks failed simultaneously.
Them: No way. Really?
Me: Yep. I've seen it happen three different times.
Them: --dumbfounded look. crickets chirping--
Me: So, you're willing to sign a form that guarantees that your storage system will never have a failure of that nature?
Them: --exasperated look-- Well, we can't really do that.
Me: Hmm. That's a shame.

In the end, they were probably frustrated with me, and I didn't intend to make them mad, but sometimes a DBA has to call BS on things. There's nothing wrong with their product. It's a very good product and we may end up making use of it in some way. The problem is that they're proceeding from a false assumption: namely, that unlikely failures are impossible failures. They're not.

In my last post, I mentioned that I would talk about the second common problem I see in the DBA world with backups, and that is "shortcuts" – ways to make things better, faster, stronger that ultimately leave you with a noose around your neck. The skeptic in me says, if it sounds too good to be true, it probably is – or at least there are probably some strings attached. If these guys were selling a magical-performance-tuney thing, it would be different. But as a DBA, you need to understand that there is no area where your fannie in the on the line more than the recoverability of the data. If you lose data and can't recover - it's gone - and you may be too.

With all apologies to Harry Potter, the trouble with magic is that it isn't real. Database administration isn't an art – it's a hard, cold science. In the end, there aren't many shortcuts to doing your job. If you're going to use a magical backup solution, you have to be dead sure 1) that you know the exact process as to how you're going to magically recover that data and 2) that you've taken every eventuality into consideration.

So in the end, problem #2 is similar to problem #1. Test things and make sure you know what you're doing. If red flags go up, stop and think. I don't want to see you in the unemployment line.

We don't need no steennkking recoveries!

Since this is a new blog, let's start with something basic - backups. Everybody knows you do need those 'steenking backups'. You know it, the rest of your team knows it, even the suits know it (they read it in a Delta Airlines inflight magazine). But there are a couple of problems I see with backups these days. The first lies with the DBA and, sadly, it can get your ass fired.

Yes, you did a nice and proper RMAN backup of your database. You did the right syntax, you reviewed the log, you even did a 'report need backup' command and made sure it came back clean. The real question is: if you lose everything, do you know how to put it back together? In my observation, it's utterly confounding how few DBAs today know how to do a recovery. Because let's face it - doing a backup is a deceptively simple process. You start RMAN and type 'backup database'. You can make it more complicated than that, but it doesn't always have to be. Backup is clean, orderly and comfortable. Recovery is messy, complicated and scary if you don't know what you're doing. Ask yourself these questions.

• You lose a datafile. Do you know how to do a complete recovery without restoring the whole database? Do you know how to do it while the database is online?
• You lose an entire database – control files, redo logs and datafiles. Can you do a complete recovery from that? Try it – it's an enlightening exercise.
• A brilliant developer drops the most important table in your production database. Can you do a point-in-time incomplete recovery to roll the database forward to the point right before the table was dropped?
• You lose an entire database, including the archivelogs. Have you tried the process of pulling the last backup off of tape and then doing a restore?

The list goes on. So how do you learn to do this stuff? You learn by doing it. I tell students in my classes that if you want to know how do recoveries, break stuff and then see if you can fix it. Literally. Build a database that no one else is using. Then, delete the system datafile and try to recover. Delete two redo logs and see how far you can get. Delete the system datafile AND the control files and see what you can do. It's one of the most enlightening experiences a DBA can go through. You'll learn what really makes a database tick. Consider this scenario – your mission critical, never-goes-down, life-blood of the company database experiences a media failure. The suits are surrounding you in your cube, watching your every move, questioning your every decision, telling you how much every moment of downtime is costing them, while sweat pours off your face onto your shaking hands as you try to think of the next command to type. I've seen it happen before.

Several years ago, I worked for a company that had a division that decided they needed their own personal DBA – the "regular" DBA group wasn't giving them enough TLC, I guess. They hired a guy who claimed to have extensive knowledge of Oracle version 9iR2 (way back then, yeah). He started on a Monday. That same day, the server on which his database lived had a drive controller error that wrote corrupt data across the entire RAID array. Time for recovery! Unfortunately, new guy (who was a really nice fellow) didn't have a clue as to what to do, and, worse, he didn't know some basic 9i stuff. He didn't know what an spfile was and he kept trying to "connect internal". Long story short, new guy started on a Monday and was gone by Wednesday.

Spare yourself the agony. Practice, practice, practice. Test, test and test again. We'll talk about the second problem next time. Until then, go break something – and try to fix it.