Migrating an Oracle database with the application attached to it C #: How to manage database migration?

Question

I have a C # application that works with an Oracle database and is already submitted. Now it's time to send a new release. The C # object model was revised and influenced the structure of the table.

If I post a new release, I need to take care of the existing data. By simply dropping tables and re-creating these tables, customers will not be satisfied.

To counter this problem, I have compiled SQL scripts that will modify the previously released database structure into a new database structure. During this, data is also transferred. SQL scripts are tied to a repository similar to the C # source code. A database patch is tested on a regular basis using CruiseControl.NET. NUnit tests run against a fixed database to identify inconsistencies between the database tables and the C # object model.

The whole procedure works, but I feel it can be done better. I find database migration very critical. A shipped application that does not work with an incorrectly corrected database does not matter. Data loss is unacceptable. These horror scenarios can make me think, so as not to change the database at all. Therefore, it is very important for me to fully trust the tools and methods that I use.

Last week I stumbled upon LiquiBase , and I asked myself - and now in SO:

What tools or techniques can help with database migration with less risk and more confidence? Are there any good books or online resources?

I am particularly interested in specific solutions for C # and Oracle, which can fit into the development procedure described above.

Answer 1

. :

• VERSION ,
• , , :
  • SQL script ,
  • SQL script , (, , , script ...)
  • • script DbVerXXXX.SQL, (XXXX - ). , - , . , .
• script, :
  • script , , .
  • VERSION

:

• , .
• , .

Answer 2

, , Oracle Dataguard. , , .

, , , , , , . , , , , . , .

Answer 3

, , , , . , , 1 (-)

CREATE TABLE Customer
CustomerID INT, 
FirstName string,
Surname string,
AddressLine1 string,
AddressLine2 string,
AddressLine3 string,
AddressLine4 string

2 , :

CREATE TABLE Address
AddressID INT,
CustomerID INT,
AddressLine1 string,
AddressLine2 string,
AddressLine3 string,
AddressLine4 string

Customer :

INSERT Address
CustomerID ,
AddressLine1 ,
AddressLine2 ,
AddressLine3 ,
AddressLine4 

SELECT
*
FROM Customer

Customer:

ALTER TABLE Customer
DROP COLUMNS 
AddressLine1 ,
AddressLine2 ,
AddressLine3 ,
AddressLine4 

. , , . - , . , , , .

, ,

- , , :

SELECT 
 *
FROM

  OldCustomerTable  OCT LEFT JOIN Address A
  ON OCT.CustomerID = A.CustomerID
WHERE 
  A.CustomerID IS NULL

- , ,

SELECT
  *
FROM 
 OldCustomerTable  OCT INNER JOIN Address A
  ON OCT.CustomerID = A.CustomerID
WHERE
  OCT.Address1 != A.Address1 
  OR OCT.Address2 != A.Address2
  OR OCT.Address3 != A.Address3

OCT.Address4!= A.Address4

, 1

SELECT
 CustomerID
 , COUNT(AddressID)
FROM
 Address
GROUP BY
 CustomerID
HAVING
 COUNT(AddressID) >1

Answer 4

@Zendar , , , . . , .

, , .

:

• Autopatch

​​ .