連線RAC資料庫中單個例項（一）

測試環境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