Enable or disable supplemental logging in Oracle

Enable or disable supplemental logging in Oracle

Supplemental logging ensures that the Oracle redo log on the source database contains the information required to describe all data changes completely. Enabling supplemental logging is a requirement for all redo log mining replication solutions.

For Simple use for replication and Dataguard services you enable database logging.

-- Check
SELECT supplemental_log_data_min from v$database;
-- Enable
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Disable
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

For further details:

You can enable at two level:
DATABASE LEVEL
TABLE LEVEL

Check the supplemental logging is active

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;

Type of logging describe:

Minimal Supplemental Logging
Minimal supplemental logging ensures that products leveraging LogMiner technology will have sufficient information to support chained rows and cluster tables.

Enable and disable the supplemental log

-- Enable
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Disable
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Primary Key Supplemental Logging
Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Unique Key Supplemental Logging
Unique key supplemental logging includes all columns for a unique key are written to undo if any unique key columns are modified.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Foreign Key Supplemental Logging
Foreign key supplemental logging includes all other columns belonging to a foreign key will be logged in the undo if any foreign key columns are modified.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

All Column Supplemental Logging
If no primary key or unique key is available then it is possible to specify that all columns are logged. In this case all columns in a row will be logged in the undo. When the row is replicated in the target database, equality predicates will be applied to all columns. LONG, LONG RAW and LOB columns will be omitted from the supplemental logging.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Procedural Replication Supplemental Logging
Procedural replication supplemental logging includes additional information in the redo log during invocation of procedures in Oracle-supplied packages for which procedural replication is supported. I have never investigated this option.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION;

Advertisements

1 thought on “Enable or disable supplemental logging in Oracle

  1. FirstErik

    I have noticed you don’t monetize your website, don’t waste your
    traffic, you can earn additional cash every month because you’ve got hi quality content.
    If you want to know how to make extra bucks, search for:
    Mrdalekjd methods for $$$

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.