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:

?View Code ENGLISH
DB NAME    SERVER NAME  EXECUTING USER  TIMESTAMP                                         Message 
HRPRD      oraprod1     drobete         10-AUG-16 09.24.41.444094000 AM -06:00            SCOTT user exists.                                                                                      
HRTAX      oratest1     drobete         10-AUG-16 09.24.42.001880000 AM -06:00            SCOTT user doesn't exist.                                                                               
HRTST      oratest1     drobete         10-AUG-16 09.24.42.453527000 AM -06:00            SCOTT user doesn't exist.                                                                               
HRDEV      oratest1     drobete         10-AUG-16 09.24.42.933790000 AM -06:00            SCOTT user exists.                                                                                      
FINPRD     oraprod1     drobete         10-AUG-16 09.24.43.407317000 AM -06:00            SCOTT user doesn't exist.                                                                               
FINTST     oratest1     drobete         10-AUG-16 09.24.43.886375000 AM -06:00            SCOTT user exists.                                                                                      
FINDEV     oratest1     drobete         10-AUG-16 09.24.44.404109000 AM -06:00            SCOTT user doesn't exist.

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)

?View Code ENGLISH
DB_NAME     DBLINK_OWNER    DBLINK_NAME       DBLINK_USER  DBLINK_HOST  CREATED    DBLINK_DDL 
 
FINPRD      HR_USER         HRPRD             SCOTT        HRPRD        22-JUN-12  CREATE DATABASE LINK "HRPRD"
                                                                                   CONNECT TO "SCOTT" IDENTIFIED BY VALUES
                                                                                   '05806D'
                                                                                   USING 'HRPRD'
 
EMPRD       HR              HRPRD             SCOTT        HRPRD        26-NOV-15  CREATE DATABASE LINK "HRPRD"
                                                                                   CONNECT TO "SCOTT" IDENTIFIED BY VALUES
                                                                                   '05806D'
                                                                                   USING 'HRPRD'
 
EMPRD       DROBETE         HRPRD             SCOTT        HRPRD        10-DEC-15  CREATE DATABASE LINK "HRPRD"
                                                                                   CONNECT TO "SCOTT" IDENTIFIED BY VALUES
                                                                                   '05806D'
                                                                                   USING 'HRPRD'

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)

A Good DBA Automates (Almost Everything)

A Good DBA Automates (Almost Everything)

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

-Diana


Leave a Reply

Your email address will not be published. Required fields are marked *