Automating the Oracle Database

In DBA, Oracle database on April 16, 2011 at 09:16

Believe it or not, the more you automate the activities around database management, the more problems you can get (derived from the automation itself) and on the other hand the more time you can get in order to concentrate on something more urgent, important or interesting. However you see it.

Of course, the argument “If you automate everything well, then you will have almost no issues” is pretty valid but in reality it it is not more than just an argument.

You have seen, used, and probably worked with Automatic Storage Management (ASM), Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), Automatic SGA Management (ASM).

There is even a book called “Easy Oracle Automation” not to mention the 1990s book “High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL” when automation in the database had totally different meaning from what it has now.

Just look at the prices above, you can get both books for 10 bucks!

You may decide for yourself if the words easy and Oracle fit in the same sentence. Perhaps yes, perhaps no. Setting AMM for example is just one (or two) parameter(s) in the init.ora file but beware the time you might spend on bug research. Try to search the Metalink bug database for AMM, guess if you will get dozens or grosses of hits.

Are you still with me? Now, there are 5 major ares of database automation:

1. Backup automation: this is the oldest area of automation around the database. Very few DBAs bother to manually backup the databases. However, how many can automate restore and recovery 🙂 But someone should go manually through the logs of failed automated backups and debug the reasons and re-run the backups.

2. Monitoring automation: Grid Control is doing it for you. Just make sure that agents’ status is also monitored for if an agent is down/hung then there is no monitoring. Of course, we want to make sure that the agents’ monitoring mechanism is also under monitoring and so on and so on … 🙂 In my opinion Grid Control is the best tool developed by Oracle around the database. And it is free if you do not use the extra packs. Of course the values of Grid Control without the packs is like the value of a database with no users in it (but then security is not an issue, right?)

3. Automation of general DBA activities: in theory almost everything can be automated: startup and shutdown of the database, etc. Question is: is it really worth it? Do you automate Oracle installations and patching? Well patching can be semi-automated using the provisioning pack in case you have enough time to spend on setting up the system. I have seen all opinions on that subject. Can you automate autoextend of the file system when it is 99% full. No way, right? Do you automatically add datafiles to a tablespace when it is almost full?

4. Security automation: now this is a nice one. Often neglected by some DBAs automated security of the database stack can be extremely beneficial in case of should-not-have-happened situations. Often auditing is configured wrongly. The value you get from properly set database security automation is tremendous. Many companies understand very well the security risks and issues but whether they really comprehend it or not is an open question.

5. Automatic SQL tuning: it is my favorite (I have already and I will talk about it at Oracle Open World). I will post separately on the subject but here in brief what it is:

– Oracle automatically runs the SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates
– This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis
– You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week
– Automatic SQL tuning runs by default for at most one hour during a maintenance window
– Automatic SQL Tuning needs one-time configuration
– Available from 11g onwards
– One of the best new features in 11g and the best 11g new feature for Data Center DBAs’

Have you seen such promises on how DBA’s life can become easier due to automation?

  1. […] results with databases can be achieved by benefiting from all automated features offered by Oracle. Automating the Oracle Database is something overlooked and underestimated. Mostly by the old generation of DBAs who are often […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: