Database backup and recovery using separate history files for database backup and audit backup

[0002] The present invention generally relates to database management systems, and more particularly to database backup and recovery.

[0003] Databases have played a critical role in business areas such as banking and airline reservations for many years. These two lines of business illustrate how critical availability of a database can be. If a bank cannot process customer transactions (deposits, payments, transfers, etc.) because of a computer system failure, a customer may be unable to accomplish its business. Likewise, if an airline is unable to book reservations, customers may be lost and its planes may fly empty. Therefore, database availability is critical.

[0004] There are generally two components to backing-up a transaction oriented database. One component is creating a backup copy of the files that comprise the database, and the second component is tracking updates made to the database. Thus, for a full recovery of a database, the most recent backup copies of the database files are reloaded, and the database updates made subsequent to the backup of the database files must be applied to the reloaded files. The process of backing-up database files is sometimes referred to as “dumping” the database, and the process of logging status information and database updates is sometimes referred to as “auditing.” The database backup files are “database dumps” and the file to which update information is logged is the “audit file.”

[0005] In one prior system, for example, the Integrated Recovery Utility from Unisys Corporation, information related to the database dumps and audit file backups was maintained in a history file. The history file included the tape identifiers of the tapes on which the dumps and backups were stored, the times of the dumps, and the ranges of audit records stored on respective tapes.

[0006] The history file freed the database administrator from having to manually track the tapes and times associated with the database dumps and audit backups. However, simultaneous database dumping and audit archiving were not permitted in order to prevent corruption to the history file. Furthermore, redundant copies of archived audit information required a separate administrator-maintained mechanism (in addition to the history file) for tracking the locations of additional audit archives.

[0007] A system and method that addresses the aforementioned problems, as well as other related problems, is therefore desirable.

[0008] In various embodiments, a system and method for database backup and recovery are provided. In one embodiment, a computer-implemented process is provided. The process includes creating a backup copy of the database and writing to a first history file a timestamp indicating the time at which the backup copy was created. A plurality of backup copies of a database audit file are concurrently created, and device identifiers referencing respective devices on which the backup copies of the audit file were created and timestamps indicating respective ranges of audit records in the backup copies of the audit file are written to a plurality of move-history files respectively associated with the plurality of backup copies of the audit file.

[0009] A system for database backup and recovery is provided in another embodiment. The system comprises an audit processor coupled to an audit file, and a recovery processor coupled to the database and to the audit file. The audit processor is configured and arranged to write audit records to the audit file. The recovery processor is configured and arranged to create a backup copy of the database and write to a first history file a timestamp indicating the time at which the backup copy was created. The recovery processor is further configured and arranged to concurrently create a plurality of backup copies of a database audit file and write a plurality of move-history files respectively associated with the plurality of backup copies of the audit file, device identifiers referencing respective devices on which the backup copies of the audit file were created and timestamps indicating respective ranges of audit records in the backup copies of the audit file.

[0010] In another embodiment, an apparatus is provided for database backup. The apparatus comprises means for creating a backup copy of the database; means for writing to a first history file a timestamp indicating the time at which the backup copy was created; means for concurrently creating a plurality of backup copies of a database audit file; and means for writing to a plurality of move-history files respectively associated with the plurality of backup copies of the audit file, device identifiers referencing respective devices on which the backup copies of the audit file were created and timestamps indicating respective ranges of audit records in the backup copies of the audit file.

[0011] In accordance with an example embodiment of the invention, there is provided a computer program product that is configured to be operable to create a backup copy of the database and write to a first history file a timestamp indicating the time at which the backup copy was created, concurrently create a plurality of backup copies of a database audit file, and write to a plurality of move-history files respectively associated with the plurality of backup copies of the audit file, device identifiers referencing respective devices on which the backup copies of the audit file were created and timestamps indicating respective ranges of audit records in the backup copies of the audit file.

[0012] The above summary of the present invention is not intended to describe each disclosed embodiment of the present invention. The figures and detailed description that follow provide additional example embodiments and aspects of the present invention.

[0013] Other aspects and advantages of the invention will become apparent upon review of the Detailed Description and upon reference to the drawings in which:

[0014] FIG. 1A is a functional block diagram of a system for managing backing-up and recovering a database, in accordance with an example embodiment of the invention;

[0015] FIG. 1B is a flow diagram of the data that moves from multiple instances of a recovery utility to multiple database dump tapes 114, multiple dump history files 116, multiple audit archives 118, and multiple move history files;

[0016] FIG. 2 is a data flow diagram illustrating operation of a dump command;

[0017] FIG. 3 is a flowchart of an example process for dumping a database;

[0018] FIG. 4 shows an example dump history file;

[0019] FIG. 5 is a flow diagram showing the flow of data to and from the recovery utility in response to a move command;

[0020] FIG. 6 is a flowchart of the processing performed in archiving audit file data;

[0021] FIG. 7 illustrates an example move history file;

[0022] FIG. 8 illustrates the data contained on an audit archive tape; and

[0023] FIG. 9 is a flowchart of an example process for recovering a database.

[0024] While the invention is susceptible to various modifications and alternative forms, specific embodiments thereof have been shown by way of example in the drawings and will herein be described in detail. It should be understood, however, that the detailed description is not intended to limit the invention to the particular forms disclosed. On the contrary, the intention is to cover all modifications, equivalents, and alternatives falling within the spirit and scope of the invention as defined by the appended claims.

[0025] FIG. 1A is a functional block diagram of a system for managing backing-up and recovering a database, in accordance with an example embodiment of the invention. System 100 includes database management system (DBMS) 102, audit manager 104, recovery utility 106, database 108, audit control information 110, audit file 112, database dumps 114, dump history file 116, audit archives 118, and move history file 120.

[0026] DBMS 102 is a conventional database management system, such as the Relational Data Management System from Unisys. User and administrator input/output for the files that comprise database 108 are controlled by DBMS 102.

[0027] Audit Manager 104 is a conventional software component that is interfaced with DBMS 102 and audits operations performed by DBMS 102 on database 108. The operations are logged to audit file 112, and various status information pertaining to information in audit file 112 is stored in audit control information file 110.

[0028] Recovery utility 106 is based on conventional software that makes backup copies of database 108 and audit file 112. In the example embodiment, separate history files 116 and 120 are maintained by recovery utility 106 in describing backup activities for database dumps 114 and audit archives 118, respectively.

[0029] In response to a user-input dump command, recovery utility 106 copies to tape 114 selected files of database 108. Recorded in dump history file 116 are the names of the files, the tape identifier associated with the tape to which the file was copied, and a timestamp indicating the time at which the copy was made.

[0030] In response to a user-input move command, recovery utility 106 copies a selected portion of audit file 112 to audit archives tape 118, and a corresponding entry in move history file 120 is updated. Move history file 120 includes the tape identifier of the tape to which the audit file information was copied, a begin timestamp, and an end timestamp. The begin timestamp indicates the timestamp of the first audit record on the associated tape, and the end timestamp indicates the timestamp of the last audit record on the tape.

[0031] A user-input reload command causes recovery utility 106 to copy selected database files from database dumps 114 back to online database 108. The database files retrieved from tape will generally be those having the most recent timestamps, for example, as indicated in dump history file 116.

[0032] Recovery utility 106, in response to a recover command, reads audit information from audit archives 118, based on the dump history timestamps of the reloaded database files, and applies the audits to the reloaded files. Move history file 120 is used by recovery utility 106 to determine which tape has the desired audit information.

[0033] FIG. 1B is a flow diagram of the data that moves from multiple instances of recovery utility 106 to multiple database dump tapes 114, multiple dump history files 116, multiple audit archives 118, and multiple move history files 120. In accordance with an example embodiment, separation of information in move history file 120 from information in dump history file 116 permits multiple concurrent backups of information in audit file 112. Multiple backups provide additional protection against disasters, and concurrency allows multiple move requests to be processed at once. It will be appreciated that in an alternative embodiment, a single instance of recovery utility 106 could process multiple user commands to produce multiple audit archive tapes 118.

[0034] FIG. 2 is a data flow diagram illustrating operation of a dump command. A user inputs a dump command to recovery utility 106, which in response reads database files and writes the files to tape 114. For each of the files written to tape, a dump history record is written to dump history file 116.

[0035] FIG. 3 is a flowchart of an example process for dumping a database. The process generally entails copying certain database files to tape and updating the corresponding entries in dump history file 116. The process for dumping database files to tape and updating the dump history file is done iteratively. That is, a file is written to tape, and then the dump history file is updated with the appropriate timestamp. The next file is then written to tape and the dump history file is updated. The steps are repeated until all the database files have been copied to tape.

[0036] At step 152, the process obtains the name of the dump history file. Generally, a default dump history file can be specified in a configuration file used by recovery utility 106. Otherwise, a user can optionally specify an alternate dump history file. Similarly at step 154, the user must specify the names of the database files to dump. At step 156, each of the files is written to tape, one following another. At step 158, respective records are written to dump history file 116 for each of the dumped database files.

[0037] FIG. 4 shows an example dump history file 116. Each time a database file is dumped to tape, a new record is written to dump history file 116. Each record indicates the name of the file dumped, the tape identifier of the tape on which the file was dumped, and a timestamp indicated the time at which the file was dumped. It will be appreciated that some of the records may reference the same tape if the associated dump files reside on the same tape.

[0038] Locking of dump history file 116 is performed at the block level. Thus, multiple dump activities may concurrently write to a particular dump history file. “Block” as used herein references a physical unit of storage managed by the OS-2200 operating system from UNISYS.

[0039] FIG. 5 is a flow diagram showing the flow of data to and from recovery utility 106 in response to a move command. The move command is used to backup information in audit file 112. Recovery utility 106 responds to a move command input from a user by reading a record from the move history file to determine the last archived audit record. Audit data is read from audit file 112 beginning with the first record following the last archived record, and the audit data is written to audit archive 118. A history record is written to the move history file 120. Each record in the move history file includes the tape identifier of the tape on which archived audit data is stored and begin and end timestamps. The begin timestamp is the timestamp of the first audit record on the associated tape, and the end timestamp is the timestamp of the last audit record on the tape. The timestamps are used in the recovery process to locate the audit archive tape 118 having the necessary audit data.

[0040] FIG. 6 is a flowchart of the processing performed in archiving audit file data. At step 202, a user may optionally select a move history file. If the user does not provide the name of a move history file, recovery utility 106 uses a default move history file, which can be specified in a configuration file, for example. With possible multiple move history files, redundant backups can be made of the audit file 112 data, thereby providing further protection against data loss.

[0041] If the move history file is locked, decision step 204 directs control to step 206 where an error message is issued to the user and the process ends. Otherwise, control is directed to step 208 where the move history file is locked. The move history file is locked to prevent corruption that may result from another user issuing a move command and using the same move history file.

[0042] At step 210, the record in audit file 112 at which to begin the backup is located. The record in audit file 112 at which to begin backing up data is the record immediately following the last record backed-up in the previous move command. Audit records are read from that record to the end of the file and written to the destination audit archive tape 118.

[0043] The move history file is updated at step 214. The update information includes the tape identifier of the tape to which the audit records were written and begin and end timestamps. At step 216, the mass storage audit file is optionally deleted, for example at the option of the database administrator, and the move history file is unlocked at step 218.

[0044] FIG. 7 illustrates an example move history file 120. Each record includes, for example, the tape identifier and begin and end timestamps. The tape identifier references a particular audit archive tape 118a-n. In an example embodiment, recovery utility 106 writes to a new archive tape with each move command. Thus, each move command results in a new record being written to move history file 120. The begin timestamp references the timestamp of the first audit record on the associated audit archive tape, and the end timestamp references the timestamp of the last audit record on the associated audit archive tape.

[0045] FIG. 8 illustrates the data contained on an audit archive tape 118. An audit archive tape contains one or more “f-cycles” of audit file 112 in accordance with an example embodiment. Audit manager 104 creates a new f-cycle when, for example, the database administrator disables and then enables auditing of database operations, or a maximum size is reached. Each f-cycle is a type of file, including a header, audit records, and a trailer.

[0046] The begin timestamp is the timestamp of the first audit record in the first f-cycle, and the end timestamp is the timestamp on the last audit record in the last f-cycle on the audit archive tape 118. A move command causes one or more f-cycles of audit file 112 to be written to an audit archive tape.

[0047] Returning now step 216 of FIG. 6, the move history file is unlocked after backup of the audit data is complete. This ends the process of archiving the audit file.

[0048] FIG. 9 is a flowchart of an example process for recovering a database. In the example embodiment, database recovery proceeds in two phases: a reload phase and an audit recovery phase. Each phase is initiated by a database administrator, for example. Steps 306 and 308 are directed to the process of reloading database dump files, and the remaining steps are directed to recovering the database using the archived audit files.

[0049] Depending on the level of recovery required, the database files may or may not need to be reloaded. If reloading the database files is required, then the administrator specifies which files are to be reloaded.

[0050] A particular move history file 120 can be optionally specified at step 304. If no move history file is specified, a default is used.

[0051] At step 306, the database dump files are reloaded from tape 114. The tape identifiers in the dump history file are used to identify the tapes from which the database files are restored. Dump history file 116 is updated at step 308 to indicate which dump files were reloaded. For example, a reload flag is set to indicate (to the audit recovery process) which files were reloaded. The audit recovery process clears the flags when audit recovery is complete.

[0052] Step 310 generally begins phase of database recovery involving applying audit information to the recovered database files. Dump history file 116 is scanned at step 310 to identify which database files were reloaded in order to obtain the timestamps associated with the reloaded files. Recall that a timestamp associated with a database dump file indicates the time at which the file was backed-up to tape.

[0053] At step 312, audit control information 110 is checked to determine whether recovery can begin using the active audit file 112, or whether recovery must begin with an archived audit file. Audit control information 110 indicates the earliest timestamp of the audit records in audit file 112. If the earliest audit record predates the earliest timestamp of the reloaded database files (in other words, the active audit file contains audit records that predate the database dump and there is no need to reference archived audit records), then the active audit file 112 can be used. Otherwise, recovery must begin with an archived audit file.

[0054] If recovery can begin with the active audit file 112, decision step 314 directs control to step 316, where audit records in the active audit file are applied to the reloaded database files. Otherwise, control is directed to step 318 where move history file is searched for the record having the range of timestamps that includes the earliest timestamp (“dump timestamp”) obtained at step 310. The associated tape identifier is obtained, and at step 320 the tape is searched for the first audit record having a timestamp later than the dump timestamp. From that audit record forward, the audits are applied to the reloaded database at step 322. Processing then continues at step 316 to apply the remaining audit records from the active audit file to the reloaded database.

[0055] Accordingly, the present invention provides, among other aspects, a system and method for backing-up and recovering a database. Other aspects and embodiments of the present invention will be apparent to those skilled in the art from consideration of the specification and practice of the invention disclosed herein. It is intended that the specification and illustrated embodiments be considered as examples only, with a true scope and spirit of the invention being indicated by the following claims.