RAC的load balance和failover的配置过程
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
在一个RAC中存在两个节点：power1和power2，service_name是P5，SID分别为P51,P52
1、参数设置
power1:
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string P5
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string P5
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string P51
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_P51
mts_listener_address string
mts_multiple_listeners boolean FALSE
remote_listener string LISTENERS_P5
power2:
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string P5
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string P5
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string P52
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_P52
mts_listener_address string
mts_multiple_listeners boolean FALSE
remote_listener string LISTENERS_P5
2、配置服务端的listener.ora
power1:
$ vi /opt/oracle/product/9.2/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/9.2)
(SID_NAME = P51)
)
)
power2:
$ vi /opt/oracle/product/9.2/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/9.2)
(SID_NAME = P52)
)
)
配置好后，重启监听，然后执行lsnrctl service检查：
power1:
$ lsnrctl service
LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production on 27-APR-2005 17:33:42
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "P5" has 2 instance(s).
Instance "P51", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=power1)(PORT=1521)))
"DEDICATED" established:146 refused:0 state:ready
LOCAL SERVER
Instance "P52", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:129 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=power2)(PORT=1521)))
Service "P51" has 1 instance(s).
Instance "P51", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
Power2:
$ lsnrctl service
LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production on 27-APR-2005 17:36:51
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "P5" has 2 instance(s).
Instance "P51", 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=power1)(PORT=1521)))
Instance "P52", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=power2)(PORT=1521)))
"DEDICATED" established:117 refused:0 state:ready
LOCAL SERVER
Service "P52" has 1 instance(s).
Instance "P52", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
3、配置服务端的tnsnames.ora
power1:
$ vi /opt/oracle/product/9.2/network/admin/tnsnames.ora
LISTENERS_P5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
)
LISTENER_P51 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
)
)
P51 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = P5)
(INSTANCE_NAME = P51)
)
)
P52 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = P5)
(INSTANCE_NAME = P52)
)
)
P5 =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = P5)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
power2:
$ vi /opt/oracle/product/9.2/network/admin/tnsnames.ora
LISTENERS_P5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
)
LISTENER_P52 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
)
P51 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = P5)
(INSTANCE_NAME = P51)
)
)
P52 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = P5)
(INSTANCE_NAME = P52)
)
)
P5 =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = P5)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
测试
$ tnsping P51
TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production on 27-APR-2005 17:43:37
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = P5) (INSTANCE_NAME = P51)))
OK (10 msec)
$ tnsping P52
TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production on 27-APR-2005 17:43:54
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = P5) (INSTANCE_NAME = P52)))
OK (0 msec)
$ tnsping P5
TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production on 27-APR-2005 17:44:13
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/opt/oracle/product/9.2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (LOAD_BALANCE = yes) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = power1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = power2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = P5)))
OK (10 msec)
4、配置客户端的tnsnames.ora
P5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.111.231)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.111.232)(PORT = 1521))
)
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVICE_NAME = P5)
(FAILOVER_METHOD =
(TYPE = SESSION)
(METHOD = BASIC)
)
)
)
测试：
D:\HwJava\sqlstress>tnsping P5
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 27-4月 -2005 16:42:34
Copyright (c) 1997 Oracle Corporation. All rights reserved.
已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.111.231)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.111.232)(PORT = 1521))) (LOAD_BALANCE = ON) (FAILOVER = ON) (CONNECT_DATA = (SERVICE_NAME = P5) (FAILOVER_METHOD = (TYPE = SESSION) (METHOD =BASIC))))
OK（20毫秒）
5、配置客户端的 hosts
这个一定要配！
对xp，hosts文件的路径是C:\WINDOWS\system32\drivers\etc\hosts
10.71.111.231 power1
10.71.111.232 power2
测试：
D:\HwJava\sqlstress>ping power1
Pinging power1 [10.71.111.231] with 32 bytes of data:
Reply from 10.71.111.231: bytes=32 time<1ms TTL=251
D:\HwJava\sqlstress>ping power2
Pinging power2 [10.71.111.232] with 32 bytes of data:
Reply from 10.71.111.232: bytes=32 time<1ms TTL=251
6、连接测试：
C:\Documents and Settings\H36922>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 4月 27 17:20:33 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P52
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P52
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P51
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P52
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P51
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P52
SQL> conn system/manager@p5
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
P51
多次连接，会分别连到P51、P52两个节点。
6、java通过OCI连接
package racbalance;
import java.sql.*;
import oracle.jdbc.driver.OracleDriver;
public class MyThread extends Thread {
String ThreadName="";
public MyThread(String sName) {
this.ThreadName = sName;
}
public void run()
{
try {
ConnOracle();
}
catch (SQLException ex) {
System.out.print( ex.toString() );
}
}
public void ConnOracle() throws SQLException
{
int total = 1000;
int sucessful = 0;
int fail = 0;
int aip1 = 0;
int aip2 = 0;
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
while (total > 0) {
if (total % 100 == 0 && total != 1000) {
System.out.println(ThreadName);
System.out.print("power1=");
System.out.println(aip1);
System.out.print("power2=");
System.out.println(aip2);
System.out.print("sussful=");
System.out.println(sucessful);
System.out.print("fail=");
System.out.println(fail);
System.out.println("-------------------------------------------------");
}
total--;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String url = "jdbc:oracle:oci:@p5";
conn = DriverManager.getConnection(url, "system", "manager");
stmt = conn.createStatement();
rset = stmt.executeQuery("select host_name from v$instance");
while (rset.next()) {
String res = null;
res = rset.getString(1);
System.out.println(ThreadName+':'+res);
if (res.equals("power1"))
aip1++;
else
aip2++;
// System.out.println( res );
}
sucessful++;
}
catch (Exception e) {
fail++;
System.out.println(e.getMessage());
}
finally {
if (rset != null)
rset.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
}
try {
Thread.currentThread().sleep(10);
}
catch (Exception e) {
System.out.println("sleep exception");
}
}
System.out.print("power1=");
System.out.println(aip1);
System.out.print("power2=");
System.out.println(aip2);
System.out.print("sussful=");
System.out.println(sucessful);
System.out.print("fail=");
System.out.println(fail);
double dd = 0.00;
System.out.print("aip1/aip2=");
if (aip2 > 0) {
dd = aip1 / aip2;
System.out.println(dd);
}
else {
System.out.println("~~~~~~~~");
}
System.out.print("fail/sucessful=");
if (sucessful > 0) {
dd = fail / sucessful;
System.out.println(dd);
}
else {
System.out.println("~~~~~~~~");
}
}
}
| 上一篇：Oracle的语句中的提示(1) | 下一篇：创建监控表的DML的触发器 |
| 本类中的所有文章 |