In Oracle : Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> ORA-12514, but tnsping works

 

 ORA-12514, but tnsping works

 

 

TNSPING WORKS, but sqlplus cannot connect to the service

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

 

ORA-12514 after service creation with Entreprise Manager 11.2

oracle@linux1rac:/oracle/grid/11.2/bin>tnsping serviciu_creat_cata1

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 14-AUG-2010 18:44:36

Copyright (c) 1997, 2009, Oracle. All rights reserved.


Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan.mfol.ist.ro)(PORT = 1521)) (LOAD_BALANCE = YES) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = serviciu_creat_cata1) (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))
OK (0 msec)
oracle@linux1rac:/oracle/grid/11.2/bin>sqlplus scott/s     (here another service is used)

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 14 18:45:08 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options

SQL> conn scott/s@serviciu_creat_cata1
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Warning: You are no longer connected to ORACLE.


more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/db/11.2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.mfol.ist.ro)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST.mfol.ist.ro)
)
)

serviciu_creat_cata1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.mfol.ist.ro)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = serviciu_creat_cata1)
(FAILOVER_MODE =
(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
)
)
)

 

COMMENT: serviciu_creat_cata1 tnsnames.ora entry was created by the EM automatically.

 

Now, edit the tnsnames.ora on each node and add the domain to the SERVICE_NAME to have:

(SERVICE_NAME = serviciu_creat_cata1.mfol.ist.ro)

 

oracle@linux1rac:/home/oracle>sqlplus scott/s@serviciu_creat_cata1;

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 14 18:58:21 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options

SQL>
 

However, "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" could have a lot of causes

 

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.

 

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

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> ORA-12514, but tnsping works

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.