命令总结:
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();