Systems and methods for recovery of consistent database indexes
AssigneeBMC Software, Inc.
InventorsWilliam J. Pothoff, Thomas G. Price, Vladimir Prasolov
Life of this patent
Estimating the term…
Abstract
A method to efficiently create a consistent image of a database index at any point-in-time by reversing the effect of uncommitted updates by applying database transaction logs to an index page set in a single pass. The created image can be used to recover the database index to a point in time that is consistent with its associated table, or for physical migration where data and indexes are migrated to another database that may or may not be on the same DBMS and the indexes and data must be consistent as of the same point in time.
Claims30
A method to recover a consistent database index to reference, at a recovery point-in-time, data records that are contained in a database recovered from a backup copy or a migration copy of the database, the data records identified by a record identifier (RID) and the index including one or more index pages with (key/RID) pair entries, the method comprising:
- obtaining an index copy at a log point-in-time that precedes database transactions on the database that were uncommitted at the recovery point;
- finding transaction log records describing transactions from the log point to the recovery point including both log records describing transactions that were committed and log records describing transactions that were uncommitted at the recovery point;
- sorting the log records in an index page order and an ascending log record sequence number (LRSN) order;
- applying, in sorted page order and LRSN order, the updates reflected in the sorted log records to the pages of the index copy in a single pass operation on the index copy,
- wherein applying the updates reflected in the sorted log records to the pages of the index copy includes marking the RID from each key/RID entry caused by an uncommitted insert as a deleted RID;
- and
- after or during the single pass operation, restoring each key/RID entry that is a result of an uncommitted transaction to its last committed state by deleting the RIDs that were added by an uncommitted insert and un-deleting each RID entry caused by an uncommitted delete,
- whereby each key/RID entry in the index is restored to a state that is consistent with the database in that only updates that were committed at the recovery point are reflected in the recovered index.
A computer system to recover a consistent database index to reference, at a recovery point-in-time, data records that are contained in a database recovered from a backup copy or data records that are contained in a migration copy of the database, each data record being identified by a record identifier (RID) and the database index including one or more index pages with (key/RID) pair entries, the computer system comprising:
- a database management system (DBMS) coupled to the database and maintaining a log of transactions involving data records in the database, the DBMS hosted on a computer network including at least one processor and at least one memory;
- and
- an index recovery module configured to:
- obtain an index copy at a log point-in-time that precedes database transactions on the database that were uncommitted at the recovery point;
- find transaction log records describing transactions from the log point to the recovery point including both log records describing transactions that were committed and log records describing transactions that were uncommitted at the recovery point;
- sort the log records in an index page order and an ascending log record sequence number (LRSN) order;
- apply, in sorted page order and LRSN order, the updates reflected in the sorted log records to the pages of the index copy in a single pass operation on the index copy,
- wherein applying the updates reflected in the sorted log records to the pages of the index copy includes marking the RIDs from each key/RID entry caused by an uncommitted insert as a deleted RID;
- and
- after or during the single pass operation, restore each key/RID entry that is a result of an uncommitted transaction to its last committed state by deleting the RIDs that were added by an uncommitted insert and un-deleting each RID entry caused by an uncommitted delete to obtain the consistent database index at the recovery point.
A computer program product including instructions recorded on a non-transitory computer-readable storage medium and configured to cause at least one processor to recover a consistent database index to reference, at a recovery point-in-time, data records that are contained in a database recovered from a backup copy or data records that are contained in a migration copy of the database, each data record being identified by a record identifier (RID) and the database index including one or more index pages with (key/RID) pair entries, the instructions including instructions that when executed by the at least one processor cause the at least one processor to:
- obtain an index copy at a log point-in-time that precedes database transactions on the database that were uncommitted at the recovery point;
- find transaction log records describing transactions from the log point to the recovery point including both log records describing transactions that were committed and log records describing transactions that were uncommitted at the recovery point;
- sort the log records in an index page order and an ascending log record sequence number (LRSN) order;
- apply, in sorted page order and LRSN order, the updates reflected in the sorted log records to the pages of the index copy in a single pass operation on the index copy,
- wherein applying the updates reflected in the sorted log records to the pages of the index copy includes marking the RID from each key/RID entry caused by an uncommitted insert as a deleted RID;
- and
- after or during the single pass operation, restore each key/RID entry that is a result of an uncommitted transaction to its last committed state by deleting the RIDs that were added by an uncommitted insert and un-deleting each RID entry caused by an uncommitted delete to obtain the consistent database index at the recovery point.
Dependencies are inferred from the claim text.