DBAs learn that if they have to perform a task twice, it is worth the time and effort to automate it. There are scripts, and multiple scripts are part of the DBA arsenal. It feels that there is not always the time to automate because there is a way to quickly execute a script and pass along a script for further management. However, in the environments that we will be dealing with, automation is essential. There are too many databases, and the more tasks and times they are executed, it allows for the possibility of an error of a manual action to occur.
Automation prevents tasks from being skipped and be logged along with the script changes going through change control. Automation is an art of recognizing patterns that should not require hands-on keyboard and a state that is desired. Just as with security, data quality, and other processes, this is an iterative cycle. The tasks can be automated, and then new tasks and other processes that were not first considered can be added to the list. It can start with the initial database tasks, look to other components such as security, data migrations, and reach out to application with an additional circle of automation, including the environment tasks.
The art of automation requires reviewing older processes that might have been automated already and verify that they are still a valid way of doing things. The DBA scripts several processes with previous versions of the database, and the new features need to be updated in the scripts. The possibility of automating updates would be interesting but might not be possible. The circle of automation might start with some administration tasks and then continue to incorporate more and more. The attempt to automate everything might make sense, but there might be difficulties with some jobs, and review with new releases of the tools and databases to leverage to implement the automations.
DBA transformation understands the patterns of the tasks and workflows to be able to instruct which jobs and tasks should be automated. Even once jobs are automated, there are other opportunities to include new tasks or review any gaps or processes that should be considered. The application processes can then be included as well as other processes of the environment.
Tasks
There are many areas of the databases that can be automated. On the operations side, jobs like backups, patching, storage, and high availability tasks can have automated processes in place. Other options might be to look for tuning and security automations. Automating tasks is ongoing and it is not to take away from the positions of the DBAs but free up opportunities to perform other jobs in working on projects and reviewing various details of the databases such as new features.
Table 8-1 has a list of tasks that seem to be manual or might be something that is still being run manually because of resource constraints.
Table 8-1 Manual Tasks to review
Tasks | Execution | Validation | Success/Failure |
|---|---|---|---|
Backups | Scheduled | Available in OEM and log files | Reports with complete or errors for failures |
Adjusting resource capacity | Manual add storage, auto adjust memory | Additional space added | Additional space available or errors reported |
Patching | Executing the patching script with pre- or post-tasks | Available log | Reports with success or failure with error messages |
Standby Failover | Manual failover to standby database with data guard manager | Running on failover node | Logging into standby as primary, use data guard manager to validate the status of the primary and standby databases |
Tuning | Manual capture of SQL statements for tuning | Statements Captured | Benchmarks for performance improvements |
Security Audit Reports | Run reports for auditing | Review of reports | Captured data for audits |
Refresh of development data | Manually run jobs to refresh | Test database refreshed | Access to test database successful |
The tasks listed in Table 8-1 are just a sample of the various jobs that can be automated either through scripts that can be scheduled or part of a process that gets executed and passes criteria to the next job for execution.
Dependencies
Automations can come with dependencies in the system: either a task that must run first or a piece of data that is needed. An action can trigger another action and a piece of data that is needed can trigger another action. This is sometimes why automations might not be put in place because of complexities of the systems or steps that have to be followed. I will argue that it is for this reason that jobs like this need to be automated. Anytime that there is a dependency and a task requires a pre- or post-step these should be sequenced automated jobs.
The database provides DBMS_SCHEDULER and can handle multiple steps and passing in of values to the different processes in the job. Other scheduler software will also take this into consideration when executing the automated processes. There are many tasks that need to be run on the application and database side that need to support to validate execution and dependencies in the jobs.
The DBA transitioning to do automations has opportunities to script out processes, do database development, examine processes for migration to automation, and conduct testing with regular testing plans and QA.
Automating Test Plans
Database and software development require structured testing and QA. The database environments need to be refreshed to handle this too. As part of the database automation, the unit testing tools can be used and automated tests can be developed for the database testing. Application code is normally set up for the automation, but the database scripts of tables, data changes, etc., will work in the unit testing using something like SQLDeveloper. These tests can verify database objects and upgrade of objects.
Having the test plans automated makes it easier to test database changes, upgrades, and other processes on the database. There are other tools to use besides SQLDeveloper and there is also Real Application Testing (RAT) , and this captures workload with a way to regression test it. The unit testing is providing testing opportunities with Agile developers to test the code and database objects as part of an automation plan.
Figure 8-1 shows the place to get started with the SQLDeveloper tool for Unit testing .
Figure 8-1 Unit testing in SQLDeveloper
Automation processes and unit testing make it possible for DBAs to transition to discovering, maintaining, and creating automated processes around these database jobs and testing procedures.
Conclusion
With the Autonomous Database recently announced, the role of the DBA is definitely changing. Then it seems like more processes are automatically being configured. The database is able to deliver more automation on its own and provide configurations already implemented and even backups in place. The self-driving database is available in Oracle Cloud and it automates the management and performance tuning of the database.
The database automation of the Autonomous Database is based on machine learning and provides stability and performance because of the few human interactions of these needed tasks. From a security perspective, it also provides automated patching. This example is probably making you question why automation is another possibility for the DBA in transition, but it is the direction that processes have to go. The manual running of processes is definitely a thing of the past. Whatever can be automated should be, and if you can’t automation something can it be triggered by one that can.
The art is to understand the patterns and recognizing that procedures and tasks should be automated for the databases. A DBA transformation into this area is more coding and becoming a manager for the development processes and how to automate refreshes and unit testing for database components. The processes start specifically around the database and may then expand in scope as there are ways to look at how we work, and there are more possibilities to run code more efficiently and with less intervention.