RAC服务器端负载均衡失效，请各位高手指教！

显示全部楼层 · 2009-2-3 14:04:30

各位高手，RAC在服务器端做的负载均衡好像失效了，一个节点上只有SYS用户，应用的用户全部在另外一个节点上，负载严重不均衡。

AIX的系统，oracle放在GPFS文件系统上，两个节点共享
服务器配置文件：

# more tnsnames.ora
REMOTE_LISTENER_PRD_APPRD1.WORLD =
(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prd2)(PORT = 1527))
)
REMOTE_LISTENER_PRD_APPRD2.WORLD =
(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prd1)(PORT = 1527))
)
LISTENER_PRD_APPRD1.WORLD=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=prd1)(PORT=1527))
)
LISTENER_PRD_APPRD2.WORLD=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=prd2)(PORT=1527))
)

PRD.WORLD=
(DESCRIPTION =
(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=prd1)(PORT=1527))

(ADDRESS=(PROTOCOL=TCP)(HOST=prd2)(PORT=1527))
)
(CONNECT_DATA =

(SERVICE_NAME = PRD.WORLD)

(GLOBAL_NAME = PRD.WORLD)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

)
)
)
PRD001.WORLD=
(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=prd1)(PORT=1527))

)
(CONNECT_DATA =

(SID = PRD001)

(GLOBAL_NAME = PRD.WORLD)

)
)

PRD002.WORLD=
(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=prd2)(PORT=1527))

)
(CONNECT_DATA =

(SID = PRD002)

(GLOBAL_NAME = PRD.WORLD)

)
)

# more listener.ora
SID_LIST_LISTENER_PRD_APPRD2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PRD002)
(ORACLE_HOME = /oracle/PRD/102_64)
)
(SID_DESC =
(SID_NAME = PRD001)
(ORACLE_HOME = /oracle/PRD/102_64)
)
)
SID_LIST_LISTENER_PRD_APPRD1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PRD001)
(ORACLE_HOME = /oracle/PRD/102_64)
)
(SID_DESC =
(SID_NAME = PRD002)
(ORACLE_HOME = /oracle/PRD/102_64)
)
)
LISTENER_PRD_APPRD2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prd2)(PORT = 1527)(IP = FIRST))
)
)
LISTENER_PRD_APPRD1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prd1)(PORT = 1527)(IP = FIRST))
)
)

查看用户连接：
SQL> select inst_id,username from gv$session;
INST_ID USERNAME
---------- ------------------------------
1 SYS
1 SYS
1 SYS
1 SYS
1
1
1
1 SYS
1 SYS
1
1
INST_ID USERNAME
---------- ------------------------------
1
1
1
1
1
1
1
1
1
1
1
INST_ID USERNAME
---------- ------------------------------
1
1
1
1
1
1
1
1
1
1
1
INST_ID USERNAME
---------- ------------------------------
1
1
1
1
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SYS
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SYS
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
INST_ID USERNAME
---------- ------------------------------
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SAPSR3
2 SYS
2 SYS
INST_ID USERNAME
---------- ------------------------------
2
2
2
2 SYS
2 SYS
2
2
2
2
2
2
INST_ID USERNAME
---------- ------------------------------
2
2
2
2
2
2
2
2
2
2
2
INST_ID USERNAME
---------- ------------------------------
2
2
2
2
2
2
2
2
2
2
2
209 rows selected.

请各位高手指教！！如何排查问题所在呢？多谢！！
[ 本帖最后由 mooncd 于 2010-7-20 17:21 编辑 ]

千问 · 2009-2-3 14:04:30

show parameter listener

千问 · 2009-2-3 14:04:30

你这个问题不是失效，而是一直就没效吧。
请参看张晓明著的《大话oracle RAC》,224页。

千问 · 2009-2-3 14:04:30

在两个节点上show parameter listener
都能看到本地的listener和remote的listener列

千问 · 2009-2-3 14:04:30

张晓明著的《大话oracle RAC》,224页。
兄弟现在没地方买书列指点一下啊224页有什么关键点？谢谢！

千问 · 2009-2-3 14:04:30

原帖由 mooncd 于 2010-7-21 09:27 发表
张晓明著的《大话oracle RAC》,224页。
兄弟现在没地方买书列指点一下啊224页有什么关键点？谢谢！
服务端的负载均横跟remote_listener参数有关。要配置成tnsnames.ora里面的一个条目。
我让你把show parameter listener的结果贴出来。两天的你都没办。
大家不是不想回复你。而是不知道如何回复。抓不住关键。

千问 · 2009-2-3 14:04:30

兄弟上边回复过了在两个节点上看listener参数都是正常的列
节点1上
SQL> show parameter listener
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
local_listener
stringLISTENER_PRD_APPRD1
remote_listener
stringLISTENER_PRD_APPRD2
节点2上
SQL> show parameter listener
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
local_listener
stringLISTENER_PRD_APPRD2
remote_listener
stringLISTENER_PRD_APPRD1

千问 · 2009-2-3 14:04:30

现在发现services全部启到了一边
# srvctl status database -d PRD -v
Instance PRD002 is running on node eccprd2 with online services AP1_apprd1 AP2_apprd2
Instance PRD001 is running on node eccprd1
难道是因为这个原因导致tns解析全部指到2上了么？
但是每个services的address_list都有把两个实例写上去列？
如果把这个服务切换回来的话，需要停数据库么？
srvctl relocate service -d PRD -s AP1_apprd1 -i PRD002 -t PRD001 这个动作要停数据库么？AP2_apprd2还是正常启的
[ 本帖最后由 mooncd 于 2010-7-21 17:51 编辑 ]