ora-12545串連錯誤解決一例

在遠端用戶端串連RAC資料庫時，通過統一的服務名串連時經常會出現ORA-12545錯誤。在METALINK上查詢了一下，竟是Oracle的一個小bug。
環境：ORACLE 11G R2 RAC資料庫，兩節點。
現象：
[oracle@ted35 ~]$sqlplus /nolog
 
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 15 11:57:00 2012
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
SQL> conn sys/ted@123e@prodb as sysdba;
ERROR:
ORA-12545: 因目標主機或對象不存在, 串連失敗

故障分析：用tnsping檢查一下，發現可以ping通，這是因為tnsping只檢查IP地址和連接埠是否能連通，至於資料庫執行個體狀態，監聽註冊了哪些服務這些，它是不檢查的。

我們看一下本機資料庫TNSNAMES的配置：
TESTRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 10.58.12.124)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)

Oracle在文檔Note:364855.1：RAC Connection Redirected To Wrong Host/IP ORA-12545中進行了描述，並給出瞭解決方法：修改資料庫中的初始化參數LOCAL_LISTENER註：這種方法在修改初始化參數後，需要重啟監聽，重啟執行個體）
SQL>conn / as sysdba;
    Connected.
SQL> ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521)) 'SID = 'prodb1';
系統已更改。
登入另外一個節點
SQL>conn / as sysdba;
    Connected.
SQL> ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))' SID = 'prodb2';
系統已更改。

設定之後，再次嘗試串連資料庫：
SQL> conn sys/ted@123e@prodb as sysdba;
     Connected.
SQL> conn sys/ted@789e@prodb as sysdba;
     Connected.

修改之後，沒有再次出現同樣的錯誤。

我們也可以通過只修改用戶端hosts檔案的方式，來解決這個問題。
首先在linux主機上修改HOSTS檔案
root@prodb1 # vi /etc/hosts
#
10.58.12.120            prodb1-public   # Public
10.58.12.124            prodb1          # Virtual
23.23.23.120            prodb1-priv     # Private

10.58.12.121            prodb2-public   # Public
10.58.12.125            prodb2          # Virtual
23.23.23.121            prodb2-priv     # Private

10.58.12.126            prodb-scan      # SCAN

然後用戶端的tnsnames.ora中的配置如下：
$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2/database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENERS_prodb =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
)
LISTENER_prodb1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
LISTENER_prodb2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))

prodb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
prodb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(INSTANCE_NAME = prodb1)
)
)
prodb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(INSTANCE_NAME = prodb2)
)
)

下面嘗試串連遠端RAC資料庫：

SQL> conn sys/ted@123e@prodb as sysdba;已串連。
SQL> conn sys/ted@789e@prodb as sysdba;已串連。

其實在本地hosts檔案中加上RAC執行個體所在伺服器的ip和主機名稱資訊，就可以避免這個錯誤。
另外注意在RAC環境中，任何修改都一定要謹慎。RAC 環境一旦安裝好後，主機名稱就不能隨意修改，除非先刪除節點，修改Hostname，再添加節點。還有一點主機名稱必須和public名一致，這一點也非常重要。

本文出自 “滴水穿石” 部落格，請務必保留此出處http://xjsunjie.blog.51cto.com/999372/902033