RAC Supplemental Logging requirements for CDC for Oracle Implementation.(ORA-1347 error

Answer

To switch on supplemental logging, execute the command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Failure to run this command at each active instance can result in an
1.) ORA-1347 error indicating that the supplemental log data could not be found.
2.) ORA-1722 error is shown.


The following quick tests can be done to validate this behavior.


Test 1
Set this parameter
drsetparm -p RedoLogThreadNumber -v 2 ( to make CDC read only RAC Node #2's log files.) No errors in this case as the SUPPLEMENTAL LOG is enabled for this node.

Insert a row into a table on node 1 it is replicated fine. Insert a row into a table on node 2 it is replicated fine.

Test 2
Set this parameter
drsetparm -p RedoLogThreadNumber -v 1 ( to make CDC read only RAC Node #1's log files.) ORA-1722 errors in this case as the SUPPLEMENTAL LOG is disabled for this node.

Insert a row in a table on node 1 it fails with ORA 1722 errors. Insert a row in a table on node 2 it fails with ORA-1722.

Test 3
Set this parameter
drsetparm -p RedoLogThreadNumber -v 0 ( to make CDC read log files from both the nodes.) ORA-1722 errors in this case as the SUPPLEMENTAL LOG is enabled for NODE 2 and disabled from node 1.

Insert a row in a table on node 1 it fails with ORA 1722 errors. Insert a row in a table on node 2 it gets replicated without any errors.

Test 4
Set this parameter
drsetparm -p RedoLogThreadNumber -v 0
Enable supplemental logging for both the nodes and everything is replicated fine.