Dropped database table recovery

• US 6,684,225 B1
• Filed: 07/27/2000
• Issued: 01/27/2004
• Est. Priority Date: 07/29/1999
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Zheng, Roger Luo Q., Huras, Matthew Albert, Winer, Michael J., McInnis, Dale M., Ofer, Effi
• Primary Examiner(s): Vu, Kim
• Assistant Examiner(s): Chen, Chongshan
• Application Number:
  US09/626,673
• Time in Patent Office: 1,279 Days
• Field of Search: 707/1, 707/100, 707/201, 707/202, 707/203, 707/204
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A database management system having a dropped table recovery flag. If the dropped table recovery flag is on at the time that a table is dropped, an entry will be made in a dropped table history file. The dropped table history file contains a timestamp of the time of table drop, a unique dropped table identifier, and table definition information. The system includes a command to permit the table-space of a dropped table to be restored and rolled forward to the point of the dropped table drop. The restored and rolled forward dropped table data is written to a flat file. The flat file data is loaded into a recreated table in the current table space to recover the dropped table.

Claims

1. 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 upon determining that a dropped table flag associated with the selected table space has a first value that indicates that information about the dropped table is to be recorded in the dropped table history means, wherein the dropped table flag is set with the first value prior to the table being dropped;

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, wherein information about the dropped table is stored in the dropped table history means;

means for copying 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.

2. The system of claim 1 wherein the dropped table flag enables 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.

3. The system of claim 1 in which the storage data structure is a flat file.

4. The system of claim 1 in which the means for updating the selected table space to a desired current state comprises a user-defined time up to which a second set of stored transactions after the time reflected in the time stamp are replayed against the selected table space.

5. The system of claim 1 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.

6. 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 comprising one or more table spaces, each table space containing one or more tables having table definition attributes, said computer program product comprising:

computer readable program code means for causing a computer to generate for a dropped table in a selected table space, a time stamp reflecting the time of drop and a unique table identifier;

computer readable program code means for causing a computer to create and maintain a dropped table history means for storing the table identifier, the time stamp and the table definition attributes for the dropped table upon determining that a dropped table flag associated with the selected table space has a first value that indicates that information about the dropped table is to be recorded in the dropped table history means, wherein the dropped table flag is set with the first value prior to the table being dropped;

computer readable program code means for causing a computer to restore and roll 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, wherein information about the dropped table is stored in the dropped table history means;

computer readable program code means for causing a computer to copy data from the dropped table in the rolled forward selected table space to a storage data structure;

computer readable program code means for causing a computer to update the selected table space to a desired current state;

computer readable program code means for causing a computer to access the table definition attributes for the dropped table, in the dropped table history means, to create a new table in the table space; and

computer readable program code means for causing a computer to load the data in the storage data structure into the new table.

7. The computer program product of claim 6, wherein the dropped table flag enables 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.

8. The computer program product of claim 6 in which the storage data structure is flat file.

9. The computer program product of claim 6 in which the computer readable program code means for causing a computer to update the selected table space to a desired current state comprises a user-defined time up to which a second set of stored transactions after the time reflected in the time stamp are replayed against the selected table space.

10. The computer program product of claim 6 further comprising a computer readable program code means for causing a computer to store a current state of the selected table space and in which the computer readable program code means for causing a computer to update the selected table space to a desired current state further comprises a means to retrieve the stored current state of the selected table space.

11. A method for recovering a dropped table in database management system comprising one or more table spaces, each table space containing one or more tables having table definition attributes, the method comprising:

(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 upon determining that a dropped table flag associated with the selected table space has a first value that indicates that information about the dropped table is to be recorded in the dropped table history data structure, wherein the dropped table flag is set with the first value prior to the table being dropped;

(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, wherein information about the dropped table is stored in the dropped table history data structure;

(d) copying 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) loading the data in the storage data structure into the new table.

12. The method of claim 11, wherein the dropped table flag enables 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 being set.

13. The method of claim 11, in which the step of updating the selected table space to a desired current state is dependent on a user-defined time up to which a second set of stored transactions after the time reflected in the time stamp are replayed against the selected table space.

14. The method of claim 11, further comprising the step of storing a current state of the selected table space prior to restoring and rolling forward the selected table space and in which the means for updating the selected table space to a desired current state further comprises retrieving the stored current state of the selected table space.

15. A computer program product tangibly embodying a program of instructions executable by a computer to perform the method steps of claim 11.

16. A computer program product tangibly embodying a program of instructions executable by a computer to perform the method steps of claim 12.

17. A computer program product tangibly embodying a program of instructions executable by a computer to perform the method steps of claim 13.

18. A computer program product tangibly embodying a program of instructions executable by a computer to perform the method steps of claim 14.

19. A method for recovering data in a database comprised of at least one table space, wherein each table space is capable of including at least one table, comprising:

one or more table spaces each table space containing one or more tables having table definition attributes;

generating for a dropped table in a table space, a time reflecting the time at which the table in the database table space was removed from the table space and a unique table identifier;

indicating in a data structure the table identifier, the time at which the table in the database table space was removed from the table space, and the table definition attributes, wherein for each table space there is a flag, wherein the step of indicating in the data structure the time at which the table was removed from the table space is performed only if the flag has a first value that indicates that information about the dropped table is to be recorded in the data structure, wherein the flag is set with the first value prior to the table being removed from the table space;

restoring the table space including the table to a state prior to the time at which the table was removed from the database;

applying stored transactions for the table space against the restored table space to make the restored table space current as of the time the table was removed from the database as indicated in the data structure;

recovering the removed table from the restored table space;

copying data from the dropped table in the restored table space to a storage data structure;

updating the table space to a desired current state;

accessing the table definition attributes for the dropped table, in the data structure, to create a new table in the table space; and

loading the data in the storage data structure into the new table.

20. The method of claim 19, wherein during the restoration of the table space, parts of the database excluding the table space being restored arc accessible to users.

21. The method of claim 19, further comprising:

storing the restored table space to a file after the stored transactions are applied to the restored table space, wherein the removed table is generated from the table space stored in the file.

22. The method of claim 21, further comprising:

applying additional transactions to the table space stored in the file before recovering the removed table from the file.

23. A system for recovering data in a database, comprising:

a computer;

a database system accessible to the computer, wherein the database system is comprised of at least one table space, wherein each table space is capable of including at least one table;

wherein the computer includes;

(i) one or more table spaces, each table space containing one or more tables having table definition attributes;

(ii) means for generating, for a dropped table in a table space, a time reflecting the time at which the table in the database table space was removed from the table space and a unique table identifier;

(iii) means for indicating in a data structure the table identifier, the time at which the table in the database table space was removed from the table space, and the table definition attributes, wherein for each table space there is a flag, wherein the step of indicating in the data structure the time at which the table was removed from the table space is performed only if the flag has a first value that indicates that information about the dropped table is to be recorded in the data structure, wherein the flag is set with the first value prior to the table being removed from the table space;

(iv) means for restoring the table space including the table to a state prior to the time at which the table was removed from the database;

(v) means for applying stored transactions for the table space 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;

(vi) means for recovering the removed table from the restored table space;

(vii) means for copying data from the dropped table in the restored table space to a storage data structure;

(viii) means for updating the table space to a desired current state;

(ix) means for accessing the table definition attributes for the dropped table, in the data structure, to create a new table in the table space; and

(x) means for loading the data in the storage data structure into the new table.

24. The system of claim 23, wherein during the restoration of the table space, parts of the database excluding the table space being restored are accessible to users.

25. The system of claim 23, wherein the computer further comprises:

means for storing the restored table space to a file after the stored transactions are applied to the restored table space, wherein the removed table is generated from the table space stored in the file.

26. The system of claim 25, wherein the computer further comprises:

means for applying additional transactions to the table space stored in the file before recovering the removed table from the file.

27. A program for recovering data in a database comprised of at least one table space, wherein each table space is capable of including at least one table, wherein the program is implemented in a computer readable medium and capable of causing a computer to perform:

one or more table spaces, each table space containing one or more tables having table definition attributes;

generating, for a dropped table in a table space, a time reflecting the time at which the table in the database table space was removed from the table space and a unique table identifier;

indicating in a data structure the table identifier, the time at which the table in the database table space was removed from the table space, and the table definition attributes, wherein for each table space there is a flag, wherein the step of indicating in the data structure the time at which the table was removed from the table space is performed only if the flag has a first value that indicates that information about the dropped table is to be recorded in the data structure, wherein the flag is set with the first value prior to the table being removed from the table space;

restoring the table space including the table to a state prior to the time at which the table was removed from the database;

applying stored transactions for the table space 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;

recovering the removed table from the restored table space;

copying data from the dropped table in the restored table space to a storage data structure;

updating the table space to a desired current state;

accessing the table definition attributes for the dropped table, in the data structure, to create, a new table in the table space; and

loading the data in the storage data structure into the new table.

28. The program of claim 27, wherein during the restoration of the table space, parts of the database excluding the table space being restored are accessible to users.

29. The program of claim 27, wherein the program is further capable of causing the computer to perform:

storing the restored table space to a file after the stored transactions are applied to the restored table space, wherein the removed table is generated from the table space stored in the file.

30. The program of claim 29, wherein the program is further capable of causing the computer to perform:

applying additional transactions to the table space stored in the file before recovering the removed table from the file.