Method for maximizing throughput and minimizing transactions response times on the primary system in the presence of a zero data loss standby replica

• US 8,868,492 B2
• Filed: 06/15/2011
• Issued: 10/21/2014
• Est. Priority Date: 06/15/2011
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Garin, Benedicto E. Jr., Girkar, Mahesh B., Li, Yunrui, Panteleenko, Vsevolod, Srihari, Vinay H.
• Primary Examiner(s): Leroux, Etienne
• Assistant Examiner(s): Khoshnoodi, Fariborz
• Application Number:
  US13/161,315
• Publication Number:
  US 20120323849A1
• Time in Patent Office: 1,224 Days
• Field of Search: 707/615, 707/617, 707/610
• 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

A method and system is provided for reducing delay to applications connected to a database server that guarantees no data loss during failure or disaster. After storing a log record persistently in a local primary log, the log writer returns control to the application which continues running concurrently with the database server sending the session'"'"'s log records to a standby database. A separate back channel is used by the standby to communicate, out-of-band to the primary, the location of the last log record stored persistently to the standby log. An application waiting for a transaction to commit may wait until the transaction'"'"'s commit record has been persisted. Also described is a technique for reducing application delay when there is contention between nodes of a multi-node cluster for updating the same block. The technique provides for an asynchronous ping protocol that guarantees zero data loss during failure or disaster.

Claims

1. A method for synchronizing a commit of a same transaction at both a primary database server and a secondary database server that replicates transactions made at the primary database server, comprising steps of:

via a forward channel to said secondary database server, said primary database server transmitting, to said secondary database server for replication, log records recording changes made by said transactions;

said primary database server persistently storing in a first persistent log a commit record recording a commitment of a transaction of said transactions;

via said forward channel to said secondary database server, a first process executing at said primary database server transmitting said commit record to said secondary database server;

via a back channel that is different than said forward channel, a second process that is different than the first process and that is executing at said primary database server receiving, from the secondary database server, an indication that said secondary database server has persistently stored said commit record in a second persistent log;

in response to (1) receiving said indication that said secondary database server has persistently stored said commit record in said second persistent log and (2) said primary database server persistently storing said commit record in the first persistent log, said primary database server determining that said transaction has been committed;

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

2. The method of claim 1, further comprising:

said primary database server transmitting via the forward channel a first set of log records;

after transmitting said first set of log records, said primary database server transmitting via the forward channel a second set of log records;

after transmitting said second set of log records, said primary database server receiving via the back channel an indication that the first set of log records has been stored persistently in said second persistent log.

3. A method for synchronizing a commit of a same transaction at both a primary database server and a secondary database server that replicates transactions made at the primary database server, comprising steps of:

after storing log records recording changes made by said transactions persistently to a first persistent log of the primary database server, said primary database server transmitting said log records to said secondary database server for replication;

said primary database server transmitting a commit record to said secondary database server;

said primary database server receiving data from said secondary database server, the data identifying (a) a certain location, in a second persistent log of the secondary database server, where a last log record has been persistently stored by said secondary database server or (b) the last log record;

determining that multiple transactions of said transactions has been committed based at least on the data;

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

4. The method of claim 3, further comprising:

said primary database server transmitting a first set of log records;

after transmitting said first set of log records, said primary database server transmitting a second set of log records;

wherein said commit record is transmitted with the first set of log records or the second set of log records;

after transmitting said second set of log records, said primary database server receiving data from said secondary database server, the data including the certain location in the second persistent log where said secondary database server has persistently stored the last log record.

5. The method of claim 3, wherein determining that the transaction has committed comprises determining that the certain location is beyond a corresponding location in the first persistent log where said commit record is stored.

6. A method for synchronizing updates to a same data block received at a secondary database server from instances of a primary database server running on two distinct nodes comprising:

the secondary database server receiving from a first primary database instance one or more first log records recording a change to a particular data block including a synchronizing log record, and storing the one or more first log records to a first persistent log;

wherein the synchronizing log record records dependency information representing a dependency of the one or more first log records stored in the first persistent log on a second log record recording a second change stored in a second persistent log;

said secondary database server receiving a third log record recording a transaction commit to said particular data block;

based at least in part on the dependency information recorded in the synchronizing log record stored in the first persistent log, determining that the dependency has not been met;

in response to determining that the dependency has not been met, the secondary database server delaying notification that the transaction commit recorded in the third log record has committed;

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

7. The method of claim 6, wherein the synchronizing log record comprises identification information that may be used to identify the second persistent log.

8. The method of claim 6, wherein the synchronizing log record comprises a location within a primary redo log where the second log record recording the second change is stored.

9. The method of claim 6, wherein notification of a last redo record stored persistently in the first persistent log is generated only after particular redo records that are identified in the synchronizing log record are written to the second persistent log.

10. The method of claim 6, further comprising:

determining to delay applying changes to data blocks based at least in part on the dependency information recorded in the synchronizing log record.

11. The method of claim 6, wherein changes to data blocks, identified by redo records written to the first persistent log, are applied only after particular redo records that are identified in the synchronizing log record are written to the second persistent log.

12. A method for synchronizing updates to a same data block made by two instances of a primary database server running on distinct nodes comprising:

a first primary database server instance;

sending change log records to a secondary database server, the change log records representing changes made by the first primary database server instance to a particular data block;

wherein the secondary database server stores the changes persistently to a first persistent log;

sending, to a second primary database server instance, the particular data block and a synchronizing log record;

wherein the synchronizing log record contains at least information that is used to identify the first persistent log;

in response to receiving the particular data block and the synchronizing log record, the second primary database server instance sending the synchronizing log record to the secondary database server to be stored persistently in a second persistent log;

sending, to the secondary database server, change records that record changes made to the particular data block by the second primary database server instance;

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

13. One or more non-transitory storage media storing instructions for synchronizing a commit of a same transaction at both a primary database server and a secondary database server that replicates transactions made at the primary database server, wherein the instructions, when executed by one or more processors, cause:

via a forward channel to said secondary database server, said primary database server transmitting, to said secondary database server for replication, log records recording changes made by said transactions;

said primary database server persistently storing in a first persistent log a commit record recording a commitment of a transaction of said transactions;

via said forward channel to said secondary database server, a first process executing at said primary database server transmitting said commit record to said secondary database server;

via a back channel that is different than said forward channel, a second process that is different than the first process and that is executing at said primary database server receiving, from the secondary database server, an indication that said secondary database server has persistently stored said commit record in a second persistent log;

in response to (1) receiving said indication that said secondary database server has persistently stored said commit record in said second persistent log and (2) said primary database server persistently storing said commit record in the first persistent log, said primary database server determining that said transaction has been committed.

14. The one or more non-transitory storage media of claim 13, wherein the instructions further include instructions for:

said primary database server transmitting via the forward channel a first set of log records;

after transmitting said first set of log records, said primary database server transmitting via the forward channel a second set of log records;

after transmitting said second set of log records, said primary database server receiving via the back channel an indication that the first set of log records has been stored persistently in said second persistent log.

15. One or more non-transitory storage media storing instructions for synchronizing a commit of a same transaction at both a primary database server and a secondary database server that replicates transactions made at the primary database server, wherein the instructions, when executed by one or more processors, cause:

after storing log records recording changes made by said transactions persistently to a first persistent log of the primary database server, said primary database server transmitting said log records to said secondary database server for replication;

said primary database server transmitting a commit record to said secondary database server;

said primary database server receiving data from said secondary database server, the data identifying (a) a certain location, in a second persistent log of the secondary database server, where a last log record has been persistently stored by said secondary database server or (b) the last log record;

determining that multiple transactions of said transactions has been committed based at least on the data.

16. The one or more non-transitory storage media of claim 15, wherein the instructions further include instructions for:

said primary database server transmitting a first set of log records;

after transmitting said first set of log records, said primary database server transmitting a second set of log records;

wherein said commit record is transmitted with the first set of log records or the second set of log records;

after transmitting said second set of log records, said primary database server receiving data from said secondary database server, the data including the certain location in the second persistent log where said secondary database server has persistently stored the last log record.

17. The one or more non-transitory storage media of claim 15, wherein determining that the transaction has committed comprises determining that the certain location is beyond a corresponding location in the first persistent log where said commit record is stored.

18. One or more non-transitory storage media storing instructions for synchronizing updates to a same data block received at a secondary database server from instances of a primary database server running on two distinct nodes, wherein the instructions, when executed by the one or more processors, cause:

the secondary database server receiving from a first primary database instance one or more first log records recording a change to a particular data block including a synchronizing log record, and storing the one or more first log records to a first persistent log;

wherein the synchronizing log record records dependency information representing a dependency of the one or more first log records stored in the first persistent log on a second log record recording a second change stored in a second persistent log;

said secondary database server receiving a third log record recording a transaction commit to said particular data block;

based at least in part on the dependency information recorded in the synchronizing log record stored in the first persistent log, determining that the dependency has not been met;

in response to determining that the dependency has not been met, the secondary database server delaying notification that the transaction commit recorded in the third log record has committed.

19. The one or more non-transitory storage media of claim 18, wherein the synchronizing log record comprises identification information that may be used to identify the second persistent log.

20. The one or more non-transitory storage media of claim 18, wherein the synchronizing log record comprises a location within a primary redo log where the second log record recording the second change is stored.

21. The one or more non-transitory storage media of claim 18, wherein notification of a last redo record stored persistently in the first persistent log is generated only after particular redo records that are identified in the synchronizing log record are written to the second persistent log.

22. The one or more non-transitory storage media of claim 18, wherein the instructions further include instruction for:

determining to delay applying changes to data blocks based at least in part on the dependency information recorded in the synchronizing log record.

23. The one or more non-transitory storage media of claim 18, wherein changes to data blocks, identified by redo records written to the first persistent log, are applied only after particular redo records that are identified in the synchronizing log record are written to the second persistent log.

24. One or more non-transitory storage media storing instructions for synchronizing updates to a same data block made by two instances of a primary database server running on distinct nodes, wherein the instructions, when executed by one or more processors, cause:

a first primary database server instance;

sending change log records to a secondary database server, the change log records representing changes made by the first primary database server instance to a particular data block;

wherein the secondary database server stores the changes persistently to a first persistent log;

sending, to a second primary database server instance, the particular data block and a synchronizing log record;

wherein the synchronizing log record contains at least information that is used to identify the first persistent log;

in response to receiving the particular data block and the synchronizing log record, the second primary database server instance sending the synchronizing log record to the secondary database server to be stored persistently in a second persistent log;

sending, to the secondary database server, change records that record changes made to the particular data block by the second primary database server instance.