JOE_达梦DM_日志挖掘初体验

命令总结：

SQL> SELECT NAME ,FIRST_TIME , NEXT_TIME , FIRST_CHANGE# , NEXT_CHANGE# FROM V$ARCHIVED_LOG order by 2;

SQL> DBMS_LOGMNR.ADD_LOGFILE('/home/dmdba/dmdbms/arch1/ARCHIVE_LOCAL1_20200222155826399_0.log');

SQL> SELECT LOW_SCN,NEXT_SCN, LOW_TIME, HIGH_TIME, LOG_ID, FILENAME FROM V$LOGMNR_LOGS;

SQL>DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128);

或者：

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128 , STARTTIME=>TO_DATE('2020-02-22 15:02:00','YYYY-MM-DD HH24:MI:SS') , ENDTIME=>TO_DATE('2020-02-22 15:03:00','YYYY-MM-DD HH24:MI:SS'));

SQL> select TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,OPERATION_CODE ,ROLL_BACK ,TABLE_NAME ,ROW_ID ,USERNAME ,DATA_OBJ# , DATA_OBJV# , SQL_REDO, REDO_VALUE, UNDO_VALUE from V$LOGMNR_CONTENTS where table_name= 'LJW';

SQL>DBMS_LOGMNR.END_LOGMNR();

 

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

DBMS_LOGMNR.ADD_LOGFILE('/home/dmdba/dmdbms/arch1/ARCHIVE_LOCAL1_20200222155826399_0.log');

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128);

select TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,OPERATION_CODE ,ROLL_BACK ,TABLE_NAME ,ROW_ID ,USERNAME ,DATA_OBJ# , DATA_OBJV# , SQL_REDO,sql_undo from V$LOGMNR_CONTENTS where table_name= 'TEST';

 

DBMS_LOGMNR.END_LOGMNR();

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

 

SQL> sp_set_para_value(1,'RLOG_APPEND_LOGIC',1);

注： RLOG_APPEND_LOGIC 需要设置为 1 或 2 ， 1 代表如果有主键列，记录 UPDATE 和 DELETE 操作时只包含主键列信息，若没有主键列则包含所有列信息； 2 代表不论是否有主键列，记录 UPDATE 和 DELETE 操作时都包含所有列的信息。

 

SQL> select para_name,para_value from v$dm_ini where para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');

 

行号      PARA_NAME         PARA_VALUE

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

1          RLOG_APPEND_LOGIC 1

2          ARCH_INI          1

 

已用时间 : 4.182( 毫秒 ).  执行号 :14.

 

SQL> select * from ljw;

 

行号      ID

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

1          66

 

已用时间 : 0.327( 毫秒 ).  执行号 :18.

SQL> delete from ljw;

影响行数  1

 

已用时间 : 0.794( 毫秒 ).  执行号 :19.

 

SQL> commit;

操作已执行

已用时间 : 9.917( 毫秒 ).  执行号 :20.

 

SQL> SELECT NAME ,FIRST_TIME , NEXT_TIME , FIRST_CHANGE# , NEXT_CHANGE# FROM V$ARCHIVED_LOG order by 2;

 

SQL> DBMS_LOGMNR.ADD_LOGFILE('/home/dmdba/dmdbms/arch1/ARCHIVE_LOCAL1_20200222150312883_0.log');

 

SQL> SELECT LOW_SCN,NEXT_SCN, LOW_TIME, HIGH_TIME, LOG_ID, FILENAME FROM V$LOGMNR_LOGS;

 

SQL>DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128);

或者：

 

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128 , STARTTIME=>TO_DATE('2020-02-22 15:02:00','YYYY-MM-DD HH24:MI:SS') , ENDTIME=>TO_DATE('2020-02-22 15:03:00','YYYY-MM-DD HH24:MI:SS'));

 

SQL> select TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,OPERATION_CODE ,ROLL_BACK ,TABLE_NAME ,ROW_ID ,USERNAME ,DATA_OBJ# , DATA_OBJV# , SQL_REDO, REDO_VALUE, UNDO_VALUE

2   from V$LOGMNR_CONTENTS where table_name= 'LJW';

 

行号      TIMESTAMP                   START_TIMESTAMP             COMMIT_TIMESTAMP            OPERATION OPERATION_CODE ROLL_BACK   TABLE_NAME ROW_ID USERNAME

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

           DATA_OBJ#   DATA_OBJV#  SQL_REDO                                   REDO_VALUE           UNDO_VALUE          

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

1          2020-02-22 15:03:33.288000  2020-02-22 15:03:33.288000  2020-02-22 15:03:35.797000  DELETE    2              0           LJW        NULL   SYSDBA

           1285        0           DELETE FROM "SYSDBA"."LJW" WHERE "ID" = 66 2                    3

 

DBMS_LOGMNR.END_LOGMNR();