利用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 < 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 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.

參考連結：

https://www.cnblogs.com/jiaowenhao/p/6994230.html