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]
[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>
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