测试添加删除11.2 RAC节点的时候，碰到了这个bug。
通过dbca添加实例，又通过dbca删除实例。检查数据库的日志：
SQL> select group#, thread#, sequence#,
bytes, status
2
from v$log;
GROUP# THREAD# SEQUENCE#
BYTES STATUS
---------- ---------- ---------- ---------- ----------------
1
1 11 524288000 INACTIVE
2 1 12
524288000 CURRENT
3 2 5
524288000 CURRENT
4 2 4
524288000 INACTIVE
7 4 1
524288000 ACTIVE
8 4 0
524288000 UNUSED
6 rows selected.
实例3对应thread 3的日志已经被清除，但是怎么会多出来一个thread 4的日志。这个thread 4是什么时候加入到数据库中的。
可惜在删除了实例3后才发现这个问题，因此实例3上所有的日志都已经没有了。
不过dbca添加和删除节点都是在实例1上进行的，检查实例1对应的alert文件：
Thu Dec 23 16:35:58 2010
CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS3" DATAFILE SIZE 2048M AUTOEXTEND
ON NEXT 5120K MAXSIZE 65535M BLOCKSIZE 16384
Thu Dec 23 16:36:32 2010
Completed: CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS3" DATAFILE SIZE 2048M
AUTOEXTEND ON NEXT 5120K MAXSIZE 65535M BLOCKSIZE 16384
ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5
SIZE 512000K,
GROUP 6
SIZE 512000K
Thu Dec 23 16:37:03 2010
Completed: ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 SIZE 512000K,
GROUP 6
SIZE 512000K
ALTER DATABASE ENABLE THREAD 3
Completed: ALTER DATABASE ENABLE THREAD 3
ALTER SYSTEM SET instance_number=3 SCOPE=SPFILE SID='testrac3';
ALTER SYSTEM SET thread=3 SCOPE=SPFILE SID='testrac3';
ALTER SYSTEM SET undo_tablespace='UNDOTBS3' SCOPE=SPFILE SID='testrac3';
Thu Dec 23 16:38:18 2010
Reconfiguration started (old inc 4, new inc 6)
List of instances:
1 2 3 (myinst: 1)
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Dec 23 16:38:18 2010
LMS 0: 0 GCS shadows cancelled, 0 closed,
0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
Thu Dec 23 18:26:38 2010
Reconfiguration started (old inc 6, new inc 8)
List of instances:
1 2 (myinst: 1)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid
= TRUE
Communication channels reestablished
Thu Dec 23 18:26:38 2010
* domain 0 not valid according to instance
2
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Dec 23 18:26:38 2010
LMS 0: 1 GCS shadows cancelled, 0 closed,
0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
Thu Dec 23 18:27:15 2010
ALTER DATABASE DISABLE THREAD 3
Completed: ALTER DATABASE DISABLE THREAD 3
ALTER SYSTEM ARCHIVE LOG
ALTER DATABASE DROP LOGFILE GROUP 5
Deleted Oracle managed file +DATA/testrac/onlinelog/group_5.296.738520593
Deleted Oracle managed file +DATA/testrac/onlinelog/group_5.297.738520601
Completed: ALTER DATABASE DROP LOGFILE GROUP 5
ALTER DATABASE DROP LOGFILE GROUP 6
Deleted Oracle managed file +DATA/testrac/onlinelog/group_6.298.738520609
Deleted Oracle managed file +DATA/testrac/onlinelog/group_6.299.738520617
Completed: ALTER DATABASE DROP LOGFILE GROUP 6
DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES
Deleted Oracle managed file +DATA/testrac/datafile/undotbs3.295.738520559
Completed: DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES
ALTER SYSTEM RESET thread SCOPE=SPFILE SID='testrac3';
ALTER SYSTEM RESET instance_number SCOPE=SPFILE SID='testrac3';
ALTER SYSTEM RESET undo_tablespace SCOPE=SPFILE SID='testrac3';
可以看到完整的实例3添加thread 3日志以及最后删除实例是对应的diable thread 3的过程。
这里没有thread 4的信息，检查一些节点2上的alert文件：
Thu Dec 23 16:37:19 2010
Redo thread 3 internally disabled at seq 1 (CKPT)
Thu Dec 23 16:37:19 2010
ARC3: Archiving disabled thread 3 sequence 1
Archived Log entry 10 added for thread 3 sequence 1 ID 0x6e4846f dest 1:
Thu Dec 23 16:38:18 2010
Reconfiguration started (old inc 4, new inc 6)
List of instances:
1 2 3 (myinst: 2)
Global Resource Directory frozen
Communication channels reestablished
Thu Dec 23 16:38:18 2010
* domain 0 valid = 1 according to instance
1
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Dec 23 16:38:18 2010
LMS 0: 0 GCS shadows cancelled, 0 closed,
0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
Thu Dec 23 16:38:19 2010
alter database add logfile thread 4 SIZE 524288000 , SIZE 524288000
Thu Dec 23 16:38:53 2010
Completed: alter database add logfile thread 4 SIZE 524288000 , SIZE 524288000
alter database enable public thread 4
Completed: alter database enable public thread 4
Thu Dec 23 16:47:54 2010
db_recovery_file_dest_size of 65536 MB is 6.15% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Dec 23 18:26:38 2010
Reconfiguration started (old inc 6, new inc 8)
List of instances:
1 2 (myinst: 2)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid
= TRUE
Communication channels reestablished
Thu Dec 23 18:26:38 2010
* domain 0 valid = 0 according to instance
1
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Thu Dec 23 18:26:38 2010
LMS 0: 0 GCS shadows cancelled, 0 closed,
0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Thu Dec 23 18:26:39 2010
Instance recovery: looking for dead threads
Submitted all GCS remote-cache requests
Instance recovery: lock domain invalid but no dead threads
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
在实例2上果然看到了thread 4启用以及添加日志的信息。
查询v$thread可以看到详细的信息：
SQL> select thread#, status, enabled,
instance, current_group#, sequence#
2
from v$thread;
THREAD# STATUS ENABLED INSTANCE CURRENT_GROUP# SEQUENCE#
---------- ------ -------- -------------------- -------------- ----------
1 OPEN PUBLIC
testrac1 2 12
2 OPEN PUBLIC
testrac2 3 5
4 CLOSED PUBLIC UNNAMED_INSTANCE_4 0 1
奇怪的是，这里的操作并不是我执行的，而且可以确认的是，也不可能是其他人执行的，那么只有一个可能性，就是Oracle自动运行的。
如果这个动作没有什么额外的深意，那么就是bug。在metalink上查询了一下，居然这么新的bug也能找到：Bug 9916360: EXTRA ONLINE REDO LOG FILES GENERATED FOR NON-EXISTENT RAC THREADS。
这个bug的状态还没有被fixed，在11.2.0.2的bug列表中倒是包括了这个bug的BASE bug号，但是没有进一步的说明。
其实解决导致的问题并不复杂：
SQL> alter database disable thread 4;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> select group#, thread#, sequence#,
bytes, status
2
from v$log;
GROUP# THREAD# SEQUENCE#
BYTES STATUS
---------- ---------- ---------- ---------- ----------------
1 1 11
524288000 INACTIVE
2 1
12 524288000 CURRENT
3 2 5
524288000 CURRENT
4 2 4
524288000 INACTIVE
SQL> select thread#, status, enabled,
instance, current_group#, sequence#
2
from v$thread;
THREAD# STATUS ENABLED INSTANCE CURRENT_GROUP# SEQUENCE#
---------- ------ -------- -------------------- -------------- ----------
1 OPEN PUBLIC
testrac1 2 12
2 OPEN PUBLIC
testrac2 3 5