解决11gR2 RAC 客户端ORA-12545错误一则

在一套Linux环境下X86_64的11gR2 RAC数据库集群中，客户端通过SCAN IP访问数据库时遇到下述错误：

C:\Users\huangw.GILLION>sqlplus hr/hr@11grac

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 13 10:54:55 2012

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

其中，客户端的tnsnames.ora文件配置如下，其中172.16.0.203为SCAN IP，并且客户端ping SCAN IP一切正常：

11grac =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST=172.16.0.203)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

ping SCAN IP:

C:\Users\huangw.GILLION>ping 172.16.0.203

Pinging 172.16.0.203 with 32 bytes of data:
Reply from 172.16.0.203: bytes=32 time=1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64

Ping statistics for 172.16.0.203:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms

服务器端网络配置信息如下：

[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1              localhost.localdomain localhost
127.0.0.1               localhost
::1             localhost6.localdomain6 localhost6

172.16.0.191            rac1.localdomain        rac1
172.16.0.193            rac1-vip.localdomain    rac1-vip
192.168.93.1            rac1-priv.localdomain   rac1-priv

172.16.0.192            rac2.localdomain        rac2
172.16.0.194            rac2-vip.localdomain    rac2-vip
192.168.93.2            rac2-priv.localdomain   rac2-priv

172.16.0.203            rac-scan.localdomain    rac-scan
[root@rac1 ~]#

服务器端SCAN IP信息如下：

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl config scan
SCAN name: 172.16.0.203, Network: 1/172.16.0.0/255.255.240.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.localdomain/172.16.0.203
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac2
[root@rac1 ~]#

服务器端集群信息如下：

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE               
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
[root@rac1 ~]#

通过查找MetaLink：ORA-12545 or ORA-12537 While Connecting to RAC through SCAN name [ID 970619.1]得到解决方法，修改数据库的local_listener参数：

修改之前：

[root@rac1 ~]# su - oracle
rac1-> sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 13 10:42:58 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set line 160
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/devdb/spfiledevdb.ora
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      devdb1
SQL> show parameter listener

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>

修改local_listener参数：

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.193)(PORT=1521))))' sid='devdb1';

System altered.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.194)(PORT=1521))))' sid='devdb2';

System altered.

SQL> show parameter listener

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

修改之后,客户端连接正常：

C:\Users\huangw.GILLION>sqlplus hr/hr@11grac

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 13 11:04:00 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
Data Mining and Real Application Testing options

SQL> show user;
USER is "HR"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

8 rows selected.

SQL>

至此，问题解决。

产生问题原因：

Client is able to resolve all Fully Qualified Domain Name (FQDN) SCAN and VIP name but not short ones(without domain name) as its in different domain; even though FQDN names were specified during Grid Infrastructure setup, due to bug 9150053 by default DBCA set database parameter local_listener to short node VIP name while database is created.

解决方法除了上述之外，我们也可以按照下面的方法解决，其实，都是换汤不换药，个人倾向于直接在监听里配置IP地址：

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.localdomain)(PORT=1521))))' sid='devdb1';

System altered.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip.localdomain)(PORT=1521))))' sid='devdb2';

System altered.

SQL> alter system register;

System altered.

SQL>