Zahid Karim
Recent Posts
1.Write fast and efficient PLSQL - DBMS_PROFILER
2.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
3.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
4.ORA-24248 XMLDB extensible security not installed
5.Upgrade Oracle 10g Release 2 to Oracle 11g Release 1
Find me on
How to read or analyze redo log files using LogMiner Oracle 10g Transparent Data Encryption examples

Using Oracle LogMiner to undo incorrectly committed changes

Posted by Zahid on June 11, 2010.

A data loss due to user error means rows in a table accidentally updated or deleted by user and is also known as logical corruption. This usually happens when users lack training on how to properly use the application and its work flows. With appropriate training these kind of problems can be minimized, however oracle does provide a few ways to recover from such kind of losses. e.g. RMAN incomplete recovery if you have backups of data that is lost, FLASHBACK database (Oracle 10g) if FLASHBACK Logs are setup and FLASHBACK QUERY if UNDO management is being done appropriately etc.

One other way that requires almost no initial setup to be able to recover from such user errors is undoing the bad sql from Oracle redo log files using LogMiner.

Using LogMiner to analyze transactions between two SCN's (STARTSCN and ENDSCN):

If you happen to know the system change numbers where the bad sql was executed then you can target those SCN's in the redo log files and generate UNDO sql statements.

$ sqlplus / as sysdba

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     650778

SQL> conn scott/tiger
Connected.
SQL> select dname from dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL> update dept set dname = 'X';

4 rows updated.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     650800

This is a disastrous update which changed and committed data incorrectly and it happened somewhere in between SCN 650778 and 650800.

begin
  dbms_logmnr.start_logmnr (
         startscn => '650778',
         endscn   => '650800',
         options  => dbms_logmnr.dict_from_online_catalog +
                     dbms_logmnr.continuous_mine +
                     dbms_logmnr.no_sql_delimiter +
                     dbms_logmnr.print_pretty_sql
                                );
end;
/

PL/SQL procedure successfully completed.

column sql_undo format a35
column sql_redo format a35
set lines 10000
set pages 200

select scn , sql_redo , sql_undo from v$logmnr_contents
where username = 'SCOTT'
and   seg_name = 'DEPT';

       SCN SQL_REDO                            SQL_UNDO
---------- ----------------------------------- -----------------------------------
    650794 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
             set                                 set
               "DNAME" = 'X'                       "DNAME" = 'ACCOUNTING'
             where                               where
               "DNAME" = 'ACCOUNTING' and          "DNAME" = 'X' and
               ROWID = 'AAAMfKAAEAAAAAQAAA'        ROWID = 'AAAMfKAAEAAAAAQAAA'

    650794 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
             set                                 set
               "DNAME" = 'X'                       "DNAME" = 'RESEARCH'
             where                               where
               "DNAME" = 'RESEARCH' and            "DNAME" = 'X' and
               ROWID = 'AAAMfKAAEAAAAAQAAB'        ROWID = 'AAAMfKAAEAAAAAQAAB'

    650794 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
             set                                 set
               "DNAME" = 'X'                       "DNAME" = 'SALES'
             where                               where
               "DNAME" = 'SALES' and               "DNAME" = 'X' and
               ROWID = 'AAAMfKAAEAAAAAQAAC'        ROWID = 'AAAMfKAAEAAAAAQAAC'

    650794 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
             set                                 set
               "DNAME" = 'X'                       "DNAME" = 'OPERATIONS'
             where                               where
               "DNAME" = 'OPERATIONS' and          "DNAME" = 'X' and
               ROWID = 'AAAMfKAAEAAAAAQAAD'        ROWID = 'AAAMfKAAEAAAAAQAAD'


SQL> 
SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

dbms_logmnr.start_logmnr with startscn, endscn and option => dbms_logmnr.continuous_mine,

finds a list of online redo log files or archived redo log files having the transactions happened in the range of provided SCN's and add those files to the LogMiner session.

dbms_logmnr.dict_from_online_catalog

uses the online oracle data dictionary to translate the object ids in the log file to object names.

dbms_logmnr.no_sql_delimiter

makes sure there is no sql delimiter like ";" or "/" in the end of the undo or redo sql generated by the LogMiner. If you have plans to run them via PL/SQL "execute immediate", these delimiters will cause problem.

dbms_logmnr.print_pretty_sql

is used to format the sql redo or undo so they look pretty.

Using LogMiner to analyze transactions between two timestamps (STARTTIME and ENDTIME):

If you have no idea about the system change number when the data loss occurred, then you can provide a date and time range where you suspect the incorrect change happened.

$ sqlplus / as sysdba

SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-JUN-2010 13:07:53

SQL> conn scott/tiger
Connected.
SQL> select dname from dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL> update dept set dname = 'XYZ';

4 rows updated.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";

Session altered.

/*
 Setting nls_date_format omits the need of applying to_date() on the date value that you are
 going to provide to dbms_logmnr.start_logmnr().
*/ 

SQL> select sysdate from dual;

SYSDATE
--------------------
10-JUN-2010 13:09:06

begin
    dbms_logmnr.start_logmnr (
          starttime => '10-JUN-2010 13:07:53',
          endtime   => '10-JUN-2010 13:09:06',
          options   => dbms_logmnr.dict_from_online_catalog +
                       dbms_logmnr.continuous_mine +
                       dbms_logmnr.no_sql_delimiter +
                       dbms_logmnr.print_pretty_sql
                             );
end;
/

PL/SQL procedure successfully completed.

column sql_undo format a35
column sql_redo format a35
set lines 10000
set pages 200

select timestamp , sql_redo , sql_undo
from   v$logmnr_contents
where  username = 'SCOTT'
and    seg_name = 'DEPT';

TIMESTAMP            SQL_REDO                            SQL_UNDO
-------------------- ----------------------------------- -----------------------------------
10-JUN-2010 13:08:29 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                       set                                 set
                         "DNAME" = 'XYZ'                     "DNAME" = 'ACCOUNTING'
                       where                               where
                         "DNAME" = 'ACCOUNTING' and          "DNAME" = 'XYZ' and
                         ROWID = 'AAAMfKAAEAAAAAQAAA'        ROWID = 'AAAMfKAAEAAAAAQAAA'

10-JUN-2010 13:08:29 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                       set                                 set
                         "DNAME" = 'XYZ'                     "DNAME" = 'RESEARCH'
                       where                               where
                         "DNAME" = 'RESEARCH' and            "DNAME" = 'XYZ' and
                         ROWID = 'AAAMfKAAEAAAAAQAAB'        ROWID = 'AAAMfKAAEAAAAAQAAB'

10-JUN-2010 13:08:29 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                       set                                 set
                         "DNAME" = 'XYZ'                     "DNAME" = 'SALES'
                       where                               where
                         "DNAME" = 'SALES' and               "DNAME" = 'XYZ' and
                         ROWID = 'AAAMfKAAEAAAAAQAAC'        ROWID = 'AAAMfKAAEAAAAAQAAC'

10-JUN-2010 13:08:29 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                       set                                 set
                         "DNAME" = 'XYZ'                     "DNAME" = 'OPERATIONS'
                       where                               where
                         "DNAME" = 'OPERATIONS' and          "DNAME" = 'XYZ' and
                         ROWID = 'AAAMfKAAEAAAAAQAAD'        ROWID = 'AAAMfKAAEAAAAAQAAD'

dbms_logmnr.start_logmnr with starttime, endtime and option => dbms_logmnr.continuous_mine,

builds a list of online or archived redo log files which contain transactions happened in the range of provided time range and add them to the LogMiner session.

Apply SQL_UNDO generated by LogMiner to recover from user error data loss:

Once you are able to identify the bad sql in the redo log files, simply write a PL/SQL program to execute the UNDO_SQL generated by the LogMiner as below and you should be able to recover the data back.

SQL> select dname from scott.dept;

DNAME
--------------
XYZ
XYZ
XYZ
XYZ

set serveroutput on
declare
  CURSOR c1 IS
  select sql_undo from v$logmnr_contents
  where username = 'SCOTT'
  and   seg_name = 'DEPT';
begin
  for rec in c1 loop
    execute immediate rec.sql_undo;
    dbms_output.put_line(sql%rowcount||' row(s) updated.');
  end loop;
end;
/
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select dname from scott.dept;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

See also:
Using LogMiner to Analyze Redo Log Files
How to read or analyze redo log files using LogMiner
Oracle 11g Using LogMiner to analyze redo log files