目前已经有很多的集成/同步工具都使用LogMiner进行变化数据获取，虽然，目前官方依旧不推荐这么做，文档中的原文如下：“Note:LogMiner is intended for use as a debugging tool, to extract information from the redo logs to solve problems. It is not intended to be used for any third party replication of data in a production environment.”
根据经验来看，LogMiner用于数据集成并没有什么太大的问题。
打开补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
查看归档日志
SQL> SELECT name FROM v$archived_log ORDER BY FIRST_TIME DESC FETCH FIRST 3 ROWS ONLY;
NAME
----------------------------------------------------------------------------------------------------
/u03/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2025_04_24/o1_mf_1_761_n0mbccbd_.arc
/u03/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2025_04_24/o1_mf_1_760_n0mbcc62_.arc
/u03/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2025_04_24/o1_mf_1_759_n0m6tdfp_.arc
SELECT
SCN,
TIMESTAMP,
OPERATION,
SQL_REDO,
SQL_UNDO,
SEG_OWNER,
TABLE_NAME,
USERNAME
FROM
V$LOGMNR_CONTENTS
WHERE
OPERATION IN ('INSERT', 'UPDATE', 'DELETE')
-- AND SEG_OWNER = 'TEST_USER'
AND (TABLE_NAME = 'T2' OR TABLE_NAME = 't2')
ORDER BY TIMESTAMP DESC
FETCH FIRST 3 ROWS ONLY;
SELECT
FIRST_CHANGE#,
TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS'),
NEXT_CHANGE# ,
TO_CHAR(NEXT_TIME, 'YYYY-MM-DD HH24:MI:SS'),
NAME
FROM
V$ARCHIVED_LOG
ORDER BY NEXT_CHANGE# DESC
FETCH FIRST 3 ROWS ONLY;
强制切换归档日志文件
ALTER SYSTEM SWITCH LOGFILE;
获取当前正在使用的redo文件
SELECT A.GROUP#,A.MEMBER, B.STATUS
FROM V$LOGFILE A
JOIN V$LOG B ON A.GROUP# = B.GROUP#
WHERE B.STATUS = 'CURRENT';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int,
nick varchar(32),
age int,
KEY ind_n (nick),
PRIMARY KEY (id)
);
INSERT INTO t1 VALUES ( 1, "a", 27 );
INSERT INTO t1 VALUES ( 11, "k" ,23 );
INSERT INTO t1 VALUES ( 24, "x" ,22 );
START TRANSACTION;
SELECT * FROM t1 WHERE nick >= "k" for update;
+----+------+------+
| id | nick | age |
+----+------+------+
| 11 | k | 23 |
| 24 | x | 22 |
+----+------+------+
2 rows in set (0.00 sec) Warnings: 0
在上述事务中，为了实现将 SELECT 涉及的“相关记录”全部加上“排它锁”，从而阻止其他事务对该部分数据进行修改。即，如果有任何其他的事务，尝试修改该事务中的“相关记录”，都需要被阻塞。这里的“相关记录”，具体是指：WHERE nick >= "k" 查询扫描到的索引入口（二级索引），以及对应的数据（即主键入口）。
试想，如果有其他事务尝试写入一条 nick = 'm'的记录，那么上述的...for update语句则也会返回该记录。为了阻止上述的不一致，上述事务不仅要对单个记录或索引入口进行加锁，还需要对索引入口之间的间隙进行加锁。
“Even without knowing too much about how databases like InnoDB operate, we can guess, that sometimes the operation involves just the record, sometimes the gap before a record, and at yet another times we need to access both, the record and a gap. One way to model that, would be to consider records and gaps to be two different kinds of resources which you can lock independently. Current InnoDB implementation takes a different approach: there is just one resource for each point, but there are multiple kinds of access right you can request for it, and the access right specifies if you need the row, the gap or both parts. One benefit of this is that it is optimized for the most common case where you need both.”
在show engine innodb status\G中看到的Dm会是：0: len 4; hex 00000001; asc ;;
D(m-1)则表示在索引中，Dm前面的一个索引入口或主键取值
(...)表示开区间；(...]表示半开半闭区间
在 show engine innodb status\G 输出示例：
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `sysbenchdb`.`t1` trx id 10094 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00000000276d; asc 'm;;
2: len 7; hex 810000008d0110; asc ;;
3: len 1; hex 61; asc a;;
4: len 4; hex 8000000c; asc ;;
UPDATE 语句的间隙锁
环境说明
mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
场景构造
Session A
Session B
DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int, nick varchar(32), age int, KEY ind_n (nick), PRIMARY KEY (id) );
INSERT INTO t1 VALUES ( 1, "a", 27 ); INSERT INTO t1 VALUES ( 11, "k" ,23 ); INSERT INTO t1 VALUES ( 24, "x" ,22 );
docker image ls
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
REPOSITORY TAG IMAGE ID CREATED SIZE
container-registry.oracle.com/mysql/community-server 9.1 f1f889678a73 6 months ago 606 MB
container-registry.oracle.com/database/express 18.4.0-xe 364598d20118 4 years ago 6.03 GB
docker logs -f oracle-18ex
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
ORACLE PASSWORD FOR SYS AND SYSTEM: oracledocker
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
****************
Enter SYSTEM user password:
*************
Enter PDBADMIN User Password:
*************
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
31% complete
34% complete
38% complete
41% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.
Connect to Oracle Database using one of the connect strings:
Pluggable database: bd127ae4faab/XEPDB1
Multitenant container database: bd127ae4faab
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
2025-04-17T03:40:43.663079+00:00
XEPDB1(3):Resize operation completed for file# 10, old size 358400K, new size 368640K
2025-04-17T03:40:44.483587+00:00
XEPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/XE/XEPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
XEPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/XE/XEPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
XEPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
XEPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2025-04-17T03:40:44.930766+00:00
ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
Completed: ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE
登录容器中的 Oracle 数据库
在上面的输出中可以看到，安装时会默认创建如下数据库：
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 17 03:56:27 2025
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Apr 17 2025 03:49:37 +00:00
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>
如果没有认证或提前在官网统一协议，则可能遇到如下报错：requested access to the resource is denied：
docker pull container-registry.oracle.com/database/enterprise:12.2.0.1
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
Trying to pull container-registry.oracle.com/database/enterprise:12.2.0.1...
Error: initializing source docker://container-registry.oracle.com/database/enterprise:12.2.0.1: reading manifest 12.2.0.1 in container-registry.oracle.com/database/enterprise: requested access to the resource is denied
docker exec -it oracle-1202ee sqlplus / as sysdba
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
Error: crun: executable file `sqlplus` not found in $PATH: No such file or directory: OCI runtime attempted to invoke a command that was not found
当前的绕过方案是，进入容器的bash，然后再执行即可：
[root@oracle-docker-test ~]# docker exec -it oracle-1202ee bash
Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.
[oracle@8bb1ec09ec5e /]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 18 02:37:30 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
