Enable Supplemental Logging

We can enable supplemental logging using,

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

But sometimes while enabling the supplemental logging we can face issue.


Issue:
Enabling supplemental logging hang or take too much time to complete.

Cause:
The statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  is waiting for TX lock in shared mode when there is any uncommitted transaction. This is the expected behavior.
You can issue ALTER DATABASE ADD SUPPLEMENTAL LOG DATA when the database is open. However, Oracle Database will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. Besides,we also need to wait for the completion of all the in-flight transaction so that any redo generated after this DDL would have the right supplemental logging attributes.

Solution:
1) Wait for the completion of all the in-flight transaction.
or
2) In those databases with 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;