|
|
 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Bert Scalzo's Blog
|
|
| Bert |
Wednesday, February 27, 2008 9:01 AM |
One key task for many DBAs is maintaining a database version control or change revision history. Database version control affords the DBA the ability to restore the database to any prior state, or to detect unplanned database revisions that cause the database to be out of compliance with its specification. One could argue that the latter scenario would unlikely happen in a well controlled production environment – however sometimes even the best run shops can experience this. Being able to catch this – and then to quickly and accurately restore the database to its desired state is critical. Thus many DBA’s have several generally accepted methods for handling this special need, including the following:
- Simply rely on the DBA’s knowledge – don’t laugh, because many people still do it this way (although few will openly admit since its reliability is less than 100%)
- For each database version event, keep copies of the DDL script(s) in source code control software (e.g. CVS), and simply perform textual differences between the current database DDL and the historical copies
- Use expensive and/or complex database version management software, such as Oracle’s Change Management Pack for OEM, Embarcadero’s Change Manager, BMC’s Change Manager
- Use inexpensive and less robust database version management software, such as EMS DB Comparer for Oracle, Soft Tree Tech’s DB Difference Expert, Orbium’s Comparenicus for Oracle , Alratec’s CompareMyDB, DKG’s DB Diff for Oracle
- Use freeware/shareware database version management software, such as DB Side-by-Side for Oracle, DTM’s Schema Comparer, Schema Compare Tool for Oracle, Oracle Schema Compare Tool, Schema Version Control for Oracle, DB Explorer, TOYS (Tool for Organizing Your Schemas)
- Use general purpose DBA tools which include schema compare and sync features, such as Toad with DBA Module, Aqua Data Studio, DB Solo, DB Orchestra for Oracle, PL/SQL Developer
- Use data modeling tools that offer physical model to database compare and sync capabilities, such as Toad Data Modeler, CA’s Erwin, Sybase’s PowerDesigner, or Embarcadero’s ER Studio
With so many choices, what’s a DBA to do? Well of course turn to Toad – which offers a very powerful but unfortunately seldom used feature: Database Definition Files. Think of the Toad def files as “snapshots” of the Oracle Data Dictionary definitions or meta-data at a given point in time. These are simply binary files which can be saved in your version control software – thus Toad offers you a combination of choices 2 and 7 from above. No other DBA tool offers such a unique and flexible capability. So let’s see how it works.
Use Case: I want to record a snapshot of my MOVIES schema, make a series of ad-hoc changes, and then see what has changed – because I did not track it while doing it.
First, I need to capture the Data Dictionary snapshot for my MOVIES schema. I do this via Main Menu->Database->Export->Generate Schema Script. I then simply choose the MOVIES schema. But notice that instead of specifying a DDL Filename (i.e. the text file where the DDL script would be generated, I chose to create a Data Dictionary Definition File (or snapshot) located in my C:\Temp directory. This is a binary format file, so you cannot read it. But Toad offers several places where you can use these files.
Second, I need to make some changes to my MOVIES schema. I’d do that via the Toad Schema Browser. I’ll do several different kinds of changes:
- Drop an existing table
- Create a new table
- Drop a table column
- Add a table column
- Change a table column data type
- Create a new index on new column
OK, so now my database has been modified – and I did not write down the specifics of all the changes I made. So I’ll use Toad’s Schema Compare to answer that Question.
Third and finally, I’ll compare the database to the def file (i.e. snapshot) to see a nice report of all the changes I made. I do this via Main Menu->Database->Compare->Schemas. But instead of comparing schemas using two database connections, I instead compare my live database to the def file captured earlier. So I’m thus comparing my before and after change database schema states. Notice that I simply choose the comparison source to be the def file created earlier in my C:\Temp directory. It’s that simple!
So now when I press the compare button, I’m going to see a nice report of all the changes I made and did not record. Here’s the report – note how it identifies the specific cases of the six kinds of changes I said I’d do. If I had been storing my Data Dictionary def files (i.e. snapshots) in my version control system – I would have to do a check-out for read operation first. Hey – that sounds like a candidate for using Toad’s Team Coding!
There’s also a very unique advantage to using Toad Data Dictionary def files – they are lightning fast. Toad does not have to SELECT against the Oracle database nor send SQL Net traffic in order to perform this task. So this comparison will run faster than if I had specified two database connections as source and target. And if I were comparing two prior versions (i.e. thus comparing two Data Dictionary def files), the comparison will run so fast, that if I blink I’ll miss it.
Here’s a breakdown of the screens which can work off def files:
- Generate Schema Script (can create def file)
- Compare Schemas
- HTML Schema Doc Generator
- Generate Database Script (can create def file)
- Compare Databases
So try them out. You’ll not be sorry … |
|
| Permalink |
Trackback |
|
|
|
|
|
|
|
|
|