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.
- My definition of automation of tasks is very much different to other people’s.
- 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.