Search This Blog

Saturday, November 13, 2010

Using logminer to undo incorrectly committed changes in Oracle 10g.

In order to work with logminer, the Oracle database should be in archive log mode.
Skip the steps mentioned below if your database is already in archive log mode.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  285212672 bytes                                    
Fixed Size                  1218992 bytes                                    
Variable Size             100664912 bytes                                    
Database Buffers          180355072 bytes                                    
Redo Buffers                2973696 bytes                                    
Database mounted.
Database opened.

SQL> alter system set log_archive_start=true scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  285212672 bytes                                    
Fixed Size                  1218992 bytes                                    
Variable Size             100664912 bytes                                    
Database Buffers          180355072 bytes                                    
Redo Buffers                2973696 bytes                                    
Database mounted.
SQL> archive log start
Statement processed.

SQL> alter database archivelog;

Database altered.

# The archive log files just contain enough data to do database
# recovery in case of an instance crash. In order for logminer
# to work you have to enable supplemental log data prior to or
# before the generation of archived log files.
SQL> select supplemental_log_data_min from v$database;

SUPPLEME                                                                     
--------                                                                     
NO                                                                          

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME                                                                     
--------                                                                     
YES                                                                          

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

SQL> alter user scott identified by tiger account unlock;

User altered.

# Unlocking scott user.
SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";

Session altered.

# Set the session date format with nls_date_format because
# then you don't have to use character conversion or date
# conversion later.
SQL> select sysdate from dual;

SYSDATE                                                                      
--------------------                                                         
16-OCT-2010 11:28:16                                                         

SQL> show user;
USER is "SYS"
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"

SQL> select dname from dept;

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

SQL> update dept set dname = 'XYZ';

4 rows updated.

# Records update with wrong entries.
SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

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

Session altered.

SQL> select sysdate from dual;

SYSDATE                                                                      
--------------------                                                         
16-OCT-2010 11:30:52                                                         

SQL> show user
USER is "SYS"


SQL> alter system switch logfile;

System altered.

# Generate archived log files through the above command.
SQL>begin
 dbms_logmnr.start_logmnr (
 starttime => '16-OCT-2010 11:28:16',
 endtime   => '16-OCT-2010 11:30:52',
 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.

# 1)With the dbms_logmnr.start_logmnr we will start the logminer session.
#
# 2)The starttime and endtime gives a time window. The logminer will collect
# all the data within this time window.
#
# 3)The dbms_logmnr.dict_from_online_catalog means that the logminer will
# collect all the information directly from the Data Dictionary. This is because
# logminer needs the Data Dictionary information so that it can present the
#Archived redo log information in a human readable format.
#
# 4)The Continuous_mine option means that it will automatically hunt for the required
# archived redo log files, you don't have to specify them manully.
#
# 5)The no_sql_delimiter and print_pretty_sql options make the output a little less
# cluttered and more readable.




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

SQL> select timestamp, sql_redo, sql_undo
2  from v$logmnr_contents
3  where username = 'SCOTT'
4  and seg_name = 'DEPT';

TIMESTAMP            SQL_REDO                            SQL_UNDO
-------------------- ----------------------------------- ----------------------------
16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'ACCOUNTING'
                    where                               where
                      "DNAME" = 'ACCOUNTING' and          "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAA'        ROWID = 'AAAMfKAAEAAAAAQAAA'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'RESEARCH'
                    where                               where
                      "DNAME" = 'RESEARCH' and            "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAB'        ROWID = 'AAAMfKAAEAAAAAQAAB'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'SALES'
                    where                               where
                      "DNAME" = 'SALES' and               "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAC'        ROWID = 'AAAMfKAAEAAAAAQAAC'

16-OCT-2010 11:29:54 update "SCOTT"."DEPT"               update "SCOTT"."DEPT"
                    set                                 set
                      "DNAME" = 'XYZ'                     "DNAME" = 'OPERATIONS'
                    where                               where
                      "DNAME" = 'OPERATIONS' and          "DNAME" = 'XYZ' and
                  ROWID = 'AAAMfKAAEAAAAAQAAD'        ROWID = 'AAAMfKAAEAAAAAQAAD'

# What we have done above is queried the V$logmnr_contents and retrieved all the changes
# that have been made by the user scott in the dept table.
# The sql_redo shows the changes that has been made and the sql_undo shows the opposite
# entries of the changes made. The statements from the sql_undo can be used to undo
# the changes that had been made accidentally.
SQL> select dname from scott.dept;

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

SQL> set serveroutput on
SQL> declare
2  cursor c1 is
3  select sql_undo from v$logmnr_contents
4  where username = 'SCOTT'
5  and seg_name = 'DEPT';
6  begin
7  for rec in c1 loop
8  execute immediate rec.sql_undo;
9  dbms_output.put_line(sql%rowcount||' row(s) updated.');
10  end loop;
11  end;
12  /
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
PL/SQL procedure successfully completed.

# With the above PL/SQL procedure i have recovered all the accidental changes.
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.

# After doing everything we have to close the logminer session
# by executing the above statement.

0 comments:

Post a Comment