Log Miner used for recover data in Oracle

Log Miner is the utility for analyze transactions between two timestamps (STARTTIME and ENDTIME):

Example of Log Miner for recover any transaction in Oracle

1. Set the NLS_DATE_FORMAT in hours and minutes for monitor:

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

2. Before start the example of log Miner note down the time

SQL> select sysdate from dual;
SYSDATE
--------------------
10-JUN-2010 13:07:53

3. Connect with the scott user

SQL> conn scott
Password: *******
Connected.

4. Note down the value of DEPT table present in SCOTT User:

SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

5. Update the data of DEPT table

SQL> update dept set dname = 'XYZ';
4 rows updated.
SQL> commit;
Commit complete.

5. Note down the time after update operation done. Now all rows all update and we will going to use the log miner utility for recover the updated rows

SQL> select sysdate from dual;
SYSDATE
--------------------
10-JUN-2010 13:09:06

Note:
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().

6. From above steps we have the start time and end time of transaction recover, we are going to start the log miner process with following package:

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.

Note: If we have SCN Number of Database in between update happen we can use it in Log Miner process as follows:
LogMiner to analyze transactions between two SCN’s (STARTSCN and ENDSCN)

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.

7. It insert the data into logmnr contents table, we will fetch update query from Logmnr contents table:

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'

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

8. Check the Scott.dept table

SQL> select dname from scott.dept;
DNAME
--------------
XYZ
XYZ
XYZ
XYZ

9. Rollback the changes done on Scott.dept table with help of following cursor:

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.

10. Check the Dept table

SQL> select dname from scott.dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

11. End the logmnr process with following command:

SQL> eec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s