A Good DBA Automates (Almost Everything)

Published: Thu, 08/11/16

Do you want to get more done in a day, at work? Automate.
Do you want to free up some of your time and focus on important things? Automate.
Do you want to deliver fast and reliable reports over and over again? Automate.

Imagine

…being able to provide a report with all the databases where a particular user exists, within seconds.
…being able to change sys and system password in all the databases, withing seconds or minutes.
…being able to turn on or off the log shipping property for all the DataGuard databases in you environment, within seconds.
…being able to recycle the alert log for one or all of your databases, within seconds.
…being able to answer the question: what database links are connecting to HRPRD database, using the SCOTT user, within seconds.

Do you see anything in common in my statements? Just in case you missed it, I’ll point it out: “within seconds or minutes“.

Yes, that is right, when you automate, a process or a task, it becomes fast, reliable and repeatable.

The most common questions around the subject of automating DBA processes are:

1.Why Should I Automate?
2.What Do I Need To Automate?
3.Where Do I Start?

1.Why Should I Automate?

What are the three benefits of automating DBA tasks and processes? (I am sure there are more than these three)

Benefit#1: You get more done in a day, because you provide results faster, if a process is automated.

Let me give you an example:

You need to provide a report that shows the database name where user SCOTT exists. Let’s assume you are managing 50 databases.

Compare the time spent on manually logging in to each database, and running a query to confirm if SCOTT exists or not, versus running a script against all your databases that would check if SCOTT exists, and spool it to a nice report, just like this:

Benefit#2: You deliver fast and reliable reports, without errors.

When a process is automated, the probability of having a typo or omitting something, gets reduced. You have to get the automation right only once, then the process works well.

Imagine manually checking all your databases for db links that connect as user SCOTT to HRPRD database? You would login to 50 databases, run a query against dba_db_links and compile a report.

This sounds so boring…

You could miss a database, forget to record one database link…Manually checking, is prone to errors and time consuming.

If you automate it all, you could have a nice report withing seconds, that looks like this:
(*for formatting purposes, I have removed part of the encrypted password)

Benefit#3: You can run automated reports or tasks again and again on demand without minimal effort, on demand.

Benefit#4: You free up some of your time.

Instead of spending 1-2 hours on delivering one of the reports I mentioned above, you can deliver the same report in seconds or minutes. Isn’t that AWESOME?

NOW you can focus on that project you never got time for.
NOW you can focus on writing that script.
NOW you have the time to study or read a technical article.
NOW finally you can leave work on time.

2.What Do I Need To Automate?

Automate anything that has to do with the following: (this might seem so obvious for many, but never assume that it is)

backing up databases
backing up archived logs
gathering statistics on tables
recycling alert logs and cleaning up trace files
recycling listener logs and reporting on the listener log file

Automate repetitive tasks such as:

changing sys and system passwords
changing DBA passwords
terminating user accounts,
creating user accounts, locking user accounts, listing user accounts
database refreshes

Automate anything that you run often. Anything that would make your job and life easier.

Do you wonder if you should automate a task or or not, then automate.

3.Where Do I Start?

When automating a task or process, use scripts:

sql, plsql scripts
Unix shell scripts
Windows scripts
Perl scripts

When automating, keep in mind the following:

anything that changes, such as username, password, has to be passed to your script as a parameter
never ever hard code passwords in scripts
make the scripts runnable in one database, or all databases (where applicable)

Do you have anything automated already? Hopefully the backups are already automated. If not start there.

Bring awareness to the daily/weekly tasks that you perform and are tedious, boring and time consuming.

Make a list of these things. These are your starting points.

You might not have time to work on scripting and automating these processes right away, but you will, once you make them a priority.

Once you get a taste for automating things, you will never stop.

Remember: A Good DBA Automates (Almost Everything)