Wouldn’t it be nice if there was an easy way to free up the time spent performing some of those routine manually-performed DBA tasks? Toad’s Automation Designer helps accomplish this by providing a hassle-free method to automate tasks.
Scenario 1: Check if the database server is up, and if so, then run a script and email me the results. If the server is not up, then email me as well letting me know.
Solution: Under the Utilities menu, click on Automation Designer. To begin, click on “Create new App”. Click the Control tab in the right-side pane and choose the “If..then” action (third icon).
Once we have the logic in place, we can add the additional actions.
- The first action to add would be the “TNS Ping” action located under the Utilities tab. This will allow us to check to see if the database is up and running.
- Next, under the DB Misc tab, we can add the “Execute Script” action. Right click on this action and choose Properties. Here you can select which script you would like to execute and also where you want the output to be saved.
- Third, under the Utilities tab, select the “Email” action and add that. In the properties for this action you can point to the file export location and select that as an attachment.
- Lastly, we will add the “Email” action again to notify us if the server is down. Please view the screenshot to see how this looks when completed.
Scenario 2: What if the server is down and the script never gets executed? Do we try again?
Solution: We can build on top of what we already have by adding another action. Under the Control tab, we can add the “Repeat..until” action. The key part is also adding the “Check if file exists” action located under the File Management tab. Now what we have in place is that we will continue to check if the server is up and run a script until we find the output file. Once the output file is located, we will know the script has been executed properly and we can stop the repeat loop. Please view the screenshot below to see how this looks.
Scenario 3: What if the server is down for a while? Will the task continue to repeat nonstop?
Solution 3: If we want to pause the repeat loop for a specific amount of time, then we can add the “Pause” action. You can place the action in different places to see what fits your logic the best. As you can see from my screenshot, I felt it was best to put it as the last action so the pause would occur once everything has been processed and then repeated.
Hopefully this blog has shown that we can automate a variety of tasks with different combinations of logic.