连接到RAC数据库的时候经常会出现ORA-12545错误

连接到RAC数据库的时候经常会出现ORA-12545错误

针对这个问题Oracle没有认为这个是bug，只是认为是PROBLEM。

解决办法可以有两种：修改客户端的hosts和tnsnames.ora或者修改oracle服务器数据库。

前者称为客户端解决办法后者称为彻底的解决办法。(由于重启实例和监听风险较大，故通过更改hosts文件和重新配置tnsnames.ora实现)

两者都行。彻底的解决办法可以彻底的解决这个问题，而且对所有的连接都是有效的。

不过缺点也是很明显的。需要修改初始化参数，重启实例，重启监听。

 

客户端解决办法：

解决这个问题步骤如下：

1.       修改客户端tnsnames.ora文件：

RACDB =

(DESCRIPTION =

(ADDRESS_LIST =(ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.143.1)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.143.2)(PORT = 1521))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racdb)

    )

)

2.      查看数据库服务器lsnrctl配置：

$ lsnrctl service

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 11-JUL-2007 10:57:12

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "dbsr" has 2 instance(s).

Instance "racdb1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         REMOTE SERVER

         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))

      "DEDICATED" established:87 refused:0 state:ready

         LOCAL SERVER

Instance "racdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:43 refused:0 state:ready

         REMOTE SERVER

         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))

Service "racdb" has 2 instance(s).

Instance "racdb1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         REMOTE SERVER

         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))

      "DEDICATED" established:87 refused:0 state:ready

         LOCAL SERVER

Instance "racdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:43 refused:0 state:ready

         REMOTE SERVER

        (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))

Service "racdbXDB" has 2 instance(s).

Instance "racdb1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: shp_db1, pid: 250102>

         (ADDRESS=(PROTOCOL=tcp)(HOST=shp_db1)(PORT=32805))

Instance "racdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: shp_db2, pid: 185054>

         (ADDRESS=(PROTOCOL=tcp)(HOST=shp_db2)(PORT=32811))

Service "racdb_XPT" has 2 instance(s).

Instance "racdb1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         REMOTE SERVER

         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))

      "DEDICATED" established:87 refused:0 state:ready

         LOCAL SERVER

Instance "racdb2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:43 refused:0 state:ready

         REMOTE SERVER

         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))

The command completed successfully

3.      修改客户端hosts文件

Windows系统：C:\WINDOWS\system32\drivers\etc\hosts

Linux系统：/etc/hosts

增加相应项：

10.32.143.1                  db1_vip shp_db1

10.32.143.2                  db2_vip shp_db2

此时oracle客户端可以正常通过racdb链接oracle而不报错了。

 

彻底的解决方法：

连接到RAC数据库的时候经常会出现ORA-12545错误，在METALINK上查询了一下，是Oracle的一个小bug。在远端客户端连接RAC数据库时，通过统一的服务名连接时经常会出现ORA-12545错误。

安装环境：
OS: linux AS4.7
DB: oracle 10.2.0.4
DB_name: orcl
node1: rac1
node2: rac2

首先查看RAC安装完成后默认的各节点的tnsnames.ora、listener.ora配置
node rac1:
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/10g/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))

LISTENER_ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
)

ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
)

ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

[oracle@rac1 admin]$ cat listener.ora
# listener.ora.rac1 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.

LISTENER_RAC1 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRST))
    )
)

SID_LIST_LISTENER_RAC1 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10g/db1)
      (PROGRAM = extproc)
    )
)

node rac2:
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora.rac2 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/tnsnames.ora.rac2
# Generated by Oracle configuration tools.

LISTENER_ORCL2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))

LISTENER_ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
)

ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
)

ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

[oracle@rac2 admin]$ cat listener.ora
# listener.ora.rac2 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.

LISTENER_RAC2 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521)(IP = FIRST))
    )
)

SID_LIST_LISTENER_RAC2 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10g/db1)
      (PROGRAM = extproc)
    )
)

查看各节点参数的设置：
rac1:
[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string    
remote_listener                   string      LISTENERS_ORCL

rac2:
[oracle@rac1 ~]$ export ORACLE_SID=orcl2
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string    
remote_listener                    string      LISTENERS_ORCL

解决步骤：
通过查看我们可以看到，在上面的各节点的tnsnames.ora中都有LISTENERS_ORCL1、LISTENERS_ORCL2的配置，所以我们只需要设置local_listener，remote_listener参数相应的值即可
rac1:
[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL>alter system set remote_listener='' sid='orcl1';
system altered

SQL> alter system set local_listener='LISTENER_ORCL1' sid='orcl1';
system altered

SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORCL1
remote_listener                      string  

rac2:
[oracle@rac2 ~]$ export ORACLE_SID=orcl2
[oracle@rac2 ~]$ sqlplus "/as sysdba"
SQL> alter system set remote_listener='' sid='orcl2';
system altered

SQL> alter system set local_listener='LISTENER_ORCL2' sid='orcl2';
system altered

SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORCL2
remote_listener                      string  

或者也可以设置local_listener参数下面的值：
racl:
SQL>ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))' SID = 'orcl1';

rac2:
SQL>ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))' SID = 'orcl2';

这样再通过客户端连接就是不会报错了！

metalink文档解释如下：

Subject: RAC Connection Redirected To Wrong Host/IP ORA-12545
Doc ID: Note:364855.1 Type: PROBLEM
Last Revision Date: 23-APR-2007 Status: PUBLISHEDIn this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------Applies to:
Oracle Net Services - Version: 9.1 to 10.2
This problem can occur on any platform.Symptoms
When we try to connect to a RAC service name we sometimes get redirected by the first node's listener to the public address/hostname of the second node instead of its VIP address. An ORA-12545 error may be generated if that public hostname is not configured in DNS.We were expecting the connection to eventually be redirected to the VIP of the other node.
Cause
The Database on one RAC node remote registers with the wrong local IP address to the listener on the other RAC node (e.g. the public IP address instead of the wanted VIP address).The PMON process handles database registration to the local and remote listeners. For remote listeners registration PMON will have to find out what is the IP address of the local system in order to present it to the remote listener as database contact address.In the default Oracle configuration, for hosts which have more than one IP address configured on the network interfaces, it is undefined which IP address will be selected for remote registration.
Solution
Modify the local_listener database parameter to point to the local VIP address. For the parameter value use either an alias name which contains in the DESCRIPTION field only the VIP address or use an explicit connection statement like the following:local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = <VIP_address>) (PORT = 1521))'
The local_listener database parameter will give PMON a hint in respect of which IP address it should use for remote registration with other nodes' listener(s).
References
Note 235562.1 - Issues affecting Automatic Service Registration
Note 256275.1 - Dynamic Registration Fails On Multiple Network Interface ServerErrors
ORA-12541 TNS:no listener
ORA-12545 Connect failed because target host or object does not existKeywords
'RAC'   'LOCAL_LISTENER'   'IP~ADDRESS'   'REDIRECT'   'VIP'   'PMON'   'RAC'   'SERVICE~REGISTRATION'