Unobtrusive point-in-time consistent copies

• US 7,133,884 B1
• Filed: 11/26/2003
• Issued: 11/07/2006
• Est. Priority Date: 11/26/2003
• Status: Active Grant

• Current Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Original Assignee: BMC Software Incorporated (KKR & Co., Inc.)
• Inventors: Ball, Linda S., McDonald, Kenneth M., Price, Thomas G., Murley, Michael S., Dee, Stanley J.
• Primary Examiner(s): LE, UYEN T
• Application Number:
  US10/723,677
• Time in Patent Office: 1,077 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2308 Concurrency control transaction processing G06F9/466
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Techniques to create physically and transactionally consistent copies of one or more database objects without impacting the availability of the target database objects are described. In one technique, a consistent copy is created as of the time a copy utility is executed. In another technique, a consistent copy is created as of an arbitrary specified time. This approach modifies a prior copy to bring it “up-to-date.” In yet another technique, a consistent copy is created by starting with a current snapshot of the target database objects and selectively removing updates from it back to a user specified arbitrary point-in-time. Each of the described techniques generate consistent copies of the target database objects without blocking users from accessing the target objects during the copy operation.

Claims

1. An unobtrusive database object copy method, comprising:

identifying one or more source database objects;

creating a snapshot of the one or more source database objects at a point-in-time in a manner that does not substantially block access to the source database objects;

identifying changes to the source database objects as of the point-in-time that do not exist in the snapshot;

updating the snapshot to reflect the identified changes;

identifying inflight units of work associated with the source database objects as of the point-in-time, wherein each inflight unit of work is associated with one or more database object updates; and

removing each of the one or more database object updates associated with each identified inflight unit of work from the snapshot.

2. The method of claim 1, wherein the act of identifying one or more source database objects comprises identifying one or more tables in a relational database.

3. The method of claim 2, further comprising identifying one or more indexes associated with the one or more tables.

4. The method of claim 1, wherein the act of creating comprises generating a snapshot using one or more intelligent storage devices.

5. The method of claim 1, wherein the act of removing does not include removing database object updates that effect structural changes in the database objects.

6. The method of claim 1, wherein the act of identifying changes further comprises:

identifying a page in memory in the snapshot that each identified change is associated with; and

sorting the identified pages.

7. The method of claim 6, wherein the act of updating comprises updating pages in the snapshot in their sorted order.

8. The method of claim 1, wherein the act of identifying inflight units of work further comprises:

identifying a page in memory in the snapshot that each identified inflight unit of work is associated with; and

sorting the identified pages.

9. The method of claim 8, wherein the act of removing comprises removing each of the one or more database object updates from the snapshot in their sorted order.

10. The method of claim 9, wherein the act of removing does not include removing database object updates that effect structural changes in the database objects.

11. An unobtrusive database object copy method, comprising:

identifying one or more source database objects in a database;

determining a point-in-time;

obtaining a prior copy of the identified one or more source database objects, said prior copy having a creation time before the point-in-time;

identifying inflight units of work associated with the source database objects as of the point-in-time, wherein each inflight unit of work is associated with one or more database object updates, said identified inflight units of work including a unit of work having an earliest start time, the earliest start time being closer to the creation time of the prior consistent copy than a start time associated with any other identified inflight unit of work;

applying database log entries to the prior copy from the creation time until the earliest start time; and

applying database log entries to the prior copy from the earliest start time until the point-in-time only if they are not associated with inflight units of work.

12. The method of claim 11, wherein the act of identifying one or more source database objects comprises identifying one or more tables in a relational database.

13. The method of claim 12, further comprising identifying one or more indexes associated with the one or more tables.

14. The method of claim 11, wherein the act of determining comprises determining a point-in-time that is prior to initiation of the unobtrusive database object copy method.

15. The method of claim 11, wherein the act of obtaining comprises obtaining a prior copy against which database log entries may be applied.

16. The method of claim 11, wherein the act of identifying inflight units of work further comprises:

identifying a page in memory in the prior copy associated with each database object update between the creating time and the point-in-time;

identifying a page in memory in the prior copy associated with each database object update associated with each identified inflight unit of work; and

sorting the identified pages.

17. The method of claim 16, wherein the act of applying database log entries to the prior copy from the creation time until the earliest start time comprises applying said database log entries in their sorted page order.

18. The method of claim 16, wherein the act of applying database log entries to the prior copy from the earliest start time until the point-in-time comprises applying said database log entries in their sorted page order.

19. The method of claim 11, further comprising substituting the point-in-time consistent copy for the source database objects in the database.

20. The method of claim 11, further comprising:

blocking access to the source database objects in a source database;

replacing the source database objects in the source database with the point-in-time consistent copy of the database objects; and

permitting access to the point-in-time consistent copy of the database objects in the database.

21. An unobtrusive database object copy method, comprising:

identifying one or more source database objects in a database;

determining a point-in-time;

creating a snapshot of the one or more source database objects at a time after the point-in-time and in a manner that does not substantially block access to the source database objects in the database;

identifying changes to the source database objects as of the point-in-time that do not exist in the snapshot;

updating the snapshot to reflect the identified changes;

removing all updates from the snapshot made after the point-in-time;

identifying inflight units of work associated with the source database objects as of the point-in-time, wherein each inflight unit of work is associated with one or more database object updates; and

removing each of the one or more database object updates associated with each identified inflight unit of work from the snapshot.

22. The method of claim 21, wherein the act of identifying one or more source database objects comprises identifying one or more tables in a relational database.

23. The method of claim 22, further comprising identifying one or more indexes associated with the one or more tables.

24. The method of claim 21, wherein the act of determining comprises determining a point-in-time that is prior to initiation of the unobtrusive database object copy method.

25. The method of claim 21, wherein the act of removing each of the one or more database object updates associated with each identified inflight unit of work does not include removing database object updates that effect structural changes in the database objects.

26. The method of claim 21, wherein the act of identifying changes further comprises:

identifying a page in memory in the snapshot that each identified change is associated with; and

sorting the identified pages.

27. The method of claim 26, wherein the act of updating comprises updating pages in the snapshot in their sorted order.

28. The method of claim 21, wherein the act of removing all updates further comprises:

identifying a page in memory in the snapshot that each change to be removed is associated with; and

sorting the identified pages.

29. The method of claim 28, wherein the act of removing all updates further comprises removing all updates from the snapshot made after the point-in-time in their sorted order.

30. The method of claim 21, wherein the act of identifying inflight units of work further comprises:

identifying a page in memory in the snapshot that each identified inflight unit of work is associated with; and

sorting the identified pages.

31. The method of claim 30, wherein the act of removing each of the one or more database object updates associated with each identified inflight unit of work comprises removing each of the one or more database object updates associated with each identified inflight unit of work from the snapshot in their sorted order.

32. The method of claim 21, wherein the act of removing each of the one or more database object updates associated with each identified inflight unit of work does not include removing database object updates that effect structural changes in the source database objects in the snapshot.

33. The method of claim 21, further comprising substituting the point-in-time consistent copy for the source database objects in the database.

34. The method of claim 21, further comprising:

blocking access to the source database objects in the source database;

replacing the source database objects in the source database with the point-in-time consistent copy of the database objects; and

permitting access to the point-in-time consistent copy of the database objects in the database.

35. A program storage device, readable by a programmable control device, comprising instructions stored therein for causing the programmable control device to:

identify one or more source database objects;

create a snapshot of the one or more source database objects at a point-in-time in a manner that does not substantially block access to the source database objects;

identify changes to the source database objects as of the point-in-time that do not exist in the snapshot;

update the snapshot to reflect the identified changes;

identify inflight units of work associated with the source database objects as of the point-in-time, wherein each inflight unit of work is associated with one or more database object updates; and

remove each of the one or more database object updates associated with each identified inflight unit of work from the snapshot.

36. The program storage device of claim 35, wherein the instructions to remove do not include instructions to remove database object updates that effect structural changes in the database objects.

37. A program storage device, readable by a programmable control device, comprising instructions stored therein for causing the programmable control device to:

identify one or more source database objects in a database;

determine a point-in-time;

obtain a prior copy of the identified one or more source database objects, said prior copy having a creation time before the point-in-time;

identify inflight units of work associated with the source database objects as of the point-in-time, wherein each inflight unit of work is associated with one or more database object updates, said identified inflight units of work including a unit of work having an earliest start time, the earliest start time being closer to the creation time of the prior consistent copy than a start time associated with any other identified inflight unit of work;

apply database log entries to the prior copy from the creation time until the earliest start time; and

apply database log entries to the prior copy from the earliest start time until the point-in-time only if they are not associated with inflight units of work to generate a consistent point-in-time copy.

38. The program storage device of claim 37, further comprising instructions to substitute the point-in-time consistent copy for the source database objects in the database.