Online recovery of user tables using flashback table

• US 7,499,953 B2
• Filed: 04/23/2004
• Issued: 03/03/2009
• Est. Priority Date: 04/23/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Krishnaswamy, Vasudha, Ganesh, Amit
• Primary Examiner(s): Lu; Kuen S
• Application Number:
  US10/831,449
• Publication Number:
  US 20050240633A1
• Time in Patent Office: 1,775 Days
• Field of Search: 707/202, 707/101, 707/3
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1458 Management of the backup or restore process
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 16/10 File systems; File servers
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and apparatus for updating databases are disclosed. In response to a flashback request, a database object is restored to a state as of a specified flashback time. The changes introduced into the database object are reversed in a sequence starting with a change introduced into the database object immediately before issuance of the flashback request and ending with a change introduced into the database object immediately after the specified flashback time.

Claims

1. A method for online recovery for a database system using an undo record comprising:

receiving a flashback request;

generating a flashback operation for a database associated with the undo record, wherein changes introduced to the database by each transaction are stored in the undo record associated with the transaction and a database object in the database;

identifying the object in the database that was changed since the flashback time by scanning for metadata associated with the object, wherein the metadata tracks the changes and is not a portion of the transaction log; and

restoring the database object to a state as of a flashback time by reversing the changes introduced into the database object after the flashback time specified in the flashback request without taking the database system offline.

2. The method of claim 1 wherein the reversing the changes comprises reversing the changes in a sequence starting with a change introduced into the database object immediately before issuance of the flashback request and ending with a change introduced into the database object immediately after specified flashback time.

3. The method of claim 1 wherein the reversing the changes comprises restoring every changed block of the database object one at the time.

4. The method of claim 1 wherein the reversing the changes comprises identifying blocks of the database object that were changed since the flashback time.

5. The method of claim 4 wherein the reversing the changes further comprises identifying transactions that modified the blocks.

6. The method of claim 5 wherein the reversing the changes further comprises identifying undo records associated with the transactions.

7. The method of claim 6 further comprising notifying a user issuing the request that a flashback operation failed if not enough undo records are available.

8. The method of claim 7 wherein the user has privileges for issuing the flashback request.

9. The method of claim 1 further comprising restoring database objects that depend on the database object and on which the database object depends.

10. The method of claim 4 further comprising performing a full scan of a specified object and identifying undo records associated with the specified object.

11. The method of claim 4 further comprising examining metadata associated with the database object.

12. The method of claim 4 further comprising examining log files comprising information identifying changed blocks of the database object.

13. The method of claim 1 further comprising maintaining state of constraints and indexes.

14. The method of claim 13 further comprising maintaining NULL, NOT NULL and CHECK constraints enabled for duration of the flashback operation.

15. The method of claim 13 further comprising enabling UNIQUE and PRIMARY KEY constraints during the flashback operation and enabling the UNIQUE and PRIMARY KEY constraints at the end of the flashback operation.

16. The method of claim 13 further comprising deferring IMMEDIATE constraints for duration of the flashback operation.

17. The method of claim 13 further comprising deferring REFERENTIAL INTEGRITY constraints for duration of the flashback operation.

18. The method of claim 1 further comprising disabling triggers defined on the specified database object for duration of the flashback operation.

19. The method of claim 1 further comprising reversing the flashback operation and restoring the database object back to its state before issuance of the flashback request.

20. The method of claim 1 wherein the flashback time is a wall clock time.

21. The method of claim 1 wherein the flashback time is a system commit number.

23. The apparatus of claim 18 wherein the database object is a database table.

24. The apparatus of claim 18 further comprising means for restoring database objects that depend on the database object and on which the database object depends.

25. The method of claim 18 further comprising means for maintaining state of constraints and indexes.

22. An apparatus for online recovery for a database system using an undo record comprising:

means for generating a flashback operation for a database associated with the undo record, wherein changes introduced to the database by each transaction are stored in the undo record associated with the transaction and a database object in the database in response to a flashback request;

means for identifying the object in the database that was changed since the flashback time by scanning for metadata associated with the object, wherein the metadata tracks the changes and is not a portion of the transaction log; and

means for restoring the database object to a state as of a flashback time by reversing the changes introduced into the database object after the flashback time specified in the flashback request without taking the database system offline.

30. The apparatus of claim 22 further comprising the flashback module to restore database objects that depend on the database object and on which the database object depends.

31. The method of claim 22 further comprising the flashback module to maintain state of constraints and indexes.

26. The apparatus for online recovery for a processing system having a database system using an undo record comprising:

flashback module of the processing system for a database associated with the undo record to restore a database object in the database to a state as of a specified flashback time in response to a flashback request by

identifying blocks of the database object that were changed since the flashback time by scanning for metadata associated with the object,

wherein the metadata tracks the changes and is not a portion of the transaction log;

identifying transactions that changed the blocks;

identifying undo records and restoring the database object in the database to a state as of the flashback time utilizing the undo records without taking the database system offline,

wherein changes introduced to the database by the transactions are stored in the undo records associated with the transactions and the database object.

27. The apparatus of claim 26 wherein the identifying blocks of the database object comprises performing a full scan of the database object.

28. The apparatus of claim 26 wherein the identifying blocks of the database object comprises accessing log files including information about changed blocks.

29. The apparatus of claim 26 wherein the identifying blocks of the database object comprises accessing metadata associated with the database object.

32. An article of manufacture for online recovery for a database system using an undo record comprising:

a computer-readable memory or storage medium having stored therein instructions which, when executed by a processor, cause a processing system to perform a method comprising;

generating a flashback operation for a database associated with the undo record, wherein changes introduced to the database by each transaction are stored in the undo record associated with the transaction and a database object in the database in response to a flashback request;

identifying the object in the database that was changed since the flashback time by scanning for metadata associated with the object, wherein the metadata tracks the changes and is not a portion of the transaction log; and

restoring the database object in the database to a state as of a specified flashback time by reversing the changes introduced into the database object after a flashback time specified in the flashback request without taking the database system offline.

33. The article of manufacture of claim 32 wherein the database object is a table.

34. The article of manufacture of claim 32 comprising a computer-readable medium having stored therein instructions which, when executed by a processor cause a processing system to perform a method wherein the flashback time is a wall clock time.

35. The article of manufacture of claim 32 comprising a computer-readable medium having stored therein instructions which, when executed by a processor cause a processing system to perform a method wherein the flashback time is a system commit number.