WARNING: This web is not used anymore. Please use PDBService.HandlingHumanErrors instead!
 

Handling Human Errors

Human errors and logical data corruptions they cause are one of the most often faced types of failures in any database environment. Handling them is not always a trivial task and required procedure depends on many factors like: current database configuration, scope of data corruption, type of destructive action or time passed after the mistake was made.

This document describes tools useful for handling human errors, classifies human errors and proposes recovery procedures that can be used to fix logical data corruption caused by them.

Tools

Log Miner

Log Miner is a piece of functionality of Oracle database that can be extremely useful for handling human errors. It can be especially helpful for determining the exact point in time when the error has occurred and therefore it can improve recovery accuracy.

Oracle Log Miner analyzes one or more redo and archived redo logs and provides a SQL interface to query their contents. These contents can be used not only to determine the exact point in time when someone made a mistake but also in some cases to undo unwanted changes.

Log Miner is available through the DBMS_LOGMNR PL/SQL package existing in the SYS schema.

The main problem about the Log Miner is that it can be useful for human errors handling only if supplemental logging (at least on minimum level) has been enabled on the affected database prior to the error. The supplemental logging is not enabled by default in a newly created database but it is required by Streams so all the databases taking advantage of the Streams technology have the supplemental logging configured. To verify if a given database uses supplemental logging one can query the v$database view:

sqlsys_DB
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If the result is 'YES' or 'IMPLICIT' then supplemental logging is enabled and Log Miner can be used.

Flashback Query/Table

Undrop table

Flash backups and point in time recovery

Automatic recovery script

Procedures

Faulty DML statements

Truncate table

Drop table

Other DDL statements

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE

Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r2 - 2008-01-27 - JacekWojcieszuk
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback