Detecting database events using recovery logs
A method and apparatus for determining when an event occurred in a database is provided. At least a portion of the database is restored to a point in time that is prior to the event. Recovery logs are translated to database operations (e.g., SQL) that are capable of causing the changes described in the recovery logs. A mechanism is created for detecting the event based on execution of the statements. For example, a database trigger is created to detect the event. The database operations are executed against the restored database to cause the mechanism to detect the event.
1. A computer-implemented method that detects occurrence of an event in a database, comprising:
restoring a copy of at least a portion of the database to a point prior to the occurrence of a beginning event of a temporal query;
translating redo log records that describe changes to the database into statements that reflect database operations that are capable of causing the changes to the database;
generating a first database trigger for detecting the beginning event based on execution of the statements; and
causing the first database trigger to detect the beginning event by executing the statements;
generating a second database trigger for detecting an end event of the temporal query based on execution of the statements;
accessing a base query that is based on the temporal query;
after the first database trigger detects the beginning event, performing the following:
causing the second database trigger to detect the end event by executing the statements; and
in response to the second database trigger detecting the end event, issuing the base query to determine a response to the temporal query, wherein results of the base query provide a response to the temporal query;
wherein the steps of restoring, translating, and issuing are performed on one or more computing devices.
2. The method of claim 1 , wherein the statements are compliant with SQL.
3. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 2 .
4. The method of claim 1 , further comprising:
determining that there are no updates or deletes to the database object between the beginning event and the end event as a condition to issuing the base query to determine a response to the temporal query.
5. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 4 .
6. The method of claim 1 , further comprising:
determining that the base query is monotonic as a condition to issuing the base query to determine a response to the temporal query.
7. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 6 .
8. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 1 .
9. The method of claim 1 , further comprising:
after the first database trigger detects the beginning event and before the second database trigger detects the end event, determining whether there is a delete or update to a row that is related to the base query.
10. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 9 .
11. The method of claim 1 , wherein the temporal query does not specify at least one of the beginning event or the end event.
12. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 11 .
13. The method of claim 1 , further comprising analyzing the temporal query to determine a set of database objects that are to be restored, wherein:
the set of database objects are less than all of the database objects stored in the database; and
restoring a copy of at least a portion of the database comprises restoring a copy of only the set of database objects.
14. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 13 .
15. A computer-implemented method that detects occurrence of an event in a database, the method comprising the steps of:
restoring a copy of at least a portion of the database to a point prior to the occurrence of a beginning event of a temporal query;
translating redo log records that describe changes to the database into statements that reflect database operations that are capable of causing the changes to the database;
generating a first database trigger for detecting the beginning event based on execution of the statements;
causing the first database trigger to detect the beginning event by executing the statements;
generating a second database trigger for detecting an end event of the temporal query based on execution of the statements;
after detecting the beginning event, performing the following:
activating a view based on the temporal query;
executing a set of the statements, wherein the set of the statements reflect database operations that are capable of causing the changes to the database which occurred after the beginning event;
analyzing changes to data in the view as the set of the statements are executed to determine a response to the temporal query; and
in response to the second database trigger detecting the end event, providing the response to the temporal query;
wherein the steps of restoring, translating, and activating are performed on one or more computing devices.
16. The method of claim 15 , wherein the view is a materialized view.
17. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 16 .
18. The method of claim 15 , further comprising comparing current data in the view with previous data in the view to determine changes made to the database object.
19. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 18 .
20. The method of claim 15 , wherein the first database trigger activates the view.
21. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 20 .
22. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 15 .
23. The method of claim 15 , further comprising determining that a base query of the temporal query is monotonic as a condition to activating the view.
24. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 23 .
25. The method of claim 15 , wherein the temporal query does not specify at least one of the beginning event or the end event.
26. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 25 .
27. The method of claim 15 , further comprising analyzing the temporal query to determine a set of database objects that are to be restored, wherein:
the set of database objects are less than all of the database objects stored in the database; and
restoring a copy of at least a portion of the database comprises restoring a copy of only the set of database objects.
28. A machine-readable storage medium storing instructions which, when executed by one or more processors, cause the one or more processors to perform the method recited in claim 27 .