解決:客戶端連線11gR2 RAC報ORA-12545錯誤

denglt發表於2012-08-21
剛在Oracle Linux 6環境下搭建了一套11gR2 RAC資料庫叢集,客戶端(Oracle 10g)通過SCAN IP訪問資料庫時遇到下述錯誤:
  SQL> conn sys/sys@studydb as sysdba
  ERROR:
  ORA-12545: Connect failed because target host or object does not exist
 
在伺服器端直接登入沒有問題:
 
  [root@study1 ~]# su - oracle
  [oracle@study1 ~]$ sqlplus /nolog
  
  SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 21 16:22:48 2012
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  SQL> conn / as sysdba
  Connected.
  SQL>      

伺服器的網路配置:
 
  [oracle@study1 ~]$ more /etc/hosts
  127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  
  # Public
  172.16.89.231    study1.dlt      study1
  172.16.89.232    study2.dlt      study2
  # Private
  192.168.152.128     study1-priv
  192.168.152.129     study2-priv
  # Virtual
  172.16.90.231       study1-vip
  172.16.90.232       study2-vip
  # SCAN
  172.16.90.201    study-scan
  172.16.90.202    study-scan
  172.16.90.203    study-scan
 
客戶端的tnsnames.ora檔案配置如下:
 
  STUDYDB =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.90.201)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.90.202)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.90.203)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = studydb)
      )
    )
  
tnsping studydb是成功的:  
 
  tnsping studydb
  
  TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-AUG-2012 16:28:06
  
  Copyright (c) 1997, 2005, Oracle.  All rights reserved.
  
  Used parameter files:
  c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
  
  
  Used TNSNAMES adapter to resolve the alias
  Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.90.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1
  OK (60 msec)
  
服務端的scan ip資訊如下:

  [oracle@study1 admin]$ srvctl config scan
  SCAN name: study-scan, Network: 1/172.16.88.0/255.255.252.0/eth0
  SCAN VIP name: scan1, IP: /study-scan/172.16.90.201
  SCAN VIP name: scan2, IP: /study-scan/172.16.90.202
  SCAN VIP name: scan3, IP: /study-scan/172.16.90.203
  [oracle@study1 admin]$ srvctl status scan
  SCAN VIP scan1 is enabled
  SCAN VIP scan1 is running on node study2
  SCAN VIP scan2 is enabled
  SCAN VIP scan2 is running on node study1
  SCAN VIP scan3 is enabled
  SCAN VIP scan3 is running on node study1  
  
檢視資料庫的local_listener引數:

  [oracle@study1 ~]$ sqlplus /nolog
  
  SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 21 16:32:39 2012
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  SQL> conn / as sysdba
  Connected.
  SQL> show parameter local_listener
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                   DRESS=(PROTOCOL=TCP)(HOST=stud
                                                   y1-vip)(PORT=1521))))
  
  [oracle@study2 ~]$ sqlplus /nolog
  
  SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 21 16:35:55 2012
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  SQL> conn / as sysdba
  Connected.
  SQL> show parameter local_listener
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                   DRESS=(PROTOCOL=TCP)(HOST=stud
                                                   y2-vip)(PORT=1521))))
                                                  
可以看到local_listener中沒有直接指定ip
修改local_listener引數
 
 SQL> alter system set local_listener = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.90.231)(PORT=1521))))' sid='studydb1' scope=both;
 
 System altered.                                                  
            
 SQL> alter system set local_listener = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.90.232)(PORT=1521))))' sid='studydb2' scope=both;
 
 System altered.       
 
 
之後客戶端連線正常:
  SQL> conn sys/sys@studydb as sysdba
  Connected.
  SQL> select inst_name from v$active_instances;
  
  INST_NAME
  --------------------------------------------------------------------------------
  study1.dlt:studydb1
  study2.dlt:studydb2   
  
  
其實還有一個解決方法:
  在客戶端的hosts檔案中配置如下內容
   172.16.90.231       study1-vip
   172.16.90.232       study2-vip  
   當然沒有前面的方法方便,但這說明了scan ip的執行原理:
    Oracle客戶端通過scan ip連線到服務端,服務端把local_listener配置傳送到客戶端,客戶端再通過local_listener配置真正連線到資料庫.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-741512/,如需轉載,請註明出處,否則將追究法律責任。

相關文章