Oracle数据库开启附加日志，用于Logminer或基于Logminer的一些操作。 客户咨询关于开启附加日志，SUPPLEMENTAL_LOG_DATA_MIN显示为啥是implicit，如何改成yes呢？ 因为要同步数据给某国产库，有对应工具要求开启附加日志，可以简单理解为类似ogg的同步工具吧，调试时有报错，国产厂商认为是implicit不为yes导致。
首先查阅Oracle官方文档，看下SUPPLEMENTAL_LOG_DATA_MIN这个字段的含义：
Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables: NO - None of the database-wide supplemental logging directives are enabled. In a CDB, a value of NO means that minimal supplemental logging is not enabled in all of the PDBs in the CDB. IMPLICIT - Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled YES - Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement. In a CDB, a value of YES means that minimal supplemental logging is enabled in all of the PDBs in the CDB. See Also: Oracle Database SQL Language Reference for additional information about the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement
可以清楚的看到IMPLICIT的意思也是启用了，只不过是通过启用具体主键、唯一键、外键中的一个或多个实现的，所以implicit意思也就是含蓄、隐含的算设置了，有什么区别/影响后面再说。 现在我们先查询下当前这些设置吧：
代码语言：javascript复制select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;1. 数据库默认配置
代码语言：javascript复制如果没有设置任何附加日志，结果应该都是NO：
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
NO NO NO NO NO2. 检查客户的设置情况
代码语言：javascript复制客户这里的情况是：
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
IMPLICIT YES NO NO YES3. 模拟客户的设置
代码语言：javascript复制因此推断客户应该之前设置了：
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(all) columns;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(primary key) columns;
测试环境设置这两项后，结果和客户显示一致。4. 设置SUPPLEMENTAL_LOG_DATA_MIN
代码语言：javascript复制那么如何将SUPPLEMENTAL_LOG_DATA_MIN也设置为yes呢？
上面文档也说了，可以执行：
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
设置，测试环境执行后设置成功：
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES NO NO YES但有意思的是，客户去自己环境这样执行，显示成功，再次查询还是没有设置成功，怀疑是OGG运行有影响，去另外节点执行就成功修改了。
最后，先不管客户用的这类小众的同步工具是怎样，因为也不普遍，我们就看看OGG是否也有这种情况，查了下MOS，还真有因为IMPLICIT的设置ogg有问题的这类情况：
- Does OGG Support IMPLICIT db level Supplemental Logging (Doc ID 2039204.1)
文章中提到，
GOAL Clarify and confirm for the user if OGG supports implicit db level supplemental logging. SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database; SUPPLEME SUP SUP SUP SUP
IMPLICIT NO NO NO YES SOLUTION ADD TRANDATA does not work if v$database.supplemental_log_data_min is IMPLICIT The reason is, if you drop the database-wide primary key logging, the minimal setting will revert to NO, and hence Extract's ALWAYS log group will not have any effect GG requires v$database.supplemental_log_data_min to say YES Users can issue the following statement to get the setting of IMPLICIT to change to YES SQL> alter database add supplemental log data;
解决方案中同样也是设置了supplemental_log_data_min为yes解决。
