How do you version your Oracle database changes?

Question

I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files with an external version control system? Triggers? Other software?

Answer 1

At the sites I've worked at, any changes that need to be made to the production instance(s) must be scripted as change scripts that will run in SQL*Plus; in addition, scripts needed to recreate all schema objects from scratch must be kept up-to-date. All these scripts are checked into change control, and are migrated from there.

You can audit DDL changes or use DDL triggers to pick up changes, or even use diff software to compare two instances, but these methods are indiscriminate; often a developer will make and undo a number of changes to a schema (e.g. little test changes, creating dummy tables to test out concepts, etc) before working out what exactly needs to be changed.

Answer 2

I have thought and read a lot about this topic. This is a broad topic of configuration control and change management strategy. CMMI has a domain in this topic. Even in companies which has a accreditation of CMMI 3-5, they sometimes do not version control their databases.

This question should be answered while keeping in mind following constraints.

1. You have a keeper and every DML is executed by this keeper.
2. Other People have ability to execute DML statements.
3. You need only to log what changes has been done but you do not need to compare vast differences.
4. Your database design is done via external tool then is published to database. This external tool can be DML scripts in source control even. But key point is you source control this then publish to database.
5. You do not need to know instantaneous changes but from time to time: i.e. hourly, daily.
6. You have a defined server structure: Development, Test, Production. And a good testing strategy.

Answer 1

• if 1 ,4,6 is true then you can use an external source control. For example
  • Embercadero has a database change management tool (http://www.embarcadero.com/products/db-change-manager-xe). Which has ability to reverse engineer a database (Oracle) and put it in their source control. Then any number of developer, dba can reach this schema and make changes to it.
  • Oracle SQL Designer is similar to this approach.
  • Putting you create table scripts to source control (svn, mercurial etc) and maintaining them also same thing.
  • http://www.liquibase.org is automated approach of above.
  • I wrote code generator which generated DAL (Data Access Layer), DML (Create Table) statements. We put them source control and maintained there. I think dedicated solution like liquibase may work better.

This approach works well if have 6. You put DML statements,which is also a code, in source control and maintain it. Nobody will change Test and Production Servers without due consideration.

Disadvantages is if you make any changes to production or test servers for any reason, a quick bug fix, primary key change etc. You need to roll that changes to development server also. Since actually Development server is your GROUND TRUTH. Not other way around.

This is a very developer oriented approach. But when you first developing a new module it works pretty well.

Answer 2 - if 1 and 6 true:

Similar approach to answer 1 is maintaining a development server. Everybody uses it changes it. Than when time comes to update. You use a database comparison tool. Get these as scripts , put it under source control.

- Red Gate Schema Compare supports Oracle
- Embercadero has similar tool
- https://github.com/carbonfive/db-migration
- http://www.sumsoftsolutions.com/svco/ (I have not used this product but I believe it belongs to this category.)
- Rails Active Migration (http://www.oracle.com/technetwork/articles/kern-rails-migrations-100756.html)

Difference between Answer 1 and Answer 2 is, in answer 1 you collect DML statements for entire database and you store them. In answer 2 you need to store every version of change.

1. Start
2. V1
3. V2
4. V3
5. ...

If you put a column to a table and later decide to remove it. You scripts will show this in answer2 while in answer1 you will only see last version. And you need to compare V2 and V1 to see differences. Personally I like answer 1 better since I can easily compare Start and V3, V1 and V3. In answer2, I need to look for all changes. Also in answer 2 script in source control tends to be a big bang, complex script. Hard to find information.

Answer 3 If 3 is true. Note that in this situation you do not have constraint 6 , that is : you do not have Development, Test, Product servers. Only production server. You can use DML triggers to log what changes has been done. This is mostly used to discourage people from abusing their DML grants. If any problem occurs, you can find responsible. For this to work every person should connect with their user account and Application account should not have any DML grants. Since every developer knows Application account and can use it.

Answer 4 If you have 3 and 5. Note that in this situation you do not have constraint 6 , that is : you do not have Development, Test, Product servers. Only production server. Instead of trigger to store changes. You use an external tool find changes and store DML scripts in the source control.

If these tool has an ability to record who has done changes, it would be useful. Note that in this solution you loose extra DML which are done in intervals.

Answer 3

On some of our databases we are using DDL triggers to catch changes and save them to a table. We then have a web interface to pull up these previous versions. It has severe drawbacks, which is why I am looking for alternatives, but it is easy and is better than no version control.

Answer 4

We have used Schema Version Control for our 11g databases, but have had some problems with the software on 11.2. If it weren't for those problems which we are still working through, it would be a great product.

Answer 5

Just found an interesting tutorial on using LiquiBase to version Oracle.

Answer 6

We used to work with Oracle SQL Designer, which (I guess) has been replaced with SQL Developer Data Modeler now. http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

That was quite nice, esp. the ability to set DOMAINs for columns and save a lot of time creating common columns (mtime, ctime etc).

Answer 7

We use oracle-ddl2svn set of tools for automatization storing of oracle DDL schema in SVN.