Shulou(Shulou.com)06/01 Report--

Oracle cluster database sometimes does not achieve automatic load balancing, and almost all business is on a certain node, so it is easy to cause downtime of a certain node. In view of this situation, we need to configure load balancer manually for a period of time. There are two methods for load balancing configuration: server-side and client-side (but I find that the effect of server-side configuration is more obvious)

I. Server-side configuration

1. First of all, the tnsnames.ora file on each node server should be carried out as follows. Pay attention to the red part, and I suggest writing the IP address directly in the value after HOST. If you write the hostname of the hosts file, there may be a TNS connection timeout.

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.153) (PORT = 1521)) / * IP*/ of one node

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.154) (PORT = 1521)) / * two-node IP*/

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.155) (PORT = 1521)) / * if it is 11G, you need to write scan ip too, otherwise you can't connect with scan ip * /

(LOAD_BALANCE = yes) / * this parameter must be added, otherwise load balancing cannot be achieved * /

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

)

LISTENERS_RACDB =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.153) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.154) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.155) (PORT = 1521))

)

2. Any node performs the following operations. Set the parameter remote_listeners in the initialization parameters

SQL > conn system/admin@racdb

Connected.

SQL > show parameter remote_listener

NAME TYPE VALUE

-

Remote_listener string / * you can see that the current value is empty * /

SQL >

SQL > alter system set remote_listener='LISTENERS_RACDB' sid='*'; / * this value must be the same as in the previous tnsnames.ora file * /

# (the reset command can undo the setting and restore the default value)

The system has changed.

Show parameter remote_listener

NAME TYPE VALUE

-

Remote_listener string LISTENERS_RACDB / * you can see that the modified one has already taken effect * /

3. Now you can take a look at the listening information. After configuring the parameters correctly, you can see that there are 2 instance on the listener after the listener is started through the lsnrctl status command.

[grid@node01 bin] # lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0-Production on 18-DEC-2014 05:40:08

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))

STATUS of the LISTENER

-

Alias LISTENER_NODE01

Version TNSLSNR for Linux: Version 10.2.0.1.0-Production

Start Date 18-DEC-2014 00:18:07

Uptime 0 days 15 hr. 22 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File / orac/orahome/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File / orac/orahome/oracle/product/11.2.0/db_1/network/log/listener_node01.log

Listening Endpoints Summary...

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.230.153) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.230.154) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.230.155) (PORT=1521))

(DESCRIPTION= (ADDRESS= (PROTOCOL=ipc) (KEY=EXTPROC)

Services Summary...

Service "+ ASM" has 1 instance (s).

Instance "+ ASM1", status BLOCKED, has 1 handler (s) for this service...

Service "+ ASM_XPT" has 1 instance (s).

Instance "+ ASM1", status BLOCKED, has 1 handler (s) for this service...

Service "RACDB" has 2 instance (s).

Instance "RACDB1", status READY, has 2 handler (s) for this service...

Instance "RACDB2", status READY, has 1 handler (s) for this service...

Service "RACDBXDB" has 2 instance (s).

Instance "RACDB1", status READY, has 1 handler (s) for this service...

Instance "RACDB2", status READY, has 1 handler (s) for this service...

Service "RACDB_XPT" has 2 instance (s).

Instance "RACDB1", status READY, has 2 handler (s) for this service...

Instance "RACDB2", status READY, has 1 handler (s) for this service...

The command completed successfully

II. Client configuration

In every client or JDBC program, use the parameter LOAD_BALANCE = yes.

The tnsnames.ora of the client is modified as follows

RAC_DB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.153) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.230.154) (PORT = 1521))

(LOAD_BALANCE = yes)

(FAILOVER = on)

)

(CONNECT_DATA =

(SERVICE_NAME = RAC_DB)

(FAILOVER_MODE = (TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 20)

(DELAY = 10)

)

)

)

These parameters of the standard red represent the meaning:

LOAD_BALANCE=YES represents load balancing, and this should be the load balancing of the client, that is, random connection allocation.

FAILOVER = ON represents the failover when the client connects. That is to say, when establishing a session, if one node is found to be malfunctioning, the other node will be detected. The default is on. But now ORACLE generally uses TAF, which means that even if a failure occurs during the connection, it will be transferred to another node to continue the operation.

Failover_mode has several parameters, TYPE MOTHOD RETRIES DELAY.

The value of mothod is basic preconnect (the former establishes a connection to other nodes after perceiving an error, while the latter establishes a connection to other nodes in advance)

TYPE values are SELECT or SESSION (the former will not discard the query results on the failed node and continue the remaining operations on the new node, while the latter will re-operate and discard the previous operations)

Retries is the number of retries

DELAY retry interval, you can also use SERVICE to do TAF on the server side.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.