oracle的补全日志--Supplemental Logging

补充日志不是独立的一种日志，是对重做记录中变更矢量的补充信息，增加了变更矢量记载的记录量，Oracle数据库某些功能要求启用补充日志才能正常地或更好的工作，如logminer、DG、闪回事务查询、闪回事务。

Oracle中insert、delete命令在默认情况下产生的重做记录足以表明被修改的行的每个字段在被修改前后的值是什么，insert，变更前行不存在，产生的重做记录会记载新行的每个字段的值，delete，变更后不存在，但是由于重做记录也要负责产生撤销数据块的变更，所以行被删除之前的各字段的值也记录在撤销数据块的变更矢量中，也就是insert和delete命令的重做记录中，能找到整行的所有信息。

但是update不同于insert和delete一定会涉及一行的所有字段，常常只是更改一个或几个字段，处于对性能的考虑，Oracle没必要把修改的字段的值也保存在变更矢量中，事实上是在撤销块的变更矢量中只记载被修改的字段的更改前的值，而在数据块的变更矢量中则记载了被修改的字段的新值，同一行中其他没有被修改的字段，不会记载其修改前的值，因为没有被修改。

Oracle日志（redo log）一般用于实例恢复和介质恢复，但是如果需要靠日志还原完整的DML操作信息（比如Logmnr、Streams和这里的Goldengate），默认记录的日志量还不够。比如一个UPDATE的操作，默认redo只记录了rowid以及被修改的字段信息，但这里GoldenGate还原这个事务，因为不是根据rowid而是SQL层面根据唯一键值来定位记录，所以还需要将主键或者其他字段的信息附加到日志中去。要往日志中增加这些额外字段信息的操作，就是开启补全日志，即Add Supplemental Logging。打开补全日志，会使数据库的日志量增加，所以只打开需要的级别和对象即可。

Oracle补全日志可以在数据库级别设置，也可以在表级别设置。在数据库级别中，补全日志按补全的信息量，对应好几个级别：

（1）最小附加日志（Minimal supplemental logging）：是开启logmnr的最低日志要求，提供了行链接（chained rows）和多种数据存储（比如聚簇表、索引组织表）的信息。在Oracle 9.2之后的版本中，默认都不开启。

（2）主键补全（Primary key supplemental logging）：在日志中补全所有主键列。如果表中无主键，则补全一个非空唯一索引列；如果非空唯一索引键也没，那么会补全除了LOB和LONG类型字段以外的所有列，这时就和下面的所有补全一样了。

（3）唯一键补全（Unique key supplemental logging）：当唯一键列或位图索引列被修改时，在日志中补全所有唯一键列或位图索引列。打开唯一键补全也会同时打开主键补全。注意这个级别是需要条件触发的。

（4）外键补全（Foreign Key supplemental logging）：当外键列被修改时，将在日志中补全所有外键列。这个级别也是需要条件触发的。

（5）所有补全（All supplemental logging）：在日志中补全所有字段（排除LOB和LONG类型）。

数据库级别中的5个类型中，除了最小附加日志级别，都可以在表级进行设置。除此之外，表级还可以明确指定需要补全的列。

Oracle表级补全日志需要在最小补全日志打开的情况下才起作用，即若一个数据库没有开最小补全日志或之前drop supplemental log data操作则即便指定了表级补全日志，实际在重做日志输出的过程中描述的记录仍只记录rowid和相关列值。而要关闭最小补全日志，也必须首先关闭数据库级别的其他补全级别后，才能关闭。

打开数据库级别的最小补全日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;  

查看是否开启
select supplemental_log_data_min min from v$database ;

关闭最小补全日志
alter database drop supplemental log data ;

标识关键字段补充日志有四种：主键、唯一索引、外键、全体字段补充日志

（1）主键补充日志

主键补充日志的作用是在update命令的重做记录中添加被修改行的主键字段的旧值，这是无条件式的补充日志，所谓无条件即无论主键字段本身是否被update命令修改，其旧值都会被记录。但是，不能保证每张表一定有主键。如果存在没有主键的表，则主键字段由长度最小的非空唯一索引字段代替。如果表结构中一个非空索引字段都没有，那么oracle将被修改行的所有字段(除了lob和long类型)的旧值都记录下来，这将导致重做记录的数据量暴涨，所以如果要启用主键补充日志，又为了维护lgwr和重做日志，每张表最好具有主键或至少一个非空唯一字段。

启用主键补充日志：
alter database add supplemental log data (primary key) columns ;
 
查看是否开启
select supplemental_log_data_pk as pk from v$database ;

标识关键字段补充日志(包括主键补充日志)必须建立在最小补充日志的基础上，当其被启用时，若最小补充日志尚未启用，则oracle会隐式开启最小补充日志 状态为IMPLICIT，同样在没有关闭标识关键字段补充日志的时候，不能先关闭最小补充日志。

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 "all"
  from v$database;

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 "all"
  6    from v$database;

MIN	 PK  UI  FK  all
-------- --- --- --- ---
YES	 NO  NO  NO  NO

SQL> alter database drop supplemental log data ;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min from v$database;

MIN
--------
NO

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 "all"
  6    from v$database;

MIN	 PK  UI  FK  all
-------- --- --- --- ---
IMPLICIT YES NO  NO  NO


SQL> alter database drop supplemental log data ;
alter database drop supplemental log data
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging

--删除主键补充日志
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;  


SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;  

Database altered.

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 "all"
  6    from v$database;

MIN	 PK  UI  FK  all
-------- --- --- --- ---
NO	 NO  NO  NO  NO

（2）唯一索引补充日志

只有当唯一字段被update时，才会记录该字段被修改前的值，因为唯一键索引是能够建立在多个字段上的。

打开唯一索引补充日志
alter database add supplemental log data (unique) columns ;

查看是否打开
select supplemental_log_data_ui ui from v$database ;

删除唯一索引补全日志
alter database drop supplemental log data (unique) columns ;

如：
create unique index name_idx on hr.employees(first_name,last_name);

alter database add supplemental log data (unique) columns ;
 
SQL> select supplemental_log_data_ui ui from v$database ;
 
UI
---
YES

update 命令现在更新first_name，常规日志会只记载first_name字段的旧值，而唯一键索引将会记录first_name和last_name，所以这种补充日志完全可以说是为复合字段唯一索引服务的。

（3）外键补充日志

外键补充日志和唯一索引补充日志一样同为有条件式的，只有当外键字段被update命令修改时，其修改前的旧值才会被记录。所以这种补充日志完全是为复合字段外键服务的。

打开外键补全日志
alter database add supplemental log data (foreign key) columns ;

查看是否打开
select supplemental_log_data_fk as fk from v$database ;

删除外键补全日志
alter database drop supplemental log data (foreign key) columns ;

（4）全体字段补充日志

全体字段补充日志和主键补充日志一样为无条件式的，无论哪个字段被update命令修改，所有字段(除了lob，long类型)的旧值都将被记录，其效果相当于启用了主键补充日志的前提下既没有主键也没有非空唯一索引字段的情况，这样几乎所有的表数据都搬到了重做日志中，不但存在当前的，历史数据也没有丢下。对恢复操作来说比较好，但是对于lgwr和磁盘空间就不是太好，一般很少启用这样的日志。

打开all补全日志
alter database add supplemental log data (all) columns; 

查看是否打开
select supplemental_log_data_all as all from v$database ;

删除all补全日志
alter database drop supplemental log data (all) columns; 

数据库级的补充日志，共同点是启用和关闭导致所有SQL游标非法。无论需要启用哪一种标识关键字段补充日志，都需要启用最小补充日志，4种标识关键字段的效果可以累加，不冲突。

alter database add supplemental log data (primary key,unique,foreign key,all) columns ;

alter database drop supplemental log data (primary key,unique,foreign key,all) columns ;

表级补充日志

只是针对某个表，没有必要在整个数据库范围启用补充日志功能，在启用表级补充日志之前，应该先启用数据库级最小补充日志。表级补充日志分为主键、唯一索引、外键、全字段和自定义5种类型，前四种和数据库级对应的类型特点一致，只是在特定的表上启用。

alter table scott.emp add supplemental log data (primary key) columns ;

alter table scott.emp add supplemental log data (unique) columns ;

alter table scott.emp add supplemental log data (foreign key) columns ;

alter table scott.emp add supplemental log data (all) columns ;

自定义字段是表级补充日志特有的功能，用户可以任意指定哪些字段的旧值需要被补充记录。

alter table hr.employees add supplemental log group emp_info (first_name,last_name,email) ;

以上将first_name,last_name,email字段指派为一个名为empinfo的日志组，意思就是只要update命令修改了这3个字段中的任意一个，重做记录必须记载全部3个字段的旧值这称为有条件式的，无条件式的要加关键字always

alter table hr.employees add supplemental log group emp_info (first_name,last_name,email) always ;


表级补充日志的情况可以查询dba_log_groups和dba_log_grup_columns

select g.log_group_name name,
       g.owner || '.' || g.table_name table_name,
       g.always,
       g.generated,
       c.column_name
  from dba_log_groups g, dba_log_group_columns c
 where g.log_group_name = c.log_group_name(+)
   and g.table_name = c.table_name(+)
   and g.table_name = 'EMPLOYEES'
   and g.owner = 'HR'
 order by 1, 2, 3, 4;

特定表上的表级补充日志的启用与关闭会导致所有引用该表的SQL游标非法，会引起一段时间的硬分析增加。

补充日志主要为update服务，额外记录指定字段的旧值，使得有能力分析重做日志的工具可以高度还原update命令，由于额外记录了字段的旧值，也能将其看成一种特殊的备份。