oracle可以根据日志回退吗,利用oracle的日志挖掘实现回滚

archery对mysql的支持非常完美，虽然也支持oracle，但是对oracle只支持查询和执行，不支持备份和审核，还是有很大的遗憾。

现在，我们急需要一个oracle的备份功能，于是我想到了oracle自带的功能——日志挖掘，它可以实现sql语句的备份功能。

下面是我对日志挖掘的演示。

0、启动最小日志，这一步是为了日志更加详细，完整(必须)

select SUPPLEMENTAL_LOG_DATA_MIN min,

SUPPLEMENTAL_LOG_DATA_PK  pk,

SUPPLEMENTAL_LOG_DATA_UI  ui,

SUPPLEMENTAL_LOG_DATA_FK  fk,

SUPPLEMENTAL_LOG_DATA_ALL "all"

from v$database;

alter database add supplemental log data;

alter database add supplemental log data (primary key) columns ;

alter database add supplemental log data (unique) columns ;

参考：https://blog.51cto.com/sugarlovecxq/1693092

1、登录用户

SQL> conn czx/xxx;

Connected.

2、创建一张测试表

SQL> create table t (id number, name varchar2(10));

Table created.

3、插入一些测试数据

SQL> insert into t values (1,'a');

1 row created.

SQL> insert into t values (2,'b');

1 row created.

SQL> insert into t values (3,'c');

1 row created.

SQL> insert into t values (4,'d');

1 row created.

SQL> insert into t values (5,'e');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

4、 查看当前会话的sid和serial#

SQL> select s.sid,s.serial# from v$session s where s.sid = (select sid from v$mystat where rownum = 1 );

SID    SERIAL#

---------- ----------

36         3755

5、查看当前数据库时间，供后面的starttime用

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

-------------------

2020-03-15 19:02:53

6、执行一些sql语句，模拟上线sql

SQL> delete from t where id 

2 rows deleted.

SQL> update t set name = 'dddd' where id=4;

1 row updated.

SQL> insert into t values (6, 'f');

1 row created.

SQL> insert into t values (7, 'g');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID NAME

---------- ----------

3 c

4 dddd

5 e

6 f

7 g

7、再查看一下现在数据库时间，供后面的endtime用

SQL> select sysdate from dual;

SYSDATE

-------------------

2020-03-15 19:08:16

8、通过时间范围，用在线字典方式将redo日志自动加载到v$logmnr_contents这个视图中

注意：starttime和endtime就是上面获得的两个当前数据库时间。

SQL>

begin

dbms_logmnr.start_logmnr(

starttime=>to_date('2020-03-15 19:02:53','yyyy-mm-dd hh24:mi:ss'),

endtime=>to_date('2020-03-15 19:08:16','yyyy/mm/dd hh24:mi:ss'),

options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);

end;

/

PL/SQL procedure successfully completed.

9、 查看v$logmnr_contents视图的sql_redo和sql_undo内容，就会得到上述执行sql的原始语句和对应的回滚语句

sql>

SELECT

sql_redo,

sql_undo,

SESSION#,

serial#,

username,

OS_USERNAME,

MACHINE_NAME,

SESSION_INFO,

operation,

xid

FROM

v$logmnr_contents

WHERE

SESSION# = ( SELECT s.sid FROM v$session s WHERE s.sid = ( SELECT sid FROM v$mystat WHERE ROWNUM = 1 ) )

AND serial# = ( SELECT serial# FROM v$session s WHERE s.sid = ( SELECT sid FROM v$mystat WHERE ROWNUM = 1 ) );

SESSION#    SERIAL# USERNAME                       OPERATION                        XID              SQL_REDO                                                                         SQL_UNDO

---------- ---------- ------------------------------ -------------------------------- ---------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

36       3755 CZX                            START                            0A000900C8060000 set transaction read write;

36       3755 CZX                            DELETE                           0A000900C8060000 delete from "CZX"."T" where "ID" = '1' and "NAME" = 'a' and ROWID = 'AAAVSdAAEAA insert into "CZX"."T"("ID","NAME") values ('1','a');

36       3755 CZX                            DELETE                           0A000900C8060000 delete from "CZX"."T" where "ID" = '2' and "NAME" = 'b' and ROWID = 'AAAVSdAAEAA insert into "CZX"."T"("ID","NAME") values ('2','b');

36       3755 CZX                            UPDATE                           0A000900C8060000 update "CZX"."T" set "NAME" = 'dddd' where "ID" = '4' and "NAME" = 'd' and ROWID update "CZX"."T" set "NAME" = 'd' where "ID" = '4' and "NAME" = 'dddd' and ROWID

36       3755 CZX                            INSERT                           0A000900C8060000 insert into "CZX"."T"("ID","NAME") values ('6','f');                             delete from "CZX"."T" where "ID" = '6' and "NAME" = 'f' and ROWID = 'AAAVSdAAEAA

36       3755 CZX                            INSERT                           0A000900C8060000 insert into "CZX"."T"("ID","NAME") values ('7','g');                             delete from "CZX"."T" where "ID" = '7' and "NAME" = 'g' and ROWID = 'AAAVSdAAEAA

36       3755 CZX                            COMMIT                           0A000900C8060000 commit;

7 rows selected

如果会vue.js和django，就可以把上面这个功能做成web界面形式的。

10、记得最后用完了关闭日志挖掘功能

SQL>

begin

dbms_logmnr.end_logmnr;

end;

/

PL/SQL procedure successfully completed.

参考链接：