Standby index in physical data replication

Generating a standby index on a standby database server in a physical log-shipping data replication environment. Embodiments of the invention include providing a primary database server and a standby database server, replicating a primary database and a primary database index from the primary database server to the standby database server, generating a standby index on the standby database server, and cataloging the standby index on the standby database server.

BACKGROUND

Embodiments of the invention relate to maintaining a database index. And more specifically, embodiments relate to techniques for maintaining a standby index for a standby database.

System architects and engineers often use multiple computer systems in a production environment in order to ensure the stability of business solutions. In such an environment, even if one computer system fails, the solution may fail over to the other computer systems. As an example, for a solution using a database, the environment may contain both a primary database system and a standby database system. In such an environment, the primary database may be replicated to the standby database system, such that if the primary database system fails, the standby database system contains an up-to-date copy of the database and can assume the workload of the primary database. Furthermore, in some environments, the standby database system may be used to perform certain functions similar to the primary database and certain functions completely different from the primary database. The standby database system may be configured to perform these functions even while the primary database system is operational, in order to alleviate some of the workload of the primary database system. As an example, the primary database may be used to manage day-to-day operations of a business, while the standby database may be used to run reports on how the business performs over a period of time.

SUMMARY

Embodiments of the invention provide a computer-implemented method, computer program product, and system for providing a log-shipping physical replication system including (i) a primary database server, including a primary database; and (ii) a standby database server, including a copy of the primary database, wherein the copy of the primary database is replicated from the primary database server. The computer-implemented method, computer program product, and system include generating a standby index by the standby database server, wherein the standby index pertains to operations performed by the standby database server. Additionally, the computer-implemented method, computer program product, and system include accessing the standby index by the standby database server to retrieve results responsive to a query. The computer-implemented method, computer program product, and system further include outputting the retrieved results by operation of one or more computer processors.

DETAILED DESCRIPTION

Computer environments often use redundant computer systems in order to maximize the uptime and stability of computer applications hosted on those systems. For example, an environment may contain both a primary database system and a standby database system. In such an environment, if the primary database system fails, the standby database system may assume the responsibilities and workload of the primary database system. Data from the primary database may be replicated to the standby database system in order to maintain an up-to-date copy of the primary database on the standby system. The replication may be physical replication, where a log of database operations is sent to the standby system, and the database operations contained in the log are subsequently executed by the standby database management system. In any event, by replicating the database to the standby database system, the standby database system maintains an up-to-date copy of the primary database and can assume the role of the primary database at any time.

In some environments, the standby database system may be configured to perform certain functions using the replicated copy of the primary database. For example, a business may configure the primary database system to perform all data modification functionality (e.g., write actions, such as INSERT operations), and may configure the standby database system to perform some or all of the reporting functionality (i.e., read-only actions, such as SELECT operations) for the business solution. As such, a portion of the overall application workload may be offloaded from the primary database system to the standby database system. Thus, the business may take advantage of the processing resources of the standby system, and yet the standby system is still available to assume the role of the primary database system, in the event the primary system fails.

However, in a situation where the primary database and the standby database are performing substantially different tasks (data modification operations versus read-only operations), the two databases may use different indices containing different information. For example, the primary database may use an index that is defined to optimize certain data modification operations, whereas the standby database may use an index that optimizes certain reporting operations. In physical replication techniques, an identical set of indices will exist on each of the primary database and the standby database. In order for reporting-specific indices to be available to the standby database, the primary database is required to perform additional processing and maintain additional index information that the primary database is not using.

In one embodiment of the invention, a primary database may create a primary index and replicate the primary index to the standby database. The standby database server may create a standby index, which is only populated on the standby server. Upon determining that one or more data values in the primary database have changed, the standby database may update the standby index to account for the changes in the database.

Referring now toFIG. 1,FIG. 1is a block diagram of a networked computer system configured to maintain a standby index, according to one embodiment of the invention. As shown, the system100contains a primary system120and a standby system170. The primary system120contains a computer processor122, storage media124, memory128and a network interface138. Computer processor122may be any processor capable of performing the functions described herein. Storage media124contains a plurality of data pages126. The data pages126may include one or more rows of data. In one embodiment of the invention, data contained in the data pages126is associated with one or more key values in a database. The primary system120may connect to the network150using the network interface138. Furthermore, as will be understood by one of ordinary skill in the art, any computer system capable of performing the functions described herein may be used.

In the pictured embodiment, memory128contains an operating system130and a database management system (hereinafter “DBMS”)132. Although memory128is shown as a single entity, memory128may include one or more memory devices having blocks of memory associated with physical addresses, such as random access memory (RAM), read only memory (ROM), flash memory or other types of volatile and/or non-volatile memory. The DBMS132contains a primary database134, which contains a primary index136. The operating system130may be any operating system capable of performing the functions described herein.

The standby system170contains a computer processor172, storage media174, memory178and a network interface190. Computer processor172may be any processor capable of performing the functions described herein. Storage media174contains a plurality of data pages176. The data pages176may include one or more rows of data. In one embodiment of the invention, data contained in the data pages176is associated with one or more key values in a database. The primary system170may connect to the network150using the network interface190. Furthermore, as will be understood by one of ordinary skill in the art, any computer system capable of performing the functions described herein may be used.

In the pictured embodiment, memory178contains an operating system180and a DBMS182. Although memory178is shown as a single entity, memory178may include one or more memory devices having blocks of memory associated with physical addresses, such as random access memory (RAM), read only memory (ROM), flash memory or other types of volatile and/or non-volatile memory. The standby DBMS182contains a replicated database184. The database184contains a standby index186and a primary index188. Although the standby index186is shown as included in memory178, the standby index186may be stored in any storage location that will not be replicated from the primary DBMS132(or otherwise overwritten). The storage location may be physically within the standby system170, or may be outside the standby system170(e.g., on a network storage drive). In one embodiment, the standby index186is stored in special database storage spaces that exist only on the standby DBMS182. As referred to herein, the special database storage space may be any temporary storage space in the standby DBMS182. For example, in one embodiment, the special database storage space is implemented as a default temporary tablespace in the standby DBMS182. In another embodiment, the standby index186may be maintained on storage media174. The operating system180may be any operating system capable of performing the functions described herein.

Although indices136,186and188are shown as single entities, these indices136,186and188may each refer to a plurality of physical database indices. As a simple example, primary indices136and188may contain database indices “A, B, C,” while the standby index186may contain database indices “D, E.” In one embodiment, each index of “A, B, C, D, E” may correspond to a separate database table. In another embodiment, two different indices may refer to the same database table, but use different keys. For example, both index “A” and index “D” may refer to a database table containing columns “FirstName,” “LastName,” and “Address.” However, in this example, database index “A” and database index “D” may be very different from another because, for instance, index “A” may refer to the database table using “FirstName, LastName” as a key, while index “D” may refer to the same database table using “LastName, FirstName, Address” as a key. Exemplary database index structures and keys are discussed in more detail inFIGS. 3 and 4.

In one embodiment, the DBMS system132may replicate the database134to the standby system170. As such, the database134and the database184may contain identical data. The replication may be performed in part using the network150. The replication may be logical replication or physical replication. In logical database replication, the data values may be replicated from the primary database134to the standby database184. However, using logical replication, the underlying database operations performed by the primary database134may not be replicated to the standby database184.

In contrast, using physical replication (or physical log-shipping replication), the primary DBMS132may replicate a log of database operations performed on the primary database134to the standby DBMS182. Upon receiving the log of database operations, the standby DBMS182may perform the database operations on the standby database184. Under physical replication, the data values in the primary database134and standby database184may be identical. Furthermore, under physical replication, because the identical database operations may be performed on both the primary database134and the standby database184, the underlying database structures of database134and184may also be the same.

In one embodiment of the invention, the primary DBMS132and the secondary DBMS182may perform substantially different functions for a business solution or application. For instance, a business may use the primary DBMS132to perform all data modification functions for the solution. Thus, in this example, the primary DBMS132may be used to process queries that add new data to the database134, or to modify existing values in the database134. Continuing with this example, if the primary DBMS132processes a query adding a new value to the database134, the primary DBMS132may then replicate that value to the standby DBMS182using physical replication. The standby DBMS182may then update the standby database184by adding the new value to the database184.

In this example, the standby DBMS182may be used for reporting functions. The standby DBMS182may be able to perform these read-only functions for the business solution because the standby database184is updated through replication. Thus, the standby DBMS182may receive and process all or some portion of the queries related to reporting, which may alleviate some of the workload of the primary DBMS132.

However, in this example, it may be wasteful for the DBMS132to maintain an index containing both indices relating to data modification operations and indices relating to read-only operations, because only the standby DBMS182is performing reporting functions. As such, the primary index136may contain information relating only to operations performed by the primary DBMS132. The primary DBMS132may replicate the primary index136to the standby DBMS182, so that the standby DBMS182maintains an updated copy of the primary index188. Such a copy may be used if, for instance, the primary system120fails and the standby system170assumes the role and workload of the primary system120.

However, as shown, the standby DBMS182also maintains a standby index186that relates to operations performed by the standby DBMS182. Thus, in the above example, the standby index186may contain information relating to the reporting functions performed by the standby DBMS182. Because the standby index186is maintained exclusively in the standby DBMS182, the primary system120is not burdened with the additional workload of updating and replicating the index. In one embodiment, the primary DBMS132may create the standby index (e.g., in response to a CREATE INDEX statement), and may then replicate the definition of the standby index to the standby DBMS182. However, in this embodiment, the standby DBMS182may still be tasked with populating and maintaining the standby index186, while the primary DBMS132will simply contain an unpopulated definition of the standby index.

FIGS. 2A-2Bare block diagrams of components of the computer systems ofFIG. 1, according to one embodiment of the invention.FIG. 2Ashows an exemplary embodiment of the memory128of the primary system120. As shown, the memory128contains an operation system130and a primary DBMS132. The primary DBMS132contains a primary database134. The database134contains a primary index136, a primary catalog220, and tables222. The primary catalog220may contain metadata about the primary database134, including base tables, views, synonyms, value ranges, users, and user groups. The primary catalog220may also contain metadata about the primary index136for the primary database134. Such information may include metadata on the clustering of the data pages126, what tables are used in the primary index136, what keys are used in the primary index136, etc. The primary DBMS132may update the primary catalog220as the database134and primary index136change.

FIG. 2Bshows an exemplary embodiment of the memory178of the standby system170. As shown, the memory178contains an operating system180and a standby DBMS182. The standby DBMS182includes a replicated database184. The database184contains a primary index188, a primary catalog240, a standby index186, a standby catalog242and tables244. As discussed above, the database184, primary index188and primary catalog240may be replicated from the primary DBMS132to the standby DBMS182, using physical replication techniques. Furthermore, in one embodiment, the primary catalog240and the standby catalog242may be implemented as a single catalog.

The standby DBMS182also includes a standby index186. The standby index186is generally an index created based on operations performed by the standby DBMS182. For example, in an embodiment where the standby DBMS182performs all the reporting functionality for a business solution, the standby index186may contain keys and corresponding row IDs related to reporting functionality. The standby index186may contain different keys or pertain to different tables in the database184than the primary index188. Additionally, the standby index186may be populated and maintained by the standby DBMS182. Thus, when the primary DBMS132replicates a change in the database134to the standby DBMS182, the standby DBMS182may perform the change and may also update the standby index186based on the change.

As shown, the standby DBMS182also contains a standby catalog242. The standby catalog242may contain metadata about the standby database184, including base tables, views, synonyms, value ranges, users, and user groups. The standby catalog242may also contain metadata about the standby index186for the standby database184. Such information may include metadata on the clustering of the data pages126, what tables are used in the standby index186, what keys are used in the standby index186, etc. The standby DBMS182may update the standby catalog242as the database184and standby index186change. In one embodiment, the standby catalog242and the primary catalog240are implemented as a single catalog. In this embodiment, the standby catalog242refers to the metadata in the single catalog related to the standby index186.

Generally, the standby index186is populated only on the standby server170. As a result, the standby server170is able to reduce the workload of the primary server120by assuming responsibility for certain operations of the business solution (e.g., handling all reporting functions). Additionally, the standby server170may further reduce the workload of the primary server120, because the standby DBMS182exclusively maintains the standby index186. In current physical replication systems where the standby DBMS182may be responsible for certain aspects of a business solution, such as reporting functionality, the primary DBMS132is still required to maintain all indices, including those that maintain information relating to both data modification functions and read-only functions. However, embodiments of the invention enable the standby DBMS182to maintain an index186, separate from the primary index136and the replicated primary index188, which pertains only to operations performed on the standby system170. As a result, the primary DBMS132may maintain a primary index136that relates only to the functions performed by the primary DBMS132, thus resulting in a reduced workload for the primary system120.

FIG. 3is an exemplary tree structure for indexing a database table, according to one embodiment of the invention. As shown, the tree300contains various nodes, including leaf nodes320. In one embodiment, a DBMS may use the tree300to index a plurality of database keys contained in a database table. That is, the tree300may include information about each key in the database table and, for each key, may also contain one or more location values. For example, in one embodiment, the primary DBMS132may use the primary index136, defined as a tree structure300, to index one or more tables in the database134. In one embodiment, the location values are represented by a row identifier (or “RID”) that specifies a row on a particular data page126or176where the value is stored. The tree300may contain a location value for each instance of the key in the database table.

FIG. 4is a diagram of a leaf node of the tree structure ofFIG. 3, according to one embodiment of the invention. In this example,FIG. 4shows a leaf node320of a tree300indexing a database table storing phonebook data. More specifically, the database table contains columns “FirstName”, “LastName”, “State” and “Phone Number”. Furthermore, values in the database table (such as the Phone Number value) may be accessed using a key containing “LastName, FirstName, State”. As an example, the pictured leaf node320contains three database keys420. Each key420contains a key value422, and one or more row identifiers424. As noted above, each row identifier424contains a location (e.g., a data page and a row on the data page) where a data value for the key is stored. For example, key4201contains a key value4221of “Doe, Denise, California” and row identifiers4241and4242. In other words, the database table contains data for two people named Denise Doe who live in California. As an additional example, the leaf node320also contains key4202, with value4222of “Doe, Jane, Montana” and a single row identifier4243. Thus, the database table represented by the tree structure300in this example only contains data for one person named Jane Doe who lives in Montana. Although the pictured leaf node320only contains three key values420, different sizes of leaf nodes may of course be used.

In one embodiment, the primary index136and the replicated primary index188are structurally similar to the standby index186. That is, all three indices136,186and188may use, for example, the tree structure300shown inFIG. 3to index data in the corresponding databases134and184. Furthermore, the information contained in the primary index136and the replicated primary index188may be identical, because the index188is replicated from primary index136. However, although the standby index186may share the same structure as indices136and188, the standby index186may contain different data than either of the other indices. For example, in an embodiment where the primary DBMS132is used for data modification operations and the standby DBMS182is used for reporting operations, the primary index136may contain substantially different data than the standby index186, as the two indices are used for substantially different purposes. Continuing the example, while the standby index186may use the key of “LastName, FirstName, State” (as shown inFIG. 4), the primary index136may use a key of only “LastName, FirstName.”

The indices136and186may also differ in other ways, such as referring to one or multiple different tables. As discussed above, indices136,186and188may represent multiple physical database indices, each of which may relate to different database tables. For example, while the standby index186may relate to a database table containing phonebook information, the primary index136may represent two indices each relating to a different database table: one to a database table containing billing information, and another to a database table containing service information. In one embodiment, a physical database index may relate to multiple database tables. As such, two indices may differ because a first index relates to tables “A and B,” while the second index relates to tables “A and C.” Thus, although the indices136and186may be similar structurally, the information contained in the indices136and186may be partially or entirely different.

FIG. 5is a flow diagram illustrating a method of generating a standby index, according to one embodiment of the invention. As shown, the method500begins at step520, where the standby DBMS132receives a request to generate a standby index186(step520). In one embodiment, the request may be received directly from a user (e.g., a database administrator). In another embodiment, the primary DBMS132may receive an initial request from the user and generate a definition for the standby index on the primary system120. However, in this embodiment, the primary DBMS132will not populate its instance of the standby index, but rather will replicate the definition of the standby index (using physical replication techniques) to the standby DBMS182. Once the request is received, the standby DBMS182generates the definition for the standby index186(step522). In an alternate embodiment where the request is replicated from the primary DBMS132, the standby DBMS182may define the standby index186upon receiving the replicated definition.

Once the standby index186is defined, the standby DBMS182then populates the standby index186with data (step524). Generally, the standby index186is populated with data associated with the functionality of the standby DBMS182. For example, if the standby DBMS182is responsible for handling the reporting functionality of a business solution, the standby index186may be populated with data associated with the reporting functionality. In one embodiment, the standby index may be populated the first time a query attempts to make use of the index (i.e., on the first access of the index). In another embodiment, the standby index may be built immediately after the definition is replicated. In this embodiment, the standby index may be populated either in-line with the replay of the catalog operations, or may be populated asynchronously in the background. In one embodiment, the index186may store the data using a tree structure, such as the exemplary tree structure300shown inFIG. 3. Such a structure300may include multiple leaf nodes320, each containing one or more key values422and corresponding RIDs424. Of course, other data models may be used to represent the data in the standby index186.

Once the standby index186is populated, the standby DBMS182adds the standby index definition to the standby catalog242(step526). In an embodiment where the standby catalog242and primary catalog240are implemented as a single catalog, the primary DBMS132may add the standby index definition to the single catalog. In this embodiment, the primary DBMS132adds the standby index definition to the single catalog, which is then replicated to the standby DBMS182. As discussed above, the standby catalog242may contain metadata about information contained in the replicated database184. Additionally, the standby catalog242may contain metadata about the standby index186.

Once the standby catalog242is generated, the method500begins a loop, wherein the standby index186is maintained (step528). The standby DBMS182may monitor the replicated database184(step530), and if a change is detected in the database184, the DBMS182updates the standby index186(step532) based on the change. In one embodiment, the standby DBMS182may maintain the standby index186as the standby DBMS182replays the logs of operations replicated from the primary DBMS132. In such an embodiment, when the standby DBMS182determines that an operation updates data related to the standby index186, the DBMS182may update the index186accordingly. When the standby index186no longer needs to be maintained, the method ends. Examples of when the standby index186would no longer need to be maintained include when the standby DBMS182shuts down (e.g., in response to a shutdown command received from a user), when the primary DBMS132fails and the standby DBMS182assumes the role of the primary DBMS132, and when the index is no longer needed for the workload and accordingly is dropped. In an alternate embodiment, where the standby index186is not maintained in memory178(e.g., where the standby index186is maintained on storage174), the standby DBMS182may simply stop using the standby index186when it is no longer needed (e.g., when the standby DBMS182shuts down). In this alternate embodiment, the standby DBMS182may then resume using the standby index186once it is needed again (e.g., when the standby DBMS182is restarted).

One advantage of the method500is that the primary DBMS132does not have to populate and maintain the standby index186. Instead, this work is performed by the standby DBMS182on the standby system170. As a result, the primary system120may avoid using its resources (e.g., computer processor122, storage124and memory128) to create and maintain index information that the primary system120does not use. In other words, because the standby index186contains index information used only by the standby DBMS182, the primary system120gains no benefits from generating and maintaining the standby index186. By shifting this processing to the standby system170, the primary system120may conserve its resources for other processing operations. Another advantage of the method500is that queries on the standby system170may run faster, as the standby index186contains indexes pertaining to the workload of the standby DBMS182.

FIGS. 6A-6Bare flow diagrams illustrating a method of generating and maintaining a standby index, according to one embodiment of the invention. More particularly,FIG. 6Ais a flow diagram illustrating a method of generating a standby index, according to one embodiment of the invention. As shown, the method600begins at step620, where the primary system120receives a request to generate a standby index. Upon receiving the request, the primary DBMS132generates a standby index definition (step622). The standby index definition is then replicated to the standby system (step624). Furthermore, the primary DBMS132may update the standby index definition. The updates to the standby index definition may then be subsequently replicated to the standby DBMS182. Although the primary system120receives the request and generates the definition for the standby index, the primary DBMS132does not populate the standby index definition with data. Rather, in this embodiment, the primary DBMS132only defines the standby index, such that the standby index definition may be replicated to the standby system170.

Upon receiving the replicated standby index definition, the standby DBMS182generates the standby index186(step626). As discussed above, the replication may be performed using physical replication. Once the standby index186is generated, the standby DBMS182adds the standby index definition to the standby catalog242(step628). In one embodiment, where the standby catalog242and the replicated primary catalog240are implemented as a single catalog, the standby DBMS182may insert an entry representing the standby index definition into the single catalog. Furthermore, in a second embodiment using a single catalog, the primary DBMS132may insert an entry representing the standby index definition into the single catalog, and may then replicate this catalog to the standby DBMS182. As discussed above, the standby catalog242may contain metadata describing the standby index186, as well as metadata describing the database184.

The standby DBMS182then populates the standby index186with data (step630). As discussed above, the data in the standby index186may be stored using the exemplary tree structure300, as shown inFIGS. 3 and 4. Once the standby index186and standby catalog242have been generated and populated, the standby DBMS182may use the standby index186and standby catalog242in performing database operations. For example, in an embodiment of the invention where the standby DBMS182is used for reporting functions, the standby DBMS182may use the standby index186, for instance, to look up values specified by a SELECT statement.

FIG. 6Bis a flow diagram illustrating a method of maintaining a standby index, according to one embodiment of the invention. As shown, the method640begins at step632, where the primary DBMS132modifies one or more data values in the database134. In one embodiment, upon making the modification to the database134, the primary DBMS132may additionally update the primary index136, based on the changes to the database134. In such an embodiment, the updated primary index136may also be replicated to the standby system170, such that the standby DBMS182may update primary index188accordingly. Furthermore, in one embodiment, the primary DBMS132may update the standby index definition, and this modification may subsequently be replicated to the standby system170. However, even in this embodiment, the primary DBMS132does not populate the standby index definition. Rather, this task may be reserved for the standby DBMS182.

The modification is then replicated to the standby system170(step634). Upon receiving the replicated modification, the standby DBMS182modifies the data in the database184, based on the replicated modification (step636). As an example, in an environment using physical replication, the primary DBMS132may replicate the one or more database operations used to modify the data to the standby system170, whereupon the standby DBMS182may execute the one or more data operations against the database184. Once the database184is updated, the standby DBMS182updates the standby index186to account for the modification to the database184(step638). Once the standby index186is updated, the method640ends.

In this embodiment, although the primary DBMS132generates an initial definition for the standby index, the primary DBMS132is not tasked with populating the standby index. Furthermore, even when the primary DBMS132modifies data in the database134, the primary DBMS132does not update the standby index. Rather, in both cases, the events are replicated to the standby system170, where the standby DBMS182populates or updates the standby index186. As such, the primary system120is not taxed with maintaining a standby index, which is used solely by the standby system170.

FIG. 7is a flow diagram illustrating a method of failing over to a standby database containing a standby index, according to one embodiment of the invention. In this example, the primary system120is responsible for data modification operations, while the standby system170is responsible for reporting operations. As shown, the method700begins at step720, where the primary database goes offline. This may occur because of a variety of reasons including natural disaster or man-made disaster. Additionally, the primary DBMS132may be taken offline intentionally as part of a disaster recovery exercise. In any event, once the primary database is unavailable, the solution fails over to the standby database184and the standby DBMS182assumes the role previously held by the primary DBMS132(step722). For instance, because the primary DBMS132in this example is responsible for data modification operations, if the primary DBMS132goes offline, the solution may fail over to the standby system170, whereupon the standby DBMS182will then become responsible for data modification operations.

Once the solution fails over to the standby system170, the standby DBMS182marks the standby index186as invalid (step726). By marking the standby index186as invalid, the standby DBMS182indicates that the index186should be purged. As shown, the standby DBMS182then purges the previous standby index186(step730). Likewise, the standby DBMS182then purges the standby catalog242(step732). In an embodiment where the standby catalog242and primary catalog242are implemented as a single catalog, the standby DBMS182purges only the entries relating to the standby index186from the single catalog. Once the catalog242is purged, the standby DBMS182continues processing requests (step734). In one embodiment, the purging of the standby index186and standby catalog242is performed immediately upon the standby DBMS182assuming the role of the primary DBMS132. In another embodiment, the purging occurs in the background, during periods where the processing resources of the standby system are underutilized or idle170.