Re: Listener cannot read SERVICE_NAME in TNS-Descriptor
From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sun, 28 Dec 2003 07:14:44 -0800
Message-ID: <BrCHb.41616$BQ5.787_at_fed1read03>
>
>
>
> 1) Windows XP
> 2) OS version 2002
>
> 3a) contents of tnsnames.ora:
>
> # TNSNAMES.ORA Network Configuration File:
> C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
> # Generated by Oracle configuration tools.
>
> INST1_HTTP =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = SHARED)
> (SERVICE_NAME = MODOSE)
> (PRESENTATION = http://HRService)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )
>
> ORACLE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = oracle.localhost)
> )
> )
>
> MYDB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = mydb.localhost)
> )
> )
>
> 3b) contents of listener.ora:
>
> # LISTENER.ORA Network Configuration File:
> C:\oracle\ora92\network\admin\listener.ora
> # Generated by Oracle configuration tools.
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = C:\oracle\ora92)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = oracle.localhost)
> (ORACLE_HOME = C:\oracle\ora92)
> (SID_NAME = oracle)
> )
> )
>
>
> 4) ORA-12514 error: I doubleklick on the database icon of MYDB I just
> added to the tree to open it! There is a pre-configured database
> called "oracle" in the tree. When I click on this one it changes into
> "oracle - Axel as sysdba" and opens.
>
> 5) I run Oracle 9i on my laptop, so I am logged onto the database
> server system DIRECTLY. You say a Listener is NOT required to connect
> to a database directly. But why do I get bothered by this ORA-12514
> error ?
>
> 6) ping statistic for 172.0.0.1:
> response from 172.0.0.1: Bytes = 32 time <1ms TTL 128
> (4 times)
> package: sent = 4 got = 4 lost = 0
>
> 7)lsnrctl status produces these Services:
>
> -----------------------
>
> Dienst "PLSExtProc" ...
>
> Dienst "oracle.localhost" ...
>
> Dienst "oracleXDB.localhost" ...
>
> --------------------------
>
> obviously there is no service running for MYDB !?
>
>
> Thanks Axel
Date: Sun, 28 Dec 2003 07:14:44 -0800
Message-ID: <BrCHb.41616$BQ5.787_at_fed1read03>
Axel Dachtler wrote:
>>1) What Operating System (OS)? >>2) What OS version? >>3) What is contents of listener.ora file >>4) EXACTLY what are you doing to generate the ORA-12514 error? >>5) If you are logged onto the actual database server system DIRECTLY, >>then SQL*Net and Listener is NOT required to connect to the database. >>6) "ping axel-0560nntbn1" >>produces what output? >>(without double quote marks) >>7) lsnrctl status >>produces what output? >>8) while logged onto the database server >>what is produced when the following is entered at the command line? >>sqlplus >>/ as sysdba
>
>
>
> 1) Windows XP
> 2) OS version 2002
>
> 3a) contents of tnsnames.ora:
>
> # TNSNAMES.ORA Network Configuration File:
> C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
> # Generated by Oracle configuration tools.
>
> INST1_HTTP =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = SHARED)
> (SERVICE_NAME = MODOSE)
> (PRESENTATION = http://HRService)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )
>
> ORACLE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = oracle.localhost)
> )
> )
>
> MYDB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = mydb.localhost)
> )
> )
>
> 3b) contents of listener.ora:
>
> # LISTENER.ORA Network Configuration File:
> C:\oracle\ora92\network\admin\listener.ora
> # Generated by Oracle configuration tools.
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = axel-0560nntbn1)(PORT =
> 1521))
> )
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = C:\oracle\ora92)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = oracle.localhost)
> (ORACLE_HOME = C:\oracle\ora92)
> (SID_NAME = oracle)
> )
> )
>
>
> 4) ORA-12514 error: I doubleklick on the database icon of MYDB I just
> added to the tree to open it! There is a pre-configured database
> called "oracle" in the tree. When I click on this one it changes into
> "oracle - Axel as sysdba" and opens.
>
> 5) I run Oracle 9i on my laptop, so I am logged onto the database
> server system DIRECTLY. You say a Listener is NOT required to connect
> to a database directly. But why do I get bothered by this ORA-12514
> error ?
>
> 6) ping statistic for 172.0.0.1:
> response from 172.0.0.1: Bytes = 32 time <1ms TTL 128
> (4 times)
> package: sent = 4 got = 4 lost = 0
>
> 7)lsnrctl status produces these Services:
>
> -----------------------
>
> Dienst "PLSExtProc" ...
>
> Dienst "oracle.localhost" ...
>
> Dienst "oracleXDB.localhost" ...
>
> --------------------------
>
> obviously there is no service running for MYDB !?
>
>
> Thanks Axel
Upon further review & reading between the lines it might be the case that you think you added a new database/instance onto this system.
Somehow I serious doubt that by simply manipulating some silly GUI that you actually created a 2nd DB.
If you log into the "oracle" DB which seems to be OK,
please do the following:
SELECT FILE_NAME FROM DBA_DATA_FILES;
The result is a collection of files which comprise
the majority of this instance.
If, in fact, a 2nd DB exists on this system, you should be able to locate files with similar or identical name which reside in a different path where typically the pathname includes the SID.
My gut says that you do not have two databases on this system. Received on Sun Dec 28 2003 - 16:14:44 CET