A Good DBA Automates (Almost Everything)
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:
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)
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)
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