ORA-28547 連線伺服器失敗，可能是Oracle Net 管理錯誤

上週去給客戶培訓，講到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。

ORA-28547: connection to server failed, probable Oracle Net admin error (文件 ID 1645680.1)

In this Document

Symptoms

Changes

Cause

Solution

APPLIES TO:

Oracle Net Services - Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

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.

CHANGES

New installation or changes to an existing listener.ora file

CAUSE

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.

SOLUTION

To resolve this issue, make sure that if a static SID_LIST section is required, then it only contains the SID_NAME value and (if the instance is not in the same Home) the ORACLE_HOME value.
So edit the listener.ora file and remove any other values (unless directed by Oracle Support, such as inclusion of ENVS values specific to your instance).

In the above example, the corrected entry will be as follows:

SID_LIST_LISTENER1 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL1)
      (ORACLE_HOME = E:\app\oracle\product\11.2.0\dbhome_1)
    )
    (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")
    )
)

Restart the Listener using "lsnrctl stop LISTENER1" and "lsnrctl start LISTENER1" (or via the Windows Services if on Windows).

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2135162/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

ORA-28547 連線伺服器失敗，可能是Oracle Net 管理錯誤

ORA-28547 連線伺服器失敗，可能是Oracle Net 管理錯誤

APPLIES TO:

SYMPTOMS

CHANGES

CAUSE

SOLUTION

相關文章