Oracle10g RAC客户端访问配置

clusterware version

[oracle@racnode1 ~]$ crsctl query css votedisk
 0.     0    /dev/raw/raw3
 1.     0    /dev/raw/raw4
 2.     0    /dev/raw/raw5


located 3 votedisk(s).
[oracle@racnode1 ~]$ crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.5.0]

10g RAC安装好之后local_listener默认参数

[oracle@racnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 20 17:04:41 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string
SQL> 

windows客户端配置,HOST均为VIP

rac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

rac1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (INSTANCE_NAME = racdb1)
    )
  )

rac2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
      (INSTANCE_NAME = racdb2)
    )
  )

tnsping测试是通的

C:\Documents and Settings\Administrator>tnsping rac

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-12月-2012 17:05:59

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

已使用的参数文件:
E:\oracle\product\102~1.0\db_1\NETWORK\ADMIN\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) (ADDRESS = (PROTOCO
L = TCP)(HOST = 192.168.1.152)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = r
acdb)))
OK (10 毫秒)

C:\Documents and Settings\Administrator>tnsping rac1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-12月-2012 17:06:01

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

已使用的参数文件:
E:\oracle\product\102~1.0\db_1\NETWORK\ADMIN\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) (CONNECT_DATA = (SE
RVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb1)))
OK (10 毫秒)

C:\Documents and Settings\Administrator>tnsping rac2

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 20-12月-2012 17:06:02

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

已使用的参数文件:
E:\oracle\product\102~1.0\db_1\NETWORK\ADMIN\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) (CONNECT_DATA = (SE
RVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb2)))
OK (0 毫秒)

C:\Documents and Settings\Administrator>

尝试客户端连接

C:\Documents and Settings\Administrator>sqlplus hr/hr@rac

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 12月 20 17:06:57 2012

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

ERROR:
ORA-12170: TNS: 连接超时


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

metalink

ORA-12520: Listeners Running on VIP Address in 10g or Newer RAC [ID 342419.1]

This is due to Unpublished BUG: 4338578 RAC-CRITICAL: INSTANCE DOESN'T USE VIP ENDPOINT TO REGISTER WITH LISTENER.

In any 10g and newer RAC setup,  a problem exists where the instances are not registering correctly with the virtual IP address. They're attempting to register against the physical ip address instead.

Set LOCAL_LISTENER for each instance in the cluster.  Log in as a privileged user and issue:

SQL>Alter system set LOCAL_LISTENER="(address=(protocol=tcp)(host=your_node)(port=1521))" scope=both sid='<instance_name>';

Where "your_node" is the VIP for the host where the instance is running and "instance_name" is the unique instance name for this SID.
Issue this statement for all instances in the cluster using the correct values for host and SID. 

修改local_listener参数

[oracle@racnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 20 17:18:37 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521))' sid='racdb1';

System altered.

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521))' sid='racdb2';

System altered.

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS = (PROTOCOL = TCP)(HO
						 ST = 192.168.1.151)(PORT = 152
						 1))
SQL> conn system@racdb2
Enter password: 
Connected.
SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS = (PROTOCOL = TCP)(HO
						 ST = 192.168.1.152)(PORT = 152
						 1))
SQL> conn system@racdb1
Enter password: 
Connected.
SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 (ADDRESS = (PROTOCOL = TCP)(HO
						 ST = 192.168.1.151)(PORT = 152
						 1))
SQL> 

client

C:\>sqlplus system@rac

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 12月 20 17:21:04 2012

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

输入口令:

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
racdb2           OPEN
racdb1           OPEN