Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
This is really similar to dba.stackexchange.com/questions/2/… - you might get some non-Oracle-specific ideas for this from there! –  Gaurav Jan 5 '11 at 16:45
    
@Gaurav I saw that, but I wanted some Oracle specific answers. –  Leigh Riffel Jan 5 '11 at 16:47
    
Ah, cool, just thought I'd bring it up in case you hadn't :) –  Gaurav Jan 5 '11 at 16:49
    
Not what you were asking for but related: edition based redefinition –  Jack Douglas Nov 15 '11 at 14:11
    
Liquibase is easy to configure and integrate with existing java application. here is a simple and basic tutorial on Liquibase. –  kamal 14 hours ago
add comment

8 Answers 8

up vote 11 down vote accepted

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.

share|improve this answer
1  
My workplace has a similar workflow to the one mentioned here –  Sathya Jan 5 '11 at 17:25
add comment

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

share|improve this answer
    
We use git and liquibase to manage our code and releases effectively. –  Raj Oct 23 '13 at 19:37
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
Thanks for posting this. –  Leigh Riffel May 7 '13 at 13:33
    
Leigh Riffel, you're welcome! I'm author of oracle-ddl2svn :) –  popalka May 8 '13 at 9:42
add comment

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).

share|improve this answer
    
Does Data Modeler save database changes to packages, tables, etc.? –  Leigh Riffel Jan 6 '11 at 16:39
    
I don't know. Might need to give it a try. –  Sebastian Roth Jan 6 '11 at 16:50
add comment

Have a look on DBmaestro TeamWork which its database enforced change management approach.


disclosure: I work for dbMaestro

share|improve this answer
    
Please read this meta post: promoting tools you are affiliated with an be acceptable if it isn't the only reason you contribute, but you must disclose your relationship with the company or product –  Jack Douglas Nov 26 '13 at 17:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.