supplemental logging相关测试
- 概述
- database-level
- table-level
- User-defined conditional supplemental log groups
- User-defined unconditional log groups
- Identification Key Logging
- supplemental logging 的应用
- 总结：
概述
根据oracle 19c 官方文档对supplemental logging的描述：https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-D857AF96-AC24-4CA1-B620-8EA3DF30D72E
离线版书名是utilities：
总结supplemental logging分为以下几种：
下面对这张图做一个解释：
关于supplemental logging，Oracle是这样解释的：
Redo log files are generally used by for instance recovery and media recovery.The data needed for such operations is automatically recorded in the redo log files.However,a redo-based application may require that additional columns be logged in the redo log files.The process of logging these additional columns is called supplemental logging.
下面介绍下supplemental log group：
supplemental log group意思就是当启用了supplemental logging后，需要被额外记录的列的集合。
有两种类型：
- Unconditional supplemental log groups:
在任何时候，不管更新的columns是否为指定的columns，都要在redo log中记录指定列的前镜像（before-images），有时也被称为Always log group。 - Conditional supplemental log groups:
在指定列中，至少有一个列被更新了，那么就会记录所有指定列的前镜像。
补充日志包含数据库级的和对象级的，而Database-level Supplemental Logging中又分为两种类型：
minimal supplemental logging和identification key logging。需要说明的是，启用minimal logging不会给数据库在产生redo log files时带来严重的性能开销；而启用identification key logging会带来一定的性能影响。
Minimal supplemental logging: Minimal supplemental logging会收集最少数量的信息来满足LogMiner的需要，不过可以确保LogMiner拥有足够的信息去支持chained rows和各种storage arrangements，如簇表和索引组织表
Database-level Identification Key Logging: 使用identification key logging，可以通过alter database add supplemental log语句指定某些选项，可以使数据库在数据库范围内记录所有被更新的前镜像。
- ALL system-generated unconditional supplemental log group
该选项指定了当一行被更新后，那么改行的所有列的前镜像都会被记录到redo log file中（当然，LOBs、LONGS和ADTs除外）。在数据库级启用all columns logging的语句为：
alter database add supplemental log data (all) columns; - PRIMARY KEY system-generated unconditional supplemental log group
该选项会使数据库在一包含主键的行被更新时，数据库记录行的所有主键列的前镜像记录到redo log中（即使主键的值没有发生改变，也会被记录）。（后面验证是强制记录的，即使主键的值没有被更新，数据库仍然会把主键列的前镜像记录到redo log中）。
如果一张表没有主键，但是有一个或者多个non-null unique index key constraints或者index key，那么一个或者多个的unique index key将会被认为是可以唯一标识更新行的列被记录。
如果一张表既没有primary key，也没有non-null unique index key，那么所有列将会被记录（除了LOB）。这个时候就跟ALL选项的效果是一样的了。
因此Oracle建议当你选择使用database-level primary key supplemental logging时，尽量使所有的表都定义primary key或者unique index keys.
启用数据库级启用primary key logging的语句为：
alter database add supplemental log data (primary key) columns; - UNIQUE system-generated conditional supplemental log group
如果属于composite unique key或者bitmap index的任何列被更新，那么数据库就会在redo log中记录修改行的所有列。
启用数据库级的unique index key和bitmap index logging的语句为：
alter database add supplemental log data (unique) columns; - FOREIGN KEY system-generated conditional supplemental log group
当任何属于foreign key的列被修改时，那么数据库就会在redo log中记录改行的所有列。
在数据库级启用foreign key logging的语句为：
alter database add supplemental log data (foreign key) columns;
你选择使用identification key logging时，需要记住：
- 如果在数据库为open状态时启用identification key logging，在cursor cache中的所有DML cursor都将失效，此时，性能将会受到影响，直到cursor cache被重新载入。
- 如果在数据库级启用了identification key logging，那么**minimal supplemental logging将会被隐式启用 **
- 在数据库中执行的supplemental logging语句是被累积的。例如发出如下语句时，primary key和unique key supplemental logging都会被启用。
alter database add supplemental log data (primary key) columns;
alter database add supplemental log data (unique) columns;
我们如果要禁用Database-level supplemental logging，需要逐步的禁用supplemental logging，你必须首先禁用掉所有的identification key logging，然后才能禁用minimal supplemental logging。如下面这个例子：
SQL>alter database add supplemental log data (primary key) columns;
SQL>alter database add supplemental log data (unique) columns;
SQL>alter database drop supplemental log data (primary key) columns;
SQL>alter database drop supplemental log data (unique) columns;
SQL>alter database drop supplemental log data;
如果不按照这个顺序的话，就会报错，如：
ORA-32589: unable to drop minimal supplemental logging
database-level
测试环境：仍然延续上次oracle 19c rac环境。
测试表:
SQL> conn test1/test1@10.1.11.27:1521/ORAPDB
create table logminer(a int,b int,c int,d int);
insert into logminer values(1,1,1,1);
insert into logminer values(2,2,2,2);
insert into logminer values(3,3,3,3);
insert into logminer values(4,4,4,4);
insert into logminer values(5,5,5,5);
insert into logminer values(6,6,6,6);
insert into logminer values(7,7,7,7);
insert into logminer values(8,8,8,8);
commit;
alter system switch logfile;
复制为了方便观察，DML全部采用update语句。
整个过程使用logminer 查看日志的情况，此处我们采用分析在线日志，而不是归档日志。数据字典采用online catalog。所以整个过程用到的语句如下：
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ‘+DATADG/ORA19C/ONLINELOG/group_2.259.1050591517’,OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);set linesize 200
set pagesize 200
col sql_redo for a50
col SQL_UNDO for a50
SELECT SQL_REDO,SQL_UNDO FROM
V$LOGMNR_CONTENTS
WHERE upper(sql_redo) like ‘%LOGMINER%’;
supplemental_log_data_min
yes
由上篇文章https://www.modb.pro/db/469035，得知，不开启最小附加日志，update的相关操作挖掘不到，所以默认的no在这没意义。设置为yes。
session 2:
SQL> alter database drop supplemental log data;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
SQL> alter database add supplemental log data;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
YES NO NO NO NO
复制supplemental_log_data_min=yes,表示这时只启动了最小补充日志。
session 1:
SQL> update logminer set c=9 where a=2;
1 row updated.
SQL> commit;
Commit complete.
复制session 2:
SQL> select a.group#,a.sequence#,member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# SEQUENCE# MEMBER STATUS
---------- ---------- -------------------------------------------------- ----------------
1 61 +DATADG/ORA19C/ONLINELOG/group_1.258.1050591517 INACTIVE
2 62 +DATADG/ORA19C/ONLINELOG/group_2.259.1050591517 CURRENT
3 9 +DATADG/ORA19C/ONLINELOG/group_3.270.1050594005 INACTIVE
4 10 +DATADG/ORA19C/ONLINELOG/group_4.271.1050594005 INACTIVE
复制我们看UPDATE的REDO在62号日志文件,分析在线日志。logminer 分析在线日志。
session 3：
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ONLINELOG/group_2.259.1050591517',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
> DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
SQL> set linesize 200
set pagesize 200
col sql_redo for a50
SQL> SQL> SQL> col SQL_UNDO for a50
SQL> SELECT SQL_REDO,SQL_UNDO FROM
2 V$LOGMNR_CONTENTS
3 WHERE upper(sql_redo) like '%LOGMINER%';
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"C" = 9 "C" = 2
where where
"C" = 2 and "C" = 9 and
ROWID = 'AAAFsXAAOAAAAClAAB'; ROWID = 'AAAFsXAAOAAAAClAAB';
复制
更改了c字段，没有任何额外附加日志限制，最后只记录了改变的字段也就是c的值。后面的where条件都没记录，只记录了本字段和rowid的值。
结论：min开启后， 只有修改列c+rowid
IMPLICIT
接上面步骤，我们可以按照如下方式把最小追加模式设置成隐式。首先把最小追加模式删除，然后我们在数据库级打开主键追加日志，也就是日志追加了PK的列，我们再来查看数据库的日志追加情况。
SQL> SQL> alter database drop supplemental log data;
Database altered.
SQL> SELECT supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui,
supplemental_log_data_fk fk,
supplemental_log_data_all allc
FROM v$database; 2 3 4 5 6
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> SELECT supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui,
supplemental_log_data_fk fk,
supplemental_log_data_all allc
FROM v$database;
2 3 4 5 6
MIN PK UI FK ALL
-------- --- --- --- ---
IMPLICIT YES NO NO NO
复制最小追加变成隐式了，PK=YES
session 1:
SQL> update logminer set b=80 where a=3;
1 row updated.
SQL> commit;
Commit complete.
复制仍然执行UPDATE，a=3的b改成80,我们logminer后，得出的sql语句如下 :
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 80 "B" = 3
where where
"A" = 3 and "A" = 3 and
"B" = 3 and "B" = 80 and
"C" = 3 and "C" = 3 and
"D" = 3 and "D" = 3 and
ROWID = 'AAAFsXAAOAAAAClAAC'; ROWID = 'AAAFsXAAOAAAAClAAC';
复制
只是3改成80，为什么PK追加日志模式，却把所有列附加进去了呢？因为PK表示记录主键,而这个表根本没有设置PK,所以只能把所有行组合起来当做PK。
supplemental_log_data_pk
还是跟上面的情况一样。
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
IMPLICIT YES NO NO NO
复制此时min 和 pk的状态没变，所以可以看出来，只要设置了pk，那么min 必然就会是隐式。也和前面开头说的一致。
唯一区别，给表加了主键：
session 1:
SQL> alter table logminer add constraint pk_logminer primary key(a);
Table altered.
复制session 2 切换日志，然后session 1 执行update操作：
SQL> update logminer set b=66 where c=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set a=77 where c=5;
1 row updated.
SQL> commit;
Commit complete.
复制我们知道主键列是a，上面第一条UPDATE没有涉及到主键列，第二条UPDATE涉及到了主键列。直接看logminer 结果：
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 66 "B" = 4
where where
"A" = 4 and "A" = 4 and
"B" = 4 and "B" = 66 and
ROWID = 'AAAFsXAAOAAAAClAAD'; ROWID = 'AAAFsXAAOAAAAClAAD';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"A" = 77 "A" = 5
where where
"A" = 5 and "A" = 77 and
ROWID = 'AAAFsXAAOAAAAClAAE'; ROWID = 'AAAFsXAAOAAAAClAAE';
复制
第一条sql 语句，C=4的条件没有在REDO中，b=4改成66，而且关键是增加了一个主键列，“A” = 4，所以，主键追加日志方式是强制的，就是UPDATE没有涉及主键中的任何列，都强制记录到日志。sql语句中除过修改的字段，还有主键。
第二条sql 语句，直接是主键"A" = 5出现在REDO中。
其实min=IMPLICIT和supplemental_log_data_pk=yes 是一种情况，因为启用了identification key logging，那么minimal supplemental logging将会被隐式启用。
supplemental_log_data_ui
session 2:
SQL> alter database drop supplemental log data (primary key) columns; --删除主键追加日志
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
SQL> alter database add supplemental log data (unique) columns; --添加unique 日志追加
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
IMPLICIT NO YES NO NO
复制
和前面描述一致，min又是隐式开启。
数据库alert 日志，也可以看出来隐式开启了：
。。。。。
alter database drop supplemental log data (primary key) columns
2022-08-20T11:47:20.465303+08:00
SUPLOG: Previous supplemental logging attributes at scn = 3891082
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
2022-08-20T11:47:20.498863+08:00
SUPLOG: New supplemental logging attributes at scn = 3891082
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
Completed: alter database drop supplemental log data (primary key) columns
2022-08-20T11:47:53.434401+08:00
alter database add supplemental log data (unique) columns
2022-08-20T11:47:53.446789+08:00
SUPLOG: Previous supplemental logging attributes at scn = 3891668
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
2022-08-20T11:47:53.457121+08:00
SUPLOG: New supplemental logging attributes at scn = 3891668
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
2022-08-20T11:47:53.460171+08:00
SUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 3891668 (0x00000000003b61d4)
SUPPLEMENTAL LOG: All transactions started at or before scn 3891668 (0x00000000003b61d4) have completed
Completed: alter database add supplemental log data (unique) columns
。。。。。
session 1：
测试表的b和d列上增加联合unique索引
SQL> select * from logminer;
A B C D
---------- ---------- ---------- ----------
1 1 1 1
2 2 9 2
66 66 3 3
4 66 4 4
77 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
8 rows selected.
SQL> create unique index idx_logminer_t1 on logminer(b,d);
Index created.
复制那现在DML语句涉及该表的REDO是不是将UI列都会被记录到日志文件呢？ 只有(b,d)中的某列被DML了，才会将(b,d)两列全部记录到REDO中。
session1：
SQL> update logminer set b=88 where c=7;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set a=99 where c=8;
1 row updated.
SQL> commit;
Commit complete.
复制session n:
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 88 "B" = 7
where where
"B" = 7 and "B" = 88 and
"D" = 7 and "D" = 7 and
ROWID = 'AAAFsXAAOAAAAClAAG'; ROWID = 'AAAFsXAAOAAAAClAAG';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"A" = 99 "A" = 8
where where
"A" = 8 and "A" = 99 and
ROWID = 'AAAFsXAAOAAAAClAAH'; ROWID = 'AAAFsXAAOAAAAClAAH';
复制
注意，我们的unique索引是建立在b和d上的联合唯一性索引。
第1条测试语句更改了b字段，于是在记录了b字段的值的同时，还记录d字段的值，作为where后的限制条件。
第2条测试语句更改了a字段，不在我们的约束列中，最后只记录了改变的字段也就是a的值。
supplemental_log_data_all
session 2:
SQL> alter database drop supplemental log data (unique) columns;
Database altered.
SQL> alter database add supplemental log data (all) columns;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
IMPLICIT NO NO NO YES
复制session 1:
SQL> update logminer set a=45 where c=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set b=54 where c=7 and d=7;
1 row updated.
SQL> commit;
Commit complete.
复制session n:
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"A" = 45 "A" = 1
where where
"A" = 1 and "A" = 45 and
"B" = 44 and "B" = 44 and
"C" = 1 and "C" = 1 and
"D" = 1 and "D" = 1 and
ROWID = 'AAAFsXAAOAAAAClAAA'; ROWID = 'AAAFsXAAOAAAAClAAA';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 54 "B" = 88
where where
"A" = 7 and "A" = 7 and
"B" = 88 and "B" = 54 and
"C" = 7 and "C" = 7 and
"D" = 7 and "D" = 7 and
ROWID = 'AAAFsXAAOAAAAClAAG'; ROWID = 'AAAFsXAAOAAAAClAAG';
复制
更改一行数据，所有列都会被记录在redo中。
table-level
User-defined conditional supplemental log groups
经测试，在表级，必须开启supplemental_log_data_min=yes。要不然update 操作挖掘不到。
session 2:
SQL> alter database drop supplemental log data (all) columns;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
SQL> alter database add supplemental log data;
Database altered.
SQL> SELECT supplemental_log_data_min min,
2 supplemental_log_data_pk pk,
3 supplemental_log_data_ui ui,
4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
YES NO NO NO NO
复制session 1：
SQL> alter table logminer add supplemental log group lg_test1(a,b);
Table altered.
SQL> update logminer set b=44 where c=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set d=11 where c=6;
1 row updated.
SQL> commit;
Commit complete.
复制在logminer表上(a列和b列)定义追加日志组，日志组就是用来设置要记录补充日志的列的组合。
session 2:
SQL> select * from dba_log_groups where owner='TEST1';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
---------- -------------------- -------------------- ------------------- ----------- --------------
TEST1 LG_TEST1 LOGMINER USER LOG GROUP CONDITIONAL USER NAME
SQL> select * from dba_log_group_columns where owner='TEST1';
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
---------- -------------------- -------------------- -------------------- ---------- ------
TEST1 LG_TEST1 LOGMINER A 1 LOG
TEST1 LG_TEST1 LOGMINER B 2 LOG
复制从上面两个视图可以看出：1、(a,b)两日志组的列是有条件记录到补充日志中的；2、日志组是创建在LOGMINER表的a和b列上的。
session n:
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 44 "B" = 1
where where
"A" = 1 and "A" = 1 and
"B" = 1 and "B" = 44 and
ROWID = 'AAAFsXAAOAAAAClAAA'; ROWID = 'AAAFsXAAOAAAAClAAA';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"D" = 11 "D" = 55
where where
"D" = 55 and "D" = 11 and
ROWID = 'AAAFsXAAOAAAAClAAF'; ROWID = 'AAAFsXAAOAAAAClAAF';
复制
第1条，满足条件了，因为b包含在日志组(a,b)中，所以第1条，日志组会记录到REDO中。
而第2条，d=88，没有在日志组中，没有满足条件，不记录REDO。
与前面数据库级的条件相似。
User-defined unconditional log groups
无条件的日志组的补充日志模式.
先删除前面有条件的日志组,然后建立无条件的日志组.
session 1:
SQL> alter table logminer drop supplemental log group lg_test1;
Table altered.
SQL> alter table logminer add supplemental log group lg_test1(a,b) always;
Table altered.
复制session 2：
SQL> SQL> alter session set container=ORAPDB;
Session altered.
SQL> select * from dba_log_groups where owner='TEST1';
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
---------- -------------------- -------------------- ------------------- ----------- --------------
TEST1 LG_TEST1 LOGMINER USER LOG GROUP ALWAYS USER NAME
SQL> select * from dba_log_group_columns where owner='TEST1';
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN
---------- -------------------- -------------------- -------------------- ---------- ------
TEST1 LG_TEST1 LOGMINER A 1 LOG
TEST1 LG_TEST1 LOGMINER B 2 LOG
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.sequence#,member,a.status from v$log a,v$logfile b where a.group#=b.group#;
GROUP# SEQUENCE# MEMBER STATUS
---------- ---------- -------------------------------------------------- ----------------
1 69 +DATADG/ORA19C/ONLINELOG/group_1.258.1050591517 CURRENT
2 68 +DATADG/ORA19C/ONLINELOG/group_2.259.1050591517 ACTIVE
3 9 +DATADG/ORA19C/ONLINELOG/group_3.270.1050594005 INACTIVE
4 10 +DATADG/ORA19C/ONLINELOG/group_4.271.1050594005 INACTIVE
复制session 1:
SQL> update logminer set b=11 where c=6;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set d=22 where c=8;
1 row updated.
SQL> commit;
Commit complete.
复制session n:
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 11 "B" = 6
where where
"A" = 6 and "A" = 6 and
"B" = 6 and "B" = 11 and
ROWID = 'AAAFsXAAOAAAAClAAF'; ROWID = 'AAAFsXAAOAAAAClAAF';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"D" = 22 "D" = 8
where where
"A" = 99 and "A" = 99 and
"B" = 8 and "B" = 8 and
"D" = 8 and "D" = 22 and
ROWID = 'AAAFsXAAOAAAAClAAH'; ROWID = 'AAAFsXAAOAAAAClAAH';
复制
不管更新哪一行，指定的列，都会被记录。
Identification Key Logging
作用和前面数据库级别的相同，只是粒度更细一点。数据库级别的是对全库所有的表生效，影响范围太广。如果只想对某几张表生效，产生附加日志，那么就使用表级即可。
However, if you enter the following SQL statement (specifying table-level supplemental logging) instead, then only when a column in the employees table is changed will the entire row (except for LOB, LONGs, and ADTs) of the table be placed in the redo log file. If a column changes in the departments table, then only the changed column will be placed in the redo log file.
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Keep the following in mind when you use table-level identification key logging:
If the database is open when you enable identification key logging on a table, then all DML cursors for that table in the cursor cache are invalidated. This can affect performance until the cursor cache is repopulated.
Supplemental logging statements are cumulative. If you issue the following SQL statements, then both primary key and unique index key table-level supplemental logging is enabled:
ALTER TABLE HR.EMPLOYEES
ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE HR.EMPLOYEES
ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
all
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
primary key
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
仍然采用上述表，a是主键。
session 1:
SQL> ALTER TABLE logminer ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table altered.
SQL> update logminer set b=32 where c=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set a=37 where c=4;
1 row updated.
SQL> commit;
Commit complete.
复制session n:
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 32 "B" = 66
where where
"A" = 66 and "A" = 66 and
"B" = 66 and "B" = 32 and
ROWID = 'AAAFsXAAOAAAAClAAC'; ROWID = 'AAAFsXAAOAAAAClAAC';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"A" = 37 "A" = 4
where where
"A" = 4 and "A" = 37 and
ROWID = 'AAAFsXAAOAAAAClAAD'; ROWID = 'AAAFsXAAOAAAAClAAD';
复制
和数据库级的primary相同，都会强制记录主键。
unique
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
仍然采用上述表，（b,d）列组合唯一约束。
session 1：
SQL> ALTER TABLE logminer drop SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Table altered.
SQL> ALTER TABLE logminer ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Table altered.
SQL> update logminer set b=32 where c=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> update logminer set a=87 where c=6;
1 row updated.
SQL> commit;
Commit complete.
复制session n：
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"B" = 32 "B" = 77
where where
"B" = 77 and "B" = 32 and
"D" = 5 and "D" = 5 and
ROWID = 'AAAFsXAAOAAAAClAAE'; ROWID = 'AAAFsXAAOAAAAClAAE';
update "TEST1"."LOGMINER" update "TEST1"."LOGMINER"
set set
"A" = 87 "A" = 6
where where
"A" = 6 and "A" = 87 and
ROWID = 'AAAFsXAAOAAAAClAAF'; ROWID = 'AAAFsXAAOAAAAClAAF';
复制
只有更新的字段是任意的约束列时，日志里面才会记录所有约束列。
supplemental logging 的应用
https://www.gxlcms.com/mysql-343111.html
事实上，目前最有可能用到Supplemental Logging的也只有逻辑standby和streams这两项特性(10g以后如果希望logminer能够分析出所有操作，建议也要启用Supplemental Logging)，我们都知道，这两项特性的数据应用都属于逻辑应用，类似source database执行update语句之类，结果也要反映到target database，如何准确判断出source端修改的列，在target端中应该更新到哪条记录上呢？根据rowid？显然不靠谱啊，都说了是逻辑的，块的位置完全不一样，rowid已无用武之地~~~你想说，能否通过表中的唯一键？在更新表中记录时，将主键或唯一键做为附加的信息，与被修改的列一起存入redolog中，对于没有主键唯一键的表，则将表中所有列统统组合做为附加信息存入redolog，这样target端在分析redolog时，就知道该更新表中的哪些记录了！
Supplemental Logging的场合是逻辑standby和streams，
总结：
-
supplemental_log_data_min=yes
redo里面只会记录发生更新的列，不会包含其他列。 -
supplemental_log_data_pk=yes，也会隐式开启supplemental_log_data_min=IMPLICIT。
分两种情况：
- 表上没主键
redo里面会记录全部的列，从而把全部列组合起来当作主键。（还有很多限制情况，我们只说普通的） - 表上有主键
alter table logminer add constraint pk_logminer primary key(a);
在一包含主键的行被更新时，数据库记录行的所有主键列的前镜像记录到redo log中。（第2条sql语句）
即使sql语句没有更新主键的值，所有主键列也会被记录，其他包含的列就是更新自己的列了。（第1条sql语句）
即在此种情况下，主键列会被强制记录，不管主键有没有被更新。
- supplemental_log_data_ui=yes，也会隐式开启supplemental_log_data_min=IMPLICIT。
create unique index idx_logminer_t1 on logminer(b,d);
只有(b,d)中的任意某列被DML了，才会将(b,d)两列全部记录到REDO中。
只要唯一键被修改，redo里面会记录所有唯一键的列。sql语句如果不涉及修改唯一键，只记录了改变的字段的值。
- supplemental_log_data_all
- User-defined conditional supplemental log groups
有条件的追加日志，类似与第3种情况，只有更新的列是设置的groups中的任意列，才会记录groups中的列。
6. User-defined unconditional log groups
无条件的追加日志，类似第2种情况，强制记录的，不管有无更新的列是groups中的任意列，都会强制记录groups中的列。
因为我们更新语句往往不是更新主键列，而没有主键信息的话，就不能唯一确定一行。因为ROWID在两个逻辑库间是没有意义的。能够在更新非唯一性字段时，能把唯一性的键值（如主键）也记录辅助日志中，以实现不同数据库间的同步。意思是可以补充主键，也可以补充唯一性索引日志。
- 如果表中没有主键或非空的唯一性约束（除去LONG和LOB列），那么将记录所有字段作为辅助日志。
- 如果有主键或非空的唯一性约束（除去LONG和LOB列），则只记录主键列或非空的唯一性约束列作为辅助日志。
- table Identification Key Logging
和数据库级别的规则完全相同，就只是生效的范围不通而已。额外记录的列情况，同第2，3，4种情况
supplemental logging，顾名思义，就是附加日志，额外会在redo中添加的日志，因为普通的update记录，只会记录更新值得字段变化和rowid，但是在不同库之间，rowid就不能用了，只凭一个更新字段，无法确定唯一的一行。所以以上测试的重点，就是在不同的情况下，为了确定修改的唯一一行记录，redo中到底还会额外存储哪些列。通过lognminer，可以清晰的看到，不同的场景，redo里面会额外存储的列也不同，因为触发的条件不同。
根据以上测试，容易混肴，我们应该记住这些附加日志没开启的时候，redo里面是如何记录这个更新记录的。
本身只记录修改字段的前镜像。
update logminer set a=1 where a=1; -->update logminer set a=1 where a=1 and rowid=;
update logminer set a=1 where c=1; -->update logminer set a=1 where a=1 and rowid=;
update logminer set a=1 where c=1 and b=2 and d=5; -->update logminer set a=1 where a=1 and rowid=;
其实，开启附加日志，就是为了能够唯一确定一行，粒度不同，记录的列数量也就不一样。
