The configuration relationship between ORACLERAC load balancing and local_listener and remote_listener parameters

The configuration relationship between ORACLE RAC load balancing and local_listener and remote_listener parameters
the
The configuration relationship between ORACLE RAC load balancing and local_listener and remote_listener parameters
RAC load balancing is mainly implemented in two ways: client and server.
the
1. The implementation of the client is realized by directly configuring the LOAD_BALANCE parameter in tnsnames.ora. The default value is NO
clinet_LB =
(DESCRIPTION =
(LOAD_BALANCE = YES)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.243)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.244)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.245)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.246)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = srv_epm1)
)
)
2. Server-side load balancing LB settings. It is mainly realized through two initialization parameters local_listener and remote_listener. Through these two parameters, oracle registers with the listener pointed to by these two parameters, so that the listener can know the status of each service, and then perform connection distribution.
11gr2 version, the default settings of these two parameters are as follows
the
SQL> show parameter listener
the
NAME ……
———————————— ———– — —————————
listener_networks string
local_listener           string (DESCRIPTION=(ADDRESS_LIST=(AD
.
.124.244)(PORT=1521))))
remote_listener           string   dtydb-scan2:1521
www.2cto.com
Due to the above settings, the database instance is registered with local_listener and remote_listener respectively.
the
The local listener only registers two instances of +ASM2 and epmdb2
the
LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER
LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM2”, status READY, has 1 handler(s) for this service…  
Handler(s):
“DEDICATED” established:10765 refused:0 state:ready
LOCAL SERVER
Service “epmdb” has 1 instance(s).
Instance “epmdb2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:10 refused:0 state:ready
LOCAL SERVER
The command completed successfully
the
The SCAN listener registers the default service of all database instances and the service srv_epm1 running on the epmdb1 instance
View service registration status, SCAN
the
LSNRCTL> set current_listener LISTENER_SCAN1
Current Listener is LISTENER_SCAN1
LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary…
Service “epmdb” has 3 instance(s).
Instance “epmdb1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
  REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.124.243)(PORT=1521)))
Instance “epmdb2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
  REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.124.244)(PORT=1521)))
Instance “epmdb3”, status READY, has 1 handler(s) for this service…
  Handler(s): www.2cto.com  
“DEDICATED” established:4 refused:0 state:ready
  REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.124.245)(PORT=1521)))
Service “srv_epm1” has 1 instance(s).
Instance “epmdb1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
  REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.124.243)(PORT=1521)))
The command completed successfully
the
The configuration of tnsnames.ora is as follows
tydb_srv_epm1 =
(DESCRIPTION =
(LOAD_BALANCE = NO)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.243)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.244)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.245)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.246)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = epmdb)
)
)
epmdb_scan=
(DESCRIPTION =
(LOAD_BALANCE = NO)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.230)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = epmdb)
)
)
the
When we connect to tydb_srv_epm1, due to the order relationship, it will connect to the epmdb1 instance
the
sqlplus system/oracle@tydb_srv_epm1
the
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 14 16:10:21 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
the
SQL> select instance_name from v$instance;
www.2cto.com
INSTANCE_NAME
—————-
epmdb1
the
SQL> exit
the
When connecting to the scan IP, because the scan listener registers each database instance, the effect of load balancing occurs
the
>sqlplus system/oracle@epmdb_scan
the
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 14 16:11:26 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
the
SQL> select instance_name from v$instance;
the
INSTANCE_NAME
—————-
epmdb1
the
SQL> exit
From Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options disconnected
the
sqlplus system/oracle@epmdb_scan
the
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 14 16:12:57 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
the
SQL> select instance_name from v$instance;
the
INSTANCE_NAME
—————-
epmdb3
the
SQL>
the
3. For versions above 10gr2, TAF is mainly implemented through service configuration. The service srv_epm1 has been configured as follows as an example
the
[grid@dtydb3 ~]$ srvctl config service -d epmdb -s srv_epm1
Service name: srv_epm1
Service is enabled
Server pool: epmdb_srv_epm1
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: epmdb1
Available instances: epmdb2
the
The connection configuration of tnsnames.ora is as follows
the
epmdb_scan_taf =
(DESCRIPTION =
(LOAD_BALANCE = NO)
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.124.230)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = srv_epm1)
)
)
the
Since the service only runs on epmdb1, it must be connected to the epmdb1 instance
the
sqlplus system/oracle@epmdb_scan_taf
the
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 14 16:21:42 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
the
SQL> select instance_name from v$instance;
the
INSTANCE_NAME
—————-
epmdb1
the
After the database instance EPMDB1 is down, the service is switched, and the database is connected again, which is already running on instane 2.
sys@EPMDB1(10.4.124.233)> shutdown immediate;
the
[grid@dtydb3 ~]$ srvctl status service -d epmdb -s srv_epm1
Service srv_epm1 is running on instance(s) epmdb2
www.2cto.com
SQL> select instance_name from v$instance;
the
INSTANCE_NAME
—————-
epmdb1
the
SQL> /
the
INSTANCE_NAME
—————-
epmdb2
the
the
the
Author hijk139