An example of ORA-28547 Fault Handling
Last week to the customer training Oracle, talked about the DG part of the actual configuration demonstration, encountered a ORA-28547 fault, the customer database 11.2.0.1 version, Window platform.
Previously, all DG configurations have been completed according to the steps. When you want to enable redo apply in the slave database, check whether the remote archiving paths on both sides are valid as follows:
Select dest_name, status, error from v $ archive_dest where dest_id <3; at this time, the local and remote archiving path status of the standby database is VALID, but the master database reports a: ORA-28547: connection to server failed, probable Oracle Net admin error (dbd error: OCISessionBegin) because the connection to the server fails, archiving of the master database cannot be passed to the slave database through the LNS process, this is not the same as the various ORA-12541 and ORA-12514 TNS connection errors encountered before, because the TNSPING command to ping their respective net service name is normal, and did not say that the target can not be found. Based on experience, when a TNS or Oracle Net connection error occurs, you must first pay attention to several configuration files under $ ORACLE_HOME/network/admin, which is usually caused by incorrect configuration, the following is the general content of several configuration files: 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 \ lrlr11.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 ))
Export lr_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 MERs choose
# 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 = (CNT)
NAMES. DIRECTORY_PATH = (TNSNAMES, EZCONNECT) several articles about related content on google, some of which say that sqlnet is required. SQLNET. change AUTHENTICATION_SERVICES = (CNT) to SQLNET. AUTHENTICATION_SERVICES = (NONE), or change the HOST of the listener to an IP address, but this does not work. In addition, the former will disable operating system verification, which means you must log on to the server using username/password @ orcl to solve the problem by referring to the official documentation. Let's take a look at the description in the official documentation: applies to: Oracle Net Services-Version 11.2.0.1 and later
Information in this document applies to any platform.
Symptom: 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.
Change:
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 \ lrlr11.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 \ lrlr11.dll ")
)
)
In the example above, the first section contains the SID_NAME and ORACLE_HOME values for the Database instance required for connection.
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.
Note that the above red section, here specifically mentioned, the PROGRAM and ENVS parameter settings will cause the client to connect through SID_NAME, which will cause a ORA-28547 error since the two parameters will cause problems, the solution is to remove them. As mentioned in the official document, you only need to remove the entries of PROGRAM and envs in the SID_LIST related to the Instance name. In my actual test, I couldn't remove the PROGRAM separately, so I removed both the PROGRAM and ENVS, and solved the previous problem. As for the PROGRAM and ENVS entries in SID_NAME = PLSExtProc in SID_DESC, it is not feasible to remove and has not been tested in person. Note: After modification, restart the listener or restart the listener Service in Windows.
---------------------------- Lili split line ----------------------------
Migration from 32-bit to 64-bit for a single Oracle instance
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian
Oracle Import and Export expdp IMPDP details
Solution to Oracle 10g expdp export error ORA-4031
