oracle_11g_RAC负载均衡实现

主机：

SQL>  show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 33.96.107.6)(PORT=1521))
remote_listener                      string      LISTENERS_VGOPSDP
备机：

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 33.96.107.7)(PORT=1521))
remote_listener                      string      LISTENERS_VGOPSDP

主机tnsnames.ora

% cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/db/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_VGOPSDP2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))

LISTENER_VGOPSDP1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))

LISTENERS_VGOPSDP =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
  )

VGOPSDP2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
      (INSTANCE_NAME = vgopsdp2)
    )
  )

VGOPSDP1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
      (INSTANCE_NAME = vgopsdp1)
    )
  )

VGOPSDP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
    )
  )

%

备机tnsnames.ora

% cat tnsnames.ora
# tnsnames.ora.sdp_cmcdb02 Network Configuration File: /home/oracle/db/product/11.1.0/db_1/network/admin/tnsnames.ora.sdp_cmcdb02
# Generated by Oracle configuration tools.

LISTENER_VGOPSDP2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))

LISTENER_VGOPSDP1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))

LISTENERS_VGOPSDP =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
  )

VGOPSDP2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
      (INSTANCE_NAME = vgopsdp2)
    )
  )

VGOPSDP1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
      (INSTANCE_NAME = vgopsdp1)
    )
  )

VGOPSDP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = flotip_2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vgopsdp)
    )
  )

%

每次连接都会连接到备机实例上，这是不是没有实现负载均衡呢

SQL> conn vgopcmc/vgopcmc@vgopsdp
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
vgopsdp2

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
vgopsdp2

SQL> conn vgopetl/vgopetl@vgopsdp
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
vgopsdp2

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
vgopsdp2

SQL>

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

转载于:http://blog.itpub.net/24945904/viewspace-678621/