Oracle LogMiner分析归档日志

Oracle LogMiner分析归档日志

Oracle LogMiner是Oracle数据库的一部分，使您能够通过SQL接口查询在线和存档的重做日志文件。重做日志文件包含有关数据库上活动历史记录的信息。

参考官方文档：Using LogMiner to Analyze Redo Log Files (oracle.com)

一、准备测试环境

• 该测试环境为Oracle19c单实例
• 该文章仅分析了redo log file被归档后归档日志
• 该文章后续会继续补充挖掘其他信息示例

1、开启数据库归档日志

--关闭所有节点
shutdown immediate;

--启动一个节点至mount状态
startup mount;

--修改数据库为archivelog mode
alter database archivelog;

--查看数据库归档状态
archive log list;

--打开当前实例数据库
alter database open;

--启动其他节点数据库
startup;

2、打开数据库最小附加日志

在生成LogMiner分析的日志文件之前，必须启用附加日志记录。

启用附加日志记录时，需要在重做流中记录附加信息，以使重做日志文件中的信息更加详细。所以必须至少启用最少的补充日志记录

执行以下SQL：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

确认是否启用：

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

如果查询返回值“YES”或“IMPLICIT”，则启用最小补充日志记录。参见“Supplemental Logging”有关附加日志记录的完整信息。

3、设置当前session时间日期格式

以下示例可能需要SCN或时间范围来挖掘日志信息，所有最好提前设置当前session时间日期输出格式

SQL>  ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

二、创建测试数据

1、创建数据

--使用普通用户shuaige创建测试数据
--创建测试表
SHUAIGE@orcl> 
CREATE TABLE shuaige.test_tab
(
    ID number(32),
    NAME1 varchar2(30),
    NAME2 varchar2(30),
    NAME3 varchar2(30),
    ins_time date DEFAULT SYSDATE
)
tablespace shuaige_dat;

Table created.

--添加表注释
SHUAIGE@orcl> COMMENT ON TABLE shuaige.test_tab IS '测试表';

Comment created.

--添加主键和索引
SHUAIGE@orcl> 
alter table test_tab add constraint shuaige.pk_test_tab primary key(ID)
using index
  3  tablespace shuaige_dat;

Table altered.

--创建更新数据时间触发器
SHUAIGE@orcl> 
CREATE TRIGGER shuaige.test_tab_trigger
    before  INSERT OR UPDATE ON shuaige.test_tab
FOR EACH ROW
BEGIN
    IF UPDATING then
        :NEW.INS_TIME := SYSDATE;
    END IF;
END;
  9  /

Trigger created.

--批量插入数据
SHUAIGE@orcl> 
begin
  for i in 1 .. 10 loop
    INSERT INTO "SHUAIGE"."TEST_TAB" ("ID", "NAME1", "NAME2", "NAME3")
    VALUES (i, 'name1_'|| i, 'name2_' || i, 'name3_' || i);
  end loop;
end;
/

SHUAIGE@orcl> commit;

Commit complete.

2、数据落盘

将redo log中的数据归档，否则数据没有落盘，日志挖掘结果会未空

--切换至SYS用户执行
SYS@orcl> alter system switch logfile;

System altered.

三、日志发掘测试

挖掘在上次归档的Redo Log File

检查数据库修改历史的最简单方法是在源数据库中进行挖掘，并使用online catalog来翻译重做redo log files。此示例演示如何使用 LogMiner 进行最简单的分析。

此示例查找数据库生成的最后归档重做日志中包含的所有修改(假设该数据库不是 Oracle Real Application Clusters (Oracle RAC)数据库)。

1.确定最近归档的Redo Log File

此示例假设知道要挖掘最近归档的Redo Log File

SYS@orcl> 
SELECT NAME FROM V$ARCHIVED_LOG
  2  WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------------
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_21_1130854043.dbf

2.指定要分析的Redo Log File的文件

指定步骤1中查询返回的重做日志文件。该列表将包含一个重做日志文件。

SYS@orcl> 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
  LOGFILENAME => '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_21_1130854043.dbf', -
  OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SYS@orcl>

3.启动LogMiner

启动 LogMiner 并指定要使用的字典，或添加其他条件

SYS@orcl> 
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

若想分组DML语句提交事务，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
              DBMS_LOGMNR.COMMITTED_DATA_ONLY); 

若想格式化输出SQL，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
              DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
              DBMS_LOGMNR.PRINT_PRETTY_SQL);

若想按时间范围过滤输出，使用如下示例：

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   STARTTIME => '2023-03-10 09:00:00', -
   ENDTIME   => '2023-03-10 11:00:00', -
   OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                DBMS_LOGMNR.PRINT_PRETTY_SQL);

4.查询V$LOGMNR_CONTENTS 视图

输出按照 DML 语句的执行顺序显示它们; 因此事务可能相互交错。

该示例截取了与手动创建测试表和数据部分返回值内容

SYS@orcl> select username,scn,timestamp,sql_redo from v$logmnr_contents where username = 'SHUAIGE';

USERNAME
------------------------------------------------------------------------------------------------------------------------
       SCN TIMESTAMP
---------- -------------------
SQL_REDO
------------------------------------------------------------------------------------------------------------------------

SHUAIGE
   4989509 2023-03-10 10:36:38
CREATE TABLE test_tab
(
    ID number(32),
    NAME1 varchar2(30),
    NAME2 varchar2(30),
    NAME3 varchar2(30),
    ins_time date DEFAULT SYSDATE
)
tablespace shuaige_dat;

SHUAIGE
   4989558 2023-03-10 10:37:00
COMMENT ON TABLE test_tab IS '测试表';

SHUAIGE
   4989561 2023-03-10 10:37:00
commit;

SHUAIGE
   4989580 2023-03-10 10:37:05
set transaction read write;

SHUAIGE
   4989584 2023-03-10 10:37:05
CREATE UNIQUE INDEX "SHUAIGE"."PK_TEST_TAB" on "SHUAIGE"."TEST_TAB"("ID")tablespace shuaige_dat NOPARALLEL;

SHUAIGE
   4989588 2023-03-10 10:37:05
alter table test_tab add constraint pk_test_tab primary key(ID)
using index
tablespace shuaige_dat;

SHUAIGE
   4989591 2023-03-10 10:37:05
set transaction read write;

SHUAIGE
   4989592 2023-03-10 10:37:05
commit;

SHUAIGE
   4989593 2023-03-10 10:37:05
set transaction read write;

SHUAIGE
   4989595 2023-03-10 10:37:05
commit;

SHUAIGE
   4989634 2023-03-10 10:37:17
CREATE TRIGGER test_tab_trigger
    before  INSERT OR UPDATE ON test_tab
FOR EACH ROW
BEGIN
    IF UPDATING then
	:NEW.INS_TIME := SYSDATE;
    END IF;
END;;

SHUAIGE
   4989654 2023-03-10 10:37:21
set transaction read write;

SHUAIGE
   4989655 2023-03-10 10:37:21
commit;

SHUAIGE
   4989657 2023-03-10 10:37:21
set transaction read write;

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('1','name1_1','name2_1','name3_1',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('2','name1_2','name2_2','name3_2',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('3','name1_3','name2_3','name3_3',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('4','name1_4','name2_4','name3_4',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('5','name1_5','name2_5','name3_5',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('6','name1_6','name2_6','name3_6',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('7','name1_7','name2_7','name3_7',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('8','name1_8','name2_8','name3_8',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('9','name1_9','name2_9','name3_9',TO_D
ATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989657 2023-03-10 10:37:21
insert into "SHUAIGE"."TEST_TAB"("ID","NAME1","NAME2","NAME3","INS_TIME") values ('10','name1_10','name2_10','name3_10',
TO_DATE('2023-03-10 10:37:21', 'YYYY-MM-DD HH24:MI:SS'));

SHUAIGE
   4989658 2023-03-10 10:37:21
commit;

5.其他常用查询sql

--查询用户名、SCN号、时间戳、SQL_REDO语句（按用户名）
select username,scn,timestamp,sql_redo FROM V$LOGMNR_CONTENTS WHERE username = 'SHUAIGE';

--查询用户名、SCN号、时间戳、SQL_REDO语句（按表名）
select username,scn,timestamp,sql_redo FROM V$LOGMNR_CONTENTS where  seg_name='TEST_TAB';

--查询用户名、SCN号、时间戳、SQL_REDO语句（按时间戳）
select username,scn,timestamp,sql_redo FROM V$LOGMNR_CONTENTS where timestamp > to_date('2023-03-10 08:00:00','yyyy-mm-dd hh24:mi:ss');

----------------------------------------------------------------------------------------------
--官方方式在V$LOGMNR_CONTENT视图查询SQ_REDO语句和XID
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO 
   FROM V$LOGMNR_CONTENTS;

--官方方式在V$LOGMNR_CONTENT视图查询SQL_REDO、SQL_UNDO语句和XID
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' ||  XIDSQN) AS XID, SQL_REDO, SQL_UNDO
   FROM V$LOGMNR_CONTENTS;

若添加sql_undo字段，查询结果将把undo表空间的回滚语句抓出来，如果是误操作，可以根据该sql恢复数据

6.结束LogMiner会话

SYS@orcl> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SYS@orcl>