How do you version your Oracle database changes?
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.
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.
- You have a keeper and every DDL is executed by this keeper.
- Other People have ability to execute DDL statements.
- You need only to log what changes has been done but you do not need to compare vast differences.
- Your database design is done via external tool then is published to database. This external tool can be DDL scripts in source control even. But key point is you source control this then publish to database.
- You do not need to know instantaneous changes but from time to time: i.e. hourly, daily.
- 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), DDL (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 DDL 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 DDL statements for entire database and you store them. In answer 2 you need to store every version of change.
- Start
- V1
- V2
- V3
- ...
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 DDL triggers to log what changes has been done. This is mostly used to discourage people from abusing their DDL 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 DDL 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 DDL 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 DDL which are done in intervals.
Just found an interesting tutorial on using Liquibase to version Oracle.