What is Supplemental Logging in Oracle
Oracle Redo log file has the record of all the operations 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 that do not contain enough information to generate the SQL statement so that it can be executed on another database. It requires 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 as Golden Gate, Shareplex, Oracle log miner requires 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 does. 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 help 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 the 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 the minimum supplemental logging required in order for the 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 a high activity where there are always active transactions the supplemental logging can be enabled by bouncing the database and running the statement manually:
STARTUP MOUNT
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 that 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 use 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 supplemental logging at the table level
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 it at the table level
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 supplemental logging for a table in oracle
Oracle has provided two data dictionary views to check on this
DBA_LOG_GROUPS
DBA_LOG_GROUP_COLUMNS
- 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 gives some information about Supplemental Logging in Oracle
Also Read
Primary Key in Oracle: primary key in oracle uniquely identifies the row in the table. It cannot be null & can be created at the time of table creation or after the table is created
Foreign key in Oracle: Foreign Key in Oracle is to enforce data integrity between multiple tables. It can be added at the time of table creation and after the table is created also.
how to check trigger status in oracle: Check out How to check Trigger status in Oracle, how to enable/disable the trigger, how to find the trigger definition, how to find all the triggers
v$system_parameter: This post covers the Difference between v$system_parameter and v$parameter and other parameter views available in Oracle database.
Oracle Logon Triggers: Oracle Logon Triggers are the triggers that are fired when users logon into the database. This can be used for tracing, restricting
https://docs.oracle.com/database/121/SUTIL/GUID-D2DDD67C-E1CC-45A6-A2A7-198E4C142FA3.htm