OGG添加附加日志报错，求高手DBA解决此问题

smile_jackchen

今天去一个业务系统远端部署ogg，安装过程还算比较顺利，准备启动抓取进程的时候报了一个错误，如图。
看到错误显示没开附加日志，然后去开启附加日志，可是也报错ORA-32593: database supplemental logging attributes in flux,网上查了一下也没有比较好的解决办法，求助各位帮忙提供个比较直观的方法。

xifenfei

Error:          ORA-32593  (ORA-32593)Text:          database supplemental logging attributes in flux ---------------------------------------------------------------------------Cause:        there is another process actively modifying the database wide         supplemental logging attributes. Action:        Retry the DDL or the LogMiner dictionary build that raised this         error. ////////////////////////// LOB RETENTION and FLASHBACK         //////////////////////////
你这个是不是以前加了，但是没有成功，进程一直还在执行，如果你想诊断，可以按照下面的步骤进行尝试分析看看，如果你不想诊断，直接跳到SOLUTION  重启数据库到mount状态下执行辅助日志

In some cases, this step hangs and the statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA remains waiting for TX lock in shared mode.

1. Identify the problematic session via

connect / as sysdba

alter session set tracefile_identifier='SUPP';

alter session set max_dump_file_size=unlimited;

alter session set events '32593 errorstack(3) systemstate(266)';

ALTER DATABASE add SUPPLEMENTAL LOG DATA;

Upload the *SUPP* from the database trace directory.

Note that if the database has a lot of sessions associated with it dumping the systemstate will take time and generate a large trace file.

Then kill the session.

Possibly looking at blockers and waiters would be simpler.

CHANGES
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;