General
===========================
Oracle Supplemental Logging, for Oracle 11.2
Oracle GoldenGate depends on Supplemental Logging.
Regular undo/redo logs use ROWID to identify data rows.
But with replication, ROWID cannot be used, and additional logging is required.
Supplemental logging generates additional undo which is stored in the redo log.
There are several types of supplemental logging:
Minimal
Primary Key
Unique Key
Foreign Key
All
Procedural Replication
===========================
How to check
===========================
===========================
Supplemental logging levels
===========================
Minimal Supplemental Logging
Minimal supplemental logging ensures that products leveraging LogMiner technology will have sufficient information to support chained rows and cluster tables.
Primary Key Supplemental Logging
Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes.
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.
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.
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.
Supplemental logging is not enabled for LONG, LONG RAW and LOB 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.
===========================
Database Level Supplemental Logging
===========================
Oracle implements supplemental logging at database level, schema level and at table level.
Database Level Supplemental Logging
Database level supplemental logging is configured in the control file.
The parameter does not appear to be stored in the database itself.
Enable Supplemental Logging:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
Disable Supplemental Logging:
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
Database altered.
Example syntax:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
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;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION;
The current supplemental logging configuration is reported in V$DATABASE by the following columns:
SUPPLEMENTAL_LOG_DATA_MIN
SUPPLEMENTAL_LOG_DATA_PK
SUPPLEMENTAL_LOG_DATA_UI
SUPPLEMENTAL_LOG_DATA_FK
SUPPLEMENTAL_LOG_DATA_ALL
SUPPLEMENTAL_LOG_DATA_P
===========================
Schema Level Supplemental Logging
===========================
It is enabled and/or disabled using DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION procedure.
This procedure takes two parameters:
(SCHEMA_NAME, SUPPLEMENTAL_LOGGING)
SUPPLEMENTAL_LOGGING parameter can have following parameters:
NONE - supplemental logging is not enabled for any columns in the schema.
KEYS - supplemental logging is enabled for primary key, unique key, bitmap index and foreign key columns.
Primary keys are logged unconditionally.
Unique key, bitmap index and foreign keys are logged conditionally.
ALL - supplemental logging is enabled for all columns in all existing tables in schema .
Supplemental logging is not enabled for the following types of column:
LOB
LONG
LONG RAW
User-defined types
Oracle-supplied types
Schema level supplemental logging parameters are reported in the DBA_CAPTURE_PREPARED_SCHEMAS.
SCHEMA_NAME VARCHAR2(30)
TIMESTAMP DATE
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(8)
Possible values for the SUPPLEMENTAL_LOG_DATA flags are:
-IMPLICIT
-EXPLICIT
-NO
See schema level Supplemental info
Regular undo/redo logs use ROWID to identify data rows.
But with replication, ROWID cannot be used, and additional logging is required.
Supplemental logging generates additional undo which is stored in the redo log.
There are several types of supplemental logging:
Minimal
Primary Key
Unique Key
Foreign Key
All
Procedural Replication
===========================
How to check
===========================
SELECT supplemental_log_data_fk,
supplemental_log_data_all,
supplemental_log_data_ui,
supplemental_log_data_min
FROM V$DATABASE;
SELECT * FROM DBA_LOG_GROUPS;
SELECT * FROM DBA_LOG_GROUP_COLUMNS;
===========================
Supplemental logging levels
===========================
Minimal Supplemental Logging
Minimal supplemental logging ensures that products leveraging LogMiner technology will have sufficient information to support chained rows and cluster tables.
Primary Key Supplemental Logging
Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes.
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.
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.
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.
Supplemental logging is not enabled for LONG, LONG RAW and LOB 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.
===========================
Database Level Supplemental Logging
===========================
Oracle implements supplemental logging at database level, schema level and at table level.
Database Level Supplemental Logging
Database level supplemental logging is configured in the control file.
The parameter does not appear to be stored in the database itself.
Enable Supplemental Logging:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
Disable Supplemental Logging:
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
Database altered.
Example syntax:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
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;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION;
The current supplemental logging configuration is reported in V$DATABASE by the following columns:
SUPPLEMENTAL_LOG_DATA_MIN
SUPPLEMENTAL_LOG_DATA_PK
SUPPLEMENTAL_LOG_DATA_UI
SUPPLEMENTAL_LOG_DATA_FK
SUPPLEMENTAL_LOG_DATA_ALL
SUPPLEMENTAL_LOG_DATA_P
===========================
Schema Level Supplemental Logging
===========================
It is enabled and/or disabled using DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION procedure.
This procedure takes two parameters:
(SCHEMA_NAME, SUPPLEMENTAL_LOGGING)
SUPPLEMENTAL_LOGGING parameter can have following parameters:
NONE - supplemental logging is not enabled for any columns in the schema.
KEYS - supplemental logging is enabled for primary key, unique key, bitmap index and foreign key columns.
Primary keys are logged unconditionally.
Unique key, bitmap index and foreign keys are logged conditionally.
ALL - supplemental logging is enabled for all columns in all existing tables in schema .
Supplemental logging is not enabled for the following types of column:
LOB
LONG
LONG RAW
User-defined types
Oracle-supplied types
Schema level supplemental logging parameters are reported in the DBA_CAPTURE_PREPARED_SCHEMAS.
SCHEMA_NAME VARCHAR2(30)
TIMESTAMP DATE
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(8)
Possible values for the SUPPLEMENTAL_LOG_DATA flags are:
-IMPLICIT
-EXPLICIT
-NO
See schema level Supplemental info
GGSCI> dblogin USERID my_user, password my_pass
GGSCI> info schematrandata *
2023-07-06 11:40:57 INFO OGG-01786 Schema level supplemental logging is disabled on schema "ALEC_SH_MONITOR".
2023-07-06 11:40:57 INFO OGG-10462 Schema "ALEC_SH_MONITOR" have 40 prepared tables for instantiation.
2023-07-06 11:40:57 INFO OGG-01786 Schema level supplemental logging is disabled on schema "ALEC_TEST".
2023-07-06 11:40:57 INFO OGG-10462 Schema "ALEC_TEST" have 12 prepared tables for instantiation.
2023-07-06 11:40:57 INFO OGG-01786 Schema level supplemental logging is disabled on schema "MY_SCEMA".
2023-07-06 11:40:57 INFO OGG-10462 Schema "MY_SCEMA" have 1,621 prepared tables for instantiation.
===========================
Table Level Supplemental Logging
===========================
in GG cli
GGSCI>dblogin userid my_user password my_password
GGSCI>ADD TRANDATA MY_USER.MY_TABLE
or with sqlplus
ALTER TABLE MY_SHEMA.MY_TABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE MY_SHEMA.MY_TABLE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE MY_SHEMA.MY_TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ADD TRANDATA automatically adds supplemental logging for the table based on the table’s primary key or if that’s missing, using any unique key constraints defined for that table.
Once supplemental logging is enabled, along with a column which has been updated/changed an extra column will be logged in to redo logs so that a row could be uniquely identified.
Order of extra column logging:
Primary Key
Unique Key (if Primary Key is missing)
All columns (if Primary Key and Unique Key are missing)
In case there is no Primary/Unique key, it is possible to list the logged columns, to avoid ALL COLUMNS scenario
ADD TRANDATA MY_TABLE , COLS (COLUMN1, COLUMN2, COLUMN22), NOKEY
======================================================
See Table Status
======================================================
SELECT * FROM DBA_LOG_GROUPS;See Table Status
======================================================
COL LOG_GROUP_NAME FOR A30
COL LOG_GROUP_TYPE FOR A30
SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_PROFILE'
LOG_GROUP_NAME LOG_GROUP_TYPE
------------------------------ --------------------
GGS_48337541 USER LOG GROUP
SYS_C00164213 PRIMARY KEY LOGGING
SYS_C00164214 UNIQUE KEY LOGGING
SYS_C00164215 FOREIGN KEY LOGGING
SYS_C00164216 ALL COLUMN LOGGING
SELECT * FROM DBA_LOG_GROUP_COLUMNS;
Another syntax for USER_LOG_GROUP_COLUMNS
SELECT * FROM TABLE(LOGMNR$ALWAYS_SUPLOG_COLUMNS( 'MY_SCHEMA', 'MY_TABLE' ));
SELECT * FROM TABLE(LOGMNR$ALWAYS_SUPLOG_COLUMNS( 'MY_SCHEMA', 'MY_TABLE' ));
For some reason not all table are listed in USER_LOG_GROUPS and in USER_LOG_GROUP_COLUMNS
To see actual data, use ggsci CLI INFO TRANDATA command
Example 1 - Table with ALL COLUMNS
GGSCI 56> DBLOGIN USERID my_user, password my_pass
GGSCI 57> INFO TRANDATA MY_USER.MY_TABLE
2023-07-06 11:47:42 INFO OGG-10471 ***** Oracle Goldengate support information on table MY_USER.MY_TABLE *****
Oracle Goldengate support native capture on table MY_USER.MY_TABLE.
Oracle Goldengate marked following column as key columns on table MY_USER.MY_TABLE: MSISDN.
Logging of supplemental redo log data is enabled for table MY_USER.MY_TABLE.
All columns supplementally logged for table MY_USER.MY_TABLE.
Prepared CSN for table MY_USER.MY_TABLE: 54132906898
GGSCI 58>
Example 2 - Table with PK
GGSCI 61> INFO TRANDATA MY_USER.ALARM_MAP_NAME
2023-07-06 11:53:06 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.ALARM_MAP_NAME *****
Oracle Goldengate support native capture on table MY_USER.ALARM_MAP_NAME.
Oracle Goldengate marked following column as key columns on table MY_USER.ALARM_MAP_NAME: ALARM_MAP_NAME.
Logging of supplemental redo log data is enabled for table MY_USER.ALARM_MAP_NAME.
Columns supplementally logged for table MY_USER.ALARM_MAP_NAME: "ALARM_MAP_NAME".
Prepared CSN for table MY_USER.ALARM_MAP_NAME: 54132912785
GGSCI (qanfv-2-dbs-01 as ogg@igt) 62>
======================================================
How to disable Supplemental Logging for a table
======================================================
======================================================
See current status
Before change:
GGSCI 62> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 12:55:38 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038
SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGING_PROPERTY
--------------- -------------- ------------------------- ----------- -------- ----------------
LAB_QANFV_ALLQQ GGS_48337432 SFI_CUSTOMER_USAGE_HOURLY KEY1 1 LOG
====================
Option A - - with sqlplus - This does not work!!
====================
ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
--no effect
ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
--no effect
ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
--no effect
SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGING_PROPERTY
--------------- -------------- ------------------------- ----------- -------- ----------------
LAB_QANFV_ALLQQ GGS_48337432 SFI_CUSTOMER_USAGE_HOURLY KEY1 1 LOG
Same data is there, both with sqlplus, querying USER_LOG_GROUP_COLUMNS, and with ggsci command INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
====================
Option B - Delete Trandata with ggsci command - This does not work!!
====================
GGSCI 64> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 12:57:12 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
2023-07-06 12:57:12 INFO OGG-15139 TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
GGSCI 65> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 12:57:29 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038
====================
Option C - Delete Trandata with sqlplus using LOG_GROUP_NAME - This worked!!
====================
This is the syntax:
ALTER TABLE [TABLE_NAME] DROP SUPPLEMENTAL LOG GROUP [GROUP_NAME]
SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';
OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGING_PROPERTY
--------------- -------------- ------------------------- ----------- -------- ----------------
LAB_QANFV_ALLQQ GGS_48337432 SFI_CUSTOMER_USAGE_HOURLY KEY1 1 LOG
ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG GROUP GGS_48337432;
Now, Supplemental Logging was indeed deleted
SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';
0 Rows Selected
For some reason, in ggsci, the output is different
Maybe there some time that takes to sync data to ggsci ?
Maybe there some time that takes to sync data to ggsci ?
GGSCI 69> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 13:08:58 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038
GGSCI 70>
GGSCI 70> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 13:09:41 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
2023-07-06 13:09:41 INFO OGG-15137 TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
GGSCI 71> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY
2023-07-06 13:10:15 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.
Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.
GGSCI 71>
====================
Example - Delete Trandata from table with ALLCOLS logging - This worked!!
====================
COL LOG_GROUP_NAME FOR A30
COL LOG_GROUP_TYPE FOR A30
SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_PROFILE';
LOG_GROUP_NAME LOG_GROUP_TYPE
------------------------------ ------------------------------
GGS_48337541 USER LOG GROUP
SYS_C00164213 PRIMARY KEY LOGGING
SYS_C00164214 UNIQUE KEY LOGGING
SYS_C00164215 FOREIGN KEY LOGGING
SYS_C00164216 ALL COLUMN LOGGING
GGSCI 2> dblogin USERID ogg, password xxxxxxx
GGSCI 3> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE
2023-07-13 06:48:11 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: 60656405752
GGSCI 4> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE
2023-07-13 06:55:21 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
2023-07-13 06:55:21 INFO OGG-15139 TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
Note!! - per output looks like supplemental logging was disabled.
BUT - ALLCOLS Supplemental logging still persists, and requires a separate syntax
GGSCI 4> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE
2023-07-13 07:11:00 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_PROFILE';
LOG_GROUP_NAME LOG_GROUP_TYPE
------------------------------ ------------------------------
SYS_C00164216 ALL COLUMN LOGGING
GGSCI 5> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE, ALLCOLS
2023-07-13 06:58:35 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
2023-07-13 06:58:35 INFO OGG-15137 TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
2023-07-13 06:58:35 INFO OGG-15137 TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
GGSCI 7> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE
2023-07-13 06:59:55 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.
Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.
SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_PROFILE';
no rows selected
====================
Example - Duplicat Logging - worked!!
====================
COL LOG_GROUP_NAME FOR A30
COL LOG_GROUP_TYPE FOR A30
LOG_GROUP_NAME LOG_GROUP_TYPE
------------------------------ ------------------------------
GGS_24223 USER LOG GROUP
SYS_C0083671 PRIMARY KEY LOGGING
SYS_C0083672 UNIQUE KEY LOGGING
SYS_C0083673 FOREIGN KEY LOGGING
SYS_C0083674 ALL COLUMN LOGGING
GGS_1802052 USER LOG GROUP
GGSCI 2> dblogin USERID ogg, password xxxxxxx
GGSCI 3> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS
2023-07-20 05:30:20 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: KEY1, CODE_OPTION.
Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: 27334647698
GGSCI 4> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS
2023-07-20 05:30:25 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
2023-07-20 05:30:25 INFO OGG-15139 TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
GGSCI 5> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS, ALLCOLS
2023-07-20 05:30:29 INFO OGG-15142 Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
2023-07-20 05:30:29 INFO OGG-15137 TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
2023-07-20 05:30:29 INFO OGG-15137 TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
GGSCI 7> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS
2023-07-20 05:30:34 INFO OGG-10471 ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS *****
Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: KEY1, CODE_OPTION.
Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.
Note!! - per output looks like supplemental logging was disabled.
BUT - Duplicate Supplemental logging still persists, and requires a separate syntax
SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_OPTIONS';
LOG_GROUP_NAME LOG_GROUP_TYPE
------------------------------ ------------------------------
GGS_1802052 USER LOG GROUP
This could not be deleted with ggsci commands, only from sqlplus
SQL> ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS DROP SUPPLEMENTAL LOG GROUP GGS_1802052;
SQL> SELECT log_group_name, log_group_type
FROM DBA_LOG_GROUPS
WHERE table_name='SFI_CUSTOMER_OPTIONS';
no rows selected
===========================
Reference
===========================
Introduction to Oracle Supplemental Logging for Logical Replication
Reference
===========================
Introduction to Oracle Supplemental Logging for Logical Replication
No comments:
Post a Comment