Recover accidental lost data with Oracle Flash-back functionality

"Guide" This paper presents the principle of flashback function, describes the use of Oracle 9i or Oracle 10g Flash-back function to recover accidental loss of data methods.

Objective

Human error is one of the important reasons for the failure of database system, according to survey about 40% of the system problems are operating errors or user errors caused by, these man-made errors are particularly difficult to avoid. The main solution to data loss and data error problem is data import/export, backup/restore technology. These methods require a correct backup before a data error occurs before recovery can take place. Recovery does not depend on the extent of the error, but only on the backup/recovery policy. This method is time-consuming and makes the database system unable to provide services, for some users accidentally delete data such as small error is somewhat "overqualified." So how do you recover from this accidental error operation caused by data loss? The rollback segment based flashback query (Flashback query) feature is available from Oracle 9i and can be used to recover erroneous DML operations. The flash-back query has been greatly improved in Oracle 10g, no longer limited to flash-back queries, and can be used to recover erroneous DDL (Drop) operations, flash-back tables, flash-back databases, and so on.

Oracle 9i Flash-back Query overview

1, Oracle 9i Flash-back query function

Prior to Oracle 9i, there was no good workaround, except for incomplete recovery, if the user had failed to manipulate the data. Flash-back queries are provided in Oracle 9i and are implemented by a new package dbms_flash. Users use the Flash-back query to get the wrong manipulation of the image view of a point in time before DML (Delete, Update, Insert), and the user can specify this read-only view using system time or system change number (Scn:system). and the appropriate recovery measures can be taken against the error. The flashback query function is completely dependent on automatic rollback segment Management (AUM) and cannot be recovered for drop and other misoperation. The flashback feature can be applied in the following ways:

(1) Repair in the self-maintenance process: When important records are accidentally deleted, users can move back to a point in time, view the missing rows, and reinsert them into the current table.

(2) Restore email and voice email: When the user accidentally deleted the email or voice information, you can move back to the fixed point of time to restore the deletion.

(3) Account balance Status: You can view the previous historical data. such as the exchange rate used to record a particular time in bank foreign currency management. In the past, exchange rate changes were recorded in a history table and can now be queried by the flashback feature.

(4) Decision support systems for trend analysis: Decision support systems and online analysis applications must perform a long transaction. Using Flash-back queries, these applications can perform analysis and modeling of historical data. For example, changes in the demand for specific products such as mineral water with seasonal changes.

2, Roll back section overview

The rollback segment is used to hold the position and value before the data is modified, and the head of the rollback segment contains information about the rollback segment transaction in use. The rollback segment functions as follows:

(1) Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction, Oracle uses the image in the rollback segment to restore the modified data to its original value.

(2) Transaction recovery: When the transaction is being processed, the routine fails and the rollback segment information is saved in the Redo log file, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened.

(3) Read consistency: When a session is modifying data, other sessions will not see the uncommitted changes to the session. Also, when a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) that have been executed since the statement started.

3. Process analysis of Delete and commit operations in Oracle

(1) Remove (delete) process

· Oracle Read block (data blocks) to buffer Cache (buffer) if it does not exist in buffer;

• The details of the delete operation are recorded in the Redo log buffer (redo logs buffers);

• Create an undo (rollback) entry in the things table of the corresponding rollback segment header;

• Create a mirror image of the record to be deleted and store it in the Undo block (rollback);

• Delete records on the corresponding blocks in the buffer cache and mark the corresponding blocks of data as dirty (dirty).

(2) Submit (Commit) process

· Oracle produces a SCN;

• Mark the state of the thing as commited in the rollback section;

· LGWR (log read-write process) Flush log buffer to log file;

• If the data block is still in buffer cache, then the SCN will be recorded on the blocks header, which is referred to as fast submission;

• If the dirty block has been written back to disk, then the next process that accesses the blocks will get the state of the thing from the rollback section to confirm that the thing is committed. The process then gets the submit SCN and writes back to the block header, which is known as the delay chunk cleanup.