通过客户端连接RAC偶尔会出现错误ORA-12545 （转）

操作系统hp unix 11.23 数据库Oracle 10203
错误现象:通过客户端连接RAC偶尔会出现错误ORA-12545

msn： olivenan@hotmail.com
C:\Documents and Settings\olivenan>sqlplus test/test@billdb

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 9月 27 13:44:37 2007

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

ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

 

1、在两个节点分别检查local_listener，remote_listener，如果没有设置该参数，那么需要将该参数指定为相应的值
oracle@CHNFSCC1[billdb1]:/home/oracle$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 27 13:49:19 2007

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

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

SQL> show parameter local_l

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      BILLDB1
SQL> show parameter remote_li

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      BILLDB
SQL>

oracle@CHNFSCC2[billdb2]:/u01/app/oracle/product/rac10g/network/admin$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 27 13:48:01 2007

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

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

SQL> show parameter local_l

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      BILLDB2
SQL> SQL> show parameter remote_l

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      BILLDB
remote_login_passwordfile            string      EXCLUSIVE
SQL> show parameter remote_li

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      BILLDB
SQL>

2、我的系统已经指定了local_listener/remote_listener  相应的值，但是仍然出现该错误
将listener.ora中的crs_vip1或crs_vip2改为其ip地址

oracle@CHNFSCC1[billdb1]:/u01/app/oracle/product/rac10g/network/admin$ vi listener.ora
"listener.ora" 20 lines, 575 characters
# listener.ora.chnfscc1 Network Configuration File: /u01/app/oracle/product/rac10g/network/admin/listener.ora.chnfscc1
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_CHNFSCC1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/rac10g)
      (PROGRAM = extproc)
    )
  )

LISTENER_CHNFSCC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.208)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.206)(PORT = 1521)(IP = FIRST))
    )
  )
 
3、将tnsnames.ora中的中的crs_vip1或crs_vip2改为其ip地址

  oracle@CHNFSCC1[billdb1]:/u01/app/oracle/product/rac10g/network/admin$ vi tnsnames.ora
"tnsnames.ora" 85 lines, 2195 characters
# tnsnames.ora.chnfscc1 Network Configuration File: /u01/app/oracle/product/rac10g/network/admin/tnsnames.ora.chnfscc1
# Generated by Oracle configuration tools.

LISTENERS_BILLDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.208)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.209)(PORT = 1521))
  )

BILLDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.208)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.209)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = billdb.fs)
    )
  )

BILLDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.121.209)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST =***.***.***.***)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = billdb.fs)
      (INSTANCE_NAME = billdb2)
    )
  )

BILLDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.121.208)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST =***.***.***.***)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = billdb.fs)
      (INSTANCE_NAME = billdb1)
    )
  )

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

 

修改完后要重新启动监听。问题搞定，呵呵。

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:  PUBLISHED

In 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 = ) (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 Server

Errors
ORA-12541 TNS:no listener
ORA-12545 Connect failed because target host or object does not exist

Keywords
'RAC'   'LOCAL_LISTENER'   'IP~ADDRESS'   'REDIRECT'   'VIP'   'PMON'   'RAC'   'SERVICE~REGISTRATION'  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1863/viewspace-246429/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1863/viewspace-246429/