Logminer Help Straws _ Recover deleted data

Logminer Log mining techniques can sometimes be likened to life-saving straws, such as accidental deletion of data (such as delete execution improperly), which can be interpolated back by Logminer the mining log file

Using Logminer preview:

The principle of O-Logminer is to find the log files (redo file), corresponding to the undo. If you add 1W rows of data, in the Redo Insert form, the corresponding undo is Deleteo Logminer all operations to execute in a single session O Logminer is to dig online logs or archive logs, so it is best to know the specific (precise and better) time of misoperation, so that the log can be dug as little as possible, the recovery time will be shorter o logminer the data after mining will be saved in v$logmnr_contents. But one thing to be aware of, Its data is read every time, so the number of log files involved, the query will be very slow. Therefore, it is recommended to save the view content with cats, so that the log files of the source library can be mined locally in the source library, or it can be mined on other machines, but there are versions and system requirements: The target operating system should be in the same byte encoding order (Endian_format); The database version is greater than or equal to the source database version, and the same character set o all logs that are scheduled to be included in the mining need to be from the same database and based on the same resetlogs Scno Since Logminer can analyze the log, it can be used to count which tables have been added to the most, can be more in-depth understanding of their applications and data o Logminer can be mined based on TIME/SCN, accurate O users who use mining technology need to be authorized role or permissions: SELECT Any TRANSACTION, Execute_catalog_roleo if the table is mistakenly truncate or mistakenly drop,logminer recovery is not possible, you can use the Odu/dul/prm/aul tool to recover

Simple experimental process

The following simulated delete mistakenly deletes data from the entire table if the data is retrieved by Logminer digging the log

1. Enable the supplemental Logging of the database

<span style= "FONT-SIZE:12PX;" >SQL> ALTER DATABASE ADD supplemental LOG data;</span>

Logminer requires a minimum level of supplemental Logging

2. Prepare test tables and test data

<span style= "FONT-SIZE:14PX;" >SQL> CREATE TABLE T1 (a number,b char (2)); Sql> CREATE TABLE T2 (c number,d char (2)); sql> INSERT INTO T1 values (1, ' R1 '); sql> INSERT INTO T1 values (2, ' R2 '); sql> INSERT INTO T1 values (3, ' R3 '); sql> INSERT INTO T2 values (1, ' T2 '); sql> commit;        sql> SELECT * from t1;           A B------------         1 r1          2 r2         3 r3sql> archive Log list;d Atabase log mode        Archive modeautomatic archival         enabledarchive destination       /home/oracle/archoldest Online log sequence     57Next log sequence to archive   59Current log sequence         59&nbsP <<<<<<====SQL> alter system archive log current;</span>

3. Add a log to the mining list and start digging

EXECUTE DBMS_LOGMNR. Add_logfile (LogFileName = '/home/oracle/arch/1_59_847657195.dbf ', OPTIONS = DBMS_LOGMNR. NEW); Sql> EXECUTE DBMS_LOGMNR. START_LOGMNR (OPTIONS =>DBMS_LOGMNR. Dict_from_online_catalog); PL/SQL procedure successfully completed

Each time you add or remove a log to the list, you need to re-execute START_LOGMNR

Query View V$logmnr_contents

sql> Select scn,       timestamp,        (Xidusn | | '.' || Xidslt | | '.' || XIDSQN) as xid,       info,       seg_owner,        seg_name,       operation,        sql_redo,       sql_undo  from V$logmnr_contents where seg_ Name in (' T1 ', ' T2 ')/

       SCN TIMESTAMP XID Seg_owner seg_name Operation Sql_redo Sql_undo--------------------------------------------------------------------------------------------- ------------------------------------- -------------------------------------------------------------------------- ------1212893 2015/1/10 1 4.1.550 SYS T1 DDL CREATE table T1 (a number,b char (2                     ));                     1212907 2015/1/10 1 10.12.537 SYS T2 DDL CREATE TABLE T2 (c number,d char (2)); 1212913 2015/1/10 1 5.40.556 sys T1 insert INSERT INTO "sys". "     T1 "(" A "," B ") VALUES (' 1 ', ' R1 '); Delete from "SYS". "         T1 "where" A "= ' 1 ' and" B "= ' r1 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T1 Insert insert INTO "SYS". "     T1 "(" A "," B ") VALUES (' 2 ', ' R2 '); Delete FroM "SYS". "         T1 "where" A "= ' 2 ' and" B "= ' r2 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T1 Insert insert INTO "SYS". "     T1 "(" A "," B ") VALUES (' 3 ', ' R3 '); Delete from "SYS". "         T1 "where" A "= ' 3 ' and" B "= ' r3 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T2 Insert insert INTO "SYS". "     T2 "(" C "," D ") VALUES (' 1 ', ' T2 '); Delete from "SYS". " T2 "where" C "= ' 1 ' and" D "= ' t2 ' and ROWID = ' Aaanbtaabaaap

Delete Test table data

Sql> DELETE from T1; 3 rows deleted sql> COMMIT;          Commit Complete sql> SELECT * from T1; A B------------</span></p><p>SQL> ALTER SYSTEM ARCHIVE LOG current; System altered</span></p>

New log list to start digging again

Sql> EXECUTE DBMS_LOGMNR. Add_logfile (LogFileName = '/home/oracle/arch/1_60_847657195.dbf ', OPTIONS = DBMS_LOGMNR. AddFile); PL/SQL procedure successfully completed</span><p></p><span style= "FONT-SIZE:14PX;" ></span><p>SQL> EXECUTE DBMS_LOGMNR. START_LOGMNR (OPTIONS =>DBMS_LOGMNR. Dict_from_online_catalog); PL/SQL procedure successfully completed</p>

Query v$logmnr_contents

Sql> Select SCN, timestamp, (Xidusn | | '.' || Xidslt | | '.' || XIDSQN) as XID, info, Seg_owner, Seg_name, operation, Sql_redo, Sql_undo from V$LOGMN R_contents where Seg_name in (' T1 ', ' T2 ')/

<span style= "FONT-SIZE:12PX;"                                                                         > SCN TIMESTAMP XID seg_owner seg_name Operation Sql_redo Sql_undo--------------------------------------------------- ---------------- ----------- ------------- ----------------------------------------------------------------------- -----------------------------------------------------------------------------------------1212893 2015/1/10 1                                               4.1.550 SYS T1 DDL CREATE table T1 (a number,b char (2));           1212907 2015/1/10 1 10.12.537 SYS T2 DDL                                               CREATE TABLE T2 (c number,d char (2)); 1212913 2015/1/10 1 5.40.556 sys T1 insert INSERT INTO "sys". "                 T1 "(" A "," B ") VALUES (' 1 ', ' R1 ');              Delete from "SYS". "                              T1 "where" A "= ' 1 ' and" B "= ' r1 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T1 insert INSERT INTO "SYS". "                               T1 "(" A "," B ") VALUES (' 2 ', ' R2 '); Delete from "SYS". "                              T1 "where" A "= ' 2 ' and" B "= ' r2 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T1 insert INSERT INTO "SYS". "                               T1 "(" A "," B ") VALUES (' 3 ', ' R3 '); Delete from "SYS". "                              T1 "where" A "= ' 3 ' and" B "= ' r3 ' and ROWID = ' Aaanbsaabaaap 1212913 2015/1/10 1 5.40.556 SYS T2 insert INSERT INTO "SYS". "                               T2 "(" C "," D ") VALUES (' 1 ', ' T2 '); Delete from "SYS". "                              T2 "where" C "= ' 1 ' and" D "= ' t2 ' and ROWID = ' Aaanbtaabaaap 1213003 2015/1/10 1 5.47.556 SYS T1 Delete delete from "SYS". " T1 "where" A "=' 1 ' and ' B ' = ' r1 ' and ROWID = ' aaanbsaabaaap insert INTO ' SYS '.   T1 "(" A "," B ") VALUES (' 1 ', ' R1 '); 1213003 2015/1/10 1 5.47.556 sys T1 delete delete from "sys". " T1 "where" A "= ' 2 ' and" B "= ' r2 ' and ROWID = ' aaanbsaabaaap insert INTO ' SYS '."   T1 "(" A "," B ") VALUES (' 2 ', ' R2 '); 1213003 2015/1/10 1 5.47.556 sys T1 delete delete from "sys". " T1 "where" A "= ' 3 ' and" B "= ' r3 ' and ROWID = ' aaanbsaabaaap insert INTO ' SYS '." T1 "(" A "," B ") VALUES (' 3 ', ' R3 '); 9 Rows Selected</span>

Execute the SQL for the Sql_undo field

sql> insert INTO "SYS". " T1 "(" A "," B ") VALUES (' 1 ', ' R1 '); 1 row insertedsql> insert into "SYS". " T1 "(" A "," B ") VALUES (' 2 ', ' R2 '); 1 row insertedsql> insert into "SYS". " T1 "(" A "," B ") VALUES (' 3 ', ' R3 '); 1 row inserted sql> commit; Commit completesql> sql> SELECT * from T1;          A B------------         1 R1         2 R2         3 R3

At this point, the data that was mistakenly deleted is returned.

Close Logminer

Sql> EXECUTE DBMS_LOGMNR.END_LOGMNR; PL/SQL procedure successfully completed

Logminer Help Straws _ Recover deleted data