Minimal Supplemental Logging¶
The previous versions of the Oracle CDC Source connector required full supplemental logging to create a complete record when the connector populates table-specific or CDC topics. For example, if you have 100 columns in a table and one of the column’s value is changed, the connector will use the remaining 99 columns’ values to create a complete record. However, you may want to log the change portions with a primary key, or a unique index to avoid a potential storage issue in the Oracle Database.
Oracle CDC Source connector version 2.1.0 supports minimal supplemental logging
with some caveats. When a connector is configured with start.from=snapshot,
for instance, the connector will create a snapshot of your selected tables in
table-specific, or CDC topics. However, CDC events from the Oracle redo log will
only be populated for the redo log topic, for example, redo-log-topic. If
you have a LOB column, you will need a primary key. If you don’t have a
primary key, do not set the lob.topic.name.template configuration
property–this will keep the connector from populating LOB topics.
The following examples will help you in enabling minimal supplemental logging at the table level when using either a primary key or unique index. These examples use the following software versions:
- Confluent Platform v7.0.1
- Oracle CDC Source v2.1.0
- Amazon RDS Oracle 19c
Enabling minimal supplemental logging using a primary key¶
Create a table similar to the following:
CREATE TABLE CUSTOMERS ( ID NUMBER(10) NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR(100), LAST_NAME VARCHAR(100), EMAIL VARCHAR(200), GENDER VARCHAR(50), CLUB_STATUS VARCHAR(8), COMMENTS CLOB, DESCRIPTION CLOB, DATE_TS DATE DEFAULT CURRENT_DATE , TIMESTAMP_TS TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );
Enable minimal supplemental logging for a primary key:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); <- for Amazon RDS ALTER TABLE ADMIN.CUSTOMERS ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Check the supplemental logging status using the following query:
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
You should see output similar to:
SUPPLEME SUP SUP -------- --- --- YES NO NO
Enter the following query:
SELECT * FROM ALL_LOG_GROUPS WHERE OWNER='ADMIN' and TABLE_NAME='CUSTOMERS';
You should see output similar to:
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED ----- -------------- ---------- -------------- ------ --------- ADMIN SYS_C005903 CUSTOMERS PRIMARY KEY LOGGING ALWAYS GENERATED NAME
Insert and commit a few records:
INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (1, 'Rica', 'Blaisdell', 'rblaisdell0@rambler.ru', 'Female', 'bronze', TO_CLOB('test1'),TO_CLOB('test1') ); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (2, 'Jinxin', 'Liu', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test2'), TO_CLOB('test2')); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS) values (3, 'Peter', 'Parker', 'pparker@rambler.ru', 'Male', 'bronze'); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (4, 'Steph', 'Curry', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test3'), TO_CLOB('test3')); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (5, 'Brandon', 'Parker', 'pparker@rambler.ru', 'Male', 'bronze', EMPTY_CLOB(), EMPTY_CLOB()); COMMIT;
Create a connector with the following configuration property settings:
{ "name": "SimpleOracleCDC_RDS", "config":{ "connector.class": "io.confluent.connect.oracle.cdc.OracleCdcSourceConnector", "name": "SimpleOracleCDC_RDS", "tasks.max":3, "key.converter": "io.confluent.connect.avro.AvroConverter", "key.converter.schema.registry.url": "http://localhost:8081", "value.converter": "io.confluent.connect.avro.AvroConverter", "value.converter.schema.registry.url": "http://localhost:8081", "confluent.topic.bootstrap.servers":"localhost:9092", "oracle.server": "<amazon-rds>", "oracle.port": 1521, "oracle.sid":"ORCL", "oracle.username": "<username>", "oracle.password": "<password>", "start.from":"snapshot", "redo.log.topic.name": "redo-log-topic", "redo.log.consumer.bootstrap.servers":"localhost:9092", "table.inclusion.regex":"ORCL[.]ADMIN[.]CUSTOMERS", "table.topic.name.template": "${databaseName}.${schemaName}.${tableName}", "lob.topic.name.template":"${databaseName}.${schemaName}.${tableName}.${columnName}", "redo.log.row.fetch.size":100000, "numeric.mapping": "best_fit_or_double", "oracle.supplemental.log.level":"msl", "confluent.topic.replication.factor":1 } }
Check
ORCL.ADMIN.CUSTOMERSfor a snapshot:kafka-avro-console-consumer -bootstrap-server localhost:9092 --property schema.registry.url=http://localhost:8081 --topic ORCL.ADMIN.CUSTOMERS --property print.key=true --property key.separator="-" --from-beginning
You should see output similar to:
1-{"ID":1,"FIRST_NAME":{"string":"Rica"},"LAST_NAME":{"string":"Blaisdell"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Female"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655921447239},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"785524"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655921523296"},"row_id":null,"username":null} 2-{"ID":2,"FIRST_NAME":{"string":"Jinxin"},"LAST_NAME":{"string":"Liu"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655921447281},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"785524"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655921523375"},"row_id":null,"username":null} 3-{"ID":3,"FIRST_NAME":{"string":"Peter"},"LAST_NAME":{"string":"Parker"},"EMAIL":{"string":"pparker@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655921447320},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"785524"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655921523377"},"row_id":null,"username":null} 4-{"ID":4,"FIRST_NAME":{"string":"Steph"},"LAST_NAME":{"string":"Curry"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655921447357},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"785524"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655921523442"},"row_id":null,"username":null} 5-{"ID":5,"FIRST_NAME":{"string":"Brandon"},"LAST_NAME":{"string":"Parker"},"EMAIL":{"string":"pparker@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655921447395},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"785524"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655921523510"},"row_id":null,"username":null} ^CProcessed a total of 5 messagesCheck
ORCL.ADMIN.CUSTOMERS.COMMENTSfor a snapshot:kafka-avro-console-consumer -bootstrap-server localhost:9092 --property schema.registry.url=http://localhost:8081 --topic ORCL.ADMIN.CUSTOMERS.COMMENTS --property print.key=true --property key.separator="-" --from-beginning
You should see output similar to:
{"table":"ORCL.ADMIN.CUSTOMERS","column":"COMMENTS","primary_key":1}-"test1" {"table":"ORCL.ADMIN.CUSTOMERS","column":"COMMENTS","primary_key":2}-"test2" {"table":"ORCL.ADMIN.CUSTOMERS","column":"COMMENTS","primary_key":4}-"test3" ^CProcessed a total of 3 messagesInsert a record:
INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (6, 'LeBron', 'James', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test7'), TO_CLOB('test7')); COMMIT;
Check
redo-log-topic:kafka-avro-console-consumer -bootstrap-server localhost:9092 --property schema.registry.url=http://localhost:8081 --topic redo-log-topic --from-beginning --property print.key=true --property key.separator="-"
You should see output similar to:
"CUSTOMERS"-{"SCN":{"long":785826},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655921639000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":10},"XIDSLT":{"long":28},"XIDSQN":{"long":424},"XID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"PXIDUSN":{"long":10},"PXIDSLT":{"long":28},"PXIDSQN":{"long":424},"PXID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"INSERT"},"OPERATION_CODE":{"int":1},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":153},"RBABYTE":{"long":372},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":0},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"INSERT into \"ADMIN\".\"CUSTOMERS\"(\"ID\",\"FIRST_NAME\",\"LAST_NAME\",\"EMAIL\",\"GENDER\",\"CLUB_STATUS\",\"COMMENTS\",\"DESCRIPTION\",\"DATE_TS\",\"TIMESTAMP_TS\") values ('6','LeBron','James','rblaisdell0@rambler.ru','Male','bronze',EMPTY_CLOB(),EMPTY_CLOB(),TO_DATE('2022-06-22 11:13:59', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP_TZ('2022-06-22 11:13:59.487 -07:00'));"},"SQL_UNDO":{"string":"delete from \"ADMIN\".\"CUSTOMERS\" where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 11:13:59', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 11:13:59.487 -07:00') and ROWID = 'AAAAAAAAAAAAAAAAAA';"},"RS_ID":{"string":" 0x000024.00000099.0174 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":2},"UNDO_VALUE":{"long":3},"SAFE_RESUME_SCN":null,"CSCN":{"long":785827},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} "CUSTOMERS"-{"SCN":{"long":785826},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655921639000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":10},"XIDSLT":{"long":28},"XIDSQN":{"long":424},"XID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"PXIDUSN":{"long":10},"PXIDSLT":{"long":28},"PXIDSQN":{"long":424},"PXID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"UPDATE"},"OPERATION_CODE":{"int":3},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMLAAL"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":2},"RBASQN":{"long":36},"RBABLK":{"long":155},"RBABYTE":{"long":248},"UBAFIL":{"long":3},"UBABLK":{"long":10545},"UBAREC":{"long":27},"UBASQN":{"long":65},"ABS_FILE_NUM":{"long":4},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":779},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"COMMENTS\" = 'test7', \"DESCRIPTION\" = 'test7' where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 11:13:59', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 11:13:59.487 -07:00') and ROWID = 'AAAF3OAAAAAAAMLAAL';"},"SQL_UNDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"COMMENTS\" = NULL, \"DESCRIPTION\" = NULL where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 11:13:59', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 11:13:59.487 -07:00') and ROWID = 'AAAF3OAAAAAAAMLAAL';"},"RS_ID":{"string":" 0x000024.0000009b.00f8 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":4},"UNDO_VALUE":{"long":5},"SAFE_RESUME_SCN":null,"CSCN":{"long":785827},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":785827},"START_SCN":{"long":785826},"COMMIT_SCN":{"long":785827},"TIMESTAMP":{"long":1655921639000},"START_TIMESTAMP":{"long":1655921639000},"COMMIT_TIMESTAMP":{"long":1655921639000},"XIDUSN":{"long":10},"XIDSLT":{"long":28},"XIDSQN":{"long":424},"XID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"PXIDUSN":{"long":10},"PXIDSLT":{"long":28},"PXIDSQN":{"long":424},"PXID":{"bytes":"\n\u0000\u001C\u0000¨\u0001\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":157},"RBABYTE":{"long":148},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"COMMIT;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000024.0000009d.0094 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":6},"UNDO_VALUE":{"long":7},"SAFE_RESUME_SCN":{"long":785827},"CSCN":{"long":785827},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} ^CProcessed a total of 3 messagesThere should be no change for
ORCL.ADMIN.CUSTOMERSandORCL.ADMIN.CUSTOMERS.COMMENTSas expected.Update a record:
UPDATE CUSTOMERS SET FIRST_NAME='Rical' WHERE FIRST_NAME='Rica'; COMMIT;
Check the
redo-log-topic. You should see output similar to:"CUSTOMERS"-{"SCN":{"long":786030},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655921716000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":9},"XIDSLT":{"long":10},"XIDSQN":{"long":571},"XID":{"bytes":"\t\u0000\n\u0000;\u0002\u0000\u0000"},"PXIDUSN":{"long":9},"PXIDSLT":{"long":10},"PXIDSQN":{"long":571},"PXID":{"bytes":"\t\u0000\n\u0000;\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"UPDATE"},"OPERATION_CODE":{"int":3},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMLAAG"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":240},"RBABYTE":{"long":16},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":779},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"FIRST_NAME\" = 'Rical' where \"ID\" = '1' and \"FIRST_NAME\" = 'Rica' and ROWID = 'AAAF3OAAAAAAAMLAAG';"},"SQL_UNDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"FIRST_NAME\" = 'Rica' where \"ID\" = '1' and \"FIRST_NAME\" = 'Rical' and ROWID = 'AAAF3OAAAAAAAMLAAG';"},"RS_ID":{"string":" 0x000024.000000f0.0010 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":4},"UNDO_VALUE":{"long":5},"SAFE_RESUME_SCN":null,"CSCN":{"long":786031},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":786031},"START_SCN":{"long":0},"COMMIT_SCN":{"long":786031},"TIMESTAMP":{"long":1655921716000},"START_TIMESTAMP":{"long":567993600000},"COMMIT_TIMESTAMP":{"long":1655921716000},"XIDUSN":{"long":9},"XIDSLT":{"long":10},"XIDSQN":{"long":571},"XID":{"bytes":"\t\u0000\n\u0000;\u0002\u0000\u0000"},"PXIDUSN":{"long":9},"PXIDSLT":{"long":10},"PXIDSQN":{"long":571},"PXID":{"bytes":"\t\u0000\n\u0000;\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"UNKNOWN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":0},"SESSION_NUM":{"long":0},"SERIAL_NUM":{"long":0},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":241},"RBABYTE":{"long":164},"UBAFIL":{"long":0},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"commit;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000024.000000f1.00a4 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":0},"UNDO_VALUE":{"long":1},"SAFE_RESUME_SCN":{"long":786031},"CSCN":{"long":786031},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}}There should be no change for
ORCL.ADMIN.CUSTOMERSandORCL.ADMIN.CUSTOMERS.COMMENTSO as expected.Delete a record:
DELETE CUSTOMERS WHERE FIRST_NAME='Brandon'; COMMIT;
Check the
redo-log-topic. You should see output similar to:"CUSTOMERS"-{"SCN":{"long":786250},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655921798000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":8},"XIDSLT":{"long":16},"XIDSQN":{"long":562},"XID":{"bytes":"\b\u0000\u0010\u00002\u0002\u0000\u0000"},"PXIDUSN":{"long":8},"PXIDSLT":{"long":16},"PXIDSQN":{"long":562},"PXID":{"bytes":"\b\u0000\u0010\u00002\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"DELETE"},"OPERATION_CODE":{"int":2},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMLAAK"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":353},"RBABYTE":{"long":16},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":779},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"delete from \"ADMIN\".\"CUSTOMERS\" where \"ID\" = '5' and \"FIRST_NAME\" = 'Brandon' and \"LAST_NAME\" = 'Parker' and \"EMAIL\" = 'pparker@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 11:10:47', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 11:10:47.395 -07:00') and ROWID = 'AAAF3OAAAAAAAMLAAK';"},"SQL_UNDO":{"string":"INSERT into \"ADMIN\".\"CUSTOMERS\"(\"ID\",\"FIRST_NAME\",\"LAST_NAME\",\"EMAIL\",\"GENDER\",\"CLUB_STATUS\",\"COMMENTS\",\"DESCRIPTION\",\"DATE_TS\",\"TIMESTAMP_TS\") values ('5','Brandon','Parker','pparker@rambler.ru','Male','bronze',EMPTY_CLOB(),EMPTY_CLOB(),TO_DATE('2022-06-22 11:10:47', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP_TZ('2022-06-22 11:10:47.395 -07:00'));"},"RS_ID":{"string":" 0x000024.00000161.0010 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":2},"UNDO_VALUE":{"long":3},"SAFE_RESUME_SCN":null,"CSCN":{"long":786251},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":786251},"START_SCN":{"long":786250},"COMMIT_SCN":{"long":786251},"TIMESTAMP":{"long":1655921798000},"START_TIMESTAMP":{"long":1655921798000},"COMMIT_TIMESTAMP":{"long":1655921798000},"XIDUSN":{"long":8},"XIDSLT":{"long":16},"XIDSQN":{"long":562},"XID":{"bytes":"\b\u0000\u0010\u00002\u0002\u0000\u0000"},"PXIDUSN":{"long":8},"PXIDSLT":{"long":16},"PXIDSQN":{"long":562},"PXID":{"bytes":"\b\u0000\u0010\u00002\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100144},"SESSION_NUM":{"long":636},"SERIAL_NUM":{"long":20543},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":36},"RBABLK":{"long":355},"RBABYTE":{"long":72},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"COMMIT;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000024.00000163.0048 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":4},"UNDO_VALUE":{"long":5},"SAFE_RESUME_SCN":{"long":786251},"CSCN":{"long":786251},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}}There should be no change for
ORCL.ADMIN.CUSTOMERSandORCL.ADMIN.CUSTOMERS.COMMENTSas expected.
Enabling minimal supplemental logging using a unique index¶
Create a table similar to the following:
CREATE TABLE CUSTOMERS ( ID NUMBER(10), FIRST_NAME VARCHAR(100), LAST_NAME VARCHAR(100), EMAIL VARCHAR(200), GENDER VARCHAR(50), CLUB_STATUS VARCHAR(8), COMMENTS CLOB, DESCRIPTION CLOB, DATE_TS DATE DEFAULT CURRENT_DATE , TIMESTAMP_TS TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );
Create an index using the following query:
CREATE INDEX CUSTOMERS_GENDER on CUSTOMERS (GENDER);
Enable minimal supplemental logging for a unique index:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); <- for Amazon RDS ALTER TABLE CUSTOMERS ADD SUPPLEMENTAL LOG GROUP C_GENDER (GENDER) ALWAYS;
Check the supplemental logging status using the following query:
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
You should see output similar to:
SUPPLEME SUP SUP -------- --- --- YES NO NO
Enter the following query:
SELECT * FROM ALL_LOG_GROUPS WHERE OWNER='ADMIN' and TABLE_NAME='CUSTOMERS';
You should see output similar to:
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED ---------- -------------- ---------- -------------- ------ --------- ADMIN C_GENDER CUSTOMERS USER LOG GROUP ALWAYS USER NAME
Insert and commit a few records:
INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (1, 'Rica', 'Blaisdell', 'rblaisdell0@rambler.ru', 'Female', 'bronze', TO_CLOB('test1'),TO_CLOB('test1') ); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (2, 'Jinxin', 'Liu', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test2'), TO_CLOB('test2')); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS) values (3, 'Peter', 'Parker', 'pparker@rambler.ru', 'Male', 'bronze'); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (4, 'Steph', 'Curry', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test3'), TO_CLOB('test3')); INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (5, 'Brandon', 'Parker', 'pparker@rambler.ru', 'Male', 'bronze', EMPTY_CLOB(), EMPTY_CLOB()); COMMIT;
Create a connector with the following configuration property settings:
{ "name": "SimpleOracleCDC_RDS2", "config":{ "connector.class": "io.confluent.connect.oracle.cdc.OracleCdcSourceConnector", "name": "SimpleOracleCDC_RDS2", "tasks.max":3, "key.converter": "io.confluent.connect.avro.AvroConverter", "key.converter.schema.registry.url": "http://localhost:8081", "value.converter": "io.confluent.connect.avro.AvroConverter", "value.converter.schema.registry.url": "http://localhost:8081", "confluent.topic.bootstrap.servers":"localhost:9092", "oracle.server": "<amazon-rds>", "oracle.port": 1521, "oracle.sid":"ORCL", "oracle.username": "<username>", "oracle.password": "<password>", "start.from":"snapshot", "redo.log.topic.name": "redo-log-topic", "redo.log.consumer.bootstrap.servers":"localhost:9092", "table.inclusion.regex":"ORCL[.]ADMIN[.]CUSTOMERS", "table.topic.name.template": "${databaseName}.${schemaName}.${tableName}", "lob.topic.name.template":"${databaseName}.${schemaName}.${tableName}.${columnName}", "redo.log.row.fetch.size":100000, "numeric.mapping": "best_fit_or_double", "oracle.supplemental.log.level":"msl", "confluent.topic.replication.factor":1 } }
The connector will fail with the following error “fieldSchema for field primary_key cannot be null”:
[2022-06-22 13:44:22,105] ERROR [SimpleOracleCDC_RDS2|task-1] WorkerSourceTask{id=SimpleOracleCDC_RDS2-1} Task threw an uncaught and unrecoverable exception. Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask:206) org.apache.kafka.connect.errors.ConnectException: Error while polling for records at io.confluent.connect.oracle.cdc.util.RecordQueue.poll(RecordQueue.java:372) at io.confluent.connect.oracle.cdc.OracleCdcSourceTask.poll(OracleCdcSourceTask.java:519) at org.apache.kafka.connect.runtime.WorkerSourceTask.poll(WorkerSourceTask.java:308) at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:263) at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:199) at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:254) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.apache.kafka.connect.errors.ConnectException: Failed on attempt 1 of 32768 to reading redo log from jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database-1.cdj9jhooecs2.us-west-2.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL))) with user 'admin' (pool=oracle-cdc-source:SimpleOracleCDC_RDS2-1): fieldSchema for field primary_key cannot be null. at io.confluent.connect.utils.retry.RetryPolicy.callWith(RetryPolicy.java:423) at io.confluent.connect.utils.retry.RetryPolicy.callWith(RetryPolicy.java:368) at io.confluent.connect.oracle.cdc.OracleDatabase.retry(OracleDatabase.java:569) at io.confluent.connect.oracle.cdc.OracleSnapshotGenerator.generateSnapshot(OracleSnapshotGenerator.java:127) at io.confluent.connect.oracle.cdc.util.RecordQueue.lambda$createLoggingSupplier$0(RecordQueue.java:465) at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700) ... 3 more Caused by: org.apache.kafka.connect.errors.SchemaBuilderException: fieldSchema for field primary_key cannot be null. at org.apache.kafka.connect.data.SchemaBuilder.field(SchemaBuilder.java:327) at io.confluent.connect.oracle.cdc.schemas.LobRecordSchema.keySchema(LobRecordSchema.java:53) at io.confluent.connect.oracle.cdc.record.OracleCdcSourceRecordConverters$SnapshotRecordConverter.readCurrentRow(OracleCdcSourceRecordConverters.java:612) at io.confluent.connect.oracle.cdc.OracleSnapshotGenerator.doGenerateSnapshot(OracleSnapshotGenerator.java:212) at io.confluent.connect.oracle.cdc.OracleSnapshotGenerator.lambda$generateSnapshot$1(OracleSnapshotGenerator.java:129) at io.confluent.connect.utils.retry.RetryPolicy.callWith(RetryPolicy.java:417) ... 8 more [2022-06-22 13:44:22,105] INFO [SimpleOracleCDC_RDS2|task-1] Stopping the 'SimpleOracleCDC_RDS2' Oracle CDC connector task 1 (io.confluent.connect.oracle.cdc.OracleCdcSourceTask:609)Create a new connector without
lob.topic.name.templateand checkORCL.ADMIN.CUSTOMERSfor a snapshot:kafka-avro-console-consumer -bootstrap-server localhost:9092 --property schema.registry.url=http://localhost:8081 --topic ORCL.ADMIN.CUSTOMERS --property print.key=true --property key.separator="-" --from-beginning
You should see output similar to:
null-{"ID":{"long":1},"FIRST_NAME":{"string":"Rica"},"LAST_NAME":{"string":"Blaisdell"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Female"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655924864120},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"788697"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655931551708"},"row_id":null,"username":null} null-{"ID":{"long":2},"FIRST_NAME":{"string":"Jinxin"},"LAST_NAME":{"string":"Liu"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655924864288},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"788697"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655931551802"},"row_id":null,"username":null} null-{"ID":{"long":3},"FIRST_NAME":{"string":"Peter"},"LAST_NAME":{"string":"Parker"},"EMAIL":{"string":"pparker@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655924864335},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"788697"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655931551803"},"row_id":null,"username":null} null-{"ID":{"long":4},"FIRST_NAME":{"string":"Steph"},"LAST_NAME":{"string":"Curry"},"EMAIL":{"string":"rblaisdell0@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655924864378},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"788697"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655931552217"},"row_id":null,"username":null} null-{"ID":{"long":5},"FIRST_NAME":{"string":"Brandon"},"LAST_NAME":{"string":"Parker"},"EMAIL":{"string":"pparker@rambler.ru"},"GENDER":{"string":"Male"},"CLUB_STATUS":{"string":"bronze"},"DATE_TS":{"int":19165},"TIMESTAMP_TS":{"long":1655924865651},"table":{"string":"ORCL.ADMIN.CUSTOMERS"},"scn":{"string":"788697"},"op_type":{"string":"R"},"op_ts":null,"current_ts":{"string":"1655931552284"},"row_id":null,"username":null}Insert a record using the following query:
INSERT into CUSTOMERS (ID, FIRST_NAME, LAST_NAME, EMAIL, GENDER, CLUB_STATUS, COMMENTS, DESCRIPTION) values (6, 'LeBron', 'James', 'rblaisdell0@rambler.ru', 'Male', 'bronze', TO_CLOB('test7'), TO_CLOB('test7')); COMMIT;
Check
redo-log-topic:kafka-avro-console-consumer -bootstrap-server localhost:9092 --property schema.registry.url=http://localhost:8081 --topic redo-log-topic --from-beginning --property print.key=true --property key.separator="-"
You should output similar to:
"CUSTOMERS"-{"SCN":{"long":789263},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655931658000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":6},"XIDSLT":{"long":11},"XIDSQN":{"long":590},"XID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"PXIDUSN":{"long":6},"PXIDSLT":{"long":11},"PXIDSQN":{"long":590},"PXID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"INSERT"},"OPERATION_CODE":{"int":1},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":39},"RBABLK":{"long":7892},"RBABYTE":{"long":372},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":0},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"INSERT into \"ADMIN\".\"CUSTOMERS\"(\"ID\",\"FIRST_NAME\",\"LAST_NAME\",\"EMAIL\",\"GENDER\",\"CLUB_STATUS\",\"COMMENTS\",\"DESCRIPTION\",\"DATE_TS\",\"TIMESTAMP_TS\") values ('6','LeBron','James','rblaisdell0@rambler.ru','Male','bronze',EMPTY_CLOB(),EMPTY_CLOB(),TO_DATE('2022-06-22 14:00:58', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP_TZ('2022-06-22 14:00:58.201 -07:00'));"},"SQL_UNDO":{"string":"delete from \"ADMIN\".\"CUSTOMERS\" where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 14:00:58', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 14:00:58.201 -07:00') and ROWID = 'AAAAAAAAAAAAAAAAAA';"},"RS_ID":{"string":" 0x000027.00001ed4.0174 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":2},"UNDO_VALUE":{"long":3},"SAFE_RESUME_SCN":null,"CSCN":{"long":789264},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} "CUSTOMERS"-{"SCN":{"long":789263},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655931658000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":6},"XIDSLT":{"long":11},"XIDSQN":{"long":590},"XID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"PXIDUSN":{"long":6},"PXIDSLT":{"long":11},"PXIDSQN":{"long":590},"PXID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"UPDATE"},"OPERATION_CODE":{"int":3},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMPAAF"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":2},"RBASQN":{"long":39},"RBABLK":{"long":7894},"RBABYTE":{"long":248},"UBAFIL":{"long":3},"UBABLK":{"long":11868},"UBAREC":{"long":23},"UBASQN":{"long":72},"ABS_FILE_NUM":{"long":4},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":783},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"COMMENTS\" = 'test7', \"DESCRIPTION\" = 'test7' where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 14:00:58', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 14:00:58.201 -07:00') and ROWID = 'AAAF3OAAAAAAAMPAAF';"},"SQL_UNDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"COMMENTS\" = NULL, \"DESCRIPTION\" = NULL where \"ID\" = '6' and \"FIRST_NAME\" = 'LeBron' and \"LAST_NAME\" = 'James' and \"EMAIL\" = 'rblaisdell0@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 14:00:58', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 14:00:58.201 -07:00') and ROWID = 'AAAF3OAAAAAAAMPAAF';"},"RS_ID":{"string":" 0x000027.00001ed6.00f8 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":4},"UNDO_VALUE":{"long":5},"SAFE_RESUME_SCN":null,"CSCN":{"long":789264},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":789264},"START_SCN":{"long":789263},"COMMIT_SCN":{"long":789264},"TIMESTAMP":{"long":1655931658000},"START_TIMESTAMP":{"long":1655931658000},"COMMIT_TIMESTAMP":{"long":1655931658000},"XIDUSN":{"long":6},"XIDSLT":{"long":11},"XIDSQN":{"long":590},"XID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"PXIDUSN":{"long":6},"PXIDSLT":{"long":11},"PXIDSQN":{"long":590},"PXID":{"bytes":"\u0006\u0000\u000B\u0000N\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":39},"RBABLK":{"long":7896},"RBABYTE":{"long":88},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"commit;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000027.00001ed8.0058 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":6},"UNDO_VALUE":{"long":7},"SAFE_RESUME_SCN":{"long":789264},"CSCN":{"long":789264},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}}There is no change for
ORCL.ADMIN.CUSTOMERSas expected.Update a record using the following query:
UPDATE CUSTOMERS SET FIRST_NAME='Rical' WHERE FIRST_NAME='Rica'; COMMIT;
Check
redo-log-topic. You should see output similar to:"CUSTOMERS"-{"SCN":{"long":789438},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655931720000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":5},"XIDSLT":{"long":11},"XIDSQN":{"long":583},"XID":{"bytes":"\u0005\u0000\u000B\u0000G\u0002\u0000\u0000"},"PXIDUSN":{"long":5},"PXIDSLT":{"long":11},"PXIDSQN":{"long":583},"PXID":{"bytes":"\u0005\u0000\u000B\u0000G\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"UPDATE"},"OPERATION_CODE":{"int":3},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMPAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":40},"RBABLK":{"long":37},"RBABYTE":{"long":16},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":783},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"FIRST_NAME\" = 'Rical' where \"FIRST_NAME\" = 'Rica' and \"GENDER\" = 'Female' and ROWID = 'AAAF3OAAAAAAAMPAAA';"},"SQL_UNDO":{"string":"update \"ADMIN\".\"CUSTOMERS\" set \"FIRST_NAME\" = 'Rica' where \"FIRST_NAME\" = 'Rical' and \"GENDER\" = 'Female' and ROWID = 'AAAF3OAAAAAAAMPAAA';"},"RS_ID":{"string":" 0x000028.00000025.0010 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":8},"UNDO_VALUE":{"long":9},"SAFE_RESUME_SCN":null,"CSCN":{"long":789439},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":789439},"START_SCN":{"long":789438},"COMMIT_SCN":{"long":789439},"TIMESTAMP":{"long":1655931720000},"START_TIMESTAMP":{"long":1655931720000},"COMMIT_TIMESTAMP":{"long":1655931720000},"XIDUSN":{"long":5},"XIDSLT":{"long":11},"XIDSQN":{"long":583},"XID":{"bytes":"\u0005\u0000\u000B\u0000G\u0002\u0000\u0000"},"PXIDUSN":{"long":5},"PXIDSLT":{"long":11},"PXIDSQN":{"long":583},"PXID":{"bytes":"\u0005\u0000\u000B\u0000G\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":40},"RBABLK":{"long":38},"RBABYTE":{"long":168},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"COMMIT;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000028.00000026.00a8 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":10},"UNDO_VALUE":{"long":11},"SAFE_RESUME_SCN":{"long":789439},"CSCN":{"long":789439},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}}There is no change for
ORCL.ADMIN.CUSTOMERSas expected.Delete a record using the following query:
DELETE CUSTOMERS WHERE FIRST_NAME='Brandon'; COMMIT;
Check
redo-log-topic. You should see output similar to:"CUSTOMERS"-{"SCN":{"long":789894},"START_SCN":null,"COMMIT_SCN":null,"TIMESTAMP":{"long":1655931789000},"START_TIMESTAMP":null,"COMMIT_TIMESTAMP":null,"XIDUSN":{"long":2},"XIDSLT":{"long":30},"XIDSQN":{"long":575},"XID":{"bytes":"\u0002\u0000\u001E\u0000?\u0002\u0000\u0000"},"PXIDUSN":{"long":2},"PXIDSLT":{"long":30},"PXIDSQN":{"long":575},"PXID":{"bytes":"\u0002\u0000\u001E\u0000?\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"DELETE"},"OPERATION_CODE":{"int":2},"ROLLBACK":{"boolean":false},"SEG_OWNER":{"string":"ADMIN"},"SEG_NAME":{"string":"CUSTOMERS"},"TABLE_NAME":{"string":"CUSTOMERS"},"SEG_TYPE":{"int":2},"SEG_TYPE_NAME":{"string":"TABLE"},"TABLE_SPACE":{"string":"USERS"},"ROW_ID":{"string":"AAAF3OAAAAAAAMPAAE"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":40},"RBABLK":{"long":377},"RBABYTE":{"long":16},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":783},"DATA_OBJ_NUM":{"long":24014},"DATA_OBJV_NUM":{"long":1},"DATA_OBJD_NUM":{"long":24014},"SQL_REDO":{"string":"delete from \"ADMIN\".\"CUSTOMERS\" where \"ID\" = '5' and \"FIRST_NAME\" = 'Brandon' and \"LAST_NAME\" = 'Parker' and \"EMAIL\" = 'pparker@rambler.ru' and \"GENDER\" = 'Male' and \"CLUB_STATUS\" = 'bronze' and \"DATE_TS\" = TO_DATE('2022-06-22 12:07:45', 'YYYY-MM-DD HH24:MI:SS') and \"TIMESTAMP_TS\" = TO_TIMESTAMP_TZ('2022-06-22 12:07:45.651 -07:00') and ROWID = 'AAAF3OAAAAAAAMPAAE';"},"SQL_UNDO":{"string":"INSERT into \"ADMIN\".\"CUSTOMERS\"(\"ID\",\"FIRST_NAME\",\"LAST_NAME\",\"EMAIL\",\"GENDER\",\"CLUB_STATUS\",\"COMMENTS\",\"DESCRIPTION\",\"DATE_TS\",\"TIMESTAMP_TS\") values ('5','Brandon','Parker','pparker@rambler.ru','Male','bronze',EMPTY_CLOB(),EMPTY_CLOB(),TO_DATE('2022-06-22 12:07:45', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP_TZ('2022-06-22 12:07:45.651 -07:00'));"},"RS_ID":{"string":" 0x000028.00000179.0010 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":2},"UNDO_VALUE":{"long":3},"SAFE_RESUME_SCN":null,"CSCN":{"long":789896},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}} null-{"SCN":{"long":789896},"START_SCN":{"long":789894},"COMMIT_SCN":{"long":789896},"TIMESTAMP":{"long":1655931789000},"START_TIMESTAMP":{"long":1655931789000},"COMMIT_TIMESTAMP":{"long":1655931789000},"XIDUSN":{"long":2},"XIDSLT":{"long":30},"XIDSQN":{"long":575},"XID":{"bytes":"\u0002\u0000\u001E\u0000?\u0002\u0000\u0000"},"PXIDUSN":{"long":2},"PXIDSLT":{"long":30},"PXIDSQN":{"long":575},"PXID":{"bytes":"\u0002\u0000\u001E\u0000?\u0002\u0000\u0000"},"TX_NAME":null,"OPERATION":{"string":"COMMIT"},"OPERATION_CODE":{"int":7},"ROLLBACK":{"boolean":false},"SEG_OWNER":null,"SEG_NAME":null,"TABLE_NAME":null,"SEG_TYPE":{"int":0},"SEG_TYPE_NAME":null,"TABLE_SPACE":null,"ROW_ID":{"string":"AAAAAAAAAAAAAAAAAA"},"USERNAME":{"string":"ADMIN"},"OS_USERNAME":{"string":"UNKNOWN"},"MACHINE_NAME":{"string":"UNKNOWN"},"AUDIT_SESSIONID":{"long":100042},"SESSION_NUM":{"long":1258},"SERIAL_NUM":{"long":42991},"SESSION_INFO":{"string":"UNKNOWN"},"THREAD_NUM":{"long":1},"SEQUENCE_NUM":{"long":1},"RBASQN":{"long":40},"RBABLK":{"long":380},"RBABYTE":{"long":16},"UBAFIL":{"long":3},"UBABLK":{"long":0},"UBAREC":{"long":0},"UBASQN":{"long":0},"ABS_FILE_NUM":{"long":3},"REL_FILE_NUM":{"long":0},"DATA_BLK_NUM":{"long":0},"DATA_OBJ_NUM":{"long":0},"DATA_OBJV_NUM":{"long":0},"DATA_OBJD_NUM":{"long":0},"SQL_REDO":{"string":"COMMIT;"},"SQL_UNDO":null,"RS_ID":{"string":" 0x000028.0000017c.0010 "},"SSN":{"long":0},"CSF":{"boolean":false},"INFO":null,"STATUS":{"int":0},"REDO_VALUE":{"long":4},"UNDO_VALUE":{"long":5},"SAFE_RESUME_SCN":{"long":789896},"CSCN":{"long":789896},"OBJECT_ID":null,"EDITION_NAME":null,"CLIENT_ID":{"string":"UNKNOWN"},"SRC_CON_NAME":{"string":"ORCL"},"SRC_CON_ID":{"long":0},"SRC_CON_UID":{"long":0},"SRC_CON_DBID":{"long":0},"SRC_CON_GUID":null,"CON_ID":{"boolean":false}}There is no change for
ORCL.ADMIN.CUSTOMERSas expected.