Method and apparatus for data rollback

• US 20080162590A1
• Filed: 01/03/2007
• Published: 07/03/2008
• Est. Priority Date: 01/03/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Brown, Archie W., Voss, Douglas A., Wang, Qinqin, Kundu, Joydip
• Granted Patent: US 8,880,480 B2
• Time in Patent Office: Days
• US Class Current: 707/202
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 11/1662 the resynchronized component or unit being a persistent storage device re-synchronization of failed mirror storage G06F11/2082; rebuild or reconstruction of parity RAID storage G06F11/1008
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/84 Using snapshots, i.e. a logical point-in-time copy of the data
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A database rollback processor allows rollback, or rewind, of the data and metadata to a point at which a failover or other metadata change occurred, therefore “rewinding” the metadata to a previous consistent point. The rollback processor identifies transaction entries in a redo log resulting in changes to the metadata. The changes are identified backward to a target recovery time. Transactions affecting the metadata are stored in a stack. The transactions are then backed out of the metadata according to the stack to restore the metadata to the state at the target recovery time. Data changes from the redo log may then be applied to the corresponding metadata as it existed at the target recovery time. Since the target recovery time is not bound by the timing of the snapshots, but rather may be at an arbitrary point chosen as the target recovery time, a failover, replication or recovery operation need not rely on the snapshot time.

Claims

1. A method of replicating data stored in a first database, comprising the steps of:

identifying a snapshot of the data corresponding to a first logical time;

obtaining a first snapshot of a data dictionary corresponding to a second logical time, wherein the second logical time is later than the first logical time and the data dictionary includes information that maps logical structures in the data to storage used to store the data;

analyzing a set logs representing changes made to the data and the data dictionary after the first logical time to identify the changes made to the data dictionary between the first and second logical times;

using the first snapshot of the data dictionary and the identified changes to generate a second snapshot of the data dictionary corresponding to the first logical time; and

using the snapshot of the data, the logs and the second snapshot of the data dictionary to replicate in a second database changes made to the logical structures in the data in the first database after the first logical time.

2. The method of claim 1, wherein:

the first snapshot of the data dictionary is generated in response to a request received at some time after the first logical time to replicate the data stored in the first database;

the logs representing changes made to the data and the data dictionary after the first logical time and the snapshot of the data corresponding to the first logical time are available in the first database at the time the first snapshot of the data dictionary is generated; and

the data can thereby be replicated from a logical time earlier than the time at which the request to replicate is received.

3. The method of claim 1, wherein:

a failover to the first database occurs, thereby resulting in the first database operating as a primary database and the second database as a logical standby database for the first database as of the first logical time; and

the first database permits changes to the data to be made before the first snapshot of the data dictionary is generated, thereby avoiding the need to make the first database unavailable for updates at the time of failover for a period of time required to generate a snapshot of the data dictionary.

41. The method of claim 2 wherein the available snapshot and logs correspond to a previous backup, further comprising retrieving the available logs from a long term storage medium.

42. The method of claim 2 wherein the available logs are generated from the current database and information of previous changes.

4. A method of logical data rollback comprising:

generating a snapshot of a data dictionary corresponding to a database state;

selecting a rollback time preceding the generated snapshot;

selectively identifying, in a change log, change records corresponding to a period between the generated snapshot and selected rollback time and indicating changes to the data dictionary; and

reverting the data dictionary to the selected rollback time by backing out changes in the identified records made to the database between the selected rollback time and the time of the snapshot.

5. The method of claim 4 further comprising maintaining a set of ongoing database changes, the set maintained as an ordered sequence of change log records indicative of commands indexed by a sequence number.

6. The method of claim 5 wherein the selected rollback time is independent of previous database operations and snapshots.

7. The method of claim 6 wherein reverting further comprises:

computing, from the change log, a logical change record independent of the physical data storage arrangement;

generating a sequence of undo commands corresponding to inverse logical commands derived from the logical change record; and

applying the generated undo commands to the data.

8. The method of claim 7 wherein generating the sequence of undo commands further comprises:

for each change record, identifying physical change corresponding to the physical volume to which the change record pertains;

mapping the change record to a set of logical entities that the change record modifies;

determining an inverse change from that called for by the change record, the inverse change indicative of logical entities affected by the change record; and

mapping the determined inverse change from the mapped logical entities to the corresponding physical entities.

9. The method of claim 8 further comprising applying the determined inverse change to the mapped physical entities, wherein the inverse change is applied to a different physical entity than the physical volume corresponding to the change record.

10. The method of claim 7 wherein the rolled back data is a different physical arrangement from the data corresponding to the change log.

11. A method of restoring a database comprising:

identifying, in a redo log, a time corresponding to a desired target restore time;

identifying a metadata snapshot corresponding to a time following the target restore time;

determining, from the redo log, metadata changes made from the target restore time to the time of the metadata snapshot; and

reverting the metadata to the target restore time by backing out changes made to the metadata between the target restore time and the time of the metadata snapshot.

12. The method of claim 11 wherein determining further comprises:

identifying an ordered set of changes made from the time of the metadata snapshot back to the target restore time via the redo log; and

storing the entries in a redo stack according to the order of occurrence.

13. The method of claim 12 wherein reverting further comprises:

identifying, from the redo log, logged DB commands resulting in changes affecting the primary metadata representation on the standby server, each of the changes corresponding to a transaction;

pushing transactions corresponding to the identified changes onto the redo stack;

associating each of the changes with the corresponding transaction on the redo stack; and

formulating a DB command operable to back out each of the transactions on the redo stack, backing out performed by formulating an inverse of the logged DB command for each change in each backed out transaction.

14. The method of claim 13 wherein formulating an inverse DB command further comprises generating an inverse SQL statement by:

identifying, from the redo log, a new value applied by the command to be backed out;

identifying, from the redo log, an old value replaced by the new value; and

generating a SQL statement to replace the new value with the old value.

15. The method of claim 14 where generating the SQL statement further comprises:

retrieving a physical reference to the new value;

mapping the physical reference to a logical reference indicative of a DB entity to which the SQL statement applies;

retrieving the old value from the redo log; and

concatenating the mapped logical reference with the retrieved old value to build a SQL statement applying the old value to the database entity.

16. The method of claim 15 wherein mapping the physical reference to a logical reference further comprises:

retrieving a physical location specifier from the physical reference, the physical location specifier indicative of a block on a volume;

mapping the physical location specifier to a name index of database object names, the database object names corresponding to database tables and fields; and

formulating a syntactical representation of the physical location specifier including the mapped object names, the syntactical representation operable to perform similarly as the physical location specifier on a different physical volume.

17. The method of claim 16 further comprising:

measuring time increments according to a system change number (SCN), the system change number indicative of an ordered sequence of occurrences; and

ordering database changes by the corresponding SCN, and applying each of the data based transaction from the redo log with the metadata reflecting the metadata based transactions applicable to the time of the data based transaction.

18. The method of claim 13 wherein the redo log includes physical operations to the database, the physical operations indicative of storage locations on a storage medium independent of database table labeling.

19. The method of claim 11 wherein the physical operations are block based referring to physical storage, further comprising converting physical database operations into logical operations by computing a physical reference from the logical table reference and a metadata mapping.

20. The method of claim 19 wherein the logical operations are indicative of fields in database tables, the field and table references applicable to a plurality of physical database instantiations independent of the physical medium upon which the database is stored.

21. The method of claim 20 wherein the logical operations are table based referring to logical table storage, further comprising computing a logical table reference from a physical reference and an inverse metadata mapping.

22. The method of claim 21 further comprising:

executing applications on the standby database in a read only manner; and

creating additional indices to the standby database, the standby database accessible in a non-intrusive manner independently of the primary database.

23. The method of claim 13 further comprising identifying transactions in the redo log corresponding to changes made since the point of interest.

24. The method of claim 23 wherein reverting comprises undoing, via a journaling mechanism, transactions occurring from the target restore time to the time of the metadata snapshot, the target time preceding the time of the metadata snapshot.

25. The method of claim 24 wherein building the metadata snapshot further comprises:

locking a set of DB tables at a particular time for which the snapshot is sought; and

unloading the metadata snapshot from the tables, the table indicative of the metadata state at the particular time.

26. The method of claim 25 wherein the ordering of the changes is performed by maintaining and assigning a system change number, wherein the identifying transactions corresponding to changes made since the point of interest includes popping transactions off the stack until the next stack entry to be popped precedes the time of interest based on the system change number.

27. A method of rolling back changes to a database from a redo log comprising:

identifying first log corresponding to data changes, each of the changes corresponding to a transaction;

identifying a second log corresponding to data structure;

enumerating a series of changes made in the first log;

identifying a point of interest in the first log corresponding to a time after which changes are to be backed out of the first log; and

computing a second point in the second log corresponding to the point of interest in the first log, the second point indicative of a snapshot of data structure referenced by the first log at the point of interest.

28. The method of claim 27 further comprising undoing, via a journaling mechanism, transactions occurring from the target restore time to the time of the metadata snapshot, the target time preceding the time of the metadata snapshot.

29. A database server for restoring a database comprising:

a memory operable to encode instructions;

an interface connected to a primary database and a standby database;

recovery processor responsive to the instructions, the instructions operable to;

identify, in a redo log, a time corresponding to a desired target restore time;

identify a metadata snapshot corresponding to a time following the target restore time;

determine, from the redo log, metadata changes made from the target restore time to the time of the metadata snapshot by identifying an ordered set of changes made from the time of the metadata snapshot back to the target restore time via the redo log; and

store the entries according to the order of occurrence

revert the metadata to the target restore time by backing out changes made to the metadata between the target restore time and the time of the metadata snapshot.

30. The database server of claim 29 further comprising a redo stack, the redo processor operable to:

identify, from the redo log, logged DB commands resulting in changes affecting the primary metadata representation on the standby server, each of the changes corresponding to a transaction;

push transactions corresponding to the identified changes onto the redo stack;

associate each of the changes with the corresponding transaction on the redo stack; and

formulate a DB command operable to back out each of the transactions on the stack, backing out performed by formulating an inverse of the logged DB command for each change in each backed out transaction.

31. The database server of claim 30 further comprising an unwind mapper, the unwind mapper operable to formulate an inverse DB command further comprises generating an inverse SQL statement by:

identifying, from the redo log, a new value applied by the command to be backed out;

identifying, from the redo log, an old value replaced by the new value;

retrieving a physical reference to the new value

mapping the physical reference to a logical reference indicative of a DB entity to which the SQL statement applies;

retrieving the old value from the redo log; and

concatenating the mapped logical reference with the retrieved old value to generate a SQL statement applying the old value to the database entity.

32. The database server of claim 31 wherein mapping the physical reference to a logical reference further comprises:

retrieving a physical location specifier from the physical reference, the physical location specifier indicative of a block on a volume;

mapping the physical location specifier to a name index of database object names, the database object names corresponding to database tables and fields; and

formulating a syntactical representation of the physical location specifier including the mapped object names, the syntactical representation operable to perform similarly as the physical location specifier on a different physical volume.

33. The database server of claim 30 wherein the redo log includes physical operations to the database, the physical operations indicative of storage locations on a storage medium independent of database table labeling.

34. The database server of claim 33 wherein:

the physical operations are block based referring to physical storage, further comprising converting physical database operations into logical operations by computing a physical reference from the logical table reference and a metadata mapping, and

the logical operations are indicative of fields in database tables, the field and table references applicable to a plurality of physical database instantiations independent of the physical medium upon which the database is stored.

35. The database server of claim 34 wherein the logical operations are table based referring to logical table storage, further comprising computing a logical table reference from a physical reference and an inverse metadata mapping.

36. The database server of claim 35 wherein the recovery processor is further operable to:

execute applications on the standby in read only manner; and

create additional indices to the standby database, the standby database accessible in a non-intrusive manner independently of the primary database.

37. The database server of claim 36 wherein building the metadata snapshot further comprises:

locking a set of DB tables at a particular time for which the snapshot is sought; and

unloading the metadata snapshot from the tables, the table indicative of the metadata state at the particular time.

38. The database server of claim 37 wherein the recovery processor is operable to identify transactions in the redo log corresponding to changes made since the point of interest, wherein the ordering of the changes is performed by maintaining and assigning a system change number, wherein the identifying transactions corresponding to changes made since the point of interest includes popping transactions off the stack until the next stack entry to be popped precedes the time of interest based on the system change number.

39. A computer program product having a computer readable medium operable to store computer program logic embodied in computer program code encoded thereon, the computer program code receivable by a processor for executing computer program instructions for restoring a database comprising:

computer program code for identifying, in a redo log, a time corresponding to a desired target restore time;

computer program code for identifying a metadata snapshot corresponding to a time following the target restore time;

computer program code for determining, from the redo log, metadata changes made from the target restore time to the time of the metadata snapshot;

computer program code for identifying an ordered set of changes made from the time of the metadata snapshot back to the target restore time via the redo log;

computer program code for storing the entries in a redo stack according to the order of occurrence; and

computer program code for reverting the metadata to the target restore time by backing out changes made to the metadata between the target restore time and the time of the metadata snapshot, reverting further including undoing, via a journaling mechanism, transactions occurring from the target restore time to the time of the metadata snapshot, the target time preceding the time of the metadata snapshot.

40. A computing device for database server for restoring a database comprising:

means for identifying, in a redo log, a time corresponding to a desired target restore time;

means for identifying a metadata snapshot corresponding to a time following the target restore time;

means for determining, from the redo log, metadata changes made from the target restore time to the time of the metadata snapshot;

means for identifying, from the determined changes, an ordered set of changes made from the time of the metadata snapshot back to the target restore time via the redo log;

means for storing the identified entries in a redo stack according to the order of occurrence; and

means for reverting the metadata to the target restore time by backing out changes made to the metadata between the target restore time and the time of the metadata snapshot, the means for reverting further including;

means for identifying, from the redo log, logged DB commands resulting in changes affecting the primary metadata representation on the standby server, each of the changes corresponding to a transaction;

means for pushing transactions corresponding to the identified changes onto the redo stack;

means for associating each of the changes with the corresponding transaction on the stack; and

means for formulating a DB command operable to back out each of the transactions on the stack, backing out performed by formulating an inverse of the logged DB command for each change in each backed out transaction.