Running on Oracle 19c ? – Why to enable full supplemental logging when SUBSET DATABASE REPLICATION is there
Posted by FatDBA on June 26, 2021
Hi Folks,
Today’s post is about one of the cool feature added in Oracle 19c, that is very useful and I recently discovered about it while working on one GG setup.
This is about Supplemental logging which enables database to capture extra logging for a redo-based application i.e. logical standby or Golden Gate, this may require that additional columns be logged in the redo log files and causes a great stress and overhead on the database. Things looks very unworthy specially if you are doing only a partial Golden Gate replication, I mean for only few tables.
Starting from Oracle 19c, this is something that you can now control. With this version, you can enable the fine grain supplemental logging (SUBSET DATABASE REPLICATION) which will reduce lot of logging overhead. This feature allows you to disable supplemental logging on the entire database and on all tables. So, if you are running on Oracle 19c and your have the COMPATIBLE parameter to 19.0.0.0.0 or greater and have enabled enable_goldengate_replication parameter to TRUE, you can use this option.
Note: I know few of the products where ‘Supplemental Log Data Subset Database Replication’ doesn’t work or the product doesn’t support it, i.e. HVR 6
SQL> select BANNER_FULL from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT FORCE_LOGGING, supplemental_log_data_min, supplemental_log_data_sr from v$database;
FORCE_LOGGING SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_SR
--------------------------------------- ------------------------------------------------------------ ----------------------------------------
YES YES NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
Database altered.
SQL> SELECT FORCE_LOGGING, supplemental_log_data_min, supplemental_log_data_sr from v$database;
FORCE_LOGGING SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_SR
--------------------------------------- ------------------------------------------------------------ ----------------------------------------
YES YES YES
You can disable it anytime using below mentioned steps/commands.
SQL>
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
Database altered.
SQL> SELECT FORCE_LOGGING, supplemental_log_data_min, supplemental_log_data_sr from v$database;
FORCE_LOGGING SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_SR
--------------------------------------- ------------------------------------------------------------ ----------------------------------------
YES YES NO
Hope It Helps!
Prashant Dixit
Tales From A Lazy Fat DBA 
rakeshroam said
It needs > ORA-00406: COMPATIBLE parameter needs to be 19.0.0.0.0 or greater
FatDBA said
Hi Rakesh,
Absolutely, it requires COMPATIBLE parameter needs to be 19.0.0.0.0 or greater