news 2026/6/10 12:24:15

logminer挖掘日志查询update操作的客户端信息

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
logminer挖掘日志查询update操作的客户端信息

logminer挖掘日志查询update操作的客户端信息。

如果没有启用过附加日志，无法记录。

只有 alter database add supplemental log data;启用过才能记录。

15:06:08 SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+DATA/JYC/ONLINELOG/group_1.264.1216240527', Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

15:07:19 SQL> exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

15:07:28 SQL> select count(*) from v$logmnr_contents where TABLE_NAME = 'JYC';

COUNT(*)
----------
5

15:07:44 SQL> create table log_jyc as select OS_USERNAME,MACHINE_NAME,timestamp,commit_timestamp,seg_owner,seg_name,seg_type_name,table_space,row_id,session_info,username,operation,sql_redo,sql_undo from v$logmnr_contents where TABLE_NAME = 'JYC';

Table created.

15:08:21 SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.


15:09:58 SQL> select SQL_UNDO from log_jyc;

SQL_UNDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------

delete from "SYS"."JYC" where "ID" = '1' and ROWID = 'AAAUEEAABAAAbNBAAA';
Unsupported
Unsupported
Unsupported

15:10:05 SQL> select SQL_redo,SQL_UNDO from log_jyc;
rows will be truncated


SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------
create table jyc (id int);
insert into "SYS"."JYC"("ID") values ('1');
Unsupported
Unsupported
Unsupported

15:10:31 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO NO NO

15:11:42 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
[oracle@rac1:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 17 15:26:30 2025
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>alter database add supplemental log data;--这个启用才能记录update和客户端信息

Database altered.

SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YESNO NO

SQL> set time on
15:28:33 SQL> update jyc set id=6;

1 row updated.

15:28:52 SQL> commit;

Commit complete.

15:28:54 SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
1 1 65 209715200 512 1 NO
CURRENT 12120834 2025-12-17 06:00:57 9.2954E+18
0

2 1 64 209715200 512 1 NO
INACTIVE 11880480 2025-12-16 01:00:33 12120834
2025-12-17 06:00:57 0

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------

3 2 51 209715200 512 1 NO
INACTIVE 12042656 2025-12-16 22:01:27 12189549
2025-12-17 14:49:18 0

4 2 52 209715200 512 1 NO
CURRENT 12189549 2025-12-17 14:49:18 9.2954E+18

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
0


15:29:12 SQL> set line 180
15:29:15 SQL> set wrap off
15:29:17 SQL> r
1* select * from v$Log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 65 209715200 512 1 NO CURRENT 12120834 2025-12-17 06:00:57 9.2954E+18 0
2 1 64 209715200 512 1 NO INACTIVE 11880480 2025-12-16 01:00:33 12120834 2025-12-17 06:00:57 0
3 2 51 209715200 512 1 NO INACTIVE 12042656 2025-12-16 22:01:27 12189549 2025-12-17 14:49:18 0
4 2 52 209715200 512 1 NO CURRENT 12189549 2025-12-17 14:49:18 9.2954E+18 0

15:29:17 SQL> select * from v$Logfile;
rows will be truncated

rows will be truncated


GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------
2 ONLINE +DATA/JYC/ONLINELOG/group_2.265.1216240527
1 ONLINE +DATA/JYC/ONLINELOG/group_1.264.1216240527
3 ONLINE +DATA/JYC/ONLINELOG/group_3.272.1216241361
4 ONLINE +DATA/JYC/ONLINELOG/group_4.273.1216241365

15:29:37 SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+DATA/JYC/ONLINELOG/group_1.264.1216240527', Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

15:29:48 SQL> exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

15:29:58 SQL> select count(*) from v$logmnr_contents where TABLE_NAME = 'JYC';

COUNT(*)
----------
6

15:30:09 SQL> drop table log_jyc;

Table dropped.

15:30:30 SQL> create table log_jyc as select OS_USERNAME,MACHINE_NAME,timestamp,commit_timestamp,seg_owner,seg_name,seg_type_name,table_space,row_id,session_info,username,operation,sql_redo,sql_undo from v$logmnr_contents where TABLE_NAME = 'JYC';

Table created.

15:30:40 SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

15:30:52 SQL> select MACHINE_NAME,operation,sql_redo,sql_undo from log_jyc;
rows will be truncated

rows will be truncated

rows will be truncated


MACHINE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
UNKNOWN
rac1

6 rows selected.

15:31:28 SQL> col MACHINE_NAME for a10
15:31:34 SQL> r
1* select MACHINE_NAME,operation,sql_redo,sql_undo from log_jyc
rows will be truncated


MACHINE_NA OPERATION SQL_REDO
---------- -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------
UNKNOWN DDL create table jyc (id int);
UNKNOWN INSERT insert into "SYS"."JYC"("ID") values ('1');
UNKNOWN UNSUPPORTED Unsupported
UNKNOWN UNSUPPORTED Unsupported
UNKNOWN UNSUPPORTED Unsupported
rac1 UPDATE update "SYS"."JYC" set "ID" = '6' where "ID" = '2' and ROWID = 'AAAUEEAABAAAbNBAAA';

6 rows selected.

15:31:35 SQL> select timestamp,MACHINE_NAME,operation,sql_redo,sql_undo from log_jyc;
rows will be truncated


TIMESTAMP MACHINE_NA OPERATION SQL_REDO
------------------- ---------- -------------------------------- --------------------------------------------------------------------------------------------------------------------
2025-12-17 14:31:01 UNKNOWN DDL create table jyc (id int);
2025-12-17 14:31:09 UNKNOWN INSERT insert into "SYS"."JYC"("ID") values ('1');
2025-12-17 14:31:24 UNKNOWN UNSUPPORTED Unsupported
2025-12-17 14:47:27 UNKNOWN UNSUPPORTED Unsupported
2025-12-17 14:53:30 UNKNOWN UNSUPPORTED Unsupported
2025-12-17 15:28:54 rac1 UPDATE update "SYS"."JYC" set "ID" = '6' where "ID" = '2' and ROWID = 'AAAUEEAABAAAbNBAAA';

6 rows selected.

15:32:04 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES NO NO

15:32:39 SQL> select timestamp,MACHINE_NAME,session_info,sql_redo,sql_undo from log_jyc;
rows will be truncated

rows will be truncated


TIMESTAMP MACHINE_NA SESSION_INFO
------------------- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------
2025-12-17 14:31:01 UNKNOWN UNKNOWN
2025-12-17 14:31:09 UNKNOWN UNKNOWN
2025-12-17 14:31:24 UNKNOWN UNKNOWN
2025-12-17 14:47:27 UNKNOWN UNKNOWN
2025-12-17 14:53:30 UNKNOWN UNKNOWN
2025-12-17 15:28:54 rac1 login_username=SYS client_info= OS_username=oracle Machine_name=rac1 OS_terminal=pts/0 OS_process_id=129646 OS_program_name=sqlplus@rac1 (TNS V1-V3)

6 rows selected.

15:33:10 SQL> alter database drop supplemental log data (primary key, unique index) columns;

Database altered.

15:34:40 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES NO NO

15:34:44 SQL> alter database drop supplemental log data;

Database altered.

15:35:05 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO NO NO

15:35:13 SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

15:35:55 SQL> alter database force logging;--这个数据库日志没用

Database altered.

15:36:25 SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

15:36:29 SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO NO NO

15:36:32 SQL> drop table log_jyc;

Table dropped.

15:36:53 SQL> update jyc set id=8;

1 row updated.

15:37:07 SQL> commit;

Commit complete.

15:37:10 SQL> select * from jyc;

ID
----------
8

15:37:14 SQL> exec dbms_logmnr.add_logfile(LogFileName=>'+DATA/JYC/ONLINELOG/group_1.264.1216240527', Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

15:37:26 SQL> exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

15:37:32 SQL>
15:37:33 SQL> select count(*) from v$logmnr_contents where TABLE_NAME = 'JYC';


COUNT(*)
----------
7

15:37:42 SQL> 15:37:42 SQL> create table log_jyc as select OS_USERNAME,MACHINE_NAME,timestamp,commit_timestamp,seg_owner,seg_name,seg_type_name,table_space,row_id,session_info,username,operation,sql_redo,sql_undo from v$logmnr_contents where TABLE_NAME = 'JYC';

Table created.

15:37:56 SQL> select timestamp,MACHINE_NAME,session_info,sql_redo from log_jyc;
rows will be truncated


TIMESTAMP MACHINE_NA SESSION_INFO
------------------- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------
2025-12-17 14:31:01 UNKNOWN UNKNOWN
2025-12-17 14:31:09 UNKNOWN UNKNOWN
2025-12-17 14:31:24 UNKNOWN UNKNOWN
2025-12-17 14:47:27 UNKNOWN UNKNOWN
2025-12-17 14:53:30 UNKNOWN UNKNOWN
2025-12-17 15:28:54 rac1 login_username=SYS client_info= OS_username=oracle Machine_name=rac1 OS_terminal=pts/0 OS_process_id=129646 OS_program_name=sqlplus@rac1 (TNS V1-V3)
2025-12-17 15:37:08 UNKNOWN UNKNOWN

7 rows selected.

15:38:18 SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

15:38:34 SQL> select timestamp,MACHINE_NAME,sql_redo from log_jyc;

TIMESTAMP MACHINE_NA SQL_REDO
------------------- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------
2025-12-17 14:31:01 UNKNOWN create table jyc (id int);
2025-12-17 14:31:09 UNKNOWN insert into "SYS"."JYC"("ID") values ('1');
2025-12-17 14:31:24 UNKNOWN Unsupported
2025-12-17 14:47:27 UNKNOWN Unsupported
2025-12-17 14:53:30 UNKNOWN Unsupported
2025-12-17 15:28:54 rac1 update "SYS"."JYC" set "ID" = '6' where "ID" = '2' and ROWID = 'AAAUEEAABAAAbNBAAA';
2025-12-17 15:37:08 UNKNOWN Unsupported

7 rows selected.

15:38:50 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
[oracle@rac1:/home/oracle]$

相关参考：

https://blog.csdn.net/jycjyc/article/details/144058395

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/6/5 23:50:16

跨平台翻译工具pot-desktop：打破语言障碍的智能助手

当你面对外文文档束手无策时，当图片中的文字无法复制时，当不同设备上的翻译体验参差不齐时，你需要一个真正的跨平台翻译解决方案。pot-desktop正是这样一款开源工具，它集成了划词翻译、OCR识别和20多种翻译引擎，让你在…

作者头像 李华
网站建设 2026/6/8 13:59:54

OpenUSD场景格式选择指南：5个关键因素决定你的最佳格式

OpenUSD场景格式选择指南：5个关键因素决定你的最佳格式 【免费下载链接】OpenUSD Universal Scene Description 项目地址: https://gitcode.com/GitHub_Trending/ope/OpenUSD 在3D内容创作和分发过程中，OpenUSD作为通用场景描述框架，提…

作者头像 李华
网站建设 2026/6/10 0:00:35

Druid连接池容器化部署完全指南：从入门到高可用实战

Druid连接池容器化部署完全指南：从入门到高可用实战 【免费下载链接】druid 阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池 项目地址: https://gitcode.com/gh_mirrors/druid/dru…

作者头像 李华
网站建设 2026/6/8 8:25:40

Q#如何无缝调用Python函数？（量子编程进阶实战指南）

第一章：Q#如何无缝调用Python函数？（量子编程进阶实战指南）在量子计算与经典计算混合编程的实践中，Q# 作为微软推出的量子编程语言，虽然专为量子算法设计，但其通过 .NET 生态系统与 Python 的互操…

作者头像 李华
网站建设 2026/6/7 13:47:30

稀缺资料流出：资深架构师私藏的智能Agent Docker部署标准化手册

第一章：智能Agent Docker部署概述在现代分布式系统与边缘计算场景中，智能Agent作为实现自动化决策与环境感知的核心组件，其快速部署与环境隔离需求日益增长。Docker凭借轻量级容器化技术，为智能Agent提供了高效、可移植的运行环境…

作者头像 李华