本文共 16612 字，大约阅读时间需要 55 分钟。

dbms_logmnr恢复误dml的数据（有前提 alter database add supplemental log data；）

select supplemental_log_data_min from v$database为NO的话无法记录dml的redo和undo sql

13:30:28 SQL> conn /as sysdba

Connected. 13:30:34 SQL> create user jyc identified by jyc default tablespace users;

User created.

13:30:53 SQL> grant dba to jyc;

Grant succeeded.

13:31:13 SQL> conn jyc/jyc

Connected. 13:31:16 SQL> create table t(id int);

Table created.

13:31:21 SQL> insert into t values(1);

1 row created.

13:31:26 SQL> insert into t values(2);

1 row created.

13:31:32 SQL> commit;

Commit complete.

13:31:34 SQL> select supplemental_log_data_min from v$database;

SUPPLEME

-------- NO

13:31:44 SQL> select group#,sequence#,archived,status,first_change# from v$log;

    GROUP#  SEQUENCE# ARC STATUS           FIRST_CHANGE#

---------- ---------- --- ---------------- -------------          1         22 YES INACTIVE               1772681          2         23 NO  CURRENT                1781710          3         21 YES INACTIVE               1763882

13:31:59 SQL> select * from v$Logfile;

rows will be truncated

    GROUP# STATUS  TYPE    MEMBER ---------- ------- ------- -------------------------------------------------------------------------------------------------------------------------------------          3         ONLINE  /home/db/oracle/oradata/orcl/redo03.log          2         ONLINE  /home/db/oracle/oradata/orcl/redo02.log          1         ONLINE  /home/db/oracle/oradata/orcl/redo01.log

13:32:14 SQL> exec dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo02.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

13:32:41 SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

13:32:57 SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T';

rows will be truncated

SQL_REDO ---------------------------------------------------------------------------------------------------------------------------------------------------------------- create table t as select b.obj# obj_old,b.name,a.obj# obj_new,a.owner# from obj$ a,obj b where a.name=b.name and a.owner#=93; drop table t; create table t(id int); insert into "JYC"."T"("ID") values ('1'); insert into "JYC"."T"("ID") values ('2');

13:33:12 SQL> show user;

USER is "JYC" 13:34:48 SQL> create table log1("SCN" NUMBER(10) NOT NULL, "CSCN" NUMBER(10), "TIMESTAMP" DATE NOT NULL, "USERNAME" VARCHAR2(40 byte),"SQL_REDO" VARCHAR2(4000 by13:34:48 SQL> create table log1("SCN" NUMBER(10) NOT NULL, "CSCN" NUMBER(10), "TIMESTAMP" DATE NOT NULL, "USERNAME" VARCHAR2(40 byte),"SQL_REDO" VARCHAR2(4000 by13:34:48 SQL> create table log1("SCN" NUMBER(10) NOT NULL, "CSCN" NUMBER(10), "TIMESTAMP" DATE NOT NULL, "USERNAME" VARCHAR2(40 byte),"SQL_REDO" VARCHAR2(4000 by13:34:48 SQL>  13:34:48 SQL> create table log1("SCN" NUMBER(10) NOT NULL, "CSCN" NUMBER(10), "TIMESTAMP" DATE NOT NULL, "USERNAME" VARCHAR2(40 byte),"SQL_REDO" VARCHAR2(4000 byte),"SQL_UNDO" VARCHAR2(4000 byte));

Table created.

13:35:22 SQL> select object_id,data_object_id from dba_objects where object_name = 'T';

 OBJECT_ID DATA_OBJECT_ID

---------- --------------      88166          88166

13:35:44 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents;

14200 rows created.

13:36:36 SQL> rollback;

Rollback complete.

13:36:51 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents where 13:36:51 SQL> 

13:36:51 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents where data_objd#=88166;

2 rows created.

13:37:17 SQL> commit;

Commit complete.

13:37:56 SQL> 

13:37:56 SQL>  13:37:56 SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

13:38:11 SQL> select sql_redo from log1;

SQL_REDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into "JYC"."T"("ID") values ('1'); insert into "JYC"."T"("ID") values ('2');

13:39:13 SQL> select sql_undo from log1;

SQL_UNDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAA'; delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAB';

13:39:20 SQL> select sql_redo from log1 order by TIMESTAMP asc;

SQL_REDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into "JYC"."T"("ID") values ('1'); insert into "JYC"."T"("ID") values ('2');

13:40:26 SQL> select sql_undo from log1 order by TIMESTAMP asc;

SQL_UNDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAA'; delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAB';

13:40:39 SQL> select * from t;

        ID

----------          1          2

13:40:52 SQL> delete from t;

2 rows deleted.

13:41:08 SQL> commit;

Commit complete.

13:41:10 SQL> truncate table log1;

Table truncated.

13:41:26 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------          1          1         22   52428800        512          1 YES INACTIVE               1772681 2020-12-25 10:23:31      1781710 2020-12-25 12:00:29          2          1         23   52428800        512          1 NO  CURRENT                1781710 2020-12-25 12:00:29   2.8147E+14          3          1         21   52428800        512          1 YES INACTIVE               1763882 2020-12-25 09:32:51      1772681 2020-12-25 10:23:31

13:41:32 SQL> exec dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo02.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

13:41:41 SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

13:41:48 SQL> select object_id,data_object_id from dba_objects where object_name = 'T';

 OBJECT_ID DATA_OBJECT_ID

---------- --------------      88166          88166

13:41:53 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents

13:44:19   2    where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:31:34','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<to_date('2020-12-25 13:40:52','yyyy-mm-dd hh24:mi:ss');

0 rows created.

13:44:25 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents

13:45:05   2    where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:31:34','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<=to_date('2020-12-25 13:41:10','yyyy-mm-dd hh24:mi:ss');

0 rows created.

13:45:06 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents;

15349 rows created.

13:45:31 SQL> rollback;

Rollback complete.

13:45:36 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents where data_objd#=88166;

2 rows created.

13:45:50 SQL> select sql_redo from log1;

SQL_REDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into "JYC"."T"("ID") values ('1'); insert into "JYC"."T"("ID") values ('2');

13:46:17 SQL> select sql_undo from log1;

SQL_UNDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAA'; delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAB';

13:46:27 SQL> select TIMESTAMP,sql_redo from log1;

TIMESTAMP           SQL_REDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:31:26 insert into "JYC"."T"("ID") values ('1'); 2020-12-25 13:31:32 insert into "JYC"."T"("ID") values ('2');

13:46:43 SQL> select TIMESTAMP,sql_undo from log1;

TIMESTAMP           SQL_UNDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:31:26 delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAA'; 2020-12-25 13:31:32 delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAB';

13:46:54 SQL> alter database add supplemental log data;

Database altered.

13:47:31 SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------YES

14:02:24 SQL>  SELECT force_logging,supplemental_log_data_min,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE; 

FOR SUPPLEME SUP SUP

--- -------- --- --- NO  YES      NO  NO

13:47:40 SQL> select * from t;

no rows selected

13:47:52 SQL> show user;

USER is "JYC" 13:47:55 SQL> insert into "JYC"."T"("ID") values ('1');

1 row created.

13:47:59 SQL> insert into "JYC"."T"("ID") values ('2');

1 row created.

13:48:04 SQL> insert into "JYC"."T"("ID") values ('3');

1 row created.

13:48:11 SQL> commit;

Commit complete.

13:48:13 SQL> 

13:48:21 SQL>  13:48:21 SQL>  13:48:21 SQL>  13:48:32 SQL> delete from jyc.t;

3 rows deleted.

13:48:37 SQL> commit;

Commit complete.

13:48:39 SQL> select * from jyc.t;

no rows selected

13:48:43 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------          1          1         22   52428800        512          1 YES INACTIVE               1772681 2020-12-25 10:23:31      1781710 2020-12-25 12:00:29          2          1         23   52428800        512          1 YES ACTIVE                 1781710 2020-12-25 12:00:29      1788786 2020-12-25 13:45:33          3          1         24   52428800        512          1 NO  CURRENT                1788786 2020-12-25 13:45:33   2.8147E+14

13:48:49 SQL> select * from log1;

rows will be truncated

       SCN       CSCN TIMESTAMP           USERNAME                                 SQL_REDO ---------- ---------- ------------------- ---------------------------------------- -----------------------------------------------------------------------------    1787920    1787924 2020-12-25 13:31:26 UNKNOWN                                  insert into "JYC"."T"("ID") values ('1');    1787922    1787924 2020-12-25 13:31:32 UNKNOWN                                  insert into "JYC"."T"("ID") values ('2');

13:49:13 SQL> truncate table log1;

Table truncated.

13:49:19 SQL> exec dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo03.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

13:49:36 SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

13:49:42 SQL> select object_id,data_object_id from dba_objects where object_name = 'T';

 OBJECT_ID DATA_OBJECT_ID

---------- --------------      88166          88166

13:49:46 SQL> select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents

13:50:38   2  where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:47:55','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<=to_date('2020-12-25 13:48:32','yyyy-mm-dd hh24:mi:ss'); rows will be truncated

       SCN       CSCN TIMESTAMP           USERNAME                       SQL_REDO ---------- ---------- ------------------- ------------------------------ ---------------------------------------------------------------------------------------    1788853    1788859 2020-12-25 13:48:00 JYC                            insert into "JYC"."T"("ID") values ('1');    1788855    1788859 2020-12-25 13:48:06 JYC                            insert into "JYC"."T"("ID") values ('2');    1788857    1788859 2020-12-25 13:48:12 JYC                            insert into "JYC"."T"("ID") values ('3');

13:50:39 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) 

13:50:58   2  select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents 13:50:58   3  where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:47:55','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<=to_date('2020-12-25 13:48:32','yyyy-mm-dd hh24:mi:ss');

3 rows created.

13:50:59 SQL> commit;

Commit complete.

13:51:02 SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

13:51:16 SQL> select TIMESTAMP,sql_redo from log1;

TIMESTAMP           SQL_REDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:48:00 insert into "JYC"."T"("ID") values ('1'); 2020-12-25 13:48:06 insert into "JYC"."T"("ID") values ('2'); 2020-12-25 13:48:12 insert into "JYC"."T"("ID") values ('3');

13:51:41 SQL> select TIMESTAMP,sql_undo from log1;

TIMESTAMP           SQL_UNDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:48:00 delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAC'; 2020-12-25 13:48:06 delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAD'; 2020-12-25 13:48:12 delete from "JYC"."T" where "ID" = '3' and ROWID = 'AAAVhmAAJAAAAAQAAE';

13:51:54 SQL> exec dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo03.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

13:52:19 SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

13:52:22 SQL> select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents

13:52:47   2  where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:48:32','yyyy-mm-dd hh24:mi:ss'); rows will be truncated

       SCN       CSCN TIMESTAMP           USERNAME                       SQL_REDO ---------- ---------- ------------------- ------------------------------ ---------------------------------------------------------------------------------------    1788867    1788869 2020-12-25 13:48:39 JYC                            delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAC';    1788867    1788869 2020-12-25 13:48:39 JYC                            delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAD';    1788867    1788869 2020-12-25 13:48:39 JYC                            delete from "JYC"."T" where "ID" = '3' and ROWID = 'AAAVhmAAJAAAAAQAAE';

13:52:48 SQL> truncae table log1;

SP2-0734: unknown command beginning "truncae ta..." - rest of line ignored. 13:53:09 SQL> truncate table log1;

Table truncated.

13:53:15 SQL> insert into log1(scn,cscn,TIMESTAMP,username,sql_redo,sql_undo ) 

13:53:47   2  select scn,cscn,TIMESTAMP,username,sql_redo,sql_undo from v$logmnr_contents 13:53:47   3  where data_objd#=88166 and TIMESTAMP>=to_date('2020-12-25 13:48:32','yyyy-mm-dd hh24:mi:ss');

3 rows created.

13:53:47 SQL> commit;

Commit complete.

13:53:49 SQL>  exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

13:53:53 SQL> select TIMESTAMP,sql_redo from log1;

TIMESTAMP           SQL_REDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:48:39 delete from "JYC"."T" where "ID" = '1' and ROWID = 'AAAVhmAAJAAAAAQAAC'; 2020-12-25 13:48:39 delete from "JYC"."T" where "ID" = '2' and ROWID = 'AAAVhmAAJAAAAAQAAD'; 2020-12-25 13:48:39 delete from "JYC"."T" where "ID" = '3' and ROWID = 'AAAVhmAAJAAAAAQAAE';

13:53:58 SQL> select TIMESTAMP,sql_undo from log1;

TIMESTAMP           SQL_UNDO

------------------- -------------------------------------------------------------------------------------------------------------------------------------------- 2020-12-25 13:48:39 insert into "JYC"."T"("ID") values ('1'); 2020-12-25 13:48:39 insert into "JYC"."T"("ID") values ('2'); 2020-12-25 13:48:39 insert into "JYC"."T"("ID") values ('3');

13:54:02 SQL> select * from t;

no rows selected

13:54:07 SQL> select sql_undo from log1;

SQL_UNDO

---------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into "JYC"."T"("ID") values ('1'); insert into "JYC"."T"("ID") values ('2'); insert into "JYC"."T"("ID") values ('3');

13:54:20 SQL> insert into "JYC"."T"("ID") values ('1');

1 row created.

13:54:29 SQL> insert into "JYC"."T"("ID") values ('2');

1 row created.

13:54:29 SQL> insert into "JYC"."T"("ID") values ('3');

1 row created.

13:54:30 SQL> commit;

Commit complete.

13:54:32 SQL> select * from t;

        ID

----------          1          2          3

相关参考:

转载地址：http://icsof.baihongyu.com/