ORA-28547 故障处理一例
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.
----------------------------华丽丽的分割线----------------------------
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的解决方法
评论暂时关闭