Automating repetitive tasks: If it moves, script it

Workshop DBAs can often fall into the trap of carrying out repetitive tasks and processes. The good news is that you can automate a lot of these tasks to save time, money, and above all, sanity. The bad news is that few database administrators (DBAs) are doing it.

In its 2013 database manageability survey, the Independent Oracle User Group questioned Oracle DBAs about their methods for executing common tasks. Depending on the task, between 38 per cent and 59 per cent of users were still carrying them out manually.

Why automate?

Aside from the obvious time saving benefit, automation can also weed out errors that may creep in if you try and do something manually. It also helps you to standardise your processes. You know that you're going to get the same result from the same process, every time.

Proper automation can also mitigate unexpected downtime, by catching configuration drift before it becomes a problem. And it can help you to be more agile when responding to team requirements.

Those are the benefits for your database. The other big benefit is for you. This idea of working smarter, not harder also lets you stop firefighting and start thinking strategically as an administrator.

Take a look at Stephen Covey's four-quadrant model, taken from The Seven Habits of Highly Effective People. The grid has horizontal and vertical axes, dividing the workspace into four quadrants. The horizontal access splits the square into 'Important' on the top, and 'Not Important' on the bottom. The vertical splits it into 'Urgent' on the left, and 'Not Urgent' on the right.

The tasks you don't automate will usually fall into the left-hand quadrants, either as 'Important and Urgent' (things that you need to do now, to keep the lights on) or 'Not Important and Urgent' (things that other people want you to do because it's important to them, even if it isn't critical to you).

Those are two quadrants you don't want to spend much time in, because they're stressful, and take away your autonomy to do other things as a DBA. Automating them allows you to concentrate on tasks that fall into quadrant two: Important and Not Urgent.

This is where strategic tasks live, such as taking that training course, boning up on the latest manuals, having a conversation with your business managers, or developing policies to further streamline your job. Automate your way into quadrant two. It's a happy place to live.

What tasks can you automate?

You can automate a large percentage of what you do as a DBA. In fact, once you get started, you may find it addictive. Here are some of them:

Patch management the Independent Oracle User's Group found that only one in six DBAs are patching databases manually. Yet you can develop patch plans, which allows patches to be applied with a single period of downtime. Automate patch validation to handle conflict checking, so that you can have it all ready for an automated patch rollout process at the end.

Testing Don't restrict yourself to testing patches. Compiling and running test scripts on non-production systems to validate schema changes can save valuable time.

Configuration changes Planned configuration changes ideally start in non-production databases. You can use your automation scripts to capture changes in baseline configurations, and to prepare the SQL for updating the schema on your production database.

Database provisioning 18 per cent of respondents to that survey said that they deploy databases quickly, but that it is done manually most of the time, shackling them to the 'urgent' quadrants. Almost half of all DBAs say that they only provision databases automatically some of the time, or not at all. You can automate this process using a mixture of techniques, such as database cloning via RMAN, data masking, and the creation of databases from templates.

Automated monitoring Using Adaptive Thresholds to alert DBAs when key activity moves outside a certain range stops you having to check for disasters. And schema change detection can let you know automatically when a schema has been altered, which could have ramifications for the database.

Just as you use automated scripts to monitor configuration changes on non-production databases, you can employ them to watch your production environments, helping you to keep track of configuration creep.

Diagnostics and troubleshooting Configuration issues can often be resolved automatically, but a little more than half of all DBAs are still poring through database statistics and schemas manually for troubleshooting purposes.

Log management Scripts to search for telltale problems in the logs can save you precious time poring through the entries yourself.

How to automate

There are three main ways to automate your DBA tasks. The first - which many will reach for out of convenience - is manual scripting, using the shell script of your choice, or writing something in Python, say. These are often good for quick scripting tasks, and you can refine them over time.

Oracle provides its own Enterprise Manager tools, and there are also third party tools available. Dell's Toad Automation Designer lets DBAs visually build scripts using a GUI. It lets you create automated "apps" according to user role (with DBA being one), by selecting from a list of available actions, each with their own alterable settings.

These actions can also be added to a new automation script by using a snapshot feature from most activity windows in Toad. That allows you to capture the settings for a particular activity and save them as an automated action.

These actions can be combined, so that you can drag a task such as 'export dataset' into the app's workflow, followed by an email notification.

You can check over a list of previously-executed apps in the Toad 'action recall' section, enabling you to easily audit what has been done.

Best practices

Whichever automation tool you decide on, following some best practice guidelines will help you to make the best use of automations.

Standardisation is an important practice here. Define standard procedures for automated activities, including monitoring, diagnostics, and provisioning. Develop a library of corrective actions that can be started when thresholds hit a certain level.

Building libraries in this way will also help you prepare for another best practice: grouping and mass automation. By standardising as much as possible (especially when it comes to creating standard builds for provisioning new database instances), you can apply the same actions to lots of entities at once, further minimising your time spent.

If you're just preparing to pull yourself out of the moil of manual work and start automating tasks, begin with something less critical. Applying automation to non-production databases will give you a chance to hone your skills and refine your scripts after monitoring your execution.

You can also take some of these scripts and give them to other people. One of the biggest problems a DBA will face is complaints from other teams who are working on non-production databases for testing and development of their own features. They will inevitably run into problems, and you can use automation scripts to extricate yourself from those quadrant one activities.

Giving them a diagnostic script to run, so that they can compile the output and come back to you with something to analyse, will free up your time to work on quadrant two stuff. If that script's output is actionable, you may even be able to get them to correct any errors themselves. That gives them more autonomy, and you more freedom.

In short, if you want to save yourself and your team valuable time and effort, if you want to seem more responsive to other teams and business users, and if you want to reduce the probability of unexpected database incidents, then if it moves, automate it. Those script libraries you produce could end up representing some valuable intellectual property. ®

The Register is running a series of Oracle DBA workshop articles in association with Dell Software.