Add Supplemental Log 异常等待

今天在配置OGG得时候需要开启Add Supplemental Log以前配置STANDBY的时候也开启，当时没有问题，所以今天就大胆执行了，但是经历了一段时间的等待，然后查询发现，所以以后要注意一下，这个操作会等待TX所即便是S模式也会等待，也就是说不能有任何的DML或者DDL正在操作。

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, OracleDatabase 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

You need to wait for the completion of all the in-flight transaction.

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;

MOS发现：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1101024/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1101024/