[20170703]ora-12516 ora-12514 rac.txt

[20170703]ora-12516 ora-12514 rac.txt

--//生产系统出现问题.rac环境,版本11.2.0.4.OS版本 oracle linux 5.9 for 64.设计一些安全问题,我仅仅提供解决方案:

1.问题:
--//监听提示如下:
29-JUN-2017 22:05:47 *
(CONNECT_DATA=(SERVICE_NAME=aaahis)(CID=(PROGRAM=D:\laji\laji\laji.EXE)(HOST=ZLFL20170518)(USER=Administrator)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=53974)) * establish * aaahis * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack

$ oerr ora 12516
12516, 00000, "TNS:listener could not find available handler with matching protocol stack"
// *Cause: None of the known and available service handlers for the given
// SERVICE_NAME support the client's protocol stack: transport, session,
// and presentation protocols.
// *Action: Check to make sure that the service handlers (e.g. dispatchers)
// for the given SERVICE_NAME are registered with the listener, are accepting
// connections, and that they are properly configured to support the desired
// protocols.

--//说明一下:出现问题前,网络出现问题,出现环路.不知道是否与这个问题有关.

--//检查发现有1个服务运行在另外的实例上.

2.解决ora-12516:

$  ./dba_crs | grep .svc | grep zzzz
ora.zzzz.aaa430.svc                           ONLINE     ONLINE on dm01dbadm01
ora.zzzz.aaaemr.svc                           ONLINE     ONLINE on dm01dbadm02
ora.zzzz.aaahis.svc                           ONLINE     ONLINE on dm01dbadm02
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ora.zzzz.oims.svc                             ONLINE     ONLINE on dm01dbadm02

$  srvctl status service -d zzzz
Service aaa430 is running on instance(s) zzzz1
Service aaaemr is running on instance(s) zzzz2
Service aaahis is running on instance(s) zzzz2
Service oims is running on instance(s) zzzz2

--//估计出现异常,aaahis服务漂移到实例2上了.修改回来.

$  srvctl relocate  service -d zzzz -s aaahis -i zzzz2 -t zzzz1

$ srvctl status service -d zzzz
Service aaa430 is running on instance(s) zzzz1
Service aaaemr is running on instance(s) zzzz2
Service aaahis is running on instance(s) zzzz1
Service oims is running on instance(s) zzzz2

--//依旧不行,不过错误变成了ora-12514:

3.解决ora-12514:

30-JUN-2017 17:44:21 *
(CONNECT_DATA=(SERVICE_NAME=aaa430)(CID=(PROGRAM=D:\wamp\bin\apache\apache2.4.4\bin\httpd.exe)(HOST=DELL86)(USER=SYSTEM)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xx)(PORT=1642)) * establish * aaa430 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

$  srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

$  srvctl config scan
SCAN name: dm01-scan, Network: 1/192.168.100.0/255.255.255.0/bondeth0
SCAN VIP name: scan1, IP: /dm01-scan/192.168.100.108
SCAN VIP name: scan2, IP: /dm01-scan/192.168.100.109
SCAN VIP name: scan3, IP: /dm01-scan/192.168.100.107

--//手工执行:sqlplus xxxx/xxxx@dm01-scan/aaahis ,发现一些机器报ora-12514错误,一些机器从来都不报这个错误.
--//我在client执行 $ nslookup dm01-scan,可以发现出现的3个scan Ip出现的位置是轮询的.
--//思考:既然都出现在LISTENER_SCAN1这个监听上(对应IP=192.168.100.108),使用如下连接:

sqlplus xxxx/xxxx@192.168.100.108/aaahis
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 10:50:19 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
--//问题再现.而使用aaaemr服务,无论任何scan IP都不会报错.

--//问题依旧.既然问题出现在连接192.168.100.108主机2(实例2)上,同事建议在实例2认为加入该服务看看.
--//先暂时这样看看.
SYS@zzzz2> ALTER SYSTEM SET service_names='oims','aaaemr','zzzz','aaa430','aaahis'  SCOPE=MEMORY SID='zzzz2';
System altered.

--//注:aaa430 服务也存在这个问题,也一起加上.再执行如上测试:
--//sqlplus xxxx/xxxx@192.168.100.108/aaahis
--//问题消失!! 很明显这样仅仅是权益之计,根本没有解决问题.服务aaa430,aaahis设置是实例1优先.

--//google查询ora-12514 rac,发现如下链接:
--//https://www.dba-resources.com/oracle/intermittent-ora-12514-error-connecting-to-scan-listeners/
--//http://blog.itpub.net/17252115/viewspace-1101967/
--//里面提到了bug 13066936.与里面的情况非常符合:

After SCAN VIP and SCAN listener failover, instance does not register with the SCAN listener. It might happen for only 1
of the scan listener. Client connection gets intermittent ORA-12514 TNS:listener does not currently know of service
requested in connect descriptor.

Cause:

1. Unpublished Bug 12659561  after scan listener failover, database instance might not register to the scan listener
   (refer Note 12659561.8), fixed in 11.2.0.3.2, merge patch 13354057 for 11.2.0.2 available for certain platform.
2. Unpublished Bug 13066936  Instance does not register services when scan fails over (refer Note 13066936.8)

Solution:

1) For both above bugs, the workaround is to unregister and register remote listener on the database instance which does
   not register to a SCAN listener with following steps.

show parameter remote_listener
alter system set remote_listener='';
alter system register;
alter system set remote_listener=':';
alter system register;

2) Other points to check if service is not registered with SCAN listener:
a. remote_listener and local_listener is defined correctly
b. EZCONNECT is defined in sqlnet.ora, eg: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
c. SCAN name is defined in /etc/hosts or DNS and whether there is any mismatch if it is defined in both places
d. nslookup should display SCAN VIP in round-robin fashion
e. do not set SECURE_REGITER_ in listener.ora if the class of secure transports (COST) is not configured.

--//按照链接的介绍11.2.0.4版本已经解决了这个问题.不知道为什么.按照介绍执行如下:

--//原来执行的:
--//ALTER SYSTEM SET service_names='oims','aaaemr','zzzz','aaa430','aaahis'  SCOPE=MEMORY SID='zzzz2';

--//还原:
--//ALTER SYSTEM SET service_names='aaa430','zzzz','aaahis' SCOPE=MEMORY SID='zzzz1';
--//ALTER SYSTEM SET service_names='oims','aaaemr','zzzz'   SCOPE=MEMORY SID='zzzz2';

col remote_val new_value remote_val
select value remote_val from v$parameter where name='remote_listener';
alter system set remote_listener='';
alter system register;
--//host sleep 1
alter system set remote_listener='&remote_val';
alter system register;

4.测试:
--//执行前面的sql语句.测试OK,问题解决!!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2141511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2141511/