SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO YES IMPLICIT
打开ALL类型的补充日志不包含外键补充日志，ALL表示的是ALL COLUMN。
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
数据库已更改。
SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES YES IMPLICIT
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
数据库已更改。
SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES YES YES
以上的查询结果表示打开了所有类型的补充日志。
由此实验我们可以得出，Oracle数据库补充日志分为：最小化补充日志，主键补充日志，外键补充日志，唯一键补充日志，ALL类型的补充日志。
执行ALTER DATABASE SQL语句打开数据库补充日志后，告警日志会显示补充日志打开的情况：
SUPLOG: Commencing to rollback failed DDL at scn = 13732025618576
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
另外，还需要说明一点的是如果对非常繁忙的数据库启动补充日志可能会带来性能问题和出现Hang住的情况，下面通过一篇metalink文章来说明该问题：
Alter Database Add Supplemental Log Data Hangs (文档 ID 406498.1)
修改时间:2013-2-6 类型:PROBLEM
In this Document
Symptoms
Changes
Cause
Solution
References
--------------------------------------------------------------------------------
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
As part of the Streams setup you can specify supplemental logging at the database level.
It can be done manually:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
or implicitly running procedures like DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
or if you setup Streams using Grid Control it is automatically executed by SrcSchemaRule1 Stage Step 12.
In some cases, this step hangs and the statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA remains waiting for TX lock in shared mode.
CHANGES
CAUSE
The statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA is waiting for TX lock in shared mode when there is any uncommitted transaction. This is the expected behavior.
You can issue ALTER DATABASE ADD SUPPLEMENTAL LOG DATA when the database is open. However, OracleDatabase will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. Besides,we also need to wait for the completion of all the in-flight transaction so that any redo generated after this DDL would have the right supplemental logging attributes.
SOLUTION
You need to wait for the completion of all the in-flight transaction.
In those databases with high activity where there are always active transactions the supplemental logging can be enabled by bouncing the database and running the statement manually:
STARTUP MOUNT