How To Completely Disable Supplemental Logging At Table/ DB/ Schema Level (Do...

郑全

Applies to:  

Oracle GoldenGate - Version 11.2.1.0.0 and later

Information in this document applies to any platform.

Goal

How to cleanly  delete trandata  and drop supplemental log?

Solution

When  supplemental logging has been disabled at the db level, the following statement should give the result below:

select supplemental_log_data_min
,supplemental_log_data_all
,supplemental_log_data_pk
,supplemental_log_data_ui
,supplemental_log_data_pl
from v$database

SUPPLEMENTAL_LOG_DATA_MIN|SUPPLEMENTAL_LOG_DATA_ALL|SUPPLEMENTAL_LOG_DATA_PK|SUPPLEMENTAL_LOG_DATA_UI|SUPPLEMENTAL_LOG_DATA_PL
NO|NO|NO|NO|NO

To disable supplemental logging at the db level, we must execute following statements in the same order :

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Please read the documentation:
===>http://docs.oracle.com/cd/E11882 ... gminer.htm#i1021068
====>19 Using LogMiner to Analyze Redo Log Files
========>•Supplemental Logging
=============>Disabling Database-Level Supplemental Logging

To disable supplemental logging at the table level we need to execute GGSCI command DELETE TRANDATA.

GGSCI> DBLOGIN USERID <id> PASSWORD <pw>

GGSCI> DELETE TRANDATA <schema.table name>

This would delete the records from  DBA_LOG_GROUP_COLUMNS and DBA_LOG_GROUPS.

The command  DELETE TRANDATA is supposed to be equivalent to the following SQL statement:

alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]

So after the DELETE TRANDATA,  records would be removed from DBA_LOG_GROUP_COLUMNS and DBA_LOG_GROUPS.

To disable supplemental logging at the schema level we need to execute GGSCI command DELETE SCHEMATRANDATA

Presence of schema level logging can be verified by using the INFO SCHEMATRANDATA command

GGSCI> INFO SCHEMATRANDATA <schema name>

GGSCI> DELETE SCHEMATRANDATA <schema name>