ORA-28547 故障處理一例
上周去給客戶培訓Oracle，講到DG部分做實際配置示範的時候，碰到了ORA-28547故障，客戶的資料庫時11.2.0.1的版本，Window平台。
之前已經按照步驟把DG全部配置完成了，當準備在備庫啟用redo apply的時候，照例先檢查一下兩邊遠程歸檔路徑是否有效：
select dest_name,status,error from v$archive_dest where dest_id<3; 此時備庫的本地和遠程歸檔路徑狀態都是VALID，但是主庫就報一個：ORA-28547：connection to server failed,probable Oracle Net admin error (DBD ERROR: OCISessionBegin) 由於和伺服器串連失敗，主庫的歸檔無法通過LNS進程傳遞到備庫，這和之前碰到的各種ORA-12541和ORA-12514的TNS串連錯誤還不太一樣，因為用TNSPING命令去ping各自的NET SERVICE NAME都是正常的，並沒有說找不到目標。 根據經驗，當發生TNS或Oracle Net串連錯誤，首先就要去關注幾個$ORACLE_HOME/network/admin下面的幾個設定檔，通常是配置不正確造成的，以下是幾個設定檔大致內容： listener.ora
# listener.ora Network Configuration File: D:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
#CLRExtProc
#DIRECT_HANDOFF_TTC_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\oracle
tnsnames.ora
# tnsnames.ora Network Configuration File: D:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
sqlnet.ora
# sqlnet.ora Network Configuration File: D:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) 網上google了幾篇相關內容的文章，有的說要把sqlnet.ora中的SQLNET.AUTHENTICATION_SERVICES= (NTS)改為SQLNET.AUTHENTICATION_SERVICES= (NONE)，或者是把監聽的HOST中改為IP地址，但都沒有效果。而且前者會禁用作業系統驗證，也就意味著必須用username/password@orcl的方式登入到伺服器 最終通過查閱官方文檔解決該問題，我們來看一下官方文檔的描述： 應用於： Oracle Net Services - Version 11.2.0.1 and later
Information in this document applies to any platform.
癥狀： When connecting through the Listener to a Database, an error is thrown:
ORA-28547: connection to server failed, probable Oracle Net admin error
However, local BEQUEATH connections (without the Listener) work fine.
改變：
New installation or changes to an existing listener.ora file
原因：
The listener.ora file's SID_LIST section may INCORRECTLY contain a "PROGRAM" line and/or an "ENVS" line for all database instances.
For example, for a listener called LISTENER1 the following may be set for a "static" SID_LIST section:
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL1)
(ORACLE_HOME = E:\app\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\app\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
In the example above, the first section contains the SID_NAME and ORACLE_HOME values for the Database instance required for connection to.
However, it also contains a PROGRAM and an ENVS value which will also attach to client connections through the SID_NAME Value.
This can result in Oracle*Net connection errors such as ORA-28547.
These are ONLY for External Procedures and/or Heterogeneous Services (HS) Gateway use, which is as per the second section example and correctly used for that section.
注意以上紅色部分，這裡明確地提到了，PROGRAM和ENVS參數的設定會導致用戶端通過SID_NAME來進行串連，由此會引發ORA-28547錯誤 既然這2個參數會引起問題，那麼解決方案就是把他們去掉即可，官方文檔中提到只要去掉和執行個體名相關的那個SID_LIST中的PROGRAM和ENVS的條目。我自己實際測試的時候，單獨去掉PROGRAM不行，於是把PROGRAM和ENVS都去掉，之前的問題就解決了。至於SID_DESC中SID_NAME=PLSExtProc中的PROGRAM和ENVS條目，不去掉是否可行，還未親自測試過。 注意：改完以後要重啟一下監聽，或者重啟Windows中的監聽Service。
----------------------------華麗麗的分割線----------------------------
Oracle 單一實例 從32位 遷移到 64位 方法
在CentOS 6.4下安裝Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虛擬機器中安裝步驟
Debian 下 安裝 Oracle 11g XE R2
Oracle匯入匯出expdp IMPDP詳解
Oracle 10g expdp匯出報錯ORA-4031的解決方案
