Classifications

• • G—PHYSICS
  • G06—COMPUTING OR CALCULATING; COUNTING
  • G06F—ELECTRIC DIGITAL DATA PROCESSING
  • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
  • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
  • G06F16/23—Updating
  • G06F16/2358—Change logging, detection, and notification
• • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/99951—File or database maintenance
  • Y10S707/99952—Coherency, e.g. same view to multiple users
  • Y10S707/99953—Recoverability

Definitions

• DBMS database management system
• a temporal query is a query where results may only be computed from one (or more) previous database states.
• the salesman then proceeds to approve purchase orders, wherein the DBMS records the purchase orders.
• an auditor discovers that the salesman should not have been granted this authority.
• the auditor may cause the salesman's role to be changed to no longer allow purchase order approval, if this role change has not already been made.
• the auditor also needs to know all of the purchase orders approved by the salesman with incorrect authority. The information may not be available in the database, in its current state, because the records for the purchase order have been deleted or authorization information in the records has been overwritten to reflect purchase order approval by someone with legitimate authority.
• the auditor may wish to user a temporal query such as the following. [0003] "Show me the all the purchase orders approved by John Doe during the time that he was a salesman.”
• the actual format for the temporal query may be different than the example.
• the start time for the example temporal query is when the salesman's role became “Salesman.”
• the end time for the temporal query is when the salesman's role does not include “Salesman.”
• the DBMS should return all purchase orders approved by the salesman between the start and end times.
• an operator may have incorrectly marked an item as "for sale.” In this case, it is desirable to determine all sales of the item recorded by the DBMS that were made with the incorrect price.
• a consistent read or flashback query executes a query as of a specific time in the past.
• the beginning and/or ending time that is pertinent for the temporal query may not be known.
• the response to the temporal query should provide all the results of interest between a start time and an end time. For example, all of the purchase orders that the salesman incorrectly granted are of interest. Therefore, techniques such as consistent read, flashback query etc. are not viable solutions to the problem.
• roles table that has an attribute "role name”, which may have a value of "Salesman” 1 indicating the individual with that role has purchase order authority.
• orders table may have an attribute "approver_name” whose value indicates the individual who approved the order.
• the application programmer can create two new columns, "when” and “when_deleted” in both tables.
• the programmer includes the current time in the "when” column as part of an update or an insert statement. Further, the programmer includes the current time in the "when_deleted” column when a row is deleted. (Alternatively, the programmer could change deletion of rows to insertion of a new row with a status column set to "DELETED.")
• FIG. 1 is a flowchart that illustrates a process for detecting whether an event has occurred in the database, in accordance with an embodiment of the present invention.
• FIG. 2 is a flowchart illustrating steps of a process of providing a result for a temporal query, in accordance with an embodiment of the present invention.
• FIG. 3 is a flowchart illustrating steps of a process of providing results to a temporal query using materialized views, in accordance with an embodiment of the present invention.
• FIG. 4 is a block diagram that illustrates a computer system upon which an embodiment of the invention may be implemented.
• An embodiment of the present invention is a method for determining when an event occurred in a database. 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 statements representing database operations (e.g., SQL statements) that are capable of causing 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 statements are executed against the restored database to cause the mechanism to detect the event.
• database operations e.g., SQL statements
• Another embodiment of the present invention is a method for responding to a temporal query.
• a user provides a temporal query having a begin event, an end event, and a query pertaining to the time between the begin and end events.
• the begin event can be detected in a manner similar to the embodiment discussed in the previous paragraph.
• a trigger event is generated to detect the end event and a mechanism is generated to determine results for the query block.
• a materialized view is generated and populated by executing statements representing database operations (e.g., SQL) that are based on recovery logs.
• the temporal query is answered based on analysis of the information in the materialized view.
• a response is provided for a temporal query to a DBMS.
• a temporal query For illustrative purposes the following example temporal query is used. The user may want to know the answer to the following temporal query: [0032] "Show me all the purchase orders approved by John Doe during the time that he was a salesman.”
• the END block reflects a database event that describes when John Doe no longer has the role of salesman.
• the following example END block specifies when an update or delete removes the role "Salesman" from user "John
• process 100 is not limited to a beginning event of a temporal query. More generally, process 100 can be used to delect when any specified event took place in a database.
• Process 200 of FIG. 2 and process 300 of FIG. 3 provide details of determining an answer to a temporal query, such as what orders were approved by John Doe.
• step 102 of FIG. 1 at least a portion of the database is restored to a time prior to the event under investigation. In some cases, a suitable time can be assumed. If the event is the time at which John Doe was assigned a role of purchase order authority, a suitable time could be the hire date of John Doe. The oldest version of the database can be restored if a more recent time is not arrived at.
• dependent object means any object that is used to facilitate accessing or manipulating another data object or to do so more efficiently. Further details of limiting the portion of the database that is restored are discussed below.
• a trigger is an object (e.g., a procedure) that specifies a series of actions to be automatically performed when a specific event occurs.
• the series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, California).
• a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, California).
• PL/SQL a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, California.
• a trigger is typically associated with a single database table.
• a relevant table is the roles table specified in the BEGIN block. Therefore, a database trigger can be created for the roles table. If the BEGIN block referenced multiple tables, a database trigger is created for each table.
• a database trigger can be created for other tables.
• the user may specify a wall-clock time in the BEGIN block, as opposed to specifying an event. Therefore, in the salesman example, there is not a trigger to set against the roles table.
• a trigger can be set for a table that has conversions between wall-clock time and a logical time kept by the DBMS.
• a particular table may record the SCNs (System Change Numbers) or other logical time every 100 seconds or so of real or wall- clock time. This table does not necessarily have the exact wall-clock time for when each SQL statement was committed, but can provide a very close approximation.
• the trigger is set to determine when the wall-clock time is hit (or approximately hit).
• the trigger can be optimized to specify that the trigger be executed only when columns specified in the BEGIN block are modified.
• the columns in the BEGIN block are "name” and "role_name.”
• the trigger that is generated in step 104 can be any mechanism that will detect when the condition in the BEGIN block evaluates to TRUE.
• a trigger is created to detect a condition in the END block ("the end event"). Step 106 does not need to be performed if the end goal of process 100 is to determine the wall-clock time at which the begin event occurred. However, as process 100 can be used as a starting point for answering temporal queries, creating the end trigger allows determination of the pertinent range for the temporal query. The end trigger is implemented in accordance with the desired application and is discussed below.
• step 108 statements representing database operations are reconstructed from a recovery log of the database, In one embodiment, SQL statements are reconstructed from the recovery log. For example, Oracle LogM iner provided by Oracle Corporation of Redwood Shores, Calif, may be used to generate SQL from recovery logs. However, there is no requirement of using LogMiner.
• the SQL statements that are reconstructed are not necessarily the same SQL statements that were used to make the database changes recorded in the logs. For example, there are many different SQL statements that could cause a particular change to a database and the recovery log information may not be able to determine the exact SQL statement that caused the change. However, the event can still be detected. As a particular example, many different SQL statements could assign the role of salesman to John Doe. However, process 100 can detect a change to the role of salesman to John Doe regardless of the SQL Statement that caused the change.
• process 100 is not required that statements representing database operations be reconstructed for all recovery logs associated with the database. For example, if process 100 is being used to detect the begin event for the example temporal query, then only recovery logs that belong to the set of objects in BEGIN block need to be processed. If process 100 is being used to detect the end event for the example temporal query, then recovery logs that belong to the set of objects in END block need to be processed. As previously discussed, process 100 is not limited to determining a begin event, but applies more generally to event detection. Thus, more generally, the only recovery logs for which database operations are reconstructed are logs belonging to objects associated with the event being detected.
• database operations may be reconstructed for objects associated with the following: 1) a begin event, 2) an end event, 3) a QUERY block, 4) meta-data, such as catalog objects and indexes used to detect the begin event, end event, and execution of the QUERY block.
• step 1 10 the database operations are applied to the reconstructed database in order to apply the changes to the database.
• the changes need be applied. For example, only changes related to the sales table, roles table or dependent objects are applied to the database.
• the trigger actions are applied as defined in the body of the trigger.
• the trigger can perform a wide variety of actions.
• the trigger actions determine and report when the event happened. For example, by correlating the SCN (system change number) with a wall-clock time, the time at which John Doe was granted authority to approve purchases is determined and reported.
• a sequence of actions is taken to determine changes made to the database after the begin event up until another time.
• a temporal query can be answered. For example, the DBMS determines what purchase orders were approved by John Doe after Doe was granted purchase order authority up until the authority was removed.
• PROCESS FLOWS FOR RESPONDING TO A TEMPORAL QUERY After the begin event is detected, one or more queries based on the QUERY block are issued and the results are analyzed.
• the base query is executed against one or more versions of the database. The semantics of the base query and the version of the database against which the base query is executed depend on (a) the statement(s) in the
• a single base query can be executed against the version of the database at the time of the end event. That is, the changes in the recovery logs are applied to the restored database until the end event is triggered and the result of executing the
• QUERY block can be determined by executing the QUERY block at a single point in time — the end event.
• FIG. 2 describes an embodiment of the present invention for this first case.
• the following base query can be issued at the end event using the following SQL statement.
• FlG. 2 describes a process 200 of providing a result for a temporal query, in accordance with an embodiment of the present invention.
• the example temporal query regarding the salesman is used to facilitate explanation of process 200, but the process 200 is not limited to this example.
• the process 200 may be initiated in response to the BEGIN trigger firing in process 100; however, it is not required that process 100 be used to initiate process 200. Moreover, some of the steps of process 200 can be performed prior to the trigger firing.
• Process 200 may include some or all of the acts of the trigger itself, although this is not required.
• the process 200 can exit. If process 200 exits, then process 300 in FIG. 3 may be executed to provide a result.
• step 204 database operations are reconstructed from the recovery log for the database.
• the only recovery logs that need to be processed are ones for objects related to the QUERY block, end event and other dependent objects such as catalog objects and indexes that are needed for executing the QUERY block or end event trigger.
• the example QUERY block specifies the orders table; therefore, only recovery logs for the orders table, any indexes on the orders table, catalog objects and the roles table are processed.
• Process 200 continues, until the end trigger fires, to reconstruct more database operations and determine if a relevant delete or update occurred, in step 208.
• the end trigger only fires if the base query is monotonic and there are no relevant updates or deletes.
• the base query is executed against the database, in step 210. For example the following base query may be executed to determine what orders John Doe approved between the time Doe was granted authority and the authority was revoked:
• FIG. 3 is a flowchart illustrating steps of a process 300 of providing results to a temporal query using materialized views, in accordance with an embodiment of the present invention.
• the example temporal query for the salesman is used to facilitate explanation of process 300, but the process 300 is not limited to this example.
• Process 300 can be performed if process 200 of FlG. 2 exits without providing a result to the temporal query, as previously discussed. However, process 300 can be performed without first performing process 200.
• a materialized view is a view for which a copy of the view data is stored separate from the base tables from which the data was originally gathered and derived.
• the data contained in a materialized view is referred to herein as (“materialized data").
• materialized data The data contained in a materialized view.
• the materialized view helps identify the subset of the result for the base query due to changes made between the begin and end events.
• the materialized view can be automatically populated as the recovery logs are reapplied to the database. However, other solutions may be used as well. Moreover, it is not required that the materialized views be automatically populated.
• the recovery logs are applied through the SQL layer, in one embodiment, such that the materialized views are automatically populated, [0070J
• a materialized view is created based on the QUERY block.
• the materialized view has a Boolean column, in accordance with one embodiment. The Boolean column will store the time window during which a result row in the view satisfied the temporal query. Below is an example materialized view for the temporal query in the salesman example.
• FLAG is a global state variable that is set to 0 when orders_view is created.
• the FLAG is set to ] after the BEGIN event is reached.
• the FLAG may be set by execution of the body of the BEGIN trigger.
• a staging area is created for storing the results that will be returned to the user when the end event is reached.
• the staging area will be empty initially because the salesman is presumed to not have approved any orders at the beginning point when approval authority was granted.
• the staging area is not empty at the beginning point depending on the specification of the temporal query.
• the materialized view and staging area are described as separate objects for ease of exposition and to re-use implementation of materialized views. However, the same object can be used for both.
• the materialized view can be augmented with status columns, wherein it can serve as the staging area.
• the code that populates the materialized view should be appropriately modified to handle the status columns.
• step 304 statements representing database operations are reconstructed from a recovery log of the database.
• SQL statements are reconstructed from the recovery log.
• the recovery is a logical application at the SQL layer rather than a physical application at the block level.
• process 300 is adapted to handle situations in which there are delete and update operations and/or the base query is non-monotonic.
• Step 310 is used to handle these conditions.
• the set difference between the rows in the result staging area and the materialized view is added to the staging area. Any rows found in the materialized view but not in the staging area are copied to the staging area.
• the staging area is empty at the very moment the salesman is granted authority to approve orders. Therefore, a row appearing in the materialized view but not in the staging area indicates that the salesman approved an order after being granted such authority.
• any rows found in the staging area but not in the materialized view are retained in the staging area with a status flag set to DELETED.
• a row appearing in the staging area but not in the materialized view may indicate that an order approved by the salesman was later processed and the row was later deleted because the order processing is complete. Because this is relevant to the temporal query, such information should be returned to the user. In effect, modifying the staging area results in a temporal table for the duration that is of interest to the user.
• Process 300 continues to evaluate more recovery logs until the end trigger is reached in step 312. Then control passes to step 314, wherein the results located in the staging area are returned to the user. Process 300 then ends.
• the view created in step 302 of process 300 be a materialized view.
• the base query specified in the QUERY block can be directly executed. In order for the base query to only return rows that are modified after the begin event, the base query is executed immediately after the begin event is triggered and the results are saved in a separate table. Thereafter, the redo logs are processed and after every COMMIT, the base query is directly executed, and the set of rows saved in the previous step are removed from the result set of the base query. This set of rows constitutes the rows that would have been incrementally populated by the materialized view mechanism.
• the functionality of the materialized view is simulated by reexecuting the QUERY block after every COMMIT operation.
• the base query results are compared against the staging area to compute a set difference, which is added to the staging area in a similar manner as step 310 of process 300.
• the catalog or dictionary objects belong to a special schema, such as the Oracle SYSTEM or SYS schema and can be easily identified.
• One or more conditions should be satisfied in order to restore only a subset of the database.
• a first condition is that the database should allow restoring a subset of the tables.
• a second condition is that the statements representing database operations (e.g., SQL) that are generated from the recovery logs refers to column values within a single table.
• the general SQL statement should not refer to multiple tables.
• SQL generated during recovery may not involve the side table.
• the SQL statement causes the table "emp" to be accessed in order to update the roles table.
• the value "John Doe” may be accessed from the emp table.
• the SQL that is generated during the recovery process would not reference the "emp" table.
• the following SQL might be generated during recovery, which will have the same effect as the SQL that was used during forward progress.
• the temporal query issued by the user will not always specify a begin event and an end event.
• the user may assign a wall-clock time to the BEGIN block or END block, instead of defining them as occurrence of events (i.e. a condition becomes TRUE).
• the nature of the database trigger may depend on the way the user specifies the begin and end events, as previously discussed in step 104 of FIG. 1.
• Another case is that user may not provide any information at all about either the begin or end event. If the user does not specify a begin event, the database can be restored to the beginning of time or some practical start time. If an end event is not specified, the end event can default to the current time or another practical end time.
• the begin event equals the end event and the result of answering the temporal query is a value (e.g. a SCN or Log Sequence number) which can be correlated to wall-clock time using other database information.
• a value e.g. a SCN or Log Sequence number
• the user may wish to execute a point-in-time query when a certain change was made. For example, if a column was incorrectly updated, the temporal query may request the most recent value before the incorrect update. Or, the temporal query may request some other database state at the time that the specified change was made.
• a query based on the QUERY block will be executed just before the changes described by the BEGIN block are applied to the database.
• the body of the trigger executed on encountering the begin event may be the QUERY block itself.
• FIG. 4 is a blockdiagram that illustrates a computer system 400 upon which an embodiment of the invention may be implemented.
• Computer system 400 includes a bus 402 or other communication mechanism for communicating information, and a processor 404 coupled with bus 402 for processing information.
• Computer system 400 also includes a main memory 406, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 402 for storing information and instructions to be executed by processor 404.
• Main memory 406 also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor 404.
• Computer system 400 further includes a read only memory (ROM) 408 or other static storage device coupled to bus 402 for storing static information and instructions for processor 404.
• ROM read only memory
• a storage device 410 5 such as a magnetic disk or optical disk, is provided and coupled to bus 402 for storing information and instructions.
• Computer system 400 may be coupled via bus 402 to a display 412, such as a cathode ray tube (CRT) 3 for displaying information to a computer user.
• An input device 414 is coupled to bus 402 for communicating information and command selections to processor 404.
• cursor control 416 is Another type of user input device, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor 404 and for controlling cursor movement on display 412.
• This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allows the device to specify positions in a plane.
• machine-readable medium refers to any medium that participates in providing data that causes a machine to operation in a specific fashion.
• various machine-readable media are involved, for example, in providing instructions to processor 404 for execution.
• Such a medium may take many forms, including but not limited to, non-volatile media, volatile media, and transmission media.
• Non-volatile media includes, for example, optical or magnetic disks, such as storage device 410.
• Volatile media includes dynamic memory, such as main memory 406.
• Transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus 402.
• Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infrared data communications. All such media must be tangible to enable the instructions carried by the media to be detected by a physical mechanism that reads the instructions into a machine.
• Machine-readable media include, for example, a floppy disk, a flexible disk, hard disk, magnetic tape, or any other magnetic medium, a CD-ROM, any other optical medium, punchcards, papertape, any other physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, any other memory chip or cartridge, a carrier wave as described hereinafter, or any other medium from which a computer can read.
• Various forms of machine-readable media may be involved in carrying one or more sequences of one or more instructions to processor 404 for execution.
• the instructions may initially be carried on a magnetic disk of a remote computer.
• the remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem.
• a modem local to computer system 400 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal.
• An infra-red detector can receive the data carried in the infrared signal and appropriate circuitry can place the data on bus 402.
• Bus 402 carries the data to main memory 406, from which processor 404 retrieves and executes the instructions.
• Computer system 400 also includes a communication interface 418 coupled to bus 402.
• Communication interface 418 provides a two-way data communication coupling to a network link 420 that is connected to a local network 422.
• communication interface 418 may be an integrated services digital network (ISDN) card or a modem to provide a data communication connection to a corresponding type of telephone line.
• ISDN integrated services digital network
• communication interface 418 may be a local area network (LAN) card to provide a data communication connection to a compatible LAN.
• LAN local area network
• Wireless links may also be implemented.
• communication interface 418 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.
• IJ Network link 420 typically provides data communication through one or more networks to other data devices.
• network link 420 may provide a connection through local network 422 to a host computer 424 or to data equipment operated by an Internet Service Provider (ISP) 426.
• ISP 426 in turn provides data communication services through the world wide packet data communication network now commonly referred to as the "Internet" 428.
• Internet 428 uses electrical, electromagnetic or optical signals that carry digital data streams.
• the signals through the various networks and the signals on network link 420 and through communication interface 418, which carry the digital data to and from computer system 400, are exemplary forms of carrier waves transporting the information.
• Computer system 400 can send messages and receive data, including program code, through the network(s), network link 420 and communication interface 418.
• a server 430 might transmit a requested code for an application program through Internet 428, ISP 426, local network 422 and communication interface 418.
• the received code may be executed by processor 404 as it is received, and/or stored in storage device 410, or other non-volatile storage for later execution. In this manner, computer system 400 may obtain application code in the form of a carrier wave.
• embodiments of the invention have been described with reference to numerous specific details that may vary from implementation to implementation.

Landscapes

• Engineering & Computer Science (AREA)
• Theoretical Computer Science (AREA)
• Data Mining & Analysis (AREA)
• Databases & Information Systems (AREA)
• Physics & Mathematics (AREA)
• General Engineering & Computer Science (AREA)
• General Physics & Mathematics (AREA)
• Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Priority Applications (4)

Applications Claiming Priority (2)

Publications (1)

Family

ID=38199210

Family Applications (1)

Country Status (7)

Cited By (2)

Families Citing this family (32)

Citations (2)

Family Cites Families (7)

• 2006
  • 2006-03-10 US US11/372,968 patent/US7555502B2/en active Active
• 2007
  • 2007-03-02 EP EP07752076.5A patent/EP1938228B1/en active Active
  • 2007-03-02 AU AU2007225350A patent/AU2007225350B2/en active Active
  • 2007-03-02 CN CN201410045335.5A patent/CN103745016B/zh active Active
  • 2007-03-02 CN CNA2007800084895A patent/CN101401097A/zh active Pending
  • 2007-03-02 WO PCT/US2007/005351 patent/WO2007106331A1/en not_active Ceased
  • 2007-03-02 CA CA2644779A patent/CA2644779C/en active Active
  • 2007-03-02 JP JP2008558316A patent/JP5286090B2/ja active Active

Patent Citations (2)

Non-Patent Citations (6)

Also Published As

Similar Documents

Legal Events