The only way is automation! (update)


I was a little surprised by the reaction I got to my previous post on this subject. A number of people commented about the problems with automation and many pointed to this very appropriate comic on the subject.

There are one of two conclusions I can draw from this.

  1. My definition of automation of tasks is very much different to other people’s.
  2. It is common for DBAs and middle tier administrators to do everything by hand all the time.

I’m really hoping the answer is option 1, because I think it would be really sad if being a DBA has degenerated to the point where people spend their whole life doing tasks that could be easily scripted.

So what do I mean when I speak about automation? Most of the time I’m talking about basic scripting. Let’s take and example I went through recently, which involved cloning a database to refresh a test system from production. What did this process entail?

  • Export a couple of tables, that contain environment specific data.
  • Generate a list of ALTER USER commands to reset passwords to their original value in the test system.
  • Shutdown the test database.
  • Remove all the existing database files.
  • Create a new password file.
  • Remove the current spfile.
  • Startup the auxillary DB using a minimal init.ora file
  • Do an RMAN duplicate. In this case I used an active duplicate as the DB was relatively small. If this were a backup-based duplicate, it would have required an extra step of copying the backups using SCP to somewhere that could be seen on the test server.
  • Replace some environment-specific directory objects.
  • Unregister the old test database from recovery catalog.
  • Register the new test database with the recovery catalog.
  • Remove the old physical backups.
  • Drop all database links and recreate database links to point to the correct location for the test system.
  • Reset the passwords to their original values from the old test system.
  • Lock down all users, except those I’ve been asked to leave open.
  • Truncate and import the tables I exported at the start.

None of those tasks are difficult. It requires only a basic knowledge of shell scripting to allow me to start a single shell script and come back later to see my newly refreshed test environment.

What’s the alternative? I perform all the same tasks individually, but have to sit there waiting for each step to finish before I can move on to the next. No doubt, during this time I will be distracted by phone calls or colleagues asking me questions, which drastically increases the risk of human error.

When I talk about automation, I’m not talking about some Earth shattering AI system. I’m talking about scripting basic tasks to make myself more efficient.

At times you have to draw a line. There is no point making your automation too clever because it just becomes a rod for your own back. I’m a DBA, not a software house. This is what people are really warning about, which I did not really make clear in my first post. If something is liable to change each time you do it, you are better having a written procedure to work from, reminding you of the necessary steps and how to determine what needs to be done. You can’t become a slave to automation.



Author: Tim...

DBA, Developer, Author, Trainer.

7 thoughts on “The only way is automation! (update)”

  1. I just reread my comment on your post and you know what? I think I came across as critical. That wasn’t intentional at all.

    In fact, I fully agree that all DBAs (and Developers, like me) should script up any task that the have to do multiple times – I think your post is important because most people tend to err on the side of “just get it done quickly” rather than the “automate ALL THE THINGS!!!” side.

    As a developer, most of the tasks you listed never come up in my day-to-day. I guess my tasks tend to be different every day – and when I automate something I prefer to automate it completely – if I write a script I want it to work and handle the edge cases and errors because six months later when I need it to work, and work NOW, I won’t have time to re-familiarise how it works in order to debug it.

    My personal approach is (without suggesting yours is any worse, of course):
    1st time: do it manually, usually learning as I go what needs to be done, write the steps down
    2nd, 3rd, 4th time: follow my written instructions, fixing them as I find mistakes in the instructions, or exceptions that need to be handled
    5th, 6th, 7th time: start converting the written instructions into little scripts that each do a chunk of work; eventually these get consolidated into one program
    8th time +: just run the program, and promptly forget everything I learned (but at least it’s all in code now, so I can just re-read my scripts to relearn it later)

    Cheers 🙂

  2. @Jeffrey : No worries. I think everyone’s points were valid and I don’t disagree with them. 🙂

    One of the issues about writing posts on the net is you often say something in quite a simplistic manner, without all the caveats, and expect people to take it in the spirit it is meant. When they come at that post from a different angle, their interpretation is different and they “see all the flaws” in what has been posted and quite rightly point them out.

    I think this is important, because it would be bad if other people read the same post and were mislead into thinking it meant something it did not.

    The reason for this second post was really to clarify the situation a little, so my previous post would not mislead people. I was certainly not getting narky with anyone. If anything, I’m glad people commented as it lead to this clarification. 🙂



  3. Tim,

    Glad to see you posting the details of tasks that can be automated. Absolutely agree with you on that. As you said in one of your comments, “I think this is important, because it would be bad if other people read the same post and were mislead into thinking it meant something it did not”, that is the key to me. Unfortunately, many people tend to read “headlines” only or use posts like these to further their flawed propaganda. Have seen it happening in the past and present. That is why I love what Tom Kyte has said…”Never say never, Never say Always”

Comments are closed.