DBA stories: How a table went M.I.A. in production

A few years ago, I was attending a masterclass with some fellow DBA's at our office. At some point in the evening one of the DBA's asked for help on a problem, concerning a production system of a parcel distribution company. The gist: Somehow a table was lost... So discussion was revolving around how to restore the database back into a consistent state.

But first things first. We needed to know how this situation came to be. There was a development team that reported the problem and we went to talk to them. When we arrived, three developers approached us and started ranting that this must be a bug in the Oracle database, because "a table with foreign key relations cannot just disappear". They had just deployed a small change to production, which crashed and burned the application. That's all.

I said that something like a bug in the Oracle database is possible, but that that would be quite unlikely. At this time production was down already for 2 hours! There was tension in the room. The developers were at a loss. I almost went for the keyboard to start investigating, when I remembered something Anjo Kolk told on a Miracle Open World session I attended: "When you take the place of the developer, YOU have the problem on your hands".

I asked the developer the details on this release (scripts etcetera) and how they deployed it. There weren't any scripts available... Sorry? But you're releasing changes into production! But how are you deploying the changes then?

SQL Developer. Sure, why not. It is a great tool. I then asked him to show me exactly, what he did when he issued the last DDL statement (create index) in their dev database. He then proceeded to click the table and going through some steps to add an index. Imagine my surprise and the revelation on the developers face as he saw what he had done.

Instead of creating an index, he actually renamed the table to the name of the new index! Next we checked the production database and sure enough, there was the table that went MIA. It just had a different name. They looked at me and asked what to do next: Well, how about changing it back to its correct name? The problem was solved.

So in the end I just helped the developers solve their own problem in few minutes. I told them and the 'manager', that they really need to implement some basic form of release/delivery process. You just cannot be so nonchalant with your customers IT assets.

 

Posted with Blogsy