ORA-28547 连接服务器失败，可能是Oracle Net 管理错误-CSDN博客

ORA-28547 连接服务器失败，可能是Oracle Net 管理错误

原文地址：http://www.linuxidc.com/Linux/2014-11/109686.htm

上周去给客户培训 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。

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

CHANGES

New installation or changes to an existing listener.ora file

CAUSE

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")
    )
)

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群，学习最实用的数据库技术。