SUSPEND导致RAC环境的ORA-29702错误（三）
在测试RAC环境的SUSPEND命令时，出现了这个错误。
对比两个节点在10g和11g的不同表现。
SUSPEND导致RAC环境的ORA-29702错误（一）：http://yangtingkun.itpub.net/post/468/483337
SUSPEND导致RAC环境的ORA-29702错误（二）：http://yangtingkun.itpub.net/post/468/483368
前面都是3个实例的RAC环境进行的测试，下面简化一下测试的环境，将测试的环境变成两个节点。首先测试的是10.2.0.3 for Solaris sparc 64版本：
bash-2.03$ srvctl status db -d testrac
Instance testrac1 is running on node racnode1
Instance testrac2 is running on node racnode2
Instance testrac3 is running on node racnode3
bash-2.03$ srvctl stop inst -d testrac -i testrac3
bash-2.03$ srvctl status db -d testrac
Instance testrac1 is running on node racnode1
Instance testrac2 is running on node racnode2
Instance testrac3 is not running on node racnode3
bash-2.03$ srvctl stop inst -d testrac -i testrac2
bash-2.03$ srvctl status db -d testrac
Instance testrac1 is running on node racnode1
Instance testrac2 is not running on node racnode2
Instance testrac3 is not running on node racnode3
确保整个测试过程中实例3不启动，下面在实例1将数据库置于suspend状态：
bash-2.03$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 2月 26 18:57:57 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select instance, status, database_status, startup_time from v$instance;
select instance, status, database_status, startup_time from v$instance
*
第 1 行出现错误:
ORA-00904: "INSTANCE": 标识符无效
SQL> select instance_name, status, database_status, startup_time from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
---------------- ------------ ----------------- -------------------
testrac1 OPEN ACTIVE 2009-02-21 23:09:49
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
testrac1 OPEN ACTIVE
SQL> alter system suspend;
系统已更改。
下面在节点2启动实例2：
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 2月 26 19:17:55 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 2147483648 bytes
Fixed Size 2031480 bytes
Variable Size 436207752 bytes
Database Buffers 1694498816 bytes
Redo Buffers 14745600 bytes
实例2启动过程被阻塞，下面在实例1执行RESUME操作，检查实例1是否发生重启：
SQL> alter system resume;
系统已更改。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
testrac1
SQL> select instance_name, status, database_status from gv$instance;
select instance_name, status, database_status from gv$instance
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束
实例2这时恢复正常，不过查询G$INSTANCE时同样被阻塞，直到实例1重启完成：
数据库装载完毕。
数据库已经打开。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
testrac2
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
testrac2 OPEN ACTIVE
在实例1重新连接数据库，发现实例已经重启过：
SQL> conn / as sysdba
已连接。
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select instance_name, status, database_status, startup_time from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
---------------- ------------ ----------------- -------------------
testrac1 OPEN ACTIVE 2009-02-26 19:24:28
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
testrac1 OPEN ACTIVE
testrac2 OPEN ACTIVE
可以看到，在10g的RAC环境，如果数据库处于SUSPEND状态，且其他实例在这个过程中启动，会导致已启动的实例发生重启，并产生ORA-29702错误。
下面看看11g的情况：
bash-3.00$ srvctl status db -d test
Instance test1 is running on node ser1
Instance test2 is running on node ser2
bash-3.00$ srvctl stop inst -d test -i test2
bash-3.00$ export ORACLE_SID=test1
bash-3.00$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 28 11:33:01 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test1
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
test1 OPEN ACTIVE
SQL> alter system suspend;
系统已更改。
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
test1 OPEN SUSPENDED
下面在节点2启动实例2：
bash-3.00$ export ORACLE_SID=test2
bash-3.00$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 28 12:05:21 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 776896512 bytes
Fixed Size 2098776 bytes
Variable Size 229300648 bytes
Database Buffers 541065216 bytes
Redo Buffers 4431872 bytes
实例1执行RESUME操作：
SQL> alter system resume;
系统已更改。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test1
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
test1 OPEN ACTIVE
test2 OPEN ACTIVE
实例2上实例打开：
数据库装载完毕。
数据库已经打开。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test2
SQL> select instance_name, status, database_status from gv$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
test2 OPEN ACTIVE
test1 OPEN ACTIVE
可以看到，数据库实例1并没有重启，这是11g和10g数据库的不同。
检查后台alert文件，也找不到ORA-29702错误，看来11g rac已经修正了10g rac的问题。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 断开
bash-3.00$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on 星期二 4月 28 12:34:51 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/data/oracle"
adrci> show home
ADR Homes:
diag/rdbms/ractest/ractest1
diag/rdbms/dummy/rac11g1
diag/rdbms/dummy/abc
diag/rdbms/rac11g/rac11g1
diag/rdbms/demo/rac11g1
diag/rdbms/rac11gs/rac11g1
diag/rdbms/unknown/rac11g1
diag/rdbms/rac11g_s/rac11g1
diag/rdbms/test/test
diag/rdbms/test/test1
diag/rdbms/test/TEST1
diag/rdbms/dbua0/DBUA0
diag/asm/+asm/+ASM1
diag/clients/user_oracle/host_948296199_11
diag/tnslsnr/ser1/listener_ser1
diag/tnslsnr/ser1/listener
adrci> set home diag/rdbms/test/test1
adrci> show alert -p "message_text like '%ORA-29702%'"
ADR Home = /data/oracle/diag/rdbms/test/test1:
*************************************************************************
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-592123/ ，如需转载，请注明出处，否则将追究法律责任。
