ON CALL DBA SUPPORT

– Database blog

SUPPLEMENTAL LOGGING

Posted by ssgottik on 29/04/2011

What is supplemental logging?

Redo log files are generally used for instance recovery and media recovery. The data required for instance recovery and media recovery is automatically recorded in the redo log files. However a redo log based application may require that the additional columns need to be logged into redo log files. The process of adding these additional columns into redo log files is called supplemental logging.

Supplemental logging is not the default behavior of oracle database. It has to be enabled manually after the database is created. You can enable the supplemental logging at two levels

  1. DATABASE LEVEL
  2. TABLE LEVEL

What is the user of supplemental logging in replication?

Supplemental logging at the source database side to certain columns are very much required to ensure that those changes which are happened to the columns which are supplemental logging enabled will be applied successfully at the target database. With the help of these additional columns, oracle decides the rows which need to be updated on the destination side. This is how supplement logging is more critical requirement for replication.

What is the role or use of supplemental logging in oracle streams?

In streams, capture process captures the additional information logged in to redo log file using supplemental logging and place them in the LCR (LOGGICAL CHANGE RECORD). Supplemental logging is configured at the source database side. The apply process at the target database side reads these LCR’s to properly apply DML and DDL changes that are replicated from source database side to target database.

If the table has primary key or unique key column defined, only the column which are involved in primary key or unique key column will be registered in the redo logs along with the actual column that has changed. If the table does not have any primary key or unique key defined, oracle will write all columns of the changed row data into the redo log file.

Depending on the set of additional columns logged there are two types of supplemental log groups:

  1. Unconditional supplemental log group
  2. Conditional supplemental log group

 1. UNCONDITIONAL SUPPLEMENTAL LOG GROUP:

 If you want the before image of the column to be logged in to the redo log file  even if there is no changes happen that column and  which have supplemental logging enabled, then we use UNCONDITIONAL SUPPLEMENTAL LOGGING. This is also call ALWAYS LOG GROUP.

 2. CONDITIONAL SUPPLEMENTAL LOG GROUP:

 The before image of all the columns are logged into the redo log file even if at least one of the columns in the supplemental log group is updated.

 DATABASE LEVEL SUPPLEMENTAL LOGGING:

 How to check supplemental logging is enabled or not?

 SQL> SELECT supplemental_log_data_min FROM v$database;

 How to enable supplemental logging at database level?

 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 How to disable supplemental logging at database level?

 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

 TABLE LEVEL SUPPLEMENTAL LOGGING:

 TABLE LEVEL UNCONDITIONAL SUPPLEMENTAL LOGGING: 

  • Primary Key columns
  • All columns
  • Selected columns

 To specify an unconditional supplemental log group for PRIMARY KEY column(s):

 SQL > ALTER TABLE SCOTT. EMP ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 To specify an unconditional supplemental log group that includes ALL TABLE columns:

 SQL > ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 To specify an unconditional supplemental log group that includes SELECTED columns:

 SQL> ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG GROUP t1_g1 (C1,C2) ALWAYS;

 TABLE LEVEL CONDITIONAL SUPPLEMENTAL LOGGING: 

  • Foreign  key
  • Unique
  • Any Columns

To specify a conditional supplemental log group that includes all FOREIGN KEY columns:

 SQL> ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 To specify a conditional supplemental log group for UNIQUE column(s) and/or BITMAP index column(s):

 SQL > ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 To specify a conditional supplemental log group that includes ANY columns:

 SQL>ALTER TABLE SCOTT.EMP  ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);

 To drop supplemental logging:

 SQL > ALTER TABLE <TABLE NAME >DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 SQL>ALTER TABLE <TABLE NAME >DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 SQL> ALTER TABLE <TABLE NAME> DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 SQL> ALTER TABLE <TABLE NAME> DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 VIEWS

 DBA_LOG_GROUPS

 DBA_LOG_GROUP_COLUMNS

Thanks  and  Regards,

Satish.G.S

 

 

About these ads

5 Responses to “SUPPLEMENTAL LOGGING”

  1. Ezhil said

    The commands are very useful and clear to understand.
    How to check supplemental logging is enabled or not at TABLE level ?

    Thanks
    Ezhil

  2. Jemmy said

    To check whether minimal supplemental logging is enabled:
    SELECT supplemental_log_data_min FROM v$database

  3. Mahi said

    Very good article to understand supplement logs basics

  4. Bill said

    This article is very helpful. I want to replicate only 4 tables from source to target, is it necessary to add supplemental log data at both the database and the table level? It seems as though you would only need to do one or the other not both for my scenario. I’m a total newbie in the DBA role, please be gentle.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 52 other followers

%d bloggers like this: