Home » RDBMS Server » Networking and Gateways » ORA-12514 error
ORA-12514 error [message #218287] Wed, 07 February 2007 10:53 Go to next message
ml73
Messages: 4
Registered: February 2007
Junior Member
Hello,

I have a problem with the TNS-listener. If I try to start sqlplus at the local system, where my Oracle-database is running with system/password then it comes up correctly. But if I enter system/password@compdb then it fails with
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have created the database 'compdb' and I can't find any errors in the tnsnames.ora. Also I have checked the service_names variable with "show parameter service_names", what is showing 'compdb'. A tnsping (on local system) on compdb works fine, but an compdb.ipm-srv-database-01.localdomain fails. DNS is working correctly and also a normal ping on ipm-srv-database-01.localdomain works correct.
I don't know what is going wrong here. Could anyone help me for this problem ?

OS: Linux Fedora core 6
Database: Oracle 10g (10.2.0.1.0)

content of tnsnames.ora:
COMPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ipm-srv-database-01.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = compdb)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

sqlnet.ora:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


Environment valiable ORACLE_SID=compdb


Greetings

Martin
Re: ORA-12514 error [message #218296 is a reply to message #218287] Wed, 07 February 2007 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Try changing
> (SERVICE_NAME = compdb)
to
(SID = compdb)

Also show results from
$ lsnrctl status
Re: ORA-12514 error [message #218373 is a reply to message #218287] Wed, 07 February 2007 22:42 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
And also post here your LISTENER.ORA or LSNRCLT SERVICES



regards
Taj
Re: ORA-12514 error [message #218515 is a reply to message #218373] Thu, 08 February 2007 09:10 Go to previous messageGo to next message
ml73
Messages: 4
Registered: February 2007
Junior Member
Hi,

My listener.ora contains following:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ipm-srv-database-01.localdomain)(PORT = 1521))
)
)


regards
Martin
Re: ORA-12514 error [message #218524 is a reply to message #218515] Thu, 08 February 2007 09:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
edit your listener.ora file like below
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
 (PROGRAM = extproc)
(SID_DESC =
 (SID_NAME = compdb)
 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
 )




regards
Taj
Re: ORA-12514 error [message #218525 is a reply to message #218296] Thu, 08 February 2007 09:40 Go to previous messageGo to next message
ml73
Messages: 4
Registered: February 2007
Junior Member
Hello anacedent,

Thank you very much for your answer. To exchange 'SERVICE_NAME' with 'SID' is really working on the server, but I don't unterstand what is the reason.
Additionally I have installed sqlplus as instant client on a client machine and then I have copied all three *.ora files from the server to the client. On the client is the same problem with the ORA-12514 error, also with the modified tnsnet.ora (SERVICE_NAME <-> SID). All required envionment variables are set, DNS is working and firewalls are turned off on both sides. I can't find the cause of the problem.


regards
Martin
Re: ORA-12514 error [message #218531 is a reply to message #218525] Thu, 08 February 2007 10:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In this case, since you are using the default port then the database should auto-register with the listener and adding an entry for COMPDB to the listener isn't required. As user52 suggested, can post the output of lsnrctl services?
Re: ORA-12514 error [message #218534 is a reply to message #218525] Thu, 08 February 2007 10:09 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
but I don't unterstand what is the reason.
Reason is because ORACLE_SID <> SERVICE_NAME.
consider
My ORACLE_SID = db01
My SERVICE_NAMES = taj

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
db01

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      Taj
SQL>

Take a look on my tnsnames.ora file
SQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network
\admin\tnsnames.ora
# Generated by Oracle configuration tools.

TAJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = alwarid-taj.fakhruddin.local)(PORT = 1521
))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      [B](SID = Taj)[/B]
    )
  )
SQL> conn system/oracle@taj
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> --edit my tnsnames.ora file from SID to SERVICE_NAME.
SQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network
\admin\tnsnames.ora
# Generated by Oracle configuration tools.

TAJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = alwarid-taj.fakhruddin.local)(PORT = 1521
))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      [B](SERVICE_NAME = Taj)[/B]
    )
  )

SQL> conn system/oracle@taj
Connected.




regards
Taj

[Updated on: Thu, 08 February 2007 10:14]

Report message to a moderator

Re: ORA-12514 error [message #218538 is a reply to message #218531] Thu, 08 February 2007 10:20 Go to previous message
ml73
Messages: 4
Registered: February 2007
Junior Member
Hi,

This is the output of lsnrctl services

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "compdb.ipm-srv-database-01.localdomain" has 1 instance(s).
Instance "compdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:159 refused:0 state:ready
LOCAL SERVER
Service "compdbXDB.ipm-srv-database-01.localdomain" has 1 instance(s).
Instance "compdb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ipm-srv-database-01.localdomain, pid: 16110>
(ADDRESS=(PROTOCOL=tcp)(HOST=ipm-srv-database-01.localdomain)(PORT=49168))
Service "compdb_XPT.ipm-srv-database-01.localdomain" has 1 instance(s).
Instance "compdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:159 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Previous Topic: Oracle XE 10g Portal
Next Topic: Oracle on Solaris: Named pipes(nmp) protocol adapter
Goto Forum:
  


Current Time: Tue Dec 06 08:28:44 CST 2016

Total time taken to generate the page: 0.10079 seconds