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.
# 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.
# Unlocking scott user.
# Set the session date format with nls_date_format because
# then you don't have to use character conversion or date
# conversion later.
# Records update with wrong entries.
# Generate archived log files through the above command.
# 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.
# 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.
# With the above PL/SQL procedure i have recovered all the accidental changes.
# After doing everything we have to close the logminer session
# by executing the above statement.
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