这篇文章主要讲解了“怎么利用oracle的日志挖掘实现回滚”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么利用oracle的日志挖掘实现回滚”吧！
archery对mysql的支持非常完美，虽然也支持oracle，但是对oracle只支持查询和执行，不支持备份和审核，还是有很大的遗憾。
现在，我们急需要一个oracle的备份功能，于是我想到了oracle自带的功能——日志挖掘，它可以实现sql语句的备份功能。
下面是我对日志挖掘的演示。
0、启动最小日志，这一步是为了日志更加详细，完整（必须）
alter database add supplemental log data;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 e4、 查看当前会话的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 37555、查看当前数据库时间，供后面的starttime用
SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-03-15 19:02:536、执行一些sql语句，模拟上线sql
SQL> delete from t where id < 3;
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 g7、再查看一下现在数据库时间，供后面的endtime用
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-03-15 19:08:168、通过时间范围，用在线字典方式将redo日志自动加载到v$logmnr_contents这个视图中
注意：starttime和endtime就是上面获得的两个当前数据库时间。
SQL>
begin
dbms_logmnr.start_logmnr(
starttime=>to_date('2020-03-15 19:02:53','yyyy-mm-dd hh34:mi:ss'),
endtime=>to_date('2020-03-15 19:08:16','yyyy/mm/dd hh34: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.感谢各位的阅读，以上就是“怎么利用oracle的日志挖掘实现回滚”的内容了，经过本文的学习后，相信大家对怎么利用oracle的日志挖掘实现回滚这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
