Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-07-27
2004-01-27
Vu, Kim (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06684225
ABSTRACT:
RELATED FOREIGN APPLICATION
This patent application claims priority from the commonly assigned Canadian Patent Application entitled “Dropped Database Table Recovery”, having Canadian Patent Application Serial No. 2,279,028, filed on Jul. 29, 1999 by Effi Ofer, Roger Luo Q. Zheng, Matthew A. Huras, Michael J. Winer, and Dale M. McInnis, which application is incorporated herein by reference in its entirety.
1. Field of the Invention
The present invention is directed to an improvement in database systems and in particular to the recovery of tables dropped from databases.
2. Background of the Invention
In relational databases, data is organized into tables. A collection of such tables in a database is referred to as a table space. Database users sometimes inadvertently delete, or drop, a table from a table space. Typically, a database management system (DBMS) does not permit undelete of the drop action: once the table drop is committed, the table's data is permanently deleted and cannot be brought back by way of such a command as an undelete of the drop statement. Instead, the data must be restored from a backup and then the data rolled forward by replaying stored transactions on the data, a potentially slow process.
In certain DBMS environments, such as DB2**, recovering a dropped table is made even more difficult by the fact that a table space restore followed by a roll forward of the table space to a point in time prior to the drop cannot be done. This restriction means that to roll forward to recover a dropped table, the entire database, and not only the effected table space, must be rolled forward. This restriction on the roll forward of the table space is due to the minimum recovery time property. A table space must be rolled forward to at least the minimum recovery time so that it is synchronized with the information in the system catalog tables. The minimum recovery time is updated when data definition language (DDL) statements are executed against a table space, or against tables in a table space.
**DB2 is a registered trademark of International Business Machines Corp.
The minimum recovery time will be later than the time at which the table was dropped. Because of this fact, it is a requirement in typical DBMS environments that the user must recover the entire database. This means that the entire database becomes unavailable to other users while the recovery and rollforward of the database is being carried out. It is typically slower to perform the recovery and restore on the entire database than a recovery and rollforward on the effected table space, only.
The only means in which a dropped table can be currently recovered in many relational databases, such as DB2, is through a database restore followed by a database roll forward to a point in time just prior to the table drop. As indicated above, such an approach will make the database as a whole unavailable to users. It may also be difficult to pinpoint when a table was dropped and therefore data will often be inaccurately retrieved due to uncertainties about when the table in question was dropped. In addition, the structure of the table may no longer be accurately known.
It is therefore desirable to have a DBMS in which tables that have been dropped may be recovered without the need to recover and roll forward the entire database and which permits the point at which the table was dropped to be determined with some accuracy, as well as to determine the structure of the table at the time of the drop.
SUMMARY OF THE PREFERRED EMBODIMENTS
According to one aspect of the preferred embodiments, there is provided an improved database management system for recovering data in a database comprised of at least one table space. Each table space is capable of including at least one table. An indication is made in a data structure of a time at which a table in a database table space was removed from the table space. The table space including the table is restored to a state prior to the time at which the table was removed from the database. Stored transactions for the table space are applied against the restored table space to make the restored table space current as of time the table was removed from the database as indicated in the data structure. The removed table is recovered from the restored table space.
According to another aspect of the preferred embodiments, there is provided a database management system comprising one or more table spaces, each table space containing one or more tables having table definition attributes, means for generating, for a dropped table in a selected table space, a time stamp reflecting the time of drop and a unique table identifier, a dropped table history means for storing the table identifier, the time stamp and the table definition attributes for the dropped table, means for restoring and rolling forward the selected table space containing the dropped table to the time reflected in the time stamp by replaying a first set of stored transactions up to the time reflected in the time stamp, means for copying the data from the dropped table in the rolled forward selected table space to a storage data structure, means for updating the selected table space to a desired current state, means for accessing the table definition attributes for the dropped table, in the dropped table history means, to create a new table in the table space, and means for loading the data in the storage data structure into the new table.
According to another aspect of the preferred embodiments, there is provided the above system further comprising a dropped table flag for enabling dropped table recovery for a selected table space whereby the storage of the table identifier, the time stamp and the table definition attributes for the dropped table are conditional on the dropped table flag.
According to another aspect of the preferred embodiments, there is provided the above system in which the means for updating the selected table space to a desired current state comprises a user-defined time up to which user-defined time a second set of stored transactions after the time reflected in the time stamp are replayed against the selected table space.
According to another aspect of the preferred embodiments, there is provided the above system further comprising a means for storing a current state of the selected table space and in which the means for updating the selected table space to a desired current state further comprises a means to retrieve the stored current state of the selected table space.
According to another aspect of the preferred embodiments, there is provided a computer program product for use with a computer comprising a central processing unit and random access memory, said computer program product comprising a computer usable medium having computer readable code means embodied in said medium for managing a database, as described with respect to the above systems.
According to another aspect of the preferred embodiments, there is provided a method for recovering a dropped table in a database management system comprising one or more table spaces, each table space containing one or more tables having table definition attributes, the method comprising the following steps:
(a) generating, for a dropped table in a selected table space, a time stamp reflecting the time of drop and a unique table identifier,
(b) storing the table identifier, the time stamp and the table definition attributes for the dropped table in a dropped table history data structure,
(c) restoring and rolling forward the selected table space containing the dropped table to the time reflected in the time stamp by replaying a first set of stored transactions up to the time reflected in the time stamp,
(d) copying the data from the dropped table in the rolled forward selected table space to a storage data structure,
(e) updating the selected table space to a desired current state,
(f) accessing the table definition attributes for the dropped table, in the dropped table history data structure, to create a new table in the table space, and
(g) loadi
Huras Matthew Albert
McInnis Dale M.
Ofer Effi
Winer Michael J.
Zheng Roger Luo Q.
Chen Chongshan
Konrad Raynes & Victor & Mann LLP
Victor David W.
Vu Kim
LandOfFree
Dropped database table recovery does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Dropped database table recovery, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Dropped database table recovery will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3252064