Browse Prior Art Database

System and Method for Event analytics centered database recovery

IP.com Disclosure Number: IPCOM000186163D
Original Publication Date: 2009-Aug-12
Included in the Prior Art Database: 2009-Aug-12
Document File: 5 page(s) / 139K

Publishing Venue

IBM

Abstract

Disclosed is a system and method where it is possible to do a database restore based on user Event information rather than non intuitive data like time, log file name/location or system change number. A database event is any occurrence of a user/system initiated step which can be logged in the transaction log file/control file or any other database log which can be later used by the database server to recreate the exact environment which was existing at that point in time. A user can pick and choose the most business critical events and only log information regarding those events. The system doesn't log every Event but only a set of identified business critical events, and can also auto-suggest the user with a set of events which are most frequently used or any other selection criteria. Every event will be associated with an event id which is system generated. The user has an option of specifying what kind of events he wants to add to the recovery option. Based on his selection whenever such an event occurs appropriate database checkpoints are maintained and each checkpoint is stamped with the event information like event name, event type, event timestamp and all the other details needed for database recovery. The user will no longer be required to remember non intuitive data like timestamp up to which the data has to be restored, log file name/location or system change number.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 27% of the total text.

Page 1 of 5

System and Method for Event analytics centered database recovery

1. Background: Database restoration is an activity of replacing an existing database or creating a new database using a previous version / copy of the backup taken at a older point of time and using transaction logs and archive logs (if existing) to apply the transactions to roll forward the database to a valid and consistent state.

    Restoration techniques existing in today's conventional data servers are restricted to the following models:
a. Time-based recovery model, also called point-in-time recovery (PITR), which recovers the data up to a specified point in time.
b. Transaction log based recovery model where database is rolled forward till the transactions from a specific transaction log file (Archive or un-archived) is applied.
c. Change-based recovery model or log sequence recovery model based on the system change number assigned by the data server.

    For any of these models to work, the database user or Administrator needs to be aware of the following three things:
i) Timestamp information , in case of Time-based recovery model
ii) Log file location and name information, in case of Transaction log based recovery model
iii) System change number (SCN) or Log sequence number (LSN), in case of Change-based recovery model or log sequence recovery model.

    The drawback with these models is that restoration of database without data loss is not possible unless and until anyone of the above three inputs is known accurately. If any of the above three points are not known accurately then recovery is not possible. For instance , if the user Joe knows that he had performed an bulk insert operation on the database which has entered invalid data into the data tables on 23-4-2007 between 2 and 2:30 pm. But since the exact timestamp information is missing we need to follow an iterative process using a trial and error method to arrive to the right database state. In today's mission critical 24X7 database installations, the time lost can result in loss of millions of dollars. If the database server had the functionality of doing a database restore based on the occurence of a "User Event" (which is "Insert Event" in this case) then it would have been possible to keep server down time to bare minimum with zero data loss.

    Taking into account that most data servers today are becoming increasingly autonomic, there is a need to develop a new model where it is possible to do a database restore based on user Event information rather than non intuitive data like time, log file name/location or system change number.

References to existing recovery options available in today's Data Servers:
1) DB2 : http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.


b.doc/core/ r0001976.htm
2) Oracle : http://www.oracle.com/technology/deploy/availability/htdocs/BR

    
2. Description: A database...