Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2824185
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2016-03-23 14:15:17

In this Document

Symptoms
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.

SYMPTOMS

Configed 3 node RAC and all instances are up and running, user can not connect using SCAN name, get ORA-12514:

node1:racr1:/home/oracle> sqlplus /@db_oltp
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 9 15:09:55 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

 
Both default LISTENER and SCAN LISTENER are running. All database instances are running.

In tnsnames.ora, service db_oltp is defined as:

DB_OLTP =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = scan-rac.sample.com)(PORT = 1521))
     (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = db_oltp)
    )
  )

 The database remote_listener is set correctly as:

remote_listener=scan-rac:1521

lsnrctl status listener_scan shows "The listener supports no services".

$ lsnrctl status listener_scan1 
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 9-MAY-2012 15:19:55

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                9-MAY-2012 15:19:55
Uptime                    0 days 4 hr. 0 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/112/grid/network/admin/listener.ora
Listener Log File         /u01/112/grid/log/diag/tnslsnr/rachost1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=1521)))
The listener supports no services
The command completed successfully

  
NAMES.DIRECTORY_PATH is either not set in sqlnet.ora or set with EZCONNECT included.


CAUSE

The problem is that :  is wrongly defined in RDBMS ORACLE_HOME/network/admin/tnsnames.ora or $TNS_ADMIN/network/admin/tnsnames.ora with the following content:

SCAN-RAC:1521 =
 (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node1vip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = node2vip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = node3vip)(PORT = 1521))
)


This causes  scan-rac:1521 to be translated to host vips and their port, instead of scan listener. All instances are registered on the default listener which listens on host VIP as remote server rather than on the scan listener. Hence SCAN listeners have no service registered and client connection using the SCAN name and port will fail with ORA-12514 / TNS-12514.

This can happen for EBS environment if AutoConfig has been run. 
Per Note 823587.1 Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 12 
"Known Issues:

2. Remove the ":" tns alias from the tnsnames.ora files manually after running AutoConfig."

SOLUTION

1. Remove : definition (eg: scan-rac:1521 in this example) from the tnsnames.ora under RDBMS ORACLE_HOME/network/admin or $TNS_ADMIN/network/admin directory

2. On any one of the RAC instance:

sqlplus / as sysdba
alter system set remote_listener='' scope=both sid='*';
alter system set remote_listener='scan-rac:1521' scope=both sid='*';
alter system register;

3. To verify remote service registration:

lsnrctl service Listener_SCAN

Services should now be registered with scan listeners and client connections should be successfully connecting to the SCAN name and port.

阅读(2834) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~