news 2025/12/28 3:27:58

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进行投诉反馈，一经查实，立即删除！
网站建设 2025/12/24 11:23:35

Docker Compose与Kubernetes下的智能Agent互联对比（仅限高级用户）

第一章：智能 Agent 的 Docker 容器互联在分布式系统中，多个智能 Agent 通常以独立服务的形式运行，Docker 容器化技术为这些 Agent 提供了轻量级、可移植的运行环境。实现容器间的高效互联是保障 Agent 协同工作的关键。通过自定义 Docker 网络…

作者头像 李华
网站建设 2025/12/24 15:56:43

【Q#开发进阶必备】：掌握VSCode重构工具的7个黄金法则

第一章：Q# 程序的 VSCode 重构工具概述Visual Studio Code（VSCode）作为量子计算开发的重要集成环境，为 Q# 语言提供了强大的重构支持。借助 Quantum Development Kit（QDK）插件，开发者能够在编写…

作者头像 李华
网站建设 2025/12/26 12:58:11

3大突破性纹理压缩方案：光线追踪性能实现3倍飞跃

3大突破性纹理压缩方案：光线追踪性能实现3倍飞跃 【免费下载链接】raytracing.github.io Main Web Site (Online Books) 项目地址: https://gitcode.com/GitHub_Trending/ra/raytracing.github.io 开篇痛点 当你运行光线追踪程序时，是否经常遭遇…

作者头像 李华
网站建设 2025/12/26 20:12:55

如何实现Q#对Python异常的无缝传递？5个关键技术点让你少走三年弯路

第一章：Q#-Python 的异常传递在混合量子-经典计算编程中，Q# 与 Python 的互操作性为开发者提供了灵活的开发模式。然而，在跨语言调用过程中，异常处理机制变得尤为关键。当 Q# 代码在执行量子操作时发生错误，如何将这些…

作者头像 李华
网站建设 2025/12/24 16:00:15

Locale Remulator 区域模拟工具完整配置教程

Locale Remulator 区域模拟工具完整配置教程 【免费下载链接】Locale_Remulator System Region and Language Simulator. 项目地址: https://gitcode.com/gh_mirrors/lo/Locale_Remulator Locale Remulator 是一个强大的系统区域和语言模拟工具，专门为开发者…

作者头像 李华
网站建设 2025/12/25 20:33:23

Steam挂机终极指南：3步实现自动化游戏时长增长

还在为Steam游戏时长不够而烦恼吗？想要轻松收集交易卡却不想整天开着游戏？HourBoostr和SingleBoostr这两款开源神器将彻底改变你的游戏挂机体验，让你在无需安装游戏的情况下安全增加游戏时间。无论你是多账户玩家还是单机用户，都能…

作者头像 李华