Our application uses database (mostly Oracle), and database is at the core. Each customer has its own database, with its own copy of application. Now with each new release of our product, we also need to update the database schema. These changes are adding new tables, removing columns, manipulating data etc. How do the people handle this? Are there any standard processes for this? EDIT:- The main issue is the databases are huge with many tables and more of huge amount of data. We provide the scripts and some utilities to manipulate the data. How to handle the failures and false negatives? More of looking for this kind articles. http://thedailywtf.com/Articles/Database-Changes-Done-Right.aspx
It depends on your data, but in general you want to ensure that all the data makes it through the upgrade intact.
You will probably want to prepare some DDL scripts that create/modify structures, maybe even one script per structure. You will also probably need some DML scripts that update/backup/transform data during this process. Again, how much of this you will need to do will depend on the nature of the upgrade.
For testing purposes, it might be a good idea to try and get some (or all, if possible) of the client's data before the scheduled upgrade and test it out on their data to see if there's any data in their systems that could break the upgrade process.
Finally, you will want to take a full back up of the client's database right before begining their upgrade, so you can restore if things get weird.
Some other points that might be good to think about:
I recommend you look into myBatis migrations. You can use this to wrap your modification (and rollback) scripts into a simple commmand than can be run as part of an installation/upgrade.