I'm having two oracle databases. A is used for production, B is some kind of staging instance where people are preparing content that is brought to the production system over night. It is a fully working structural clone of A that will also give you a preview of the upcoming next release. While the overnight synchronization is completely automatized (by scripts), structural changes are first applied to B and are two days later applied to A. This depends on manual operations and is build up on sql and sh scripts.

I'm wondering if there are features that lead to better maintainability and can be utilized for manually triggered synchronization. What would be the a modern approach automatizing structural database changes brought from B to A with Oracle Database 11g?

Please excuse this vague question. Since I'm not experienced in this topic I'm looking more for a starting point on what to read than a full solution to my problem.


It doesn't seem to make sense to fully automate structural changes. Presumably, there is some probability that changes are applied to B and testing reveals problems. So a human ultimately needs to make a decision about whether to promote, right?

Assuming that your scripts are stored in source control, a process that pulls the scripts from source control and runs them in each of your environments (presumably you have at least dev and test instances in addition to production and staging) is generally the best solution. That ensures that scripts are applied consistently in every environment. You still need humans to do something to indicate that a particular set of changes are ready for the next environment but since that reflects a human's decision that testing of those changes is complete, that's hard to avoid. And, of course, specific implementation details depend on what source control tool you're using and how your organization uses the tool.

| improve this answer | |

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