mysql有表里附加日志_Oracle 补充日志

附加日志(supplemental

log)可以指示数据库在日志中添加额外信息到日志流中，以支持基于日志的工具，如逻辑standby、streams、GoldenGate、LogMiner。可以在数据库和表上设置。

1.数据库级设置，分两类：

1.1最小附加日志(minimal supplemental logging)：

DATA选项启用最小附加日志。启用最小日志可以确保LogMiner(或其他任何基于LogMiner的产品)可以支持行链接、簇表、索引组织表等。

语法如下:

alter database {add|drop} supplemental log data;

1.2标识键日志(identification key logging)：

DATA(all,primary key,unique,foreign key)

columns选项启用最小日志及列数据日志。在源库日志为变化来源同步其他数据库的情况下，比如逻辑备用数据库，受影响的数据行必须以列数据标识(而不是rowid)，必须启用此种附加日志。

语法如下:

alter database {add|drop} supplemental log {data(all,primary

key,unique,foreign key) columns};

1.3缺省情况下，Oracle不启用以上任何附加日志。当使用ALL,PRIMARY,UNIQUE或FOREIGN附加日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

在删除所有导致IMPLICIT最小化附加日志的附加日志后，最小化附加日志变为NO。

1.4查询当前设置

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;

MIN PK UI FK ALLC

YES NO NO NO NO

2,表级附加日志设置，分两类

2.1，可以通过以下语句设置命名日志组：

语法：

alter table table_name

add supplemental log group group_a(column_a [no

log],column_b,...) [always];

NO LOG选项用于指定在日志中排除哪些列。在命名日志组中，至少存在一个无”NO LOG“的定长列。比如，对LONG列使用

no log选项，可以在更改LONG列时，记录其他列的内容(LONG列本身不能存在日志里)。

ALWAYS选项, 在更新时，日志组中的所有列都会记录在日志中。这就是所谓的”无条件“日志组，有时也叫”always log

group“。如果不指定该选项，只有在日志组中的任何列被修改时，所有列才会出现在日志中。这就是所谓的”有条件“日志组。

说明：同一列可以在多个日志组中存在，但日志中只记录一次；同一列在“无条件”与“有条件”日志组中存在时，该列将“无条件”记录。

2.2，可以通过以下语句设置所有列或主键/外键/唯一键组合日志组：

语法：

alter table table_name

add supplemental log data(all,primary key,unique,foreign key)

columns;

Oracle将生成无条件或有条件日志组。对于无条件日志组，日志中将记录该日志组中的所有列；对于有条件日志组，只有日志组中的列有变化时，才会记录日志组中的所有列。

如果指定“ALL”列，日志中将包含所有最大大小固定长度的列。这种日志是系统创建的无条件日志组。

如果指定“PRIMARY

KEY”列，只要有更新，组成主键的所有列都会记录在日志中。这种日志是系统创建的无条件日志组。Oracle使用如下顺序确定附加记录哪些列：

*组成主键的列(主键有效，或rely且非DISABLED or INITIALLY DEFERRED状态)

*最小的、至少有一个非空列的唯一索引

*记录所有标量列

如果指定“UNIQUE”列，如果任何组成唯一键或位图索引的列被修改，组成该唯一键或位图索引的其他列都会记录在日志中。这种日志是系统创建的有条件日志组。

如果指定“FOREIGN

KEY”列，如果任何组成外键的列被修改，组成该外键的其他列都会记录在日志中。这种日志是系统创建的有条件日志组。

2.3表级测试

drop table test;

create table test(x int,y int);

--增加附加日志

alter table test

add supplemental log data(all,primary key,unique,foreign key)

columns;

select * from dba_log_groups;

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS

GENERATED

SYS SYS_C0037555 TEST ALL COLUMN LOGGING ALWAYS GENERATED

NAME

SYS SYS_C0037556 TEST PRIMARY KEY LOGGING ALWAYS GENERATED

NAME

SYS SYS_C0037557 TEST UNIQUE KEY LOGGING CONDITIONAL GENERATED

NAME

SYS SYS_C0037558 TEST FOREIGN KEY LOGGING CONDITIONAL

GENERATED NAME

select * from dba_log_group_columns;

no row selected

--删除附加日志

alter table test

drop supplemental log data(all,primary key,unique,foreign key)

columns;

select * from dba_log_groups;

no row selected

select * from dba_log_group_columns;

no row selected

--增加命名附加日志

alter table test

add supplemental log group group_a(x,y);

select * from dba_log_groups;

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS

GENERATED

SYS GROUP_A TEST USER LOG GROUP CONDITIONAL USER NAME

select * from dba_log_group_columns;

OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION

LOGGING_PROPERTY

SYS GROUP_A TEST X 1 LOG

SYS GROUP_A TEST Y 2 LOG

alter table test

drop supplemental log group group_a;

补充日志：只要是针对 UPDATE 命令的，是对重做日志记录中

变更矢量块的补充信息，增加了变更矢量记载的记录量。日志挖掘器(LogMiner)、闪回事务查询、闪回事务等都需要

补充日志的支持。尤其是 日志挖掘器如果发现未启用补充日志，就拒绝服务。也就是说补充日志主要是为UPDATE

命令服务的，补充的目的是高度还原 UPDATE 命令，避免因为update

命令造成的行迁移和行移动，让LogMiner 通过分析重做日志中识别 update 命令 不是 由

insert 和 delete  完成的。如果未启用补充日志，重做日志只将 UPDATE

命令更改的字段的旧值保存在撤销数据块的变更矢量中。而在数据块中的变更矢量中记载被修改后的字段的新值。同行中的未被修改的字段，不会被记载。如果启用了补充日志，重做日志中的撤销数据块的变更矢量中会记录

被修改字段前的值和修改后的值，而且还会记录修改字段的那个条件的值。即变更矢量中会记载：几号数据文件+几号文件中的几号块+第几个字段+修改后的值+修改前的值+“where

条件” 的值。

行移动： 指 update

命令执行时时，在索引组织表中有时会导致整行被转移至另一个索引块中。该情况或过程称为行移动。

行迁移：如果是在堆栈表中，update

命令导致行太长，在数据块空间不足的情况下会将部分(行头部除外)迁移到另一个数据块中，行头任然保留在原数据块中。

补充日志分为：数据库级补充日志、 表级补充日志

其他补充日志都是基于数据库级最小补充日志之上的，如果未启用数据库级最小补充日志，在启用或使用其他补充日志时，会自动启用数据库的最小补充日志(Implicit)。

数据库级补充日志分为：最小补充日志、标识关键字段补充日志

最小补充日志：是最基本的一种数据库级补充日志，而 LogMiner 正是依赖最小补充日志工作服务的(即识别 行移动

行迁移)。

启用最小补充日志命令：ALTER DATABASEADD Supplemental LOG DATA;

关闭最小补充日志命令：ALTER DATABASEDROP SupplementalLOG DATA;

标识关键字段补充日志：分为主键、外键、唯一索引、全体字段 补充日志4种。

主键补充日志：

在update 命令的重做记录中添加被修改行的主键字段的旧值，无论是否被修改，都记录。

如果表没有主键，则由长度最小的唯一索引字段代替，若连唯一索引也没有，则记录该行所有字段。

alter database add supplemental log data(Primary key)

columns;

唯一索引补充日志：

唯一索引主要是为 复合索引(唯一)服务的。只有唯一索引的字段被 update 时，才会记录该字段被修改前的值。

alter database add supplemental log data(unique)

columns;

外键补充日志：

同唯一索引补充日志一样，只有外键字段被 update 时，才会记录被修改前的旧值，即也是为复合外键服务的。

alter database add supplemental  log

data(foreign key ) columns;

全体字段补充日志：

顾名思义就所有字段的值不论是否被修改都记录。会导致磁盘快速增长，LGWR进程繁忙。不建议使用。

alter database add supplemental log data(all) columns;

这 4 种补充日志可以并行使用，效果累加。

表级补充日志分为： 主键、唯一索引、外键、全体字段、用户自定义字段 5种。

前 4 种 同数据库级标识关键字段补充日志效果用法一样。只不过是在特定表上启用补充日志。

1、 altertable tb_name add supplemental log data(primary key)

columns;

2、alter  table tb_name add supplemental log

data (unique) columns;

3、 altertable tb_name add supplemental log data(foreign key)

columns;

4、 altertable tb_name add supplemental log data(all)

columns;

5、 altertable tb_name add supplemental loggroup group_name

(col01,col02,col05,col09) | always;

自定义字段表级补充日志：

用户可以任意指定哪些字段的旧值需要被补充日志记录。

有条件日志组：只要补充日志组中至少一个列被修改(update)，那么就要记录所有(日志组)指定列的前镜像(旧值)。

无条件日志组：当表被修改时要记录所有(日志组)指定列的前镜像(旧值)，无论改变是否对任一(日志组)指定列造成影响。需添加

always 关键字。

官方文档说明：

conditional log group

A supplemental log group that logs the before images of all

specified columns only if at least one of the columns in the

supplemental log group is modified.

unconditional log group

A supplemental log group that logs the before images of

specified columns when the table is changed, regardless of whether

the change affected any of the specified columns.

表级补充日志的情况 通过 dba_log_groups 和 dba_log_group_columns

视图查询获得

补充日志不是独立的日志，而是对重做日志变更矢量的补充。LogMiner、闪回事务、闪回事务查询等功能需要开启补充日志才能正常工作。

补充日志可分为两类数据库级别补充日志和表级别补充日志，数据库级别补充日志可分为最小补充日志、主键补充日志、唯一索引补充日志、外键补充日志、全体补充日志6种，表级别补充日志在数据库级别补充日志的基础上增加了一种自定义补充日志，没有最小补充日志。

因为insert会记录所有修改后字段，delete会记录所有修改前字段，所以补全日志只对update产生影响，开启补全日志时共享池中的SQL游标都会失效，他们的影响和功能如下：

最小补充日志

主键补充日志

唯一补充日志

Update操作的影响

产生特殊的日志信息，可以识别update命令产生的行迁移和行转换。Logminer和闪回事务查询功能需要最小补全日志支持。打开最小补全日志会使共享池SQL游标失效。

无条件补充日志，无论主键是否被修改旧值都会被记录到日志中。没有主键则记录长度最小唯一非空索引，没有非空唯一索引则记录所有字段。依赖最小补全日志，开启主键补充日志自动开启最小补充日志。关闭最小补全日志自动关闭主键补全日志

有条件补充日志，当复合唯一索引的其中一个字段被修改时，唯一索引的所有字段旧值都会被记录。唯一补充日志专门为复合唯一索引服务。

开启/关闭

开启/关闭：

alter database add/drop supplemental

log data。

开启/关闭(数据库级)：

alter database add/drop supplemental

log data (primary

key) columns。

开启/关闭(表级)：

alter table T1 add/drop supplemental

log data (primary

key) columns。

开启/关闭(数据库级)：

alter database add/drop supplemental

log data

(unique) columns。

开启/关闭(表级)：

alter table T1 add/drop supplemental

log data

(unique) columns。

依赖

无

依赖最小补全日志

依赖最小补全日志

外键补充日志

全体补充日志

自定义补充日志(表)

Update操作的影响

有条件补充日志，当复合外键索引的其中一个字段被修改时，外键索引的所有字段旧值都会被记录。唯一补充日志专为复合外键索引服务。

无条件补充日志，表的所有字段旧值都会被记录(除了LOB、LONG类型)。

有/无条件补充日志，设置多个字段为日志组，日志组中的任何字段被修改，整个日志组字段的旧值都记录在日志中。

开启/关闭

开启/关闭(数据库级)：

alter database add/drop supplemental

log data (foreign

key) columns。

开启/关闭(表级)：

alter table T1 add/drop supplemental

log data (foreign

key) columns。

开启/关闭(数据库级)：

alter database add/drop supplemental

log data (all)

columns。

开启/关闭(表级)：

alter table T1 add/drop supplemental

log data (all)

columns。

开启/关闭(表级)：

alter table T1 add/drop supplemental

log group GROUPNAME(C1,C2,C3)

allways。

依赖

依赖最小补全日志

依赖最小补全日志

依赖最小补全日志