RAC环境下执行alter database add supplemental log data;好久没有反应

chinadm123

在hpux 11.31 oracle10204 RAC环境下执行alter database add supplemental log data;被卡了好久没有反应，不知道如何是好！
有人说是有transactions没有完成，可是现在这个数据库是没有业务的。
我该如何解决这个问题呢？肯请高手指点一下！
alert中的信息：
alter database force logging
Fri Nov  4 19:15:35 2011
ALTER DATABASE FORCE LOGGING command is waiting for existingdirect writes to finish. This may take a long time.
Completed: alter database force logging
Fri Nov  4 19:15:48 2011
alter database add supplemental log data
Fri Nov  4 19:59:31 2011
SUPLOG: Supplemental log DDL failed at scn = 1195983379
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
ORA-1013 signalled during: alter database add supplemental log data...
Fri Nov  4 20:01:40 2011
CREATE SMALLFILE TABLESPACE GGS DATAFILE '/opt/oracle/oradata/orcl/GGS_01' SIZE 200M
Fri Nov  4 20:01:40 2011
ORA-1119 signalled during: CREATE SMALLFILE TABLESPACE GGS DATAFILE '/opt/oracle/oradata/orcl/GGS_01' SIZE 200M...
Fri Nov  4 20:05:06 2011
SUPLOG STATE OBJECT CLEANUP: Failed DDL needs rollback
Fri Nov  4 20:05:06 2011
SUPLOG SMON: Attempt to Rollback DDL
Fri Nov  4 20:05:06 2011
SUPLOG: Waiting to get supplemental DDL enqueue
Fri Nov  4 20:05:06 2011
SUPLOG: Commencing to rollback failed DDL at scn = 1195983827
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
Fri Nov  4 20:05:06 2011
SUPLOG: rolledback failed DDL at scn = 1195983827
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
Fri Nov  4 20:07:05 2011
CREATE SMALLFILE TABLESPACE GGS DATAFILE '/opt/oracle/oradata/orcl/GGS_01' SIZE 200M
Fri Nov  4 20:07:11 2011
Completed: CREATE SMALLFILE TABLESPACE GGS DATAFILE '/opt/oracle/oradata/orcl/GGS_01' SIZE 200M
Fri Nov  4 20:10:57 2011
alter database add supplemental log data
Fri Nov  4 20:30:01 2011
SUPLOG: Supplemental log DDL failed at scn = 1196019526
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
ORA-1013 signalled during: alter database add supplemental log data...
Fri Nov  4 20:51:19 2011
SUPLOG STATE OBJECT CLEANUP: Failed DDL needs rollback
Fri Nov  4 20:51:19 2011
SUPLOG SMON: Attempt to Rollback DDL
Fri Nov  4 20:51:19 2011
SUPLOG: Waiting to get supplemental DDL enqueue
Fri Nov  4 20:51:19 2011
SUPLOG: Commencing to rollback failed DDL at scn = 1196032922
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
Fri Nov  4 20:51:19 2011
SUPLOG: rolledback failed DDL at scn = 1196032922
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
Fri Nov  4 21:08:31 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:09:04 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:09:28 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:09:52 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:10:23 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:14:29 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:14:55 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:15:50 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc
Fri Nov  4 21:16:21 2011
System State dumped to trace file /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc

[ 本帖最后由 chinadm123 于 2011-11-4 22:41 编辑 ]

Maclean Liu(刘相兵

上传 /oracle/admin/hfmis/bdump/hfmis1_diag_6525.trc

chinadm123

#92 id 269 exid 8877  dests 00
  #93 id 269 exid 8878  dests 00
  #94 id 269 exid 8877  dests 00
.........很多这样的内容
  #248 id 269 exid 8878  dests 00
  #249 id 269 exid 8878  dests 00
  #250 id 269 exid 8877  dests 00
  #251 id 269 exid 8878  dests 00
  #252 id 269 exid 8877  dests 00
  #253 id 269 exid 8878  dests 00
  #254 id 269 exid 8877  dests 00
  #255 id 269 exid 8877  dests 00
kjctseventdump-end tail 65 heads 0 @ 0 65 @ -747981185
CLEANUP STATE OBJECTS:
----------------------------------------
SO: c0000000a9119ec8, type: 1, owner: 0000000000000000, flag: INIT/-/-/0x00
(cleanup state object) description: instance enqueue anchor state
latch: 0xc000000040009330
  ----------------------------------------
  SO: c0000002071f3bc0, type: 5, owner: c0000000a9119ec8, flag: INIT/-/-/0x00
  (enqueue) TA-00000006-00000001        DID: 0001-0003-00000015
  lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x3
  res: 0xc0000000ab3e4280, mode: X, lock_flag: 0x0
  own: 0x0000000000000000, sess: 0x0000000000000000, prv: 0xc0000000ab3e4290
  slk: 0xc0000002073d2c30
----------------------------------------
SO: c0000000a9119f28, type: 1, owner: 0000000000000000, flag: INIT/-/-/0x00
(cleanup state object) description: switchable channel handle anch
latch: 0xc00000004000a3f8
  ----------------------------------------
  SO: c00000020727d0c0, type: 11, owner: c0000000a9119f28, flag: INIT/-/-/0x00
  (broadcast handle) flag: (c2) ACTIVE SUBSCRIBER, owner: 0000000000000000,
                     event: 1, last message event: 1,
                     last message waited event: 1, messages read: 0
                     channel: (c0000000aa382660) KPON channel
                              scope: 2, event: 1, last mesage event: 0,
                              publishers/subscribers: 0/1,
                              messages published: 0
----------------------------------------
SO: c0000000a9119f88, type: 1, owner: 0000000000000000, flag: INIT/-/-/0x00
(cleanup state object) description: TT shared object cleanup SO
latch: 0xc000000040018ef8
----------------------------------------
SO: c0000000a9119fe8, type: 1, owner: 0000000000000000, flag: INIT/-/-/0x00
(cleanup state object) description: SS shared object cleanup SO
latch: 0xc000000040019460
END OF SYSTEM STATE
End of system state dump

wqqzlm

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