连接Oracle 10g时ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME 错误的解决...

最新推荐文章于 2022-01-10 09:53:59 发布

cmg7893

最新推荐文章于 2022-01-10 09:53:59 发布

阅读量293

点赞数

文章标签：网络操作系统数据库

环境10G RAC（2NODES）+WINDOWS2003:

症状：

一直用着好好的，重启数据库后，一开始偶尔出现以下错误，到后面都连接不上：

ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME 错误的解决

分析：

1：tnsping 服务名 OK

2：ping IP 网络正常无掉包

3:查看监听日志，没有大量客户请求排除监听负荷过大可能造成的问题；

4：有一个节点的监听日志显示所有请求都能正确服务，另外个节点都是 “ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME 错误的解决”

5：重启 instance,asm,nodeapps 后半小时内可以连接，后面又继续出现同样问题

解决：最后发现监听日志有错误的NODE 启动了 2个监听，一个是系统默认的，一个是应用特别配置的（应该只启动这个），停止禁用默认的监听后问题解决。

日志分析方法：

Analyzing Listener Log Files

Listener Log Audit Trail Information
The listener log file contains audit trail information that enables you to gather and
analyze network usage statistics, as well as information indicating the following:
■ A client connection request

A RELOAD, START, STOP, STATUS, or SERVICES command issued by the Listener
Control utility
You can use Audit Trail information to view trends and user activity by first storing it
in a table and then collating it into a report format. To import the data into a table, use
an import utility such as SQL*Loader.
Format of the Listener Log Audit Trail
The audit trail formats text into the following fields:
Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code
Properties of the audit trail are as follows:
■ Each field is delimited by an asterisk (*).
■ Protocol address information and service name or SID information appear only
when a connection is attempted.
■ A successful connection or command returns a code of zero.
■ A failure produces a code that maps to an error message.

Example: Listener Log Event for Successful Reload Request
The following output shows a log file excerpt with RELOAD command request.
14-JUL-2002 00:29:54 *
(connect_data=(cid=(program=)(host=sales-server)(user=jdoe))(command=stop)
(arguments=64)(service=listener)(version=135290880))
* stop * 0
Example: Listener Log Events for a Successful Connection Request
The following output shows a log file excerpt with a successful connection request.
14-JUL-2002 15:28:58 *
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)
(user=jdoe)))
* (address=(protocol=tcp)(host=10.10.150.35)(port=41349)) * establish
* sales.us.acme.com * 0
Example: Listener Log Events for an Unsuccessful Connection Request
The following output shows a log file excerpt with a successful execution of the
STATUS command by host sales-server, followed by an unsuccessful connection
attempt by a client with an IP address of 10.10.150.35. This connection attempt
resulted in an ORA-12525: TNS:listener has not received client’s request in time
allowed error message, which occurs when a client fails to complete its connect request
in the time specified by the INBOUND_CONNECT_TIMEOUT_listener_name
parameter in the listener.ora file. This client may be attempting a
denial-of-service attack on the listener.
03-JUL-2002 16:41:57 *

(CONNECT_DATA=(CID=(PROGRAM=)(HOST=sales-server)(USER=jdoe))(COMMAND=status)
(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * status * 0
03-JUL-2002 16:42:35 * *
(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.150.35)(PORT=53208)) * establish *
* 12525
TNS-12525: TNS:listener has not received client’s request in time allowed
TNS-12604: TNS: Application timeout occurred
Listener Service Registration Event Information
The listener records service registration events. During service registration, the PMON
process provides the listener with information about the following:
■ Service names for each running instance of the database
■ Instance names of the database
■ Service handlers (dispatchers or dedicated servers) available
■ Dispatcher, instance, and node load information
■ Dynamic listening endpoints
The service registration-related events listed in Table 16–8 are recorded in the
listener.log file:

service_register The listener received registration information for an instance.
service_update The listener received updated registration information for a
particular instance, such as dispatcher or instance load
information.
service_died The listener lost its connection to PMON. All registration
information for the instance is discarded. Clients will be
unable to connect to the instance until PMON registers it
again.

Format of the Listener Service Registration Information
The service registration events are formatted into the following fields:
Timestamp * Event * Instance Name * Return Code
Properties of service registration fields are as follows:
■ Each field is delimited by an asterisk (*).
■ It is normal for the events to appear multiple times in a row for one instance.
■ A successful registration returns a code of zero, meaning the client can connect to
the instance.
■ A failure produces a code that maps to an error message

Example: Listener Log with Service Registration Events
The following example shows a log file with service registration events. Notice how
the listener is able to receive a client request after a successful service_register
event, but is unable to receive client requests after a service_died event.
-------------------------------
14-JUL-2002 15:28:43 * service_register * sales * 0
14-JUL-2002 15:28:43 * service_register * sales * 0
14-JUL-2002 15:28:58 *
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)
(user=jdoe)))
* (address=(protocol=tcp)(host=10.10.150.35)(port=41349)) * establish
* sales.us.acme.com * 0
14-JUL-2002 15:38:44 * service_update * sales * 0
14-JUL-2002 15:38:44 * service_update * sales * 0
14-JUL-2002 15:48:45 * service_update * sales * 0
14-JUL-2002 15:48:45 * service_update * sales * 0
14-JUL-2002 15:50:57 *
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u
ser=jdoe)))
* (address=(protocol=tcp)(host=10.10.150.35)(port=41365)) * establish
* sales.us.acme.com * 0
14-JUL-2002 15:51:26 * service_died * sales * 12537
14-JUL-2002 15:51:26 * service_died * sales * 12537
14-JUL-2002 15:52:06 *
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u
ser=jdoe)))
* (address=(protocol=tcp)(host=10.10.150.35)(port=41406)) * establish
* sales.us.acme.com * 12514
TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
--------------------------------