Monday, February 8, 2021

Schema Compare for Oracle - a great version control tool

The name Schema Compare for Oracle clearly conveys the essence of the tool, the core functionality that this tool provides is that of comparing two Oracle database schemas. However, the fact that this same tool can provide one of the most effective ways to implementing database version control and change tracking capability is not obvious. So, how does the Schema Compare for Oracle make the leap from a schema compare tool to a database version control tool?  

First, let's clarify the phrase "database version control", what is it? Much like the code version control that every developer is familiar with, the goal of the database version control mechanism is to ensure that there is a reliable, unquestionable, source of the history of database schema changes that allows one to both identify any schema changes that may have happened between two versions and allow you to easily go back and forth between versions.  

Proceed with caution! In the case of databases, migrating the database schema from one version to another, whether you are moving to a new version of the database or reverting to an older version may cause loss of data because columns or whole tables that contain data in the current version but may not exist at all (and hence will be dropped) in the target version. 

Schema Compare for Oracle includes a cool feature: it allows you to take schema snapshots, small footprint, read-only files that contain 100% of the schema information. What does that mean? The schema snapshot is a frozen-in-time "picture" of your database (no data, just the schema). Furthermore, the included command line utility allows you to automate the process of taking the schema snapshots. 

How can you utilize the schema snapshots for database version control and change tracking? Schema Compare for Oracle allows you to compare any two schema snapshots, or any snapshot to a live database, and generate a target compliant synchronization script. Consider the following scenarios:

Scenario 1: you have taken nightly schema snapshots of your database and stored those into a repository. Now you are asked to identify the schema changes that happened between date1 and date2. All you would need to do is pick the right snapshot files from the repository based on those dates and compare them to see the differences. 

Scenario 2: you have stored the snapshots as in scenario 1. The request is to revert the database schema to where it was 2 days ago. In this case you take the snapshot from two days ago and compare it to your live database. Then generate the sync script to make the live database the same as the snapshot. You review the change script, especially the warnings section at the top to see if any data loss may occur in which case you would need take the appropriate steps to preserve that data. Then you execute the sync script and voila, your database schema has been reverted to the 2-days ago version. 

Scenario 3: The request this time is to propagate the latest schema changes to the production database. As in scenario 2 above, you pick the right snapshot from the repository (that is the latest stable - ready to ne published version) and compare that to your production database. Generate the sync script to make the production database the same as the snapshot in question. Take the same precautions described above to ensure no data is lost, and then execute the script. 

Download the Schema Compare for Oracle now. 


Post a Comment