In memory streaming with disk backup and recovery of messages captured from a database redo stream

• US 8,374,966 B1
• Filed: 12/02/2002
• Issued: 02/12/2013
• Est. Priority Date: 08/01/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Arora, Nimar Singh, Stamos, Jim
• Primary Examiner(s): Reagan, James A
• Application Number:
  US10/308,879
• Time in Patent Office: 3,725 Days
• Field of Search: 707 1- 10, 707100-1041, 705/52
• US Class Current: 705/52
• 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
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Techniques are disclosed for sharing information in a wide variety of contexts. An information sharing system is described that allows both an explicit capture process and an implicit capture process to add information items to a staging area. Further, the information sharing system supports both implicit and explicit consumption of information items that are stored in said staging area. A rules engine is provided to allow users to create and register rules that customize the behavior of the capture processes, the consuming processes, and propagation processes that propagate information from the staging areas to designated destinations. Techniques are also described for achieving exactly-once handling of sequence of items, where the items are maintained in volatile memory. Techniques are also provided for recording DDL operations, and for asynchronously performing operations based on the previously-performed DDL operations.

Claims

1. A method for achieving exactly-once handling of a plurality of items, the method comprising steps of:

for each item in the plurality of items that arrives at a destination site from another site, performing the steps of

reading a sequence number associated with the item;

determining whether the item has already been processed based on a comparison between the sequence number associated with the item and a persistently stored watermark value; and

processing said item only if the item has not already been processed;

as the items are processed at the destination site, based on the sequence numbers associated with said items, repeatedly updating said persistently stored watermark value to indicate which items have been processed;

periodically sending a message from said destination site to said other site, wherein said message indicates which items have been processed at said destination site; and

wherein the steps are performed by one or more computing devices.

2. The method of claim 1 wherein:

the items correspond to pieces of transactions; and

the watermark value indicates a commit time of a transaction whose changes have been completely applied at the destination site.

3. The method of claim 2 wherein the watermark value indicates a commit time of a transaction whose changes have been most recently applied by an apply engine.

4. The method of claim 2 wherein the watermark value indicates a commit time, where all transactions with commit times at or below said watermark value are guaranteed to have been applied by an apply engine.

5. The method of claim 2 wherein the step of repeatedly updating a persistently stored watermark value includes repeatedly updating the persistently stored watermark value to reflect the commit time of the most recent transaction to be fully applied at said destination site.

6. The method of claim 1 wherein:

the items correspond to pieces of transactions; and

the step of periodically sending a message includes periodically sending a message that indicates an oldest change time of a transaction whose changes have not been completely applied at the destination site.

7. The method of claim 6 further comprising the step of repeatedly updating a persistently stored OLDEST CSN value to reflect the oldest change time of the oldest transaction that has not yet been fully applied at said destination site.

8. The method of claim 7 further comprising the step of, in response to a failure at the destination site, reading the persistently stored OLDEST CSN value and requesting items to be resent to the destination site based on the persistently stored OLDEST CSN value.

9. The method of claim 1 further comprising the step of, in response to a failure at the destination site, reading the persistently stored watermark value and requesting items to be resent to the destination site based on the persistently stored watermark value.

10. The method of claim 1 wherein:

the other site is a source site; and

the method further comprises the steps of;

said source site periodically storing to persistent storage information received in said message; and

after a failure at said source site, said source site performing the steps of reading said information from persistent storage; and

resending items to said destination site based on said information.

11. The method of claim 1 wherein:

the other site is an intermediary site that receives said items from a third site; and

the method further comprises the steps of;

said intermediary site periodically storing to persistent storage information received in said message; and

after a failure at said intermediary site, said intermediary site performing the steps of

reading said information from persistent storage; and

requesting said third site to resend items to said intermediary site based on said information.

12. The method of claim 11 wherein the intermediary site periodically sends said information to said third site.

13. The method of claim 1, wherein:

the items include pieces of a first transaction and pieces of a second transaction, where the first transaction committed before the second transaction; and

the step of repeatedly updating said persistently stored watermark value includes the steps of

if the destination site fmishes applying the second transaction before the destination site fmishes applying the first transaction, then the destination site persistently stores information to indicate that the second transaction is fully applied, without changing said persistently stored water mark; and

if the destination site fmishes applying the first transaction before the destination site fmishes applying the second transaction, and all transactions with commit times below said first transaction have been fully applied, then updating said persistently stored water mark based on a sequence number associated with said first transaction.

14. The method of claim 1 wherein:

the items identify changes made by transactions performed in a first database; and

the destination site processes said items by causing said changes to be reflected in a second database that is different from said first database.

15. The method of claim 14 wherein the step of causing said changes to be reflected includes constructing a set of one or more database commands based on the changes associated with a particular transaction that was executed in said first database, and executing said set of one or more database commands as a transaction in said second database.

16. A non-transitory computer-readable storage medium that stores instructions for achieving exactly-once handling of a plurality of items, said instructions, which when executed by one or more processors, cause the one or more processors to perform steps of:

for achieving exactly-once handling of a plurality of items, the method comprising steps of;

for each item in the plurality of items that arrives at a destination site from another site, performing the steps of;

reading a sequence number associated with the item;

determining whether the item has already been processed based on a comparison between the sequence number associated with the item and a persistently stored watermark value; and

processing said item only if the item has not already been processed;

as the items are processed at the destination site, based on the sequence numbers associated with said items, repeatedly updating said persistently stored watermark value to indicate which items have been processed; and

periodically sending a message from said destination site to said other site, wherein said message indicates which items have been processed at said destination site.

17. The non-transitory computer-readable storage medium of claim 16 wherein:

the items correspond to pieces of transactions; and

the watermark value indicates a commit time of a transaction whose changes have been completely applied at the destination site.

18. The non-transitory computer-readable storage medium of claim 17 wherein the watermark value indicates a commit time of a transaction whose changes have been most recently applied by an apply engine.

19. The non-transitory computer-readable storage medium of claim 17 wherein the watermark value indicates a commit time, where all transactions with commit times at or below said watermark value are guaranteed to have been applied by an apply engine.

20. The method of claim 16 wherein the step of repeatedly updating a persistently stored watermark value includes repeatedly updating the persistently stored watermark value to reflect the commit time of the most recent transaction to be fully applied at said destination site.

21. The non-transitory computer-readable storage medium of claim 16 wherein:

the items correspond to pieces of transactions; and

the step of periodically sending a message includes periodically sending a message that indicates an oldest change time of a transaction whose changes have not been completely applied at the destination site.

22. The non-transitory computer-readable storage medium of claim 21, the steps further comprising repeatedly updating a persistently stored OLDEST CSN value to reflect the oldest change time of the oldest transaction that has not yet been fully applied at said destination site.

23. The non-transitory computer-readable storage medium of claim 22, the steps further comprising, in response to a failure at the destination site, reading the persistently stored OLDEST CSN value and requesting items to be resent to the destination site based on the persistently stored OLDEST CSN value.

24. The non-transitory computer-readable storage medium of claim 16, the steps further comprising, in response to a failure at the destination site, reading the persistently stored watermark value and requesting items to be resent to the destination site based on the persistently stored watermark value.

25. The non-transitory computer-readable storage medium of claim 16 wherein:

the other site is a source site; and

the steps further comprise;

said source site periodically storing to persistent storage information received in said message; and

after a failure at said source site, said source site performing the steps of reading said information from persistent storage; and

resending items to said destination site based on said information.

26. The non-transitory computer-readable storage medium of claim 16 wherein:

the other site is an intermediary site that receives said items from a third site; and

the steps further comprising;

said intermediary site periodically storing to persistent storage information received in said message; and

after a failure at said intermediary site, said intermediary site performing the steps of

reading said information from persistent storage; and

requesting said third site to resend items to said intermediary site based on said information.

27. The non-transitory computer-readable storage medium of claim 26 wherein the intermediary site periodically sends said information to said third site.

28. The non-transitory computer-readable storage medium of claim 16, wherein:

the items include pieces of a first transaction and pieces of a second transaction, where the first transaction committed before the second transaction; and

the step of repeatedly updating said persistently stored watermark value includes the steps of

if the destination site fmishes applying the second transaction before the destination site fmishes applying the first transaction, then the destination site persistently stores information to indicate that the second transaction is fully applied, without changing said persistently stored water mark; and

if the destination site fmishes applying the first transaction before the destination site fmishes applying the second transaction, and all transactions with commit times below said first transaction have been fully applied, then updating said persistently stored water mark based on a sequence number associated with said first transaction.

29. The non-transitory computer-readable storage medium of claim 16 wherein:

the items identify changes made by transactions performed in a first database; and

the destination site processes said items by causing said changes to be reflected in a second database that is different from said first database.

30. The non-transitory computer-readable storage medium of claim 29 wherein the step of causing said changes to be reflected includes constructing a set of one or more database commands based on the changes associated with a particular transaction that was executed in said first database, and executing said set of one or more database commands as a transaction in said second database.