系统产生大量redo,怀疑与昨天测试streams ,suppolemental log 有关!

系统产生大量redo,归档增加600多倍。怀疑与昨天测试streams ,suppolemental log 有关!

SQL>  select SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_PK from v$database;

NO  NO  NO

SQL> SELECT supplemental_log_data_min^KFROM v$database;

YES

SQL> ALTER DATABASE^KDROP SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT supplemental_log_data_min^KFROM v$database;

NO

SQL> select * from DBA_LOG_GROUPS;

OWNER                          LOG_GROUP_NAME
------------------------------ ------------------------------
TABLE_NAME                     LOG_GROUP_TYPE      ALWAYS      GENERATED
------------------------------ ------------------- ----------- --------------
SVC_CONTRACT                   SYS_C00368574
CONTRACT_OWNER                 PRIMARY KEY LOGGING ALWAYS      GENERATED NAME

SVC_CONTRACT                   SYS_C00368575
CONTRACT_OWNER                 FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME

SVC_CONTRACT                   SYS_C00368576
CONTRACT_OWNER                 UNIQUE KEY LOGGING  CONDITIONAL GENERATED NAME

SVC_CONTRACT                   SYS_C00368577
CONTRACT_STATE                 PRIMARY KEY LOGGING ALWAYS      GENERATED NAME

SVC_CONTRACT                   SYS_C00368578
CONTRACT_STATE                 FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME

SVC_CONTRACT                   SYS_C00368579
CONTRACT_STATE                 UNIQUE KEY LOGGING  CONDITIONAL GENERATED NAME
..........

alter table svc_contract.CENTER_CUSTOMER_LINK       drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.CONTRACT_STATE_HISTORY     drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.CONTRACT_OWNER             drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.SERVICEASSOCIATION         drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.CONTROLCENTER              drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.SERVICE_ASSOCIATION_STATE  drop supplemental log data (primary key,unique,foreign key) columns;
alter table svc_contract.SUNCONTRACT_TOS_LINK       drop supplemental log data (primary key,unique,foreign key) columns;

..................

发现日志果然不那么快的产生了。

suppolemental log 一方面是为LogMiner需要，另一方面与stream有关。
启用suppolemental log可以使LogMiner支持：chained rows，clustered tables，index organized tables (IOTs)
可以在四个级别启用suppolemental log：
For all columns ：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
For primary key columns：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
For unique columns：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
For foreign key columns：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
查看启用的级别：
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk,supplemental_log_data_all FROM v$database;
SELECT log_group_name, owner, table_name,
DECODE(always, 'ALWAYS', 'Unconditional',
NULL, 'Conditional') ALWAYS
FROM DBA_LOG_GROUPS