| ORA-12514 error [message #218287] |
Wed, 07 February 2007 10:53  |
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 #218515 is a reply to message #218373] |
Thu, 08 February 2007 09:10  |
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 #218525 is a reply to message #218296] |
Thu, 08 February 2007 09:40 |
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 #218534 is a reply to message #218525] |
Thu, 08 February 2007 10:09 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
