Consolidation of patch transformations for database replication

• US 8,346,723 B2
• Filed: 03/30/2010
• Issued: 01/01/2013
• Est. Priority Date: 03/30/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gonzalez, Zandro Luis
• Primary Examiner(s): Ruiz, Angelica
• Application Number:
  US12/749,799
• Publication Number:
  US 20110246422A1
• Time in Patent Office: 1,008 Days
• Field of Search: None
• US Class Current: 707/610
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

Embodiments of the invention provide for applying modifications to database instances in a way that allows some of instances to be modified without requiring that all of the instances be modified. Patching less than all of a plurality of replicated database instances can comprise applying a patch to a first version of a table in a first database instance. The first version of the table may be replicated in a corresponding first version of the table in a second database instance. Applying the patch to the first version of the table in the first database instance can result in a second version of the table in the first database instance. Transformations caused by applying the patch to the first version of the table in the first database instance can be consolidated based on differences between the first version of the table and the second version of the table.

Claims

1. A method for patching less than all of a plurality of replicated database instances, the method comprising:

applying, by a computer system, a patch to a first version of a table in a first database instance of the plurality of replicated database instances, wherein the first version of the table is replicated in a corresponding first version of the table in a second database instance of the plurality of replicated database instances and wherein applying the patch to the first version of the table in the first database instance results in a second version of the table in the first database instance;

consolidating, by the computer system, transformations caused by applying the patch to the first version of the table in the first database instance based on differences between the first version of the table in the first database instance and the second version of the table in the first database instance; and

performing, by the computer system, a replication process between the first database instance and the second database instance, wherein the second version of the table in the first database instance is a source of the replication process and the first version of the table in the second database instance is a target of the replication process, wherein the replication process applies the consolidated transformations to the table in the second database instance without applying the patch to the second database instance.

2. The method of claim 1, wherein consolidating transformations caused by applying the patch to the first version of the table comprises:

identifying changes to one or more Data Manipulation Language (DML) statements between the first version of the table and the second version of the table;

determining a statement type for each of the one or more DML statements in the second version of the table; and

generating based on the statement type one or more consolidation queries for each of the one or more DML statements in the second version of the table.

3. The method of claim 2, wherein performing the replication process comprises executing the one or more consolidation queries.

4. The method of claim 3, further comprising:

generating, by the computer system, a consolidation query table in the second database instance; and

saving, by the computer system, the consolidation queries in the consolidation query table.

5. The method of claim 3, wherein the statement type comprises an update type and wherein generating the one or more consolidation queries comprises converting the update type statement to a corresponding merge type statement and converting the merge type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the merge type statement.

6. The method of claim 3, wherein the statement type comprises a merge type and wherein generating the one or more consolidation queries comprises converting the merge type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the merge type statement.

7. The method of claim 3, wherein the statement type comprises an insert type and wherein generating the one or more consolidation queries comprises converting the insert type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the insert type statement.

8. The method of claim 3, wherein the statement type comprises a delete type and wherein generating the one or more consolidation queries comprises converting the delete type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the delete type statement.

9. A system comprising:

at least one data repository storing a plurality of replicated database instances including at least a first version of a table in a first database instance and a corresponding, replicated first version of the table in a second database instance of the plurality of replicated database instances and; and

a computer communicatively coupled with the at least one data repository and executing instructions for patching less than all of the plurality of replicated database instances, wherein the instruction cause the computer to apply a patch to the first version of the table in the first database instance, wherein applying the patch to the first version of the table in the first database instance results in a second version of the table in the first database instance, and wherein the instructions further cause the computer to consolidate transformations caused by applying the patch to the first version of the table in the first database instance based on differences between the first version of the table in the first database instance and the second version of the table in the first database instance and perform a replication process between the first database instance and the second database instance, wherein the second version of the table in the first database instance is a source of the replication process and the first version of the table in the second database instance is a target of the replication process, wherein the replication process, wherein the replication process applies the consolidated transformations to the table in the second database instance without applying the patch to the second database instance.

10. The system of claim 9, wherein consolidating transformations caused by applying the patch to the first version of the table comprises:

identifying changes to one or more Data Manipulation Language (DML) statements between the first version of the table and the second version of the table;

determining a statement type for each of the one or more DML statements in the second version of the table; and

generating based on the statement type one or more consolidation queries for each of the one or more DML statements in the second version of the table.

11. The system of claim 10, wherein performing the replication process comprises executing the one or more consolidation queries.

12. The system of claim 11, wherein the instructions further cause the computer to generate a consolidation query table in the second database instance and save the consolidation queries in the consolidation query table.

13. The system of claim 11, wherein the statement type comprises an update type and wherein generating the one or more consolidation queries comprises converting the update type statement to a corresponding merge type statement and converting the merge type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the merge type statement.

14. The system of claim 11, wherein the statement type comprises a merge type and wherein generating the one or more consolidation queries comprises converting the merge type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the merge type statement.

15. The system of claim 11, wherein the statement type comprises an insert type and wherein generating the one or more consolidation queries comprises converting the insert type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the insert type statement.

16. The system of claim 11, wherein the statement type comprises a delete type and wherein generating the one or more consolidation queries comprises converting the delete type statement to a select subquery wherein column data of the subquery is the same as column data of the target would be after application of the delete type statement.

17. A machine-readable memory having stored thereon a series of instructions which, when executed by a processor, cause the process to perform a method for patching less than all of a plurality of replicated database instances, the method comprising:

applying a patch to a first version of a table in a first database instance of the plurality of replicated database instances, wherein the first version of the table is replicated in a corresponding first version of the table in a second database instance of the plurality of replicated database instances and wherein applying the patch to the first version of the table in the first database instance results in a second version of the table in the first database instance;

consolidating transformations caused by applying the patch to the first version of the table in the first database instance based on differences between the first version of the table in the first database instance and the second version of the table in the first database instance; and

performing a replication process between the first database instance and the second database instance, wherein the second version of the table in the first database instance is a source of the replication process and the first version of the table in the second database instance is a target of the replication process, wherein the replication process applies the consolidated transformations to the table in the second database instance without applying the patch to the second database instance.

18. The machine-readable memory of claim 17, wherein consolidating transformations caused by applying the patch to the first version of the table comprises:

identifying changes to one or more Data Manipulation Language (DML) statements between the first version of the table and the second version of the table;

determining a statement type for each of the one or more DML statements in the second version of the table; and

generating based on the statement type one or more consolidation queries for each of the one or more DML statements in the second version of the table.

19. The machine-readable memory of claim 18, wherein performing the replication process comprises executing the one or more consolidation queries.