Method and apparatus for audit trail logging and data base recovery

• US 5,561,795 A
• Filed: 05/13/1994
• Issued: 10/01/1996
• Est. Priority Date: 05/13/1994
• Status: Expired due to Term

• Current Assignee: Unisys Corporation
• Original Assignee: Unisys Corporation
• Inventors: Sarkar, Shyam S.
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US08/242,507
• Time in Patent Office: 872 Days
• Field of Search: 395/600, 395/425, 364/200
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery

Abstract

The disclosure relates to a transaction processing system where audit information for database updates and the status of transactions in process is sequentially written in audit records in an audit file, where the audit file may be used to restore the database to a consistent state following a system failure. The invention decreases the overhead processing required for auditing, and at the same time minimizes the impact the auditing has on the processing time required for restoring the database. A value which references the location in the audit file at which recovery of the database may begin is conditionally updated each time a page of the database which is cached in the main memory of the data processing system is written to non-volatile storage, based upon the position of the earliest written audit record which is associated with the cached page. In addition, when processing of a transaction is complete, the transaction identifier of the oldest incomplete transaction is saved in the audit record for the completed transaction. When restoring the database to a consistent state, the value which is maintained according to the earliest written audit record and the transaction identifier of the oldest incomplete transaction are used as reference points in the audit file to minimize the processing required to restore the database.

Claims

1. In a database management system capable of having multiple transactions in process simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including cache processing for caching selected portions of a database in the main memory from non-volatile storage and audit processing for maintaining an audit file of updates to the database and the status of transactions in process, wherein a cache page is the portion of main memory allocable by the cache processing for storing selected database pages, and the audit processing sequentially writes audit records to the audit file when an update is made to the database and maintains an audit file control block for use in processing the audit file when the database requires recovery, a method of operating the data processing system for maintaining an audit file comprising the steps of:

maintaining an oldest audit value for each page of the database that is cached in the main memory of the data processing system, wherein each said oldest audit value references the earliest written audit record resulting from a modification to the corresponding cached database page;

maintaining a physical-redo-start value in the audit file control block, wherein said physical-redo-start value references the location in the audit file at which recovery processing may begin;

when a database page that is cached in the main memory of the database processing system is to be written to non-volatile storage,

updating said physical-redo-start value with an oldest audit value which is associated with said database page to be written to the non-volatile storage if said oldest audit value which is associated with said database page to be written to the non-volatile storage is less than all other oldest audit values which are associated with the other cached database pages;

performing steps (a) and (b) for a completed transaction, wherein said completed transaction is a transaction for which processing is complete;

(a) identifying the oldest transaction currently in process for which processing is incomplete, wherein said oldest transaction is the transaction for which processing began at a time which is earlier than the other transactions in process; and

(b) writing a commit type audit record to the audit file to indicate that processing of said completed transaction is complete, wherein said commit type audit record identifies said completed transaction and said oldest transaction from said identifying step.

2. The method of claim 1, further including the steps of:

removing a record number that is stored in a database record when a delete operation is performed on the database record, wherein said record number uniquely identifies the database record;

storing a transaction number which is associated with the transaction for which said delete operation is performed in the database record; and

disabling storage of data in the database record until processing is complete the transaction for which said delete operation is performed.

3. The method of claim 1, further including the step of prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file.

4. The method of claim 2, further including the step of prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file.

5. In a database management system capable of having multiple transactions in process simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including audit processing for sequentially writing audit records to an audit file and maintaining an audit file control block for use in processing the audit file when the database requires recovery, wherein the audit records contain after-looks and before-looks of database records and functional operations performed on the database, the system further including cache processing for caching selected portions of a database in the main memory and for maintaining a physical-redo-start value, wherein the physical-redo-start value references a location in the audit file at which recovery of the database may begin and a cache page is the portion of main memory allocable by cache processing for storing selected database pages, a method of operating the data processing system for recovering a database using the audit file following failure of the data processing system, comprising the steps of:

for each of the audit records which contain an after-look of a database record and beginning with the audit record referenced by the physical-redo-start value and ending with the last audit record in the audit file, performing step (a),

(a) copying each after-look to a corresponding record in the database;

for each of the audit records which contain a before-look of a database record and beginning with the last audit record in the audit file and ending when a commit type audit record is encountered, wherein said commit type audit record identifies a transaction for which processing was completed and the oldest transaction for which processing was incomplete at the time said commit type audit record was written, performing steps (b) and (c),

(b) reading an audit record; and

(c) copying each before-look to a corresponding record in the database; and

for each audit record that contains a functional operation that was performed on a database record and beginning with said commit type audit record and ending when a begin-of-transaction type audit record is encountered which corresponds to said oldest transaction identified in said commit type audit record, performing steps (d) and (e),

(d) reading an audit record; and

(e) performing the inverse of each functional operation if processing of the corresponding transaction was incomplete at the time of the system failure.

6. The method of claim 5, further comprising the steps of:

maintaining a physical-redo-start value in the audit file control block, wherein said physical-redo-start value references the location in the audit file at which recovery processing may begin;

when a database page that is cached in the main memory of the database processing system is to be written to non-volatile storage,

updating said physical-redo-start value with an oldest audit value which is associated with said database page to be written to the non-volatile storage if said oldest audit value for said database page to be written to the non-volatile storage is less than all other oldest audit values which are associated with the other cached database pages;

performing steps (f) and (g) for a completed transaction, wherein said completed transaction is a transaction for which processing is complete;

(f) identifying the oldest transaction currently in process for which processing is incomplete, wherein said oldest transaction is the transaction for which processing began at a time which is earlier than the other transactions in process; and

(g) writing a commit type audit record to the audit file to indicate that processing of said completed transaction is complete, wherein said commit type audit record identifies said completed transaction and said oldest transaction from said identifying step.

7. The method of claim 6, further including the steps of:

removing a record number that is stored in a database record when a delete operation is performed on the database record, wherein said record number uniquely identifies the database record;

storing a transaction number which is associated with the transaction for which said delete operation is performed in the database record; and

disabling storage of data in the database record until processing is completed for said transaction for which said delete operation is performed.

8. The method of claim 7, further including the step of prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file.

9. The method of claim 6, further including the step of prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete.

10. In a database management system capable of having multiple transactions in process simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including cache processing for caching selected portions of a database in the main memory from non-volatile storage and audit processing for maintaining an audit file of updates to the database and the status of transactions in process, wherein a cache page is the portion of main memory allocable by the cache processing for storing selected database pages, and the audit processing sequentially writes audit records to the audit file when an update is made to the database and maintains an audit file control block with the audit file for use in processing the audit file when the database requires recovery, a method of operating the data processing system for maintaining an audit file comprising the steps of:

maintaining an oldest audit value for each page of the database that is cached in the main memory of the data processing system, wherein each said oldest audit value references the earliest written audit record resulting from a modification to the corresponding cached database page;

when a database page that is cached in the main memory of the database processing system is to be written to non-volatile storage, performing steps (a) and (b),

(a) writing all audit records which are buffered in main memory in the audit file in non-volatile storage; and

(b) storing an oldest audit value which is associated with said database page to be written to non-volatile storage in the audit file control block if said oldest audit value which is associated with said database page to be written to non-volatile storage is less than all other oldest audit values;

performing steps (c) through (g) for each completed transaction, wherein said completed transaction is a transaction for which processing is complete;

(c) waiting for completion of any splitting or merging of database index pages;

(d) prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete;

(e) identifying the oldest transaction currently in process for which processing is incomplete, wherein said oldest transaction is the transaction for which processing began at a time which is earlier than the other transactions in process;

(f) writing a commit type audit record to the audit file to indicate that processing of said completed transaction is complete, wherein said commit type audit record identifies said completed transaction and said oldest transaction from said identifying step; and

(g) writing all audit records which are buffered in main memory in the audit file in non-volatile storage.

11. The method of claim 10, further including the steps of:

removing a record number that is stored in a database record when a delete operation is performed on a database record, wherein said record number uniquely identifies the database record;

storing the transaction number which is associated with a transaction for which said delete operation is performed in the database record; and

disabling storage of data in the database record until processing is complete for said transaction for which said delete operation is performed.

12. In a database management system capable of having multiple transactions in process simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including audit processing for sequentially writing audit records to an audit file and maintaining an audit file control block for use in processing the audit file when the database requires recovery, wherein the audit records contain after-looks, before-looks, and functional operations performed on the database, the system further including cache processing for caching selected portions of a database in the main memory and for maintaining a physical-redo-start value, wherein the physical-redo-start value references a location in the audit file at which recovery of the database may begin and a cache page is the portion of main memory allocable by cache processing for storing selected database pages, a method of operating the data processing system for recovering a database using the audit file following failure of the data processing system, comprising the steps of:

(a) reading the physical-redo-start value, wherein said physical-redo-start value indicates the location in the audit file at which physical recovery may be started;

(b) reading the audit record in the audit file at the location specified by said physical-redo-start value as the current audit record;

(c) copying the after-look from said current audit record to the corresponding database record if said current audit record is a physical or physical/functional type audit record and said after-look does not equal the corresponding database record;

(d) reading the audit record following said current audit record as a new current audit record;

(e) repeating steps (c) through (e) until the last audit record in the audit is processed;

(f) copying the before-look contained in said current audit record to the corresponding database record if said current audit record is a physical or physical/functional type audit record;

(g) reading the audit record that precedes said current audit record as the new current audit record;

(h) repeating steps (f) through (h) until said current audit record is a commit type audit record, wherein said commit type audit record identifies a transaction for which processing is complete and contains an oldest uncommitted transaction value, wherein said oldest uncommitted transaction value identifies the transaction for which, at the time of writing said commit type audit record, processing began at a time which is earlier than the other transactions in process;

(i) reading said oldest uncommitted transaction value from said commit type audit record;

(j) reading the audit record which precedes said current audit record as the new current audit record;

(k) performing the inverse of a functional operation specified in said current audit record if said current audit record is a physical/functional type audit record for transaction for which processing was not complete at the time of the system failure;

(l) reading the audit record which precedes said current audit record as the new current audit record; and

(m) repeating steps (k) through (m) until said current audit record is a begin-of-transaction type audit record that identifies the transaction specified by said oldest uncommitted transaction value.

13. In a database management system capable of processing multiple transactions simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including a cache processor for caching selected portions of a database in the main memory from non-volatile storage and an audit processor for maintaining an audit file of updates to the database and the status of transactions in process, wherein a cache page is the portion of main memory allocable by the cache processing for storing selected database pages, and the audit processor sequentially writes audit records to the audit file when an update is made to the database and maintains an audit file control block for use in processing the audit file when the database requires recovery, an apparatus for maintaining an audit file comprising:

maintenance means for maintaining an oldest audit value for each page of the database that is cached in the main memory of the data processing system, wherein each said oldest audit value references the earliest written audit record resulting from a modification to the corresponding cached database page;

update means interfaced with each said oldest audit value and responsive to the cache processor for updating a physical-redo-start value in the audit file control block when a database page that is cached in the main memory is to be written to non-volatile storage if an oldest audit value which is associated with said database page to be written to non-volatile storage is less than all other oldest audit values which is associated with the other cached database pages;

identification means interfaced with the audit processor for identifying an oldest transaction currently in process for which processing is incomplete at the time processing of another transaction is complete, wherein said oldest transaction is the transaction for which processing began at a time which is earlier than the other transactions in process; and

write means interfaced with the audit processor for writing a commit type audit record to the audit file when processing of a transaction is complete, wherein said commit type audit record identifies the transaction for which processing is complete and said oldest transaction currently in process.

14. The apparatus of claim 13, further including:

insertion means interfaced with the cache processor for inserting a record in the database;

deletion means interfaced with the cache processor for deleting a record in the database;

removal means responsive to said deletion means for removing a record number that is stored in the database record when a delete operation is performed on the database record, wherein said record number uniquely identifies the database record;

storage means responsive to said deletion means for storing the transaction number that associated with a transaction for which said delete operation is performed in the database record; and

disable means interlaced with the database record for disabling storage of data in said database record until processing of said transaction for which said delete operation is performed is complete.

15. The apparatus of claim 13, further including prohibition means interfaced with the audit processor for prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete.

16. The method of claim 14, further including prohibition means interfaced with the audit processor for prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete.

17. In a database management system capable of having multiple transactions in process simultaneously and executing on a data processing system having a main memory, wherein a database is organized with data pages that include data records and index pages that include index records to reference other index pages or the data pages, the database management system including an audit processor for sequentially writing audit records to an audit file when the database requires recovery, wherein the audit records contain after-looks and before-looks of database records and functional operations performed on the database, the system further including a cache processor for caching selected portions of a database in the main memory from non-volatile storage and for maintaining a physical-redo-start value, wherein a cache page is the portion of main memory allocable by the cache processor for storing selected database pages, an apparatus for recovering a database using the audit file, comprising:

first read means for reading audit records from the audit file beginning at the location referenced by the physical-redo-start value and ending with the last audit record in the audit file;

first copy means responsive to the audit records from said first read means for copying after-looks from the audit records to corresponding database records;

second read means for reading audit records from the audit file in reverse order of entry beginning with the last audit record in the audit file and ending when a commit type audit record is encountered, wherein said commit type audit record identifies a transaction for which processing is complete and includes an oldest uncommitted transaction value, wherein said oldest uncommitted transaction value identifies a transaction, which at the time said commit type audit record was written, was the transaction for which processing was started before all other transactions in process;

second copy means responsive to the audit records from said second read means for copying before-looks from the audit records to corresponding database records;

third read means for reading audit records from the audit file in reverse order of entry beginning with said commit type audit record and ending with a begin-of-transaction type audit record that corresponds to said oldest uncommitted transaction value from said commit type audit record; and

rollback means responsive to the audit records from said third read means for performing the inverse of functional operations specified in the audit records which are associated with transactions for which processing was not complete at the time of the system failure.

18. The apparatus of claim 17, further comprising:

maintenance means for maintaining an oldest audit value for each page of the database that is cached in the main memory of the data processing system, wherein each said oldest audit value references the earliest written audit record resulting from a modification to the corresponding cached database page;

update means interfaced with each said oldest audit value and responsive to the cache processor for updating a physical-redo-start value in the audit file control block when a database page that is cached in the main memory is to be written to non-volatile storage if an oldest audit value which is associated with said database page to be written to non-volatile storage is less than all other oldest audit values which are associated with the other cached database pages;

identification means interfaced with the audit processor for identifying an oldest transaction currently in process for which processing is incomplete at the time processing of another transaction is complete, wherein said oldest transaction is the transaction for which processing began at a time which is earlier than the other transactions in process; and

write means for writing a commit type audit record to the audit file when processing of a transaction is complete, wherein said commit type audit record identifies the transaction for which processing is complete and said oldest transaction currently in process.

19. The apparatus of claim 18, further including the steps of:

insertion means interfaced with the cache processor for inserting a record in the database;

deletion means interfaced with the cache processor for deleting a record in the database;

removal means responsive to said deletion means for removing a record number that is stored in a database record when a delete operation is performed on said database record, wherein said record number uniquely identifies said database record;

storage means responsive to said deletion means for storing the transaction number that associated with a transaction for which said delete operation is performed in said database record; and

disable means interlaced with said database record for disabling storage of data in said database record until processing of said transaction for which said delete operation is performed is complete.

20. The apparatus of claim 19, further including prohibition means interfaced with the audit processor for prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete.

21. The apparatus of claim 18, further including prohibition means interfaced with the audit processor for prohibiting any splitting or merging of database index pages while a commit type audit record is being written to the audit file, wherein a commit type audit record indicates that processing of a transaction is complete.