What is Supplemental Logging in Oracle
Oracle Redo log file has the record of all the operation performed in the database by default or automatically.So Redo log files are generally used for instance recovery and media recovery. They are also used for Physical standby or physical replication. But by default Oracle redo logs files does not contain enough information to generate the SQL statement so that it can be executed on another database. It require additional columns to be logged in the redo log files. The process of logging these additional columns is called supplemental logging.
So any redo based application such Golden Gate, Shareplex , Oracle logminer require Supplemental logging to be enabled in order to be work
Why it is required for redo based Applications
You might want to know what exactly supplemental logging do. Supplemental logging generates additional undo which is stored in the redo log. The additional information allows rows to be located when the ROWID is unavailable.Also these additional columns helps oracle decides the rows which need to be updated on the destination side
The following are examples of situations in which additional columns may be needed:
(a) An application that applies reconstructed SQL statements to a different database must identify the update statement by a set of columns that uniquely identify the row (for example, a primary key), not by the ROWID shown in the reconstructed SQL returned by the V$LOGMNR_CONTENTS view, because the ROWID of one database will be different and therefore meaningless in another database.
(b)An application may require that the before-image of the whole row be logged, not just the modified columns, so that tracking of row changes is more efficient.
Types of Supplemental Logging
Supplemental logging is not the default behavior of oracle database. Supplemental logging can be enabled at the database level and table level
Types of Supplemental Logging at the Database Level
(1)Minimal Supplemental Logging : This is minimum supplemental logging require in order for log-miner to work. it allows Log-miner to support chained rows,clustered tables, index organized tables (IOTs). You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
How to enable Minimal Supplemental Logging
sqlplus / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
If the Oracle database is open when you execute this statement, Oracle waits for in-flight transactions to complete, which can affect database performance. This issue is likely to occur for databases that have a high level of user activity. To avoid this problem, you can close and re-open the database and then issue the statement manually.
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:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE OPEN;
How to check if it is enabled
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled.
How to turn off the logging
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
(b)Supplemental Key Logging or Identification key logging
Supplemental Key Logging is the logging which is enabled at the database level. We have four types of them
For all columns
This option specifies that when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file
For primary key columns
This option causes the database to place all columns of a row’s primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).
If a table does not have a primary key, but has one or more non-null unique index key constraints or index keys, then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.
If the table has neither a primary key nor a non-null unique index key, then all columns except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging for that row. Therefore, Oracle recommends that when you use database-level primary key supplemental logging, all or most tables be defined to have primary or unique index keys.
For unique columns
This option causes the database to place all columns of a row’s composite unique key or bitmap index in the redo log file if any column belonging to the composite unique key or bitmap index is modified. The unique key can be due to either a unique constraint or a unique index.
For foreign key columns
This option causes the database to place all columns of a row’s foreign key in the redo log file if any column belonging to the foreign key is modified.
How to enable
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
How to check it
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database;
How to disable it
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
(c)Procedural Replication Supplemental Logging. This is not in used much
Types of Supplemental Logging at the Table Level
- Supplemental logging can be applied at the table level also. it gives the same options as those provided at the database level: all, primary key, foreign key, and unique key.
- This just do logging for that specified table
- For this to happen, it is required that minimum supplemental logging is on at the database level.
How to enable it
ALTER TABLE HR.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER TABLE HR.EMP ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER TABLE HR.EMP ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
Oracle supports user-defined supplemental log groups also. With user-defined supplemental log groups, you can specify which columns are supplementally logged. We can specify conditional or unconditional log group
What is conditional 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.
What is unconditional log groups or Always log Group
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
unconditional log group ALTER TABLE HR.EMP ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID) ALWAYS; conditional log group ALTER TABLE HR.EMP ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID);
How to drop the logging
SQL > ALTER TABLE HR.EMP DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS; SQL>ALTER TABLE HR.EMP DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; SQL> ALTER TABLE HR.EMP DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; SQL> ALTER TABLE HR.EMP DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; SQL>ALTER TABLE HR.EMP DROP SUPPLEMENTAL LOG GROUP emp_fulltime;
How to check it
Oracle has provided two data dictionary views to check on this
SQL> desc DBA_LOG_GROUPS Name Null? Type ---- ---- ---- OWNER NOT NULL VARCHAR2(128) LOG_GROUP_NAME NOT NULL VARCHAR2(128) TABLE_NAME NOT NULL VARCHAR2(128) LOG_GROUP_TYPE VARCHAR2(19) ALWAYS VARCHAR2(11) GENERATED VARCHAR2(14) SQL> desc DBA_LOG_GROUP_COLUMNS Name Null? Type ---- ---- ---- OWNER NOT NULL VARCHAR2(128) LOG_GROUP_NAME NOT NULL VARCHAR2(128) TABLE_NAME NOT NULL VARCHAR2(128) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER LOGGING_PROPERTY VARCHAR2(6)
- LOG_GROUP_TYPE can be PRIMARY KEY LOGGING, UNIQUE KEY LOGGING ,FOREIGN KEY LOGGING ,ALL COLUMN LOGGING, USER LOG GROUP in DBA_LOG_GROUPS. USER LOG GROUP is present when we define user-defined supplemental log groups
- DBA_LOG_GROUP_COLUMNS contains information for columns when USER LOG GROUP is present.
For a particular table, you can find if a Supplemental Log group has been created for a particular table with the query below. If it returns a row, Supplemental Logging is turned on. If it returns “no rows selected”, then Supplemental Logging is not turned on.
COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20 COLUMN TABLE_NAME HEADING 'Table' FORMAT A20 COLUMN ALWAYS HEADING 'Type of Log Group' FORMAT A30 SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS, 'ALWAYS', 'Unconditional', NULL, 'Conditional') ALWAYS FROM DBA_LOG_GROUPS where OWNER='<table owner>' and TABLE_NAME='<table name>';
We can find which columns are part of the Supplemental Log group with the query
select LOG_GROUP_NAME, COLUMN_NAME, POSITION from dba_log_group_columns where OWNER='<table owner>' and TABLE_NAME='<table name> ' order by position;
Hope this give some information about Supplemental Logging in Oracle