Re: ORA-12514 Listener can't resolve SERVICE_NAME

"Cold Cut" <jeffc_at_sympatico.ca> wrote in message news:vVv39.1619$Xf7.377301_at_news20.bellglobal.com...
> I am pulling my hair out with an ORA-12514 error on WindowsXP and an
> Oracle 8.1.6 database/listener. I also have a Linux 8.1.6 database on
> the network that connects just fine. I have been able to connect to
> my XP system for a while now and I haven't made any changes. I just
> used RMAN to backup the linux database in the XP recovery cataglog a
> few days ago.
>
> I have done a lot of reading and I know that my problem is somewhere
> in the TNSNAMES.ORA, SQLNET.ORA, and LISTENER.ORA files. I just can't
> figure out where. When ever I try to connect to XP1 I get the
> ORA-12514 error. I can connect to LIN1 just fine. I am trying to
> connect to XP1 from the server PC itself.
>
> Here is the TNSNAMES.ORA file (names changed for security of course);
>
> # TNSNAMES.ORA Network Configuration File:
> C:\Oracle\Ora81\network\admin\tnsnames.ora
> # Generated by Oracle configuration tools.
>
> XP1 =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = GEORGE)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = XP1)
> )
> )
>
> LIN1 =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = FRED)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = LIN1)
> )
> )
>
> The LISTENER.ORA file;
> # LISTENER.ORA Network Configuration File:
> C:\Oracle\Ora81\NETWORK\ADMIN\listener.ora
> # Generated by Oracle configuration tools.
>
> XPLISTEN =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = george)(PORT = 1521))
> )
>
> SID_LIST_XPLISTEN =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = XP1TEST.GEORGE)
> (ORACLE_HOME = C:\Oracle\Ora81)
> (PROGRAM = extproc)
> )
> )
>

Here's your problem. You've specified the Listener should be listening for the XP1 instance, but have remained mysteriously silent on the LIN1 one. The SID_LIST section is where the trouble starts.

Now that wouldn't necessarily be a problem, providing your LIN1 instance knows to register itself automatically with this Listener. But it doesn't. Automatic registration works out of the box when you have a Listener of a default name, listening on TCP/IP, on port 1521. Your Listener doesn't have a default name (which is 'LISTENER' by the way).

With non-default Listeners, you have to ARRANGE for the instances to register themselves. That means the LIN1 instance has to be told where to find this XPLISTEN listener. That requires the 'local_listener' init.ora parameter to be set, AND for there to be a copy of tnsnames.ora on the Linux box itself so that the parameter can be resolved. In other words, local_listener takes a tnsnames.ora argument (since all tnsnames does is resolve aliases to listener addresses).

In your case, that would mean making sure the tnsnames you provided above is sitting in the network/admin directory on the Linux box, setting local_listener=LIN1, and re-starting that instance.

Then LIN1 will register with the listener, and connections will be possible.

Alternatively, rename your listener to LISTENER, delete the SID_LIST section of the listener.ora entirely, and get both instances to register themselves with the default listener (and, incidentally, DON'T set the local_listener parameter).

By the way, at this stage, it's got nothing whatsoever to do with your 'service_name' parameter.

Regards
HJR
> The SQLNET.ORA file
> # SQLNET.ORA Network Configuration File:
> C:\Oracle\Ora81\network\admin\sqlnet.ora
> # Generated by Oracle configuration tools.
>
> SQLNET.AUTHENTICATION_SERVICES= (NONE)
>
> NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
>
> It's probably staring me right in the face, I just can't see it. Any
> help is appreciated.
Received on Mon Aug 05 2002 - 14:55:42 CDT