PL/SQL通过scan ip连接数据库

On By 去哪儿

11.2.0.1版本的一个bug，就是客户端无法通过scan连接到数据库解决方法如下：

1、地址信息

#PUBLIC
172.30.13.105    rac1
172.30.13.106    rac2
 
#PRIVATE
10.10.10.1    rac1-priv 
10.10.10.2    rac2-priv   

#VIP
172.30.13.107   rac1-vip
172.30.13.108   rac2-vip
172.30.13.109   rac-scan

2、原配置信息

RAC1

SQL> show parameter listene;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	 (DESCRIPTION=(ADDRESS_LIST=(AD
						 DRESS=(PROTOCOL=TCP)(HOST=rac1
						 -vip)(PORT=1521))))
remote_listener 		     string	 rac-scan:1521
SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
orcl1		 OPEN

RAC2

SQL>  show parameter listene;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	 (DESCRIPTION=(ADDRESS_LIST=(AD
						 DRESS=(PROTOCOL=TCP)(HOST=rac2
						 -vip)(PORT=1521))))
remote_listener 		     string	 rac-scan:1521
SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
orcl2		 OPEN

3、修改配置

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))))' sid='orcl1';

SQL>  alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))))' sid='orcl1';

System altered.

SQL> show parameter listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	 (DESCRIPTION=(ADDRESS_LIST=(AD
						 DRESS=(PROTOCOL=TCP)(HOST=rac-
						 scan)(PORT=1521))))
remote_listener 		     string	 rac-scan:1521

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))))' sid='orcl2';

SQL>  alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))))' sid='orcl2';

System altered.

SQL> show parameter listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	 (DESCRIPTION=(ADDRESS_LIST=(AD
						 DRESS=(PROTOCOL=TCP)(HOST=rac-
						 scan)(PORT=1521))))
remote_listener 		     string	 rac-scan:1521