Time-based checkpoint target for database media recovery

BENEFIT CLAIM

This application claims priority, as a continuation application, to application Ser. No. 14/270,117, filed May 5, 2014, the entire contents of which are hereby incorporated by reference as if fully set forth herein, under 35 U.S.C. § 120. The applicant(s) hereby rescind any disclaimer of claim scope in the parent application(s) or the prosecution history thereof and advise the USPTO that the claims in this application may be broader than any claim in the parent application(s).

FIELD OF THE INVENTION

The present disclosure relates to databases, and more specifically, to checkpoints for databases during media recovery.

BACKGROUND

For databases, media recovery is often an essential component to minimize potential downtime and provide the highest database availability. For databases, backups are generally scheduled periodically, with change records recorded for any database changes that occur between the backups. Besides the traditional application of restoring a failed or corrupted primary database, media recovery of database backups may also be applied to a separate database, allowing the primary database to be replicated into standby, failover, and test databases. The performance of the media recovery may thus have a direct impact on query latency and database availability.

Safeguards should be provided so that the media recovery process itself is protected from failure. For example, an unexpected crash or failure may occur during the application of change records in the media recovery process. Unless there is a prior known consistent state of the database, the media recovery process will need to restart from the beginning with the backup files. This restarting may be a very expensive operation, particularly for databases that have a large number of change records to process, as is the case for multi-node or multi-instance databases.

Periodic checkpointing may be used to safeguard the media recovery process, allowing the media recovery process to resume from the last checkpoint rather than from the backup files after a failure occurs. To minimize the amount of work that needs to be repeated, more frequent checkpoints are required. However, more frequent checkpointing incurs significant I/O and processing overhead, slowing down the media recovery process and negatively impacting database performance.

This processing overhead is especially acute when the standby database is applying redo at a high rate. For example, if the standby database has failed for a period of time and is now brought back online, it will receive and process a large batch of redo records from a primary database. The checkpointing process may consume large amounts of resources to keep up with the redo, which may starve other important processes such as a read-only standby database.

Accordingly, to spread the checkpointing load over time, incremental checkpoints can be used to continuously write dirty buffers. However, it is difficult to reliably determine an optimal resource allocation for the periodic, incremental, or periodic and incremental checkpoints. While a simple approach may adjust the checkpointing rate inversely with the apply rate, this has the undesirable effect of delaying checkpoint creation when it may be needed the most.

Based on the foregoing, there is a need for a method to provide efficient and high performance checkpointing for databases during media recovery.

The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:

<figref idref="DRAWINGS">FIG. 1A</figref> is a block diagram that depicts an example physical standby executing media recovery of an example primary database system using a time-based checkpoint target, according to an embodiment;

<figref idref="DRAWINGS">FIG. 1B</figref> is a block diagram that depicts example data structures of the example physical standby for providing a time-based checkpoint target for media recovery, according to an embodiment;

<figref idref="DRAWINGS">FIG. 1C</figref> is a block diagram that depicts an example timestamp to logical time mapping for providing a time-based checkpoint target for media recovery, according to an embodiment;

<figref idref="DRAWINGS">FIG. 1D</figref> is a block diagram that depicts an example checkpoint being created using a time-based checkpoint target for media recovery, according to an embodiment;

<figref idref="DRAWINGS">FIG. 2</figref> is a flow diagram that depicts a process for providing a time-based checkpoint target for media recovery, according to an embodiment;

<figref idref="DRAWINGS">FIG. 3</figref> is a block diagram of a computer system on which embodiments may be implemented.

DETAILED DESCRIPTION

In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

General Overview

In an embodiment, a time-based checkpoint target for media recovery is provided for standby databases. A primary database generates a plurality of change records as transactions are processed. The change records are recorded in a change record log, which is transmitted to a standby database. The standby database conducts media recovery by applying the change record log as redo records. This media recovery, as maintained by the standby, can be used to support many features such as providing a read-only standby to offload read requests from the primary database, allowing the primary database to failover in the event of a failure, or creating a test database.

To safeguard the media recovery process on the standby database, a continuous incremental checkpoint is written to storage, allowing a failed media recovery process to restart from the checkpoint rather than from the last database file backup. Non-incremental checkpoints may be also be supported by using a pending checkpoint table. As the media recovery or redo apply progresses, corresponding dirty buffers are queued in memory. To implement the time-based checkpoint targets, a timestamp mapping is maintained between system clock timestamps and logical times of applied change records in the media recovery process. The timestamp mapping is updated on a periodic basis so that adjacent timestamps in the mapping are separated by a specific periodic interval.

On an adjustable time interval, the mapping is used to determine a target logical time that is mapped to a present timestamp minus a specified checkpoint time delay, which is also fully adjustable. The dirty buffer queues are processed and flushed up to the target logical time to create an incremental checkpoint. In this manner, the incremental checkpoints follow a moving checkpoint target that is based on an adjustable delay, rather than reacting immediately to media recovery load or redo apply load. This allows the checkpointing process to be load balanced over time, keeping the standby database responsive for other processes, such as a read-only standby database.

Furthermore, a time interval between file header updates of the incremental checkpoint is also independently adjustable from the dirty buffer queue processing interval. The file headers comprise metadata for cataloging the database blocks in the checkpoint files, including a consistent logical time for the checkpoint of the standby database. The updating of file headers may incur a heavy I/O penalty, particularly for large databases having many database files. File headers may be need to be updated for particular database files, even if the data blocks remain unchanged since the last file header update. Additionally, file header updating is often an ideal time to carry out additional operations such as checksum calculations and other operations requiring partial or full data block scanning, placing an even heavier I/O and processor overhead burden. Thus, it may desirable to update the file headers less frequently than the dirty buffer queue processing, which is possible due to the independently adjustable time intervals. Further, by using a time based interval, I/O loads can be distributed over time, rather than incurring a heavy I/O spike in response to arbitrary events, such as reaching the end of a log file during media recovery or redo apply. In this manner, file header update I/O loads can be controlled to avoid I/O starvation of other processes, such as a read-only standby database.

Since the checkpoint time delay, the timestamp mapping update interval, the dirty buffer processing time interval, and the file header update time interval are all fully adjustable, checkpoints can be configured according to application and/or customer requirements. To prioritize for a shorter recovery time from failure, the time delay may be reduced and/or the intervals may be shortened. On the other hand, to prioritize for reduced overhead and greater storage efficiency, the time delay may be increased and/or the intervals may be lengthened. The time delay and/or the intervals can also be adjusted by analyzing temporal data access patterns to avoid unnecessary processing of hot data. Since the time-based checkpoint follows a moving checkpoint target that is based on an adjustable delay, the checkpointing load can be smoothly distributed over time to reduce the effects of apply rate spikes on the checkpointing process. Additionally, an independent interval to update the file headers allows the I/O load to be carefully controlled. Optionally, the checkpoint processing may be further resource throttled, for example by limiting processor usage. In this manner, the checkpointing process is carefully controlled to conserve resources for other processes, such as a read-only standby database.

Database Systems

Embodiments of the present invention are used in the context of DBMSs. Therefore, a description of a DBMS is useful.

A DBMS manages one or more databases. A DBMS may comprise one or more database servers. A database comprises database data and a database dictionary that are stored on a persistent memory mechanism, such as a set of hard disks. Database data may be stored in one or more data containers. Each container contains records. The data within each record is organized into one or more fields. In relational DBMSs, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object-oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.

DBMSs are often protected using replication. Typically, one DBMS maintains the primary copy of a database and another database system, referred to herein as a standby database, maintains a replica of the primary copy. The standby database system is used to back up (or mirror) information stored in the primary database system or other primary copy.

For a DBMS protected using replication, data files, redo log files and control files are stored in separate, logically or physically identical images on separate physical media. In the event of a failure of the primary database system, the information is preserved, in duplicate, on the standby database system, which can be used in place of the primary database system.

The standby database system is kept up to date to accurately and timely reproduce the information in the primary database system. Typically, archived redo log records (“redo records”) are transmitted automatically from the primary database system to the standby database system. Information from the redo logs is used to replicate changes on the primary database system to the standby database system.

There are two types of standby database systems, a physical standby database system and logical standby database systems, which differ in the way they archive information. In a physical standby database system, changes are made using physical replication. Under physical replication, updates made to a data unit of contiguous storage (herein “data unit”) at the primary data system are made to corresponding data unit replicas stored at the replica system. In the context of database systems, changes made to data blocks on the primary database system are replicated in replicas of those data blocks on the physical standby database system.

Another approach to replicating data is that of the logical standby database system. With the logical standby database system approach, DBMS commands that modify data on the primary system are in effect re-executed on a logical standby database system to essentially duplicate the changes made to the primary database. While executing the same DBMS commands guarantees that changes are replicated at the transactional level, the changes are not replicated at the data block level.

A database block, also referred to as a data block, is a unit of persistent storage. A database block is used by a database server to store database records (e.g. to store rows of a table, to store column values of a column). When records are read from persistent storage, a database block containing the record is copied into a database block buffer in volatile memory of a database server. A database block usually contains multiple rows, and control and formatting information, (e.g. offsets to sequences of bytes representing rows or other data structures, list of transactions affecting a row). A database block may be referenced by a database block address (DBA).

A database block is referred to as being atomic because, at least in part, a database block is the smallest unit of database data a database server may request from a persistent storage device. For example, when a database server seeks a row that is stored in a database block, the database server may only read the row from persistent storage by reading in the entire database block.

Users interact with a database server of a DBMS by submitting to the database server commands that cause the database server to perform operations on data stored in a database. A user may be one or more applications running on a client computer that interact with a database server. Multiple users may also be referred to herein collectively as a user.

A database command may be in the form of a database statement that conforms to a database language. A database language for expressing the database commands is the Structured Query Language (SQL). There are many different versions of SQL, some versions are standard and some proprietary, and there are a variety of extensions. Data definition language (“DDL”) commands are issued to a database server to create or configure database objects, such as tables, views, or complex data types. SQL/WL is a common extension of SQL used when manipulating XML data in an object-relational database.

A multi-node database management system is made up of interconnected nodes that share access to the same database or databases. Typically, the nodes are interconnected via a network and share access, in varying degrees, to shared storage, e.g. shared access to a set of disk drives and data blocks stored thereon. The varying degrees of shared access between the nodes may include shared nothing, shared everything, exclusive access to database partitions by node, or some combination thereof. The nodes in a multi-node database system may be in the form of a group of computers (e.g. work stations, personal computers) that are interconnected via a network. Alternately, the nodes may be the nodes of a grid, which is composed of nodes in the form of server blades interconnected with other server blades on a rack.

Each node in a multi-node database system hosts a database server. A server, such as a database server, is a combination of integrated software components and an allocation of computational resources, such as memory, a node, and processes on the node for executing the integrated software components on a processor, the combination of the software and computational resources being dedicated to performing a particular function on behalf of one or more clients.

Resources from multiple nodes in a multi-node database system can be allocated to running a particular database server's software. Each combination of the software and allocation of resources from a node is a server that is referred to herein as a “server instance” or “instance”. A database server may comprise multiple database instances, some or all of which are running on separate computers, including separate server blades.

System Overview

<figref idref="DRAWINGS">FIG. 1A</figref> is a block diagram that depicts an example database system and an example physical standby executing media recovery of the example database system, according to an embodiment. System <b>100</b> of <figref idref="DRAWINGS">FIG. 1A</figref> includes primary database management system (DBMS) <b>110</b>, database administrator terminal <b>114</b>, client <b>116</b>, network <b>140</b>, and physical standby <b>150</b>. Primary DBMS <b>110</b> includes primary database <b>112</b>, primary instance <b>120</b>A, primary instance <b>120</b>B, and change record log <b>130</b>. Client <b>116</b> includes application <b>118</b>. Physical standby <b>150</b> includes standby database <b>152</b>, standby instance <b>160</b>A, standby instance <b>160</b>B, dirty buffer queues <b>170</b>, dirty buffer queue processing interval <b>176</b>, timestamp to logical time mapping <b>180</b>, mapping interval <b>184</b>, time-based checkpoint process <b>190</b>, header update interval <b>191</b>, checkpoint time delay <b>192</b>, present timestamp <b>193</b>, checkpoint <b>194</b>, and pending checkpoint table <b>198</b>. Checkpoint <b>194</b> includes file headers <b>195</b> and block data <b>196</b>.

It should be noted that <figref idref="DRAWINGS">FIG. 1A</figref> only shows one specific embodiment with a single primary DBMS <b>110</b>, a single physical standby <b>150</b>, a single network <b>140</b>, and a single client <b>116</b>. In other embodiments, any number of primary DBMSs, standbys, networks, and clients may be supported. While a physical standby is shown in the Figures for illustrative purposes, alternative embodiments may use one or more logical standbys. Additionally, while network <b>140</b> is shown outside of primary DBMS <b>110</b> and physical standby <b>150</b>, network <b>140</b> may also encompass private intranets or other communications links within primary DBMS <b>110</b> and/or physical standby <b>150</b>. Further, each primary DBMS may have any number of primary instances, and each physical standby may have any number of standby instances, which may also be dynamically added and removed during media recovery or redo apply.

As shown in <figref idref="DRAWINGS">FIG. 1A</figref>, primary DBMS <b>110</b> is a multi-instance or multi-node DBMS, where multiple primary instances <b>120</b>A-<b>120</b>B are concurrently applying changes to data in primary database <b>112</b>. Thus, a particular data block or block address of primary database <b>112</b> may be modified at different times by different primary instances. For the purposes of an example, primary DBMS <b>110</b> utilizes a shared everything primary database <b>112</b>. Changes applied by primary instances <b>120</b>A-<b>120</b>B are logged into change record log <b>130</b>.

For example, a database application such as application <b>118</b> on client <b>116</b> may be sending transactions for processing on primary DBMS <b>110</b>, which utilizes primary instances <b>120</b>A-<b>120</b>B to apply the transactions. Change record log <b>130</b> may be created by recording changes as they are applied by primary instances <b>120</b>A-<b>120</b>B, merged in logical time order. In an embodiment, the logical time may correspond to a logical timestamp, a non-limiting example of which is a System Commit Number (SCN). In some embodiments, the merging may occur at physical standby <b>150</b> instead. Change record log <b>130</b> may be transferred over network <b>140</b> to physical standby <b>150</b>, for example by streaming new records as they are created, by pushing periodic batch updates, by pulling updates via periodic polling, or by any another method.

Physical standby <b>150</b> utilizes multiple standby instances <b>160</b>A-<b>160</b>B to apply the records of change record log <b>130</b> into standby database <b>152</b>, which can then be used to support a read-only standby database, a database failover, a test database, or other recovery applications. An example approach for such a multi-instance change record or redo record apply is described in co-pending U.S. patent application Ser. No. 14/067,129 to Srivastava et al., filed Oct. 30, 2013 and entitled “Multi-Instance Redo Apply”, the entire contents of which are hereby incorporated by reference for all purposes as if fully set forth herein.

Accordingly, standby instances <b>160</b>A-<b>160</b>B may receive and apply the changes indicated in change record log <b>130</b> to replicate primary database <b>112</b> to standby database <b>152</b>. As buffers of the affected database blocks are loaded into and updated in memory, the dirty buffers may be queued into dirty buffer queues <b>170</b> for processing and flushing to disk during time-based checkpoint process <b>190</b>. In some embodiments, the queues may be separate for each standby instance <b>160</b>A-<b>160</b>B. In other embodiments, a single merged queue may be maintained.

To assist in the processing of dirty buffer queues <b>170</b> in a time-based manner, timestamp to logical time mapping <b>180</b> is maintained to keep track when particular change records, identified by their logical time, are applied with reference to system clock timestamps. To avoid the overhead of creating an entry for each and every change record, a new entry may only be entered for the latest applied change record on a periodic basis specified by mapping interval <b>184</b>.

The resulting timestamp to logical time mapping <b>180</b> can then be referenced by time-based checkpoint process <b>190</b> to target a particular dirty buffer in dirty buffer queues <b>170</b> by applying checkpoint time delay <b>192</b> to present timestamp <b>193</b>. On a periodic basis specified by dirty buffer queue processing interval <b>176</b>, the dirty buffers in dirty buffer queues <b>170</b> are processed up to that targeted dirty buffer, which continually moves forward due to present timestamp <b>193</b> advancing with time. As a result, dirty buffers are continually written into block data <b>196</b>, enabling an always-incremental checkpoint <b>194</b>. At a periodic interval corresponding to header update interval <b>191</b>, file headers <b>195</b> are also updated to reflect the most recent consistent state of block data <b>196</b>. Header update interval <b>191</b> can be set to a value independent of dirty buffer queue processing interval <b>176</b>.

As mapping interval <b>184</b>, dirty buffer queue processing interval <b>176</b>, header update interval <b>191</b>, and checkpoint time delay <b>192</b> can all be specified and tuned by the user or database application, time-based checkpoint process <b>190</b> can be prioritized for low overhead or low recovery time from failure, depending on user and application requirements. Accordingly, a time-based checkpoint process <b>190</b> is provided that is flexibly adjustable to optimize for various database applications and business use cases.

Time-Based Checkpoint Process

With a basic outline of system <b>100</b> now in place, it may be instructive to review a high level overview of the processing steps to provide a time-based checkpoint target for database media recovery. Turning to <figref idref="DRAWINGS">FIG. 2</figref>, <figref idref="DRAWINGS">FIG. 2</figref> is a flow diagram that depicts a process <b>200</b> for providing a time-based checkpoint target for media recovery, according to an embodiment.

Applying the Change Records

At block <b>202</b> of process <b>200</b>, referring to <figref idref="DRAWINGS">FIG. 1A</figref>, physical standby <b>150</b> applies a plurality of change records, or change record log <b>130</b>, received from primary DBMS <b>110</b>. Block <b>202</b> may begin in response to a recovery request issued on standby instance <b>160</b>A, which may be manually invoked by a database administrator or automatically invoked to provide standby replication for primary DBMS <b>110</b>. As the change records are applied by standby instances <b>160</b>A-<b>160</b>B to replicate primary database <b>112</b> into standby database <b>152</b>, corresponding dirty buffers are also queued into dirty buffer queues <b>170</b>, wherein each dirty buffer indicates the logical time of the change record that was applied into the dirty buffer. As discussed above, in an embodiment each standby instance <b>160</b>A-<b>160</b>B may create a corresponding dirty buffer queue, in which case two dirty buffer queues are created in dirty buffer queues <b>170</b>.

Referring to <figref idref="DRAWINGS">FIG. 1B</figref>, <figref idref="DRAWINGS">FIG. 1B</figref> is a block diagram that depicts example data structures of the example physical standby for providing a time-based checkpoint target for media recovery, according to an embodiment. <figref idref="DRAWINGS">FIG. 1B</figref> includes change record log <b>130</b> and physical standby <b>150</b>. Physical standby <b>150</b> includes dirty buffer queues <b>170</b> and timestamp to logical time mapping <b>180</b>. Dirty buffer queues <b>170</b> include dirty buffer queue <b>172</b>A and dirty buffer queue <b>172</b>B. Dirty buffer queue <b>172</b>A includes dirty buffer <b>174</b>A, dirty buffer <b>174</b>B, and dirty buffer <b>174</b>C. Dirty buffer queue <b>172</b>B includes dirty buffer <b>174</b>D, dirty buffer <b>174</b>E, and dirty buffer <b>174</b>F. With respect to <figref idref="DRAWINGS">FIG. 1B</figref>, like numbered elements may correspond to the same elements from <figref idref="DRAWINGS">FIG. 1A</figref>.

As shown in change record log <b>130</b>, each change record may include two fields of data, including (1) a logical time (SCN) reflecting a consistent state of primary database <b>112</b> after the change record was applied, and (2) the data that was changed, which includes (a) a buffer of the data that was written (Data[ ]), (b) an offset within the database block where the write occurred (offset), and (c) a database block number (Block #) where the change occurred.

For brevity, only six change records are illustrated in change record log <b>130</b>, but it may be assumed that many more change records are included that are not explicitly shown. Additionally, the database block size is set to 4 KB, or 4096 bytes, but any database block size may be utilized. Further, the specific change record structure shown in <figref idref="DRAWINGS">FIG. 1B</figref> is only given as an example, and any suitable structure can be used for the change records in change record log <b>130</b>.

In the example shown in <figref idref="DRAWINGS">FIG. 1B</figref>, a simple modulo function is utilized to illustrate the distribution of change record log <b>130</b> amongst the available standby instances <b>160</b>A-<b>160</b>B, wherein standby instance <b>160</b>A may process change records addressed to an even database block number (Block # mod 2=0) and standby instance <b>160</b>B may process change records addressed to an odd database block number (Block # mod 2=1). However, as discussed in the “Multi-Instance Redo Apply” application, a more intelligent distribution function may be utilized to divide the redo work amongst multiple standby instances.

Queuing Dirty Buffers

Based on the example data shown in change record log <b>130</b> and the simple modulo workload distribution function described above, applying the change records in change record log <b>130</b> will populate dirty buffer queues <b>170</b> as shown in <figref idref="DRAWINGS">FIG. 1B</figref>. For each change record, the appropriate standby instance <b>160</b>A-<b>160</b>B may (1) read the change record from change record log <b>130</b>, (2) retrieve the associated database block from standby database <b>152</b> if the block is not already buffered in-memory, (3) apply the changes from the change record into the buffer, and (4) queue the dirtied buffer into a respective dirty buffer queue <b>172</b>A-<b>172</b>B.

Since standby instance <b>160</b>A handles change records addressed to even Block #s, the corresponding dirty buffer queue <b>172</b>A includes dirty buffers <b>174</b>A, <b>174</b>B and <b>174</b>C, which are addressed to even Block #s 100 and 25252. On the other hand, since standby instance <b>160</b>B handles change records addressed to odd Block #s, the corresponding dirty buffer queue <b>172</b>B includes dirty buffers <b>174</b>D, <b>174</b>E and <b>174</b>F, which are addressed to odd Block #s 85 and 111. Since each standby instance <b>160</b>A-<b>160</b>B applies the relevant change records from change record log <b>130</b> in logical time (SCN) order, each dirty buffer queue <b>172</b>A-<b>172</b>B is also queued in logical time (SCN) order. As shown in <figref idref="DRAWINGS">FIG. 1B</figref>, each dirty buffer <b>174</b>A-<b>174</b>F indicates the logical time (SCN) of the change record that created the dirty buffer, the affected database block (Block #), and the full contents of the block (Data[<b>4096</b>]).

Maintaining the Timestamp to Logical Time Mapping

At block <b>204</b> of process <b>200</b>, referring to <figref idref="DRAWINGS">FIG. 1A</figref>, physical standby <b>150</b> maintains timestamp to logical time mapping <b>180</b>. Referring to <figref idref="DRAWINGS">FIG. 1C</figref>, <figref idref="DRAWINGS">FIG. 1C</figref> is a block diagram that depicts an example timestamp to logical time mapping for providing a time-based checkpoint target for media recovery, according to an embodiment. System <b>104</b> of <figref idref="DRAWINGS">FIG. 1C</figref> includes timestamp to logical time mapping <b>180</b>, pointer <b>182</b>, header update interval <b>191</b> and checkpoint time delay <b>192</b>. With respect to <figref idref="DRAWINGS">FIG. 1C</figref>, like numbered elements may correspond to the same elements from <figref idref="DRAWINGS">FIG. 1A</figref>.

As previously discussed, it may be undesirable in terms of resource overhead to have mappings for every single applied change record. Accordingly, the plurality of timestamps within timestamp to logical time mapping <b>180</b> may be spaced by mapping interval <b>184</b>, for example by one second, wherein a new index entry having a logical time (SCN) corresponding to the latest applied change record for standby database <b>152</b> is entered for each new one second timestamp. The example interval of one second is only an example, and any desired interval may be utilized.

Initially, timestamp to logical time mapping <b>180</b> may be empty. Each time present timestamp <b>193</b> moves forward by the desired mapping interval <b>184</b>, or one second in this example, a new entry may be entered into timestamp to logical time mapping <b>180</b>. Pointer <b>182</b> may keep track of the position to write the next entry. As shown in <figref idref="DRAWINGS">FIG. 1C, 3600</figref> entries or an hour's worth of entries are reserved in timestamp to logical time mapping <b>180</b>. However, any number of entries may be reserved. Once the final index <b>3599</b> is written, pointer <b>182</b> may wrap back to the entry at index <b>0</b>, thereby implementing a circular buffer that overwrites the oldest entries to conserve memory space.

When an entry is to be written into timestamp to logical time mapping <b>180</b>, pointer <b>182</b> determines the index, present timestamp <b>193</b> determines the timestamp, and dirty buffer queues <b>170</b> determines the logical time (SCN). As discussed above, the logical time (SCN) to write corresponds to the logical time (SCN) of the “latest applied change record” for standby database <b>152</b>. For single-instance redo apply, the “latest applied change record” corresponds straightforwardly to the most recent dirty buffer. After the entry is populated, pointer <b>182</b> is moved forward by one entry and wrapped to index <b>0</b> if the maximum index is exceeded.

For multi-instance redo apply, the “latest applied change record” is determined by examining the progress of all standby instances as a whole for standby database <b>152</b>. Since the number of change records and the apply rate may vary between the different standby instances <b>160</b>A-<b>160</b>B, the “slowest” standby instance must be used as the baseline to determine a consistent state reflecting the global redo progress for standby database <b>152</b>.

Thus, as discussed in the “Multi-instance Redo Apply” application, a multi-instance redo apply progress may be tracked using “influx logical times”. In the case of dirty buffer queues <b>170</b>, the local logical influx times correspond to the logical time (SCN) of the most recent dirty buffer in each of the dirty buffer queues <b>172</b>A-<b>172</b>B. The global influx logical time corresponds to the least of the local logical influx times, representing the standby instance with the “slowest” redo apply. This global influx logical time is written into the entry for timestamp to logical time mapping <b>180</b>.

To walk through an example, consider a state of dirty buffer queues <b>170</b> in <figref idref="DRAWINGS">FIG. 1B</figref> where only dirty buffers <b>174</b>A, <b>174</b>D, and <b>174</b>E are present. Accordingly, the most recent dirty buffer in dirty buffer queue <b>172</b>A is dirty buffer <b>174</b>A, having a logical time (SCN) of 1400, and the most recent dirty buffer in dirty buffer queue <b>172</b>B is dirty buffer <b>174</b>E, having a logical time (SCN) of 1600. Out of 1400 and 1600, the lowest logical time (SCN) is 1400, which thus corresponds to the global influx time representing the global progress of the redo apply for standby database <b>152</b>.

Since no entries have been written yet, pointer <b>182</b> may point to the entry at index <b>0</b>. Present timestamp <b>193</b> may indicate the current date and time as Feb. 26, 2014, 6:00:00. Accordingly, entry <b>0</b> of timestamp to logical time mapping <b>180</b> is populated as shown in <figref idref="DRAWINGS">FIG. 1C</figref>. Pointer <b>182</b> is also moved forward to the entry at the next index, or index <b>1</b>. This process is repeated each time present timestamp <b>193</b> moves forward by the desired mapping interval <b>184</b>, or one second in this example. After pointer <b>182</b> reaches index <b>120</b>, timestamp to logical time mapping <b>180</b> may be populated as shown in <figref idref="DRAWINGS">FIG. 1C</figref>.

Determining the Target Logical Time

At block <b>206</b> of process <b>200</b>, referring to <figref idref="DRAWINGS">FIG. 1A</figref>, time-based checkpoint process <b>190</b> of physical standby <b>150</b> uses timestamp to logical time mapping <b>180</b> to determine a target logical time mapped to a target timestamp that is prior to present timestamp <b>193</b> by at least checkpoint time delay <b>192</b>. Referring to <figref idref="DRAWINGS">FIG. 1D</figref>, <figref idref="DRAWINGS">FIG. 1D</figref> is a block diagram that depicts an example checkpoint being created using a time-based checkpoint target for media recovery, according to an embodiment. As shown in <figref idref="DRAWINGS">FIG. 1D</figref>, the progress of writing data into checkpoint <b>194</b> from <figref idref="DRAWINGS">FIG. 1A</figref> is shown over time, with file headers <b>195</b>A and block data <b>196</b>A representing checkpoint <b>194</b> at present timestamp <b>193</b>A, file headers <b>195</b>B and block data <b>196</b>B representing checkpoint <b>194</b> at present timestamp <b>193</b>B, file headers <b>195</b>C and block data <b>196</b>C representing checkpoint <b>194</b> at present timestamp <b>193</b>C, and file headers <b>195</b>D and block data <b>196</b>D representing checkpoint <b>194</b> at present timestamp <b>193</b>D.

Starting with the time at present timestamp <b>193</b>A, since checkpoint time delay <b>192</b> is specified to be 60 seconds, the target timestamp should be at least 60 seconds prior to present timestamp <b>193</b>A, or 60 seconds prior to Feb. 26, 2014, 6:01:00, which corresponds to a target timestamp of Feb. 26, 2014, 6:00:00. Since pointer <b>182</b> references the entry at index <b>60</b> at present timestamp <b>193</b>A, a target index having the target timestamp may be calculated by dividing checkpoint time delay <b>192</b> by the time interval between each index (60 seconds/1 second), taking the ceiling of that value (60), and finally subtracting that value from pointer <b>182</b> (60−60=0). Thus, index <b>0</b> of timestamp to logical time mapping <b>180</b> includes the target timestamp, which was created by a target change record having a target logical time or SCN equal to 1000. Thus, the target logical time is determined to be SCN 1000. If redo apply has just started and no suitable entry is available yet, then block <b>206</b> may be delayed until an old enough entry is available in timestamp to logical time mapping <b>180</b>.

Flushing the Dirty Blocks

At block <b>208</b> of process <b>200</b>, referring to <figref idref="DRAWINGS">FIG. 1A</figref>, time-based checkpoint process <b>190</b> of physical standby <b>150</b> creates checkpoint <b>194</b> describing one or more database files of standby database <b>152</b> at a consistent logical time, wherein the updating flushes or writes a set of dirty buffers, from dirty buffer queues <b>170</b>, that have logical times up to the target logical time determined in block <b>206</b>. Blocks <b>206</b> and <b>208</b> may repeat on dirty buffer queue processing interval <b>176</b>, which in this example is 1 second, or the same as mapping interval <b>184</b>, but these two intervals do not necessarily have to match.

Dirty buffer queues <b>170</b> can now be processed up to the target logical time determined in block <b>206</b>, or SCN 1000. With the example shown in <figref idref="DRAWINGS">FIG. 1B</figref>, this only includes dirty buffer <b>174</b>D, as all the other dirty buffers have a SCN that is greater than 1000. Accordingly, time-based checkpoint process <b>190</b> only processes dirty buffer <b>174</b>D at present timestamp <b>193</b>A, as indicated by the writing of database block #111 into block data <b>196</b>A, as shown in <figref idref="DRAWINGS">FIG. 1D</figref>. However, if dirty buffer queues <b>170</b> did include any dirty buffers with a logical time or SCN less than 1000, then those dirty buffers would also be processed. Since dirty buffer queues <b>170</b> will be naturally sorted in logical time order due to the application of change record log <b>130</b> in logical time order, the queue processing also proceeds in logical time order and thus knows to stop after each of the dirty buffer queues <b>172</b>A-<b>172</b>B are processed up to the target logical time, or SCN 1000.

After processing, the dirty buffers have been flushed and can be removed from their respective queues. Note that while block data <b>196</b>A is shown as immediately populated with the flushed buffers, alternative embodiments may use one or more asynchronous database writer processes to coalesce I/O for greater performance. In this case, the buffers may not be written to disk immediately after flushing.

As discussed above, checkpoint <b>194</b> describes one or more database files of standby database <b>152</b> at a consistent logical time. Accordingly, file headers <b>195</b>A may indicate a consistent logical time (SCN) of checkpoint <b>194</b>. As shown in <figref idref="DRAWINGS">FIG. 1D</figref>, file headers <b>195</b>A shows a default value of 0, indicating that a file header update has not yet occurred. Thus, file headers <b>195</b>A may reflect a state of checkpoint <b>194</b> when created, which may have occurred when index <b>0</b> was written in timestamp to logical time mapping <b>180</b>. Since header update interval <b>191</b> is set to 120 seconds as shown in <figref idref="DRAWINGS">FIG. 1C</figref>, the file headers may not be updated until present timestamp <b>193</b>A advances to 120 seconds after index <b>0</b>, or until present timestamp <b>193</b>D. The updating of the file headers is described in greater detail below under the heading “UPDATING THE FILE HEADERS OF THE CHECKPOINT”.

While file headers <b>195</b>A only shows a latest consistent logical time (SCN) value, various metadata including data structures such as hash tables, trees, lists, bitmaps, pointers, and others may be included to catalog block data <b>196</b>A. For simplicity, it is assumed that file headers <b>195</b>A and block data <b>196</b>A refer to a single database file. However, alternative embodiments may support multiple database files.

The above described process for blocks <b>206</b> and <b>208</b> may be repeated for present timestamps <b>193</b>B, <b>193</b>C, and <b>193</b>D to create checkpoint <b>194</b>, as reflected by file headers <b>195</b>B, <b>195</b>C, <b>195</b>D and block data <b>196</b>B, <b>196</b>C, and <b>196</b>D, respectively. Since the target logical time will be continuously moving forward due to the passage of time moving present timestamp <b>193</b> forward, dirty buffer queues <b>170</b> will be continually processed and written to disk, enabling checkpoint <b>194</b> to be maintained as an always-incremental checkpoint.

Note that when time-based checkpoint process <b>190</b> processes multiple dirty buffers affecting a single database block, the writing of the block data may only reflect the most recently processed dirty buffer. This is illustrated at present timestamp <b>193</b>B, where time-based checkpoint process <b>190</b> processes up to SCN 1600. Database block #100 is modified by two dirty buffers <b>174</b>A (‘FOO . . . ’) and <b>174</b>B (‘BAR . . . ’), but block data <b>196</b>B only reflects the data from the most recent dirty buffer <b>174</b>B (‘BAR . . . ’).

Updating the File Headers of the Checkpoint

File headers <b>195</b> may be updated periodically on header update interval <b>191</b> to reflect the latest block data <b>196</b> that has been written. For example, file headers <b>195</b> may be updated with a consistent logical time (SCN) of the last applied change record that is reflected in block data <b>196</b>, and pointers, offsets, data sizes, and other metadata for block data <b>196</b> may be updated in file headers <b>195</b> to correctly reference the portion of block data <b>196</b> that is in accord with the consistent logical time.

Blocks <b>206</b>-<b>208</b> may update block data <b>196</b> between the periodic file header updates. However, since the updated blocks are written as incremental updates, they are simply unreferenced by the metadata of file headers <b>195</b> between file header updates. In this manner, file headers <b>195</b> maintains a consistent logical time for checkpoint <b>194</b> that describes a consistent state of standby database <b>152</b>, even as blocks <b>206</b>-<b>208</b> continuously update block data <b>196</b>.

Since header update interval <b>191</b> is set to 120 seconds, present timestamp <b>193</b>D represents the time when the file header update occurs after the creation of checkpoint <b>194</b>. At this point in time, pointer <b>182</b> points to the entry at index <b>120</b>, as shown in <figref idref="DRAWINGS">FIG. 1C</figref>. Since checkpoint time delay <b>192</b> is set to 60 seconds and since we are assuming that block data is written immediately to disk without any delay, blocks <b>206</b>-<b>208</b> will have already processed up to SCN 50000. The consistent logical time of file headers <b>195</b>D is thus updated to SCN 50000, which corresponds to index <b>60</b> of timestamp to logical time mapping <b>180</b>.

Time-Based Checkpoint Target Tuning

It should be noted that both header update interval <b>191</b> and checkpoint time delay <b>192</b> can be freely adjusted by the user, for example by using a command-line or graphical user interface provided by database administrator terminal <b>114</b>. Thus, rather than updating file headers in response to reaching arbitrary boundaries such as at the end of a redo log file, the header update interval <b>191</b> between successive file header updates can be specified. Since file header updating incurs a heavy overhead cost, the user or database application can decide whether to favor reduced overhead with a longer header update interval <b>191</b> or reduced time from recovery with a shorter header update interval <b>191</b>, depending on user and application priorities and requirements.

For example, note that the example header update interval <b>191</b>, or 120 seconds, is a much larger interval than the example dirty buffer queue processing interval <b>176</b>, or 1 second. As discussed above, the updating of file headers <b>195</b> may incur a heavy I/O load, particularly when standby database <b>152</b> includes many database files. For example, the file headers for certain database files may need to be updated with a consistent logical time, even if zero data block changes have been incurred since the last file header update. Thus, file header updates may update a large number of files even when database block activity is localized to a few database files. Furthermore, file header updates are often an ideal time to execute checksum calculations, consistency checks, and other operations requiring partial or full data block scanning, dramatically increasing I/O and processing overhead. Thus, it may be desirable to update the file headers less often than the dirty buffer queue processing. The tradeoff is that the recovery time from failure may be increased, as the file headers may be staler when the failure occurs.

Similarly, the checkpoint time delay <b>192</b> can be adjusted according to user and application requirements. A smaller checkpoint time delay <b>192</b> allows the checkpoint to more closely follow the redo apply, which may be an important consideration if recovery time from failure is to be minimized. However, setting the checkpoint time delay <b>192</b> too aggressively may be inefficient.

Mapping interval <b>184</b> for adding new entries in timestamp to logical time mapping <b>180</b> and dirty buffer queue processing interval <b>176</b> for processing dirty buffer queues <b>170</b> using time-based checkpoint process <b>190</b> are also adjustable by the user. As discussed above, an example interval of 1 second was used for both intervals, but any desired interval can be utilized. For example, a longer interval may be specified for dirty buffer queue processing interval <b>176</b> to avoid excessive overhead from processing hot or frequently modified data blocks.

While all of the variables described above are manually adjustable by the user, the variables may also be set partially or fully automatically by using data analysis. For example, prior database statistics may be analyzed for temporal data access patterns to tune the variables. One automated tuning target may analyze the average time period for the majority of data modifications to an average database block to set header update interval <b>191</b> just long enough to avoid unnecessary duplicate writing of hot data while still minimizing recovery time from failure.

While time-based checkpoint process <b>190</b> is already load balanced over time due to the continuous incremental writing of dirty buffers into block data <b>196</b> and the periodic updating of file headers <b>195</b>, time-based checkpoint process <b>190</b> may optionally be further limited or throttled, for example by limiting processor utilization, execution time, and/or other resources. In this manner, resources can be reserved for other processes, such as a read-only standby database.

Multiple Checkpoints

While <figref idref="DRAWINGS">FIG. 1A</figref> only shows a single time-based checkpoint process <b>190</b>, other embodiments may include multiple concurrent checkpoint processes and/or a mix of incremental and full checkpoints, which may be managed in pending checkpoint table <b>198</b> in a round-robin fashion or by another method. As discussed above, non-incremental checkpoints may be managed using pending checkpoint table <b>198</b>. In this case, the progress of time-based checkpoint process <b>190</b> may be periodically evaluated, and any checkpoint jobs in pending checkpoint table <b>198</b> that have already been serviced can be removed. If a condition occurs that requires the flushing of all dirty buffers to disk, then time-based checkpoint process <b>190</b> may immediately process the most recently queued checkpoint job in pending checkpoint table <b>198</b> to empty the checkpoint queue.

Hardware Summary

According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing devices may be hard-wired to perform the techniques, or may include digital electronic devices such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs) that are persistently programmed to perform the techniques, or may include one or more general purpose hardware processors programmed to perform the techniques pursuant to program instructions in firmware, memory, other storage, or a combination. Such special-purpose computing devices may also combine custom hard-wired logic, ASICs, or FPGAs with custom programming to accomplish the techniques. The special-purpose computing devices may be desktop computer systems, portable computer systems, handheld devices, networking devices or any other device that incorporates hard-wired and/or program logic to implement the techniques.

For example, <figref idref="DRAWINGS">FIG. 3</figref> is a block diagram that illustrates a computer system <b>300</b> upon which an embodiment of the invention may be implemented. Computer system <b>300</b> includes a bus <b>302</b> or other communication mechanism for communicating information, and a hardware processor <b>304</b> coupled with bus <b>302</b> for processing information. Hardware processor <b>304</b> may be, for example, a general purpose microprocessor.

Computer system <b>300</b> also includes a main memory <b>306</b>, such as a random access memory (RAM) or other dynamic storage device, coupled to bus <b>302</b> for storing information and instructions to be executed by processor <b>304</b>. Main memory <b>306</b> also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor <b>304</b>. Such instructions, when stored in storage media accessible to processor <b>304</b>, render computer system <b>300</b> into a special-purpose machine that is customized to perform the operations specified in the instructions.

Computer system <b>300</b> further includes a read only memory (ROM) <b>308</b> or other static storage device coupled to bus <b>302</b> for storing static information and instructions for processor <b>304</b>. A storage device <b>310</b>, such as a magnetic disk or optical disk, is provided and coupled to bus <b>302</b> for storing information and instructions.

Computer system <b>300</b> may be coupled via bus <b>302</b> to a display <b>312</b>, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device <b>314</b>, including alphanumeric and other keys, is coupled to bus <b>302</b> for communicating information and command selections to processor <b>304</b>. Another type of user input device is cursor control <b>316</b>, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor <b>304</b> and for controlling cursor movement on display <b>312</b>. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allows the device to specify positions in a plane.

Computer system <b>300</b> may implement the techniques described herein using customized hard-wired logic, one or more ASICs or FPGAs, firmware and/or program logic which in combination with the computer system causes or programs computer system <b>300</b> to be a special-purpose machine. According to one embodiment, the techniques herein are performed by computer system <b>300</b> in response to processor <b>304</b> executing one or more sequences of one or more instructions contained in main memory <b>306</b>. Such instructions may be read into main memory <b>306</b> from another storage medium, such as storage device <b>310</b>. Execution of the sequences of instructions contained in main memory <b>306</b> causes processor <b>304</b> to perform the process steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions.

The term “storage media” as used herein refers to any media that store data and/or instructions that cause a machine to operation in a specific fashion. Such storage media may comprise non-volatile media and/or volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as storage device <b>310</b>. Volatile media includes dynamic memory, such as main memory <b>306</b>. Common forms of storage media include, for example, a floppy disk, a flexible disk, hard disk, solid state drive, magnetic tape, or any other magnetic data storage medium, a CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, NVRAM, any other memory chip or cartridge.

Storage media is distinct from but may be used in conjunction with transmission media. Transmission media participates in transferring information between storage media. For example, transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus <b>302</b>. Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infra-red data communications.

Various forms of media may be involved in carrying one or more sequences of one or more instructions to processor <b>304</b> for execution. For example, the instructions may initially be carried on a magnetic disk or solid state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to computer system <b>300</b> can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on bus <b>302</b>. Bus <b>302</b> carries the data to main memory <b>306</b>, from which processor <b>304</b> retrieves and executes the instructions. The instructions received by main memory <b>306</b> may optionally be stored on storage device <b>310</b> either before or after execution by processor <b>304</b>.

Computer system <b>300</b> also includes a communication interface <b>318</b> coupled to bus <b>302</b>. Communication interface <b>318</b> provides a two-way data communication coupling to a network link <b>320</b> that is connected to a local network <b>322</b>. For example, communication interface <b>318</b> may be an integrated services digital network (ISDN) card, cable modem, satellite modem, or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, communication interface <b>318</b> may be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface <b>318</b> sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.

Network link <b>320</b> typically provides data communication through one or more networks to other data devices. For example, network link <b>320</b> may provide a connection through local network <b>322</b> to a host computer <b>324</b> or to data equipment operated by an Internet Service Provider (ISP) <b>326</b>. ISP <b>326</b> in turn provides data communication services through the world wide packet data communication network now commonly referred to as the “Internet” <b>328</b>. Local network <b>322</b> and Internet <b>328</b> both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link <b>320</b> and through communication interface <b>318</b>, which carry the digital data to and from computer system <b>300</b>, are example forms of transmission media.

Computer system <b>300</b> can send messages and receive data, including program code, through the network(s), network link <b>320</b> and communication interface <b>318</b>. In the Internet example, a server <b>330</b> might transmit a requested code for an application program through Internet <b>328</b>, ISP <b>326</b>, local network <b>322</b> and communication interface <b>318</b>.

The received code may be executed by processor <b>304</b> as it is received, and/or stored in storage device <b>310</b>, or other non-volatile storage for later execution.

In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details that may vary from implementation to implementation. Thus, the sole and exclusive indicator of what is the invention, and is intended by the applicants to be the invention, is the set of claims that issue from this application, in the specific form in which such claims issue, including any subsequent correction. Any definitions expressly set forth herein for terms contained in such claims shall govern the meaning of such terms as used in the claims. Hence, no limitation, element, property, feature, advantage or attribute that is not expressly recited in a claim should limit the scope of such claim in any way. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense.