11g R2的SCAN故障切换问题

gjm008 · 发表于 2010-5-21 11:40

刚刚装好11g R2  4节点的实验环境。有些方面不是很明白，请高手帮忙。

hosts 信息：
[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1             rac1 localhost.localdomain localhost
#::1          localhost6.localdomain6 localhost6
#public
192.168.4.221 rac1.localdomain rac1
192.168.4.222 rac2.localdomain rac2
192.168.4.223 rac3.localdomain rac3
192.168.4.224 rac4.localdomain rac4
#private
10.10.30.11 rac1-priv.localdomain rac1-priv
10.10.30.12 rac2-priv.localdomain rac2-priv
10.10.30.13 rac3-priv.localdomain rac3-priv
10.10.30.14 rac4-priv.localdomain rac4-priv
#virtual
192.168.4.216 rac1-vip.localdomain rac1-vip
192.168.4.217 rac2-vip.localdomain rac2-vip
192.168.4.218 rac3-vip.localdomain rac3-vip
192.168.4.219 rac4-vip.localdomain rac4-vip
#scan
192.168.4.201 rac-cluster.localdomain rac-cluster

SQL> show parameter service
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
service_names                      string    king

--节点1的local_listener参数，
SQL> show parameter local_listener
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
local_listener                      string    (DESCRIPTION=(ADDRESS_LIST=(AD
                                             DRESS=(PROTOCOL=TCP)(HOST=192.
                                             168.4.216)(PORT=1521))))

远端使用SQLPLUS ，连接SCAN  的ip登录数据库，自动登录节点3.
C:\> sqlplus [email=system/<a href=]123456@192.168.4.201[/email]:1521/king" target="_blank">system/123456@192.168.4.201:1521/king

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 5月 21 08:47:24 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
king3

SQL> select instance_name from gv$instance;
INSTANCE_NAME
----------------
king4
king3
king2
king1

这时节点3 down 机，
[oracle@rac3 admin]$ sqlplus / as sysdba

SQL> shutdown abort;
ORACLE instance shut down.

返回远端sqlplus 操作时，提示连接断开，
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束。

不清楚11g R2是怎样进行故障切换的，是我操作不对，还是漏了什么步骤。

paulyibinyi · 发表于 2010-5-21 11:46

你客户端要配置故障切换功能
即tnsnames.ora

gjm008 · 发表于 2010-5-21 11:52

我是直接用scan 的ip连接的，本地没有配置tnsnames.ora的，11g r2 里的scan应该就是代替tnsnames.ora的吧，对这个理解不深，还请指点。

paulyibinyi · 发表于 2010-5-21 11:57

那你远端sqlplus 客户端连接是用什么配置

gjm008 · 发表于 2010-5-21 12:38

远端tnsnames.ora 增加故障切换内容：
king1=
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.216)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.217)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.218)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.219)(PORT = 1521))
)
(LOAD_BALANCE=yes)
(FAILOVER=yes)
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = klir)
   (FAILOVER_MODE =
      (TYPE = SELECT)
      (METHOD = BASIC)
      (RETRIES = 180)
      (DELAY = 5)
   )
)
  )
再使用king1连接，就可以自动进行故障切换。
C:\Documents and Settings\gao> sqlplus system/123456@king1
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 5月 21 11:53:24 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options

如果不使用tnsnames.ora方式连接，直接使用 rac-cluster（IP 192.168.4.201）连接，
C:\> sqlplus system/123456@192.168.4.201:1521/king
这样也可以登录系统，但不能自动故障切换，请问这种方式应该怎么设置？

paulyibinyi · 发表于 2010-5-21 13:14

这种方式应该没办法

或者你在tnsnames.ora里面加入
scan ip
king1=
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.201)(PORT = 1521))
      )
(LOAD_BALANCE=yes)
(FAILOVER=yes)
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = klir)
   (FAILOVER_MODE =
      (TYPE = SELECT)
      (METHOD = BASIC)
      (RETRIES = 180)
      (DELAY = 5)
   )
)
  )

gjm008 · 发表于 2010-5-21 14:10

原帖由 paulyibinyi 于 2010-5-21 13:14 发表
这种方式应该没办法

或者你在tnsnames.ora里面加入
scan ip
king1=
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.201)(PORT = 1521))
      )
(LOAD_BALANCE=yes)
(FAILOVER=yes)
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = klir)
   (FAILOVER_MODE =
      (TYPE = SELECT)
      (METHOD = BASIC)
      (RETRIES = 180)
      (DELAY = 5)
   )
)
  )