连接RAC数据库中单个实例（一）

有时候希望连接RAC数据库时，只连接到其中某个实例。但是要实现这个目的，并不是仅仅通过设置TNSNAMES.ORA中服务名地址列表就可以实现的。

这篇描述通过INSTANCE_NAME来实现目标。

测试环境10.2.0.3 Rac for Solaris for sparc 64：

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TESTRAC

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 testrac1

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM GV$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 testrac1
              2 testrac2

为了避免出现ORA-12545错误，在两个节点分别设置了LOCAL_LISTENER初始化参数：

SQL> ALTER SYSTEM 
  2  SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))'
  3  SID = 'testrac1';

系统已更改。

节点2：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

SQL> ALTER SYSTEM
  2  SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))'
  3  SID = 'testrac2';

系统已更改。

分别检查两个节点上LISTENER相关的配置，节点1：

SQL> SHOW PARAMETER LISTENER

NAME             TYPE        VALUE
---------------- ----------- ------------------------------
local_listener  string      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
remote_listener string      LISTENERS_TESTRAC

节点2：

SQL> SHOW PARAMETER LISTENER

NAME             TYPE        VALUE
---------------- ----------- ------------------------------
local_listener   string      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
remote_listener  string      LISTENERS_TESTRAC

其中REMOTE_LISTENER设置的值是TNSNAMES.ORA中配置的服务名，节点1上的TNSNAMES.ORA配置为：

LISTENER_TESTRAC2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))

LISTENER_TESTRAC1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))

TESTRAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
      (INSTANCE_NAME = testrac2)
    )
  )

TESTRAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
      (INSTANCE_NAME = testrac1)
    )
  )

TESTRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
    )
  )

LISTENERS_TESTRAC =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
  )

节点2上的TNSNAMES.ORA配置为：

LISTENER_TESTRAC2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))

LISTENER_TESTRAC1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))

TESTRAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
      (INSTANCE_NAME = testrac2)
    )
  )

TESTRAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
      (INSTANCE_NAME = testrac1)
    )
  )

TESTRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testrac)
    )
  )

LISTENERS_TESTRAC =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
  )

在本地测试连接，本地TNSNAMES.ORA文件中TESTRAC服务名配置为：

TESTRAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTRAC)
    )
  )

下面尝试多次连接TESTRAC数据库，检查每次连接到哪个实例上：

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

将TESTRAC配置中的地址去掉一个，结果仍然如此：

TESTRAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTRAC)
    )
  )

测试连接：

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

可以看到，即使TNSNAMES.ORA中配置了一个节点的地址，通过这个服务名访问RAC数据库，还是可能连接到两个实例上的。

为了确保通过服务名只连接到一个实例，需要在CONNECT_DATA中不仅仅指定SERVICE_NAME，同时还需要指定INSTANCE_NAME参数：

TESTRAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTRAC)
      (INSTANCE_NAME = TESTRAC1)
    )
  )

现在再次测试连接：

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-677257/，如需转载，请注明出处，否则将追究法律责任。