Alter Database Add Supplemental Log Data 命令挂起

DBA小记 2021-02-24

4606

配置logminer，对Oracle配置追加日志的时候，命令长时间挂起：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

alert日志：

SUPLOG: Previous supplemental logging attributes at scn = 3678763842390
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = ON, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 3678763842487
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = ON, foreign key = OFF, all column = ON
SUPLOG:  procedural replication = OFF

MOS上说明：

当有任何未提交的事务时，ALTER DATABASE ADD追加日志数据正在共享模式中等待TM锁。

建议是：

STARTUP MOUNT

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE OPEN;

等待所有未决事务的完成吗，还可以找到阻塞者和等待者来kill session。

查询阻塞会话是tomcat应用shutdown后未关闭连接，已无事务，但还存在锁。

检查阻塞会话SQL：

select INST_ID,
       SID,
       TYPE,
       ID1,
       ID2,
       LMODE,
       REQUEST,
       CTIME,
       BLOCK,
       DECODE(BLOCK, 0, '', 'blocker') blocker,
       DECODE(request, 0, '', 'waiter') waiter
  from gv$lock
 where (ID1, ID2, TYPE) in
       (select ID1, ID2, TYPE from gv$lock where request > 0)
 order by blocker;

关闭错误应用程序后恢复。

--如需kill session:

alter system kill session 'sid,serial#';

MOS文档：

Alter Database Add Supplemental Log Data Hangs [ID 406498.1]
--------------------------------------------------------------------------------
  修改时间 19-JUL-2011     类型 PROBLEM     状态 PUBLISHED  

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References
--------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms
As part of the Streams setup you can specify supplemental logging at the database level.

It can be done manually:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

or implicitly running procedures like DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
or if you setup Streams using Grid Control it is automatically executed by SrcSchemaRule1 Stage Step 12.
In some cases, this step hangs and the statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA remains waiting for TX lock in shared mode.

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

References
ORA-32593 database supplemental logging attributes in flux
相关内容
--------------------------------------------------------------------------------
产品
--------------------------------------------------------------------------------
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
--------------------------------------------------------------------------------
HANGING; LOCK; STREAMS; TX LOCK

数据库

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Alter Database Add Supplemental Log Data 命令挂起

评论