TOAD acronym stands or Tools for Oracle Application Development. Amazing since now we have TOAD for SQL-Server, TOAD for DB2, TOAD for MySQL, TOAD for managing your database.
REALLY, TOAD has come a long way. You can check-out everything you want to know about TOAD here!
However, this post is not about DELL or Quest, but a specific capability that TOAD offers, which I find very useful. That feature is :- Oracle DB objects management!
Essentially, as DB configuration manager one must ensure that the application upgrade script performed its objective; All its objective; Only its objective. In other words, when DBA executes a deployment script on a weekend, he or she wants to be sure that script did all it needs to without messing something else up.
One effective way, I found is to use TOAD Compare SCHEMA or Compare DB… In this post we will explore this TOAD feature.
Scenario:
DBA is executing a application upgrade script, provided by development/configuration management team that changes DB structure. This script has been tested in unit /QA environment. This is being done at Sunday 2:00 AM in PRODUCTION.
- Adds columns to certain tables; Adds some constraints; Removes one constraint; Changes several packages (or procedures/functions); Modifies some triggers; Adds a view and drops another view.
May be some other changes…
- Adds some FGAC policies; Removes some users and roles.; Provides some grants to certain users and roles; Changes audit setting for some DB objects.
Of course DBA is executing this script at 2:00 AM on Sunday morning.
How can the DBA be confident, that all the changes and only the changes that were intended were actually applied.
TOAD privdes capabilities for schema (or DB) compare feature that helps the DBA to capture the changes, identify the differential in the database over time. It is a very effective tool.
Application upgrade PROCESS in DB
Step 1: Prior to changes DBA captures current schema, in “definition file”.
Step 2: Executes the script to implement the changes.
Step 3: Execute compare schema (or DB) utility in TOAD between current schema (or DB) to captured definition file.
That’s it.
At this point DBA still has to worry about a whole suite of things to make the application available.
Let us take a quick walkthrough the Schema/DB compare steps in TOAD.
Capture the current schema/DB definition in SDXML file.
From main menu, DBA can generate Schema / DB definition file using Export menu.
The Export Schema screen provides options to export DDLs or schema definition. Also it provides options for setting various options and filters (including storage clauses) to be set and saved.
Once extraction is complete (this could take few minutes depending on the schema (or DB) size (number of objects), TOAD will display the inventory summary of the objects.
DBA can close this export window in TOAD and perform the application upgrade.
Once the upgrade is complete, DBA can use compare utility to compare the upgrade schema (current) to the schema that was captured in previous step to produce the differential report.
This option lets the DBA compare current schema to a target schema or saved schema definition. The target for compare can be selected by clicking on “+” sign.
When compare is executed, TOAD provides a very intuitive report, as follows. This report can be extracted as XL file and emailed to other team members / management.
In addition, there are several other options to analyze the schema comparison report.
Any DBA who is intimidated by the additional manual steps to be performed at 2:00 am Sunday morning, should check out TOAD automation designer (which generates DBMS_SCHEDULER scripts).
In any case, I have found this tool very useful. Most of the time in retaining my sanity and sometime my JOB ;-)
There is nice video on TOAD forum (by Mark Kurtz): http://www.quest.com/tv/All-Videos/1254935431001/Compare-and-Sync-Schemas-with-Toad-for-Oracle/Video/
If you have any questions, please contact us @ www.tsp-inc.us
Good Luck!
