Online recovery of user tables using flashback table

• US 8,832,038 B2
• Filed: 03/02/2009
• Issued: 09/09/2014
• 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:
  US12/395,957
• Publication Number:
  US 20090164525A1
• Time in Patent Office: 2,017 Days
• Field of Search: 707/679
• US Class Current: 707/679
• 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 computer-implemented method comprising:

receiving a request to restore a database object to a state as of an earlier point in time;

using a processor to restore the database object in a database system to the earlier point in time by reversing changes introduced into the database object after the earlier point in time, in which dependencies between database objects are considered to avoid consistency violations when the database object is restored; and

displaying the database object restored to the earlier point in time on a display device or storing the database object restored to the earlier point in time in a data structure on a non-transitory computer readable medium.

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 request and ending with a change introduced into the database object immediately after the earlier point in 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 earlier point in 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 the restore of the database object failed if not enough undo records are available.

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

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

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

11. The method of claim 1 further comprising disabling triggers defined on the specified database object for duration of the restore.

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

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 restore.

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

16. The method of claim 13 further comprising deferring IMMEDIATE constraints for or deferring REFERENTIAL INTEGRITY constraints for duration of the restore.

17. The method of claim 1 further comprising reversing the restore and obtaining the database object back to its state before issuance of the request.

18. The method of claim 1, in which the database object in the database system is restored without taking the database system offline.

19. An apparatus comprising:

a processor to;

receive a request to restore a database object to a state as of an earlier point in time; and

restore the database object in a database system to the earlier point in time by reversing changes introduced into the database object after the earlier point in time, in which dependencies between database objects are considered to avoid consistency violations when the database object is restored.

20. The apparatus of claim 19, in which the processor is to reverse the changes in a sequence starting with a change introduced into the database object immediately before issuance of the request and ending with a change introduced into the database object immediately after the earlier point in time.

21. The apparatus of claim 19 wherein the reversing the changes comprises restoring every changed block of the database object one at the time.

22. The apparatus of claim 19 wherein the reversing the changes comprises identifying blocks of the database object that were changed since the earlier point in time.

23. The apparatus of claim 19, in which the processor is to disable triggers defined on the specified database object for duration of the restore.

24. The apparatus of claim 19, in which the processor is to restore database objects that depend on the database object and on which the database object depends.

25. The apparatus of claim 19 further comprising maintaining state of constraints and indexes.

26. The apparatus of claim 19, in which the database object in the database system is restored without taking the database system offline.

27. An article of manufacture comprising a non-transitory computer-readable memory or storage medium having stored therein instructions which, when executed by a processor to perform a process, causes a processing system to perform the process comprising:

receiving a request to restore a database object to a state as of an earlier point in time; and

restoring the database object in a database system to the earlier point in time by reversing changes introduced into the database object after the earlier point in time, in which dependencies between database objects are considered to avoid consistency violations when the database object is restored.

28. The article of manufacture of claim 27 further comprising reversing the changes in a sequence starting with a change introduced into the database object immediately before issuance of the request and ending with a change introduced into the database object immediately after the earlier point in time.

29. The article of manufacture of claim 27 wherein the reversing the changes comprises restoring every changed block of the database object one at the time.

30. The article of manufacture of claim 27 wherein the reversing the changes comprises identifying blocks of the database object that were changed since the earlier point in time.

31. The article of manufacture of claim 27 further comprising disabling triggers defined on the specified database object for duration of the restore.

32. The article of manufacture of claim 27 further comprising restoring database objects that depend on the database object and on which the database object depends.

33. The article of manufacture of claim 27 further comprising maintaining state of constraints and indexes.

34. The article of manufacture of claim 27, in which the database object in the database system is restored without taking the database system offline.